07:54
After installing and configuring Tivoli Storage Manager 6.1, you should seldom need to issue DB2 commands. This document briefly describes a limited set of DB2 commands that you might have reason to use.
The commands are grouped in the following three categories:
The commands are grouped in the following three categories:
- Setup, configure, or update the database environment
- Diagnosis and troubleshoot the database environment
- Monitor the status and health of the database
Setup, configure, or update the database environment
The commands in this section are used to set up, configure, or update the Tivoli Storage Manager DB2 database environment. These commands are issued by the Tivoli Storage Manager configuration wizard or by the server itself. These are provided for reference or informational purposes.
| Command and description | Examples |
| db2cmd - Open DB2 command window command Opens the CLP-enabled DB2 window, and initializes the DB2 command line environment. This command is only available on Windows operating systems. | Open the DB2 command window. db2cmd |
| db2icrt - Create instance command On Linux and UNIX operating systems, this utility is located in the DB2DIR/instance directory, where DB2DIR represents the installation location where the current version of the DB2 database system is installed. On Windows operating systems, this utility is located under the DB2PATH\bin directory where DB2PATH is the location where the DB2 copy is installed. The db2icrt command creates DB2 instances in the home directory of the instance owner. Note: The Tivoli Storage Manager configuration wizard creates the instance used by the server and database. After a server is installed and configured, the db2icrt command would not typically be used. | Create a Tivoli Storage Manager instance. # /opt/tivoli/Tivoli Storage Manager/db2/instance/db2icrt -a SERVER -u tsminst1 tsminst |
| db2osconf - Utility for kernel parameter values command Makes recommendations for kernel parameter values based on the size of a system. The recommended values are high enough for a given system that they can accommodate most reasonable workloads. This command is currently available only for DB2 on HP-UX on 64-bit instances and the Solaris operating system. It is recommended that the Tivoli Storage Manager server environment use these db2osconf values as the minimum setting for those values. It can also be beneficial to exceed the recommended value. Consideration should be given to this information and the kernel settings that are recommended to be changed. These settings can significantly affect normal operations and how well the server performs in this environment. Not following the kernel setting recommendations could result in an unstable or under performing Tivoli Storage Manager server for this environment. | Retrieve a list of currently supported options. db2osconf |
| db2set - DB2 profile registry command Displays, sets, or removes DB2 profile variables. | Before you issue the DSMSERV LOADFORMAT command, log on to the system as the server instance owner. If you are not running under the English regional locale, set the DB2CODEPAGE system environment variable to 819. db2set -i tsminst1 DB2CODEPAGE=819 Set the DSMI_ api environment variable configuration for the database instance. db2set -i server1 DB2_VENDOR_INI=d:\tsmserver1\tsmdbmgr.env |
| CATALOG DATABASE command Stores database location information in the system database directory. The database can be located either on the local workstation or on a remote database partition server. The server configuration wizard typically takes care of any catalog needed for using the server database. This command would only need to be considered or run manually after a server has been configured and running if something in the environment changes or is damaged. | Catalog the database. db2 catalog database tsmdb1 |
| CREATE DATABASE command Initializes a new database with an optional user-defined collating sequence, creates the three initial table spaces, creates the system tables, and allocates the recovery log file. This command is used only to create the Tivoli Data Warehouse database for Tivoli Storage Manager reporting and monitoring and not the database for the Tivoli Storage Manager server. When you initialize a new database, the AUTOCONFIGURE command is issued by default. Note: When the instance and database directories are created by the DB2 database manager, the permissions are accurate and should not be changed. | Create the DB2 WAREHOUS database. db2 create database WAREHOUS using codeset utf-8 territory US |
| DROP DATABASE command Deletes the database contents and all log files for the database, uncatalogs the database, and deletes the database subdirectory. Attention: This command causes the database to be dropped, which removes the references to the database from DB2 and deletes the underlying space used for the database. The drop database command should not be used for a production Tivoli Storage Manager server or a server that you want to continue to use. Dropping a database renders it unusable and unrecoverable. If a database is inadvertently dropped, the only recourse is to restore the database from an available database backup if one exists. | Drop the database: db2 drop db tsmdb1 |
| GET DATABASE CONFIGURATION command Returns the values of individual entries in a specific database configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Display the configuration information for a database alias: db2 get db cfg for tsmdb1 Get information in order to verify database configuration and settings, log mode, maintenance settings, and so on. db2 get db config for tsmdb1 show detail |
| GET DATABASE MANAGER CONFIGURATION command Returns the values of individual entries in the database manager configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Get configuration information for the database manager. db2 get dbm cfg |
| GET HEALTH SNAPSHOT command Retrieves the health status information for the database manager and its databases. The information returned represents a snapshot of the health state at the time the command was issued. Tivoli Storage Manager monitors the state of the database using the health snapshot and other mechanisms that are provided by DB2. There might be cases where the health snapshot or other DB2 documentation indicates that an item or database resource might be in an alert state, indicating that action should be considered to remedy the situation. Tivoli Storage Manager monitors the condition and takes action as appropriate. Not all declared alerts by the DB2 database are acted on. | Report on db2 health monitor indicators. db2 get health snapshot for database on tsmdb1 |
| GRANT (Database Authorities) statement Grants authorities that apply to the entire database rather than privileges that apply to specific objects within the database. | Grant access to the user ID itmuser. db2 GRANT CONNECT ON DATABASE TO USER itmuser db2 GRANT CREATETAB ON DATABASE TO USER itmuser |
| RUNSTATS command Updates statistics about the characteristics of a table and associated indexes or statistical views. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data including the most efficient means to process the data and whether or not to exploit an index and such for the operation. For a table, this utility should be called when the table has had many updates, or after reorganizing the table. For a statistical view, this utility should be called when changes to underlying tables have substantially affected the rows returned by the view. The view must have been previously enabled for use in query optimization using the ALTER VIEW command. Note: The Tivoli Storage Manager server has a monitor and tuning algorithm that evaluates the workload and changes against the server's tables. As needed it invokes RUNSTATS for a table to update the statistics as necessary. If issues arise with how this monitoring and tuning algorithm work, IBM might recommend manually performing RUNSTATS for one or more tables. | Update statistics on a single table. db2 runstats on table SCHEMA_NAME.TABLE_NAME with distribution and sampled detailed indexes all |
| START DATABASE MANAGER command Starts the current database manager instance background processes on a single database partition or on all the database partitions defined in a multi-partitioned database environment. The Tivoli Storage Manager server starts and stops the instance and database whenever the server starts and halts. While the server is running, a db2start is not needed or recommended. Similarly, while the server is running, stopping the database might adversely affect the server including causing current workloads and activity to fail or possibly causing the server to crash. It is important to allow the Tivoli Storage Manager server to manage the starting and stopping of the instance and database. | Start the database manager. db2start |
| STOP DATABASE MANAGER command Stops the current database manager instance. Unless explicitly stopped, the database manager continues to be active. This command does not stop the database manager instance if any applications are connected to databases. If there are no database connections, but there are instance attachments, it forces the instance attachments and stops the database manager. This command also deactivates any outstanding database activations before stopping the database manager. In a partitioned database environment, this command stops the current database manager instance on a database partition or on all database partitions. When it stops the database manager on all database partitions, it uses the db2nodes.cfg configuration file to obtain information about each database partition. This command can also be used to drop a database partition from the db2nodes.cfg file (partitioned database environments only). This command is not valid on a client. The Tivoli Storage Manager server starts and stops the instance and database whenever the server starts and halts. While the server is running, a db2start is not needed or recommended. Similarly, while the server is running, stopping the database might adversely affect the server including causing current workloads and activity to fail or possibly causing the server to crash. It is important to allow the Tivoli Storage Manager server to manage the starting and stopping of the instance and database. | Stop the database manager. db2 stop dbm |
| set db2instance (Windows only) Determines which instance applies to the current session (on supported Windows platforms). | set db2instance=tsminst1 |
Diagnosis and troubleshoot the database environment
Commands used to diagnosis or troubleshoot issues with the Tivoli Storage Manager servers' DB2 database environment. These are commands that might be requested by an IBM service representative for the purpose of evaluating or diagnosing an issue or problem with the Tivoli Storage Manager server.
| Command and description | Examples |
| db2cmd - Open DB2 command window command Opens the CLP-enabled DB2 window, and initializes the DB2 command line environment. This command is only available on Windows operating systems. | Open the DB2 command window. db2cmd |
| db2ilist - List instances command Lists all the instances that are created using the db2icrt command from the same DB2 copy location that you are running the db2ilist command. This command can be useful for diagnosing problems or for reporting information about the Tivoli Storage Manager database configuration. | List current server instances before uninstalling. #/opt/tivoli/tsm/db2/instance/db2ilist |
| db2mtrk - Memory tracker command Provides a complete report of memory status for instances, databases, agents, and applications. This command outputs the following memory pool allocation information: # Current size Maximum size (hard limit) # Largest size (high water mark) # Type (identifier indicating function for which memory will be used) # Agent who allocated pool (only if the pool is private) # Application The same information is also available from the Snapshot monitor. Use this command and other information available directly from the Tivoli Storage Manager server to diagnose memory and performance related issues. An IBM service representative might request that this command be run. | The following call returns database and instance normal values and repeats every 10 seconds: db2mtrk -i -d -v -r 10 |
| db2osconf - Utility for kernel parameter values command Makes recommendations for kernel parameter values based on the size of a system. The recommended values are high enough for a given system that they can accommodate most reasonable workloads. This command is currently available only for DB2 on HP-UX on 64-bit instances and the Solaris operating system. It is recommended that the Tivoli Storage Manager server environment use these db2osconf values as the minimum setting for those values. It can also be beneficial to exceed the recommended value. Consideration should be given to this information and the kernel settings that are recommended to be changed. These settings can significantly affect normal operations and how well the server performs in this environment. Not following the kernel setting recommendations could result in an unstable or under performing Tivoli Storage Manager server for this environment. | Retrieve a list of currently supported options. db2osconf |
| db2pd - Monitor and troubleshoot DB2 database command The db2pd utility retrieves information from the DB2 database system memory sets. The -reorgs or some of the db2pd options can be effective for monitoring status of the server's database as well. | Display the current status of DB2 buffer pools. db2pd -d tsmdb1 -bufferpools Display a history of database reorganizations. db2pd -d tsmdb1 -reorgs |
| db2set - DB2 profile registry command Displays, sets, or removes DB2 profile variables. | Before you issue the DSMSERV LOADFORMAT command, log on to the system as the server instance owner. If you are not running under the English regional locale, set the DB2CODEPAGE system environment variable to 819. db2set -i tsminst1 DB2CODEPAGE=819 Set the DSMI_ api environment variable configuration for the database instance. db2set -i server1 DB2_VENDOR_INI=d:\tsmserver1\tsmdbmgr.env |
| ATTACH command Enables an application to specify the instance at which instance-level commands are to be executed. This instance can be the current instance, another instance on the same workstation, or an instance on a remote workstation. Attach to an instance of the database manager. Note: This command must be run before the db2 get dbm cfg command | Attach to an instance of the database manager. db2 attach to tsminst1 |
| CATALOG DATABASE command Stores database location information in the system database directory. The database can be located either on the local workstation or on a remote database partition server. The server configuration wizard typically takes care of any catalog needed for using the server database. This command would only need to be considered or run manually after a server has been configured and running if something in the environment changes or is damaged. | Catalog the database. db2 catalog database tsmdb1 |
| DETACH command Removes the logical DBMS instance attachment, and terminates the physical communication connection if there are no other logical connections using this layer. | Detach from an instance. db2 detach |
| DROP DATABASE command Deletes the database contents and all log files for the database, uncatalogs the database, and deletes the database subdirectory. Attention: This command causes the database to be dropped, which removes the references to the database from DB2 and deletes the underlying space used for the database. The drop database command should not be used for a production Tivoli Storage Manager server or a server that you want to continue to use. Dropping a database renders it unusable and unrecoverable. If a database is inadvertently dropped, the only recourse is to restore the database from an available database backup if one exists. | Drop database tsmdb1. db2 drop db tsmdb1 |
| GET DATABASE CONFIGURATION command Returns the values of individual entries in a specific database configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Display the configuration information for a database alias: db2 get db cfg for tsmdb1 Get information in order to verify database configuration and settings, log mode, maintenance settings, and so on. db2 get db config for tsmdb1 show detail |
| GET DATABASE MANAGER CONFIGURATION command Returns the values of individual entries in the database manager configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Get configuration information for the database manager. db2 get dbm cfg |
| GET HEALTH SNAPSHOT command Retrieves the health status information for the database manager and its databases. The information returned represents a snapshot of the health state at the time the command was issued. Tivoli Storage Manager monitors the state of the database using the health snapshot and other mechanisms that are provided by DB2. There might be cases where the health snapshot or other DB2 documentation indicates that an item or database resource might be in an alert state, indicating that action should be considered to remedy the situation. Tivoli Storage Manager monitors the condition and takes action as appropriate. Not all declared alerts by the DB2 database are acted on. | Report on db2 health monitor indicators. db2 get health snapshot for database on tsmdb1 |
| GET SNAPSHOT command Collects status information and formats the output for the user. The information returned represents a snapshot of the database manager operational status at the time the command was issued. | List all connections to the database, including connection status and state information. db2 get snapshot for database on tsmdb1 applications Collect current statistics for database: memory used by various pools, database and log statistics, and connection statistics. db2 get snapshot for database on tsmdb1 Collect memory used by various pools and statistics for the state of the database manager. db2 get snapshot for dbm List locks in use for DB2. Correlates to information in the db2 get snapshot for locks on tsmdb1 command. db2 get snapshot for all |
| GRANT (Database Authorities) statement Grants authorities that apply to the entire database (rather than privileges that apply to specific objects within the database). | Grant access to the user ID itmuser. db2 GRANT CONNECT ON DATABASE TO USER itmuser db2 GRANT CREATETAB ON DATABASE TO USER itmuser |
| LIST DATABASE DIRECTORY command Lists the contents of the system database directory. If a path is specified, the contents of the local database directory are listed. | List the contents of the system database directory. db2 list database directory |
| LIST HISTORY command Lists entries in the history file. The history file contains a record of recovery and administrative events. Recovery events include full database and table space level backup, incremental backup, restore, and roll-forward operations. Additional logged events include create, alter, drop, or rename table space, reorganize table, drop table, and load. | Lists the history of database backups, reorganization activity, and so on. db2 list history dropped table all for db sample |
| RUNSTATS command Updates statistics about the characteristics of a table and associated indexes or statistical views. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data including the most efficient means to process the data and whether or not to exploit an index and such for the operation. For a table, this utility should be called when the table has had many updates, or after reorganizing the table. For a statistical view, this utility should be called when changes to underlying tables have substantially affected the rows returned by the view. The view must have been previously enabled for use in query optimization using the ALTER VIEW command. Note: The Tivoli Storage Manager server has a monitor and tuning algorithm that evaluates the workload and changes against the server's tables. As needed it invokes RUNSTATS for a table to update the statistics as necessary. If issues arise with how this monitoring and tuning algorithm work, IBM might recommend manually performing RUNSTATS for one or more tables. | Update statistics on a single table. db2 runstats on table SCHEMA_NAME.TABLE_NAME with distribution and sampled detailed indexes all |
| START DATABASE MANAGER command Starts the current database manager instance background processes on a single database partition or on all the database partitions defined in a multi-partitioned database environment. The Tivoli Storage Manager server starts and stops the instance and database whenever the server starts and halts. While the server is running, a db2start is not needed or recommended. Similarly, while the server is running, stopping the database might adversely affect the server including causing current workloads and activity to fail or possibly causing the server to crash. It is important to allow the Tivoli Storage Manager server to manage the starting and stopping of the instance and database. | Start the database manager. db2start |
| STOP DATABASE MANAGER command Stops the current database manager instance. Unless explicitly stopped, the database manager continues to be active. This command does not stop the database manager instance if any applications are connected to databases. If there are no database connections, but there are instance attachments, it forces the instance attachments and stops the database manager. This command also deactivates any outstanding database activations before stopping the database manager. In a partitioned database environment, this command stops the current database manager instance on a database partition or on all database partitions. When it stops the database manager on all database partitions, it uses the db2nodes.cfg configuration file to obtain information about each database partition. This command can also be used to drop a database partition from the db2nodes.cfg file (partitioned database environments only). This command is not valid on a client. The Tivoli Storage Manager server starts and stops the instance and database whenever the server starts and halts. While the server is running, a db2start is not needed or recommended. Similarly, while the server is running, stopping the database might adversely affect the server including causing current workloads and activity to fail or possibly causing the server to crash. It is important to allow the Tivoli Storage Manager server to manage the starting and stopping of the instance and database. | Stop the database manager. db2 stop dbm |
| UPDATE DATABASE MANAGER CONFIGURATION command Modifies individual entries in the database manager configuration file. A database configuration file resides on every database partition on which the database has been created. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and as way to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Display the DB2 log db2diag.log with a diagnostic level of 4. db2 update dbm configuration using diaglevel 4 Restart the instance for the change to take effect: db2stop db2start |
| db2 connect to This is an SQL statement. The Tivoli Storage Manager server as an application is using connections to and from the database extensively to perform normal operations as well administrative tasks. | db2 connect to tsmdb1 |
| db2 ? sqlxxxx This command displays the meaning of an sqlxxxx code. | Display the meaning of the SQL2033N code. C:\Program Files\IBM\SQLLIB\BIN>db2 ? sql2033 The sqlxxx codes are often displayed in Tivoli Storage Manager messages. The messages can include one or more reasons codes, their explanations, and suggested responses. |
| set db2instance (Windows only) Determines which instance applies to the current session (on supported Windows platforms). | set db2instance=tsminst1 |
Monitor the status and health of the database
Commands used to monitor the status and health of the Tivoli Storage Manager server's DB2 database environment.
| Command and description | Examples |
| db2cmd - Open DB2 command window command Opens the CLP-enabled DB2 window, and initializes the DB2 command line environment. This command is only available on Windows operating systems. | Open the DB2 command window. db2cmd |
| db2pd - Monitor and troubleshoot DB2 database command The db2pd utility retrieves information from the DB2 database system memory sets. The -reorgs or some of the db2pd options can be effective for monitoring status of the server's database as well. | Display the current status of DB2 buffer pools. db2pd -d tsmdb1 -bufferpools Display a history of database reorganizations. db2pd -d tsmdb1 -reorgs |
| GET DATABASE CONFIGURATION command Returns the values of individual entries in a specific database configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Display the configuration information for a database alias: db2 get db cfg for tsmdb1 Get information in order to verify database configuration and settings, log mode, maintenance settings, and so on. db2 get db config for tsmdb1 show detail |
| GET DATABASE MANAGER CONFIGURATION command Returns the values of individual entries in the database manager configuration file. Note: The database manager and database configuration parameters are typically set and managed directly by DB2. They are listed here for informational purposes and a means to view the existing settings. Changing these settings should only be done through the use of Tivoli Storage Manager server commands or procedures. Changing these settings might be recommended by IBM service or through service bulletins such as APARs or Technical Guidance documents (technotes). These settings should not be changed manually and should only be changed at the direction of IBM. | Get configuration information for the database manager. db2 get dbm cfg |
| GET HEALTH SNAPSHOT command Retrieves the health status information for the database manager and its databases. The information returned represents a snapshot of the health state at the time the command was issued. Tivoli Storage Manager monitors the state of the database using the health snapshot and other mechanisms that are provided by DB2. There might be cases where the health snapshot or other DB2 documentation indicates that an item or database resource might be in an alert state, indicating that action should be considered to remedy the situation. Tivoli Storage Manager monitors the condition and takes action as appropriate. Not all declared alerts by the DB2 database are acted on. | Report on db2 health monitor indicators. db2 get health snapshot for database on tsmdb1 |
| GET SNAPSHOT command Collects status information and formats the output for the user. The information returned represents a snapshot of the database manager operational status at the time the command was issued. | List all connections to the database, including connection status and state information. db2 get snapshot for database on tsmdb1 applications Collect current database statistics: memory used by pools, database and log statistics, and connection statistics. db2 get snapshot for database on tsmdb1 Collect memory used by various pools and statistics for the state of the database manager. db2 get snapshot for dbm List locks in use for DB2. Correlates to information in the db2 get snapshot for all applicationscommand. db2 get snapshot for locks on tsmdb1 |
| LIST HISTORY command Lists entries in the history file. The history file contains a record of recovery and administrative events. Recovery events include full database and table space level backup, incremental backup, restore, and roll-forward operations. Additional logged events include create, alter, drop, or rename table space, reorganize table, drop table, and load. | List the history of database backups, reorganization activity, and so on. db2 list history dropped table all for db sample |
| RUNSTATS command Updates statistics about the characteristics of a table and associated indexes or statistical views. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data including the most efficient means to process the data and whether or not to exploit an index and such for the operation. For a table, this utility should be called when the table has had many updates, or after reorganizing the table. For a statistical view, this utility should be called when changes to underlying tables have substantially affected the rows returned by the view. The view must have been previously enabled for use in query optimization using the ALTER VIEW command. Note: The Tivoli Storage Manager server has a monitor and tuning algorithm that evaluates the workload and changes against the server's tables. As needed it invokes RUNSTATS for a table to update the statistics as necessary. If issues arise with how this monitoring and tuning algorithm work, IBM might recommend manually performing RUNSTATS for one or more tables. | Update statistics on a single table. db2 runstats on table SCHEMA_NAME.TABLE_NAME with distribution and sampled detailed indexes all |
| db2 connect to This is an SQL statement. The Tivoli Storage Manager server as an application uses connections to and from the database extensively to perform normal operations and administrative tasks. | db2 connect to tsmdb1 |
Source:From IBM Link.
No comments
Post a Comment