Tools in db2

# Db2move
Db2move is a data movement tool that can be used to move a large number of tables between DB2 databases. The commands support EXPORT, IMPORT, and LOAD.
The syntax of db2move can be as simple as below:
Db2move database_name action options
This tool extracts the user table list from the system directory table, and then exports each table in PC/IXF format. Then, you can import or load these PC/IXF files to another
DB2 database.
The following are some examples. The following command imports all tables in the sample database using the specified user ID and password in REPLACE mode:
Db2move sample import-io replace-u userid-p password
The following command loads all tables in db2admin and db2user modes in REPLACE mode:
Db2move sample load-sn db2admin, db2user-lo REPLACE
# Db2look is a convenient tool that can be called from a command prompt or control center. This tool can:
Extract database Definition Language (DDL) statements from database objects
Generate an UPDATE statement to UPDATE the database manager and database configuration parameters.
Generate the db2set command to set the DB2 profile Registry
Extract and generate database statistics reports
Generate an UPDATE statement to copy statistics about database objects.
Utilities similar to LOAD require that the target table exist. You can use db2look to extract the table DDL, run the DLL on the target database, and then call the load operation. As shown below:
The example demonstrates that db2look is very convenient to use. The following command generates DDL statements for all objects created by peter (from the database department,
At the same time, the output is stored in alltables. SQL.

Db2look-d department-u peter-e-o alltables. SQL

The following command is generated:
DLL of all objects in the database department (specified by options-d,-a, and-e ).
Copy the UPDATE statement for statistics of all tables and indexes in the database (specified by option-m ).
GRANT authorization Statement (specified by option-x ).
The UPDATE statement used for the database manager and database configuration parameters, and the db2set Command Used for the profile Registry (specified by option-f ).
Db2look-d department-a-e-m-x-f-o db2look. SQL
# RUNSTATS Utility
DB2 uses a comprehensive cost-based optimizer to determine how data is accessed. The decision-making is largely influenced by some statistical information about the database.
The size of the table and index. Therefore, it is important to keep the database statistics up-to-date so that an effective data access solution can be selected. RUNSTATS Utility
The sequence is used to update the statistical information of the physical features and related indexes of a table. These features include the number of records (base), the number of pages, and the average length of records.
Let's use some examples to demonstrate the usage of this utility. The following command collects statistics of table db2user. employee. Allow access by the reader and recorder During calculation of statistics
Ask this table:
Runstats on table db2user. employee ALLOW WRITE ACCESS
The following command uses distributed statistics to collect statistics of the table db2user. employee and the columns empid and empname. During this command, the table can only be used for read-only requests.
Runstats on table db2user. employee with distribution on columns (empid, empname)
ALLOW READ ACCESS
The following command collects the statistical information of the table db2user. employee and the detailed statistics of all its indexes:
Runstats on table db2user. employee AND DETAILED INDEXES ALL
# REORG and REORGCHK utilities
The data added or deleted from the database may not be physically placed in order. In this case, DB2 must perform additional read operations to access data. Generally, this means that more
Disk I/O operations, and we all know that such operations are expensive. In this case, you should consider physically reorganizing the table based on the index
I/O operations are reduced as much as possible.
REORG is a utility for reorganizing data for tables and/or indexes. Although the data is physically rescheduled, DB2 provides the option to perform this operation online or offline.
By default, offline REORG allows other users to read the table. You can restrict table ACCESS by specifying the allow no access option. Online REORG (also called on-site REORG)
Read or write access to a table is not supported. Because data pages are rescheduled, concurrent applications may have to wait for REORG to complete the current page. You can use the appropriate options to easily
To stop, pause, or restart the reorganization process:
The following examples are very easy to understand:
Reorg table db2user. employee INDEX db2user. idxemp INPLACE ALLOW WRITE ACCESS
Reorg table db2user. employee INDEX db2user. idxemp INPLACE PAUSE
REORGCHK is another data maintenance utility that has an option for retrieving the current database statistics or updating the database statistics. It also generates a REORG
Indicator statistics report. REORGCHK uses an asterisk (*) to mark a table or index where REORG is needed based on statistical rules.
Let's consider some examples. The following command generates a report on the current statistics (about all tables owned by the runtime authorization identity:
REORGCHK CURRENT STATISTICS ON TABLE USER
The following command updates statistics and generates a report for all tables created in smith mode:
Reorgchk update statistics on schema smith
# REBIND utility and flush package cache command
Before executing database applications or any SQL statements, DB2 must pre-compile them and generate a package. A package is a database object containing applications.
Compiled SQL statements used in the source file. DB2 uses this package to access data referenced in SQL statements. How does the DB2 optimizer select a data access solution for these packages? It depends on
Database statistics when the package is created.
For static SQL statements, the packages are created at compilation and bound to the database. If the statistical information is updated to reflect the characteristics of the physical database, the existing
. The REBIND utility allows you to recreate a package so that you can take advantage of the current database statistics. The command is very simple:
Rebind package package_name

However, if you want to change the application source code, you must explicitly Delete the existing related packages and recreate the packages. The REBIND utility is not used for this purpose. Here,
We remind you of this because DBAs often misunderstand REBIND usage.
Dynamic SQL statements are pre-compiled at runtime and stored in the package cache. If the statistics are updated, you may refresh the cache so that
Recompile dynamic SQL statements to obtain updated statistics. The command is similar to the following:
FLUSH PACKAGE CACHE DYNAMIC
# Database maintenance process
RUNSTATS -- REORGCHK--YES--REORG
|
|
NO | ----- RUNSTATS
| ---- | --------- |
REBIND FLUSH PACKAGE CACHE
|
APPLICATION EXECUTION

# DB2 performance Advisor
1. graphical tool CONFIGURATION ADVISOR
2, clp autoconfigure using mem_percent 60 workload_type complex num_0000ts 20 APPLY DB AND DBM
# DB2 design Advisor
The design advisor helps you find the best index for SQL statements. It uses the DB2 optimizer, database statistics, and explanations (Explain)
To generate a recommendation index for a specific query or a group of SQL statements (also known as a workload. You can use db2advis on the command line to add the necessary input to start this advisor.
The following example uses an input file
Input. SQL executes the design advisor program on the sample database. The input file contains a set of SQL statements. Store the output in output. out.
Db2advis-d sample-I input. SQL-o output. out
Graphical tool: desigen Advisor
### Backup and recovery
# Database restoration concepts
System faults, transaction faults, medium faults, and disasters.
# Recovery policy
Q: Can I load data from another source again? How much data can be lost?
How long does it take to restore the database? Which storage resources can be used to store backups and logs?
# Recovery Type
Crash recovery (system crash, DB2 restart to execute rollback), version recovery (using the BACKUP command
Restore the previous database version in the obtained backup), roll back and restore (by using full database backup, combined
Log files to expand version recovery, archive log records are required)
Db2 writes logs first and then to the disk database.
# Primary and secondary log files
The primary log file is directly allocated when the database is connected for the first time or when the database is activated. A secondary log file is dynamically allocated each time it is required.
There are several log-related database configuration parameters. Some of these parameters are:
LOGPRIMARY: this parameter determines the number of main log files to be allocated.
LOGSECOND: this parameter determines the maximum number of auxiliary log files that can be allocated. (254 at maximum)
LOGFILSIZ: this parameter is used to specify the log file size (in 4 KB ).
Let's consider an example. Assume that the database configuration file contains the following values:
Log file size (4 KB) & nb