verifydb Command--Clean Up Databases
The verifydb command performs clean up operations on one or more databases in an installation.
The command can do the following:
• Delete unneeded disk files in a database directory
• Delete temporary and expired tables
• Remove all references to a specified table from the DBMS system catalogs
This command requires exclusive access to databases. Verify that there are no active sessions in the DBMS before continuing. If users are connected to the database, a runtime error is displayed. Before using this command, shut down the rmcmd process, which maintains a database connection.
Note: The verifydb command cannot be used in the read-only database.
Verifydb logs all of its actions to the terminal screen. It also logs to a verify log file, unless the -n (nologging) flag is used. The default log file is iivdb.log and is used unless another name is specified with the -lf option.
Note: Verifydb always outputs the log file to the II_CONFIG location. If II_CONFIG is not defined, it outputs to location: II_SYSTEM/ingres/files.
If the log file does not exist when you execute verifydb, the system creates it. If it does exist, verifydb appends to it. Since this file grows each time you execute verifydb with this log file, you should delete it occasionally to save disk space.
The verifydb command has the following format:
verifydb -mmode -sscope -ooperation [-n | -lflogfilename] [-v] [-uusername]
-mmode
Specifies the mode in which verifydb executes. The mode can be one of the following:
report
Directs verifydb to log its findings. Use report mode if you want verifydb to only log, rather than actually delete, the tables or files that it finds.
run
Directs verifydb to perform the specified operation and log all actions that it performs.
runinteractive
Directs verifydb to prompt the user for confirmation before each action is taken. If the user responds negatively to a prompt, verifydb skips that action and goes on to the next.
runsilent
Tells verifydb to perform the specified operations but turns off the logging to the terminal. (Logging to the log file continues.)
-sscope
Tells verifydb to perform the operation only on the specified databases. All databases must have the same owner. You can specify up to ten databases.
Scope can be any of the following:
dbname “dbname |vnode::dbname[/server_class]{dbname |vnode::dbname[/server_class]}”
dba
Directs verifydb to operate on all databases for which the user is the DBA or for all databases owned by the DBA specified by the -u flag.
installation
Directs verifydb to operate on all operative databases. You must be a privileged user to use this qualifier.
-ooperation
Specifies the operation to be performed. If report mode is specified, the files or tables found are not actually deleted, but only logged. The options for operation are:
accesscheck
Checks each database specified by the scope and returns a message that says whether the server can connect to the database and, if not, the reason. When using this option, you must also specify report mode (‑mreport).
You must be either a DBA or a privileged user to use this option. If you are a DBA and specify a scope of dbname, you must be the DBA of all the listed databases. If you use a scope of dba, verifydb checks all the databases for which you are the DBA. If you use a scope of installation, you must be a privileged user, and accesscheck checks all databases in the installation.
Additionally, if you are a privileged user, you can use the -u flag to run this option as another user.
purge
Directs verifydb to delete all disk files in the database directory that are no longer required. This operation is a combination of temp_purge and expired_purge.
temp_purge
Tells verifydb to search for and delete all temporary tables from the database.
expired_purge
Directs verifydb to search for and delete all expired tables from the database.
Note: The expired_purge operation does not require exclusive access to the database, unlike other verifydb operations.
drop_table “tablename”
Tells verifydb to remove all references to a specified table from the DBMS system catalogs. If you specify this option, you must use the dbname option for the -s flag.
table “tablename”
Checks the specified tables and reports any inconsistencies found, making recommendations to repair those inconsistencies. The table operation cannot be used on core system catalogs. Secondary indexes can be checked but cannot be repaired. A table lock is taken during verifydb table operations, but a database lock is not taken. Use this option only when you are using report mode (-mreport).
This operation also verifies referential integrity between the internal pointers for long data types stored in base table records and the extension table records they point to. Any inconsistencies are reported.
Caution! Using this option when you are in any run mode is not supported unless you are receiving assistance from customer support and are advised to do so; it can have severe, unexpected results.
alltables
Checks all tables for all users in the specified database and reports any inconsistencies found, making recommendations to repair those inconsistencies. If the -u'username' option is used, output is limited to tables owned by the specified user. Use this option only when you are using report mode (-mreport).
xtable “tablename”
Functions like the table option, however xtable uses a stricter patch algorithm, which guarantees data integrity—with the risk that some valid data may be discarded. Use this option only when you are using report mode (-mreport).
Caution! Using this option when you are in any run mode is not supported unless you are receiving assistance from customer support and are advised to do so; it can have severe, unexpected results.
dbms_catalogs
Checks the dbms catalogs and reports any inconsistencies found, making recommendations to repair those inconsistencies. Use this option only when you are using report mode (‑mreport).
Caution! Using this option when you are in runinteractive mode is not supported unless you are receiving assistance from customer support and are advised to do so; it can have severe, unexpected results. This operation is not supported in run modes other than runinteractive.
force_consistent
Permits entry into a database that is inconsistent. This does not fix the problem with the database; it merely allows you to force the database to act as if it were in a consistent state. This can be very dangerous if used against a production database. Hidden data damage may render one or more tables in the database unrecoverable at some time in the future. Use this option only when you are using report mode (‑mreport).
Caution! Using this option when you are in any run mode is not supported unless you are receiving assistance from customer support and are advised to do so; it can have severe, unexpected results.
refresh_ldbs
Directs verifydb to assure that a distributed database correctly reflects the release level of all remote databases that contain objects registered to the distributed database. It is recommended that you run this operation on a distributed database after you run upgradedb on any of the remote databases accessed by the distributed database. For additional information on using this parameter, see the Star User Guide.
The distributed databases are specified by the -sscope parameter. For the refresh_ldbs option only, verifydb skips all non-distributed databases and processes only distributed databases. (In all other cases, verifydb processes only non-distributed databases.)
-n
Turns off logging to the log file (nolog mode). Logging to the terminal continues.
-lflogfilename
Specifies an alternate log file (in the II_CONFIG location) to which verifydb is to log activity. When using this flag, the -n flag is not permitted.
-v
Provides additional dialog messages when performing the verifydb operation (verbose mode). This flag applies only for table operations.
-uusername