## db2move
DB2MOVE is a data mobile tool that can be used to move a large number of tables between the DB2 database. The operations supported in this command are Export, Import, and LOAD.
DB2MOVE grammar can be as simple as below:
DB2MOVE DATABASE_NAME Action OPTIONS
The tool first extracts the user list list from the system catalog table, then exports each table in a PC / IXF format. Then, these PC / IXF files can be imported or put into another
DB2 database.
Here are some examples. Below this command imports all the tables in the Sample database with the specified user ID and password in the REPLACE:
DB2MOVE SAMPLE IMPORT -IO Replace -u Userid -p Password
And the following command is loaded with all tables in DB2Admin and DB2User mode in the Replace mode:
DB2MOVE SAMPLE LOAD -SN DB2ADMIN, DB2USER -LO REPLACE
## db2look is a convenient tool that can be called from the command prompt or control center. This tool is:
Extract the database definition language (DDL) statement from the database object
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 report
Generate an UPDATE statement to copy statistics about the database object
A utility similar to LOAD requires a target table. You can use db2look to extract the DDL of the table, run the DLL on the target database, and then call the load operation. Below
The example is shown, and DB2LOOK is very convenient to use. Below this command generates a DDL statement for all objects created by PETER (from database department) Peter.
The output is stored in allTables.sql.
DB2LOOK -D Department -u Peter -e -o AllTables.sql
Next, this command is generated:
DLL of all objects in database departments (specified by option -d, -a, and -e).
Copy the UPDATE statement of all tables and index in the database (specified by option -m).
GRANT authorization statement (specified by option -x).
Update statement for database manager and database configuration parameters, and db2set commands for profile registry (specified by option -f).
DB2LOOK -D Department -a-E -M -X -F -O DB2LOOK.SQL
## RunStats utility
DB2 uses a perfect, cost-based optimizer to determine how to access data. Its decision has been largely affected by some statistical information. These statistics are about databases.
The size of the table and indexes. Therefore, it is important to keep the database statistics in order to be able to select a valid data access scheme, which is important. Runstats utility
The order is used to update the physical characteristics of the table and statistics of their related indexes. These features include the number of records (base), the number of pages, and the average recording length, and the like.
Let us use some examples to demonstrate the usage of this utility. The following command collects the statistics of the table DB2USER.EMPLOYEE. Allow readers and recorders to visit during calculation statistics
Ask the table:
Runstats on Table DB2User.employee Allow Write Access
The following command collects table DB2User.emPloyee and the statistics of column EMPID and EmpName. This table can only be used for read-only requests during this command.
Runstats on Table DB2User.employee with Distribution On Column (Empid, Empname) Allow Read Access
The following command collects the statistics of the table DB2USER.EMPLOYEE and the detailed statistics of all indexes:
Runstats on Table DB2User.employee and detailed indexes all
## REORG and REORGCHK utilities
Data increasing from the database is physically placed in order. In this case, DB2 must perform additional read operations to access data. Usually, this means more
Disk I / O operation, and we all know that the cost of this type of operation is expensive. In this case, you should consider physically restructuring the table according to the index of the index, so that the relevant data is
Rely more close to each other to reduce the I / O operation as much as possible.
REORG is a utility that recommends data for tables and / or index. Although the data is physically rearranged, DB2 provides an option to perform this operation online or offline.
By default, offline REORG allows other users to read the table. You can restrict your access by specifying the Allow No Access option. Online REORG (also known as a site reorg)
Read access or write access to the table is not supported. Since the data page is rearranged, the concurrent application may have to wait for Reorg to complete the current page. You can use the appropriate option to easily
Stop, suspend or restart the restructuring process:
The following example is 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, which has an option to retrieve current database statistics or update database statistics. It also generates REORG
Statistics report of the indicator. Reorgchk uses an asterisk (*) tag table or index according to the statistical rules.
Let us consider some examples. The following command generates a report of statistics: About all tables owned by the runtime authorization:
Reorgchk Current Statistics on Table User
The following command updates the statistics, then generates reports of all tables created under mode smith:
Reorgchk Update Statistics On Schema Smith
## rebind Utility and Flush package cache command
Before performing a database application or any SQL statement, you must pre-transcribe them by DB2 and generate a package. Package is a database object, which contains applications.
The compiled SQL statement used in the source file. DB2 uses this package to access the data referenced in the SQL statement. How to select a data access scheme for these packages? It relies on
Database statistics when creating.
For static SQL statements, the package is created at compile and is bound to the database. If the statistics are updated to reflect the physical database, then you should update the existing
Pack. The Rebind utility allows you to recreate the package so that you can use the current database statistics. Command is very simple:
Rebind package package_name
However, if you want to change the application source code, you have to explicitly delete an existing related package, and then recreate the package. The Rebind utility is not for this purpose. Here,
The reason why we will remind you because DBA often misses Rebind's usage.
For dynamic SQL statements, they are pre-compiled at runtime and are stored in the package cache. If you update the statistics, you may refresh the cache so that
Re-build dynamic SQL statements to get 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 Consultant Procedure
1, Graphics Tool Configuration Advisor
2, CLP AutoConfigure sale MEM_PERCENT 60 WORKLOAD_TYPE COMPLEX NUM_STMTS 20 APPLY DB AND DBM
## DB2 Design Consultancy Procedure
Design consultants can help you find the best index of SQL statements. It uses DB2 optimizer, database statistics and explanation (Explain)
The mechanism generates a recommended index for a specific query or a set of SQL statements (also known as workloads). You can start the adviser from the command line DB2ADVIS.
The following example uses the input file
INPUT.SQL performs a design advisor to the Sample Database, which contains a set of SQL statements. The output is then stored in Output.out.
DB2ADVIS -D SAMPLE -I Input.sql -o Output.out
Graphics Tool: Desigen Advisor
### Backup and Recovery
## Database Recovery Concept
System failure, transaction fault, media failure, disaster.
# Restore strategy
Question: Can I load data from another source again? How much data can you withstand?
How much is energy to recover the database? Which storage resources can be used to store backups and logs?
# Recovery Type
Crash recovery (system crash, DB2 restart execution rollback), version recovery (use from backup command
Restore previous database versions in the acquisition backup), the front rollback (binding by using a full database backup)
Log files to extend version recovery, requiring archived logging)
DB2 uses the way to write a disk database after writing a log.
# 主 主 文件 文件 and auxiliary log file
The master log file is directly assigned when the first database connection or when the database is activated. The auxiliary log file needs to be dynamically assigned one.
There are several database configuration parameters associated with logging. Some of these parameters are:
LogPrimary: This parameter determines the number of primary log files to be assigned.
LogSecond: This parameter determines the maximum number of assigned auxiliary log files. (Maximum 254)
Logfilsiz: This parameter is used to specify the size of the log file (in 4 kb pages).
Let us consider an example. Assume that there is a lower value in the database configuration file:
Log file size (4 kb) (logfilsiz) = 250
Number of Primary Log Files (Logprimary) = 3
Number of secondary log files (logsecond) = 2path to log files = c: / mylogs /
Once the connection to the database is established for the first time, three master log files are allocated by 250 4 kb pages.
DB2 will fill the first log and continue to fill the second and third logs. After filling the third log file, there is no more main
(Pre-allocated) log file, DB2 will dynamically allocate the first auxiliary log file because logsecond is greater than zero. once
This log file is filled, DB2 will continue to assign another auxiliary log file and repeat the process until logsecond day
The maximum number of records files. For this example, when DB2 is trying to assign a third auxiliary log file, it will return an error.
It is pointed out that the full condition has been met. At this point, the transaction will be rolled back.
# 无限 logging
To allow unlimited activity logging:
Set the UseRexit database configuration parameter to ON.
Set the logsecond to a value -1.
# 日 类
Activity log. If you meet any of the following two conditions, it is considered that the log is active:
It contains information about transactions that have not been submitted or rolled.
It contains information about transactions that have been submitted but its changes have not been written to database disk (externalization).
Online archive log. This type of log contains information that has been submitted and external. This log is saved in the same directory as the activity log.
Offline archive log. It is an archive log that has been moved from the active log directory to another directory or media. Can be manually or use user exit (useXIT)
Automatic processes to complete this move.
## Log record type
Cycle Log Type: Cycle logging is the default logging method of DB2. From its name, you can know that this type of log record reuses the log in a loop. For example, if
There are four master log files
Then, DB2 will use them in order: log # 1, log # 2, log # 3, log # 4, log # 1, log # 2, etc.
As long as the log contains only information about transactions that have been submitted and external to the database disk, it can be reused by loop logging. In other words, if the log is still
It is an activity log that cannot be reused.
Using the example above, if a long-term running transaction uses five logs, what happens?
In this case, DB2 will allocate another log file - auxiliary log file,
Archive logging. Similarly, you can know from its name, when archived logging is used, the (reserved) log will be archived. In the loop logging, you have to overwrite the submitted and have been externalized.
Transactions, in the archive logging, they will be saved.
For example, if there are four primary logs, DB2 may use them in order: log # 1, log # 2, log # 3, log # 4, (if all of Log # 1 has been mentioned)
If you have an externalization, it will archive it), log # 5,
(If all the transactions of log # 2 have been submitted and externalized, they archive them), log # 6, etc.
As you can see from the example above, DB2 will keep four master log files available and will not reuse log files filled with certain transactions, which have been submitted and external. In other words,
It does not override the log that has become archived.
Note: You need to enable it before using the archive log record. To enable it, you must open the following parameters or open any of them:
Logretain (DB2 Update DB CFG for Database_name Using Logretain On)
UseEREXIT (DB2 Update DB CFG for Database_Name Using UseRexit ON)
The loop logging only supports crash and version recovery, and archive logging supports all types of recovery: crash recovery, version recovery and front rollback recovery.
User export
We have repeatedly mentioned users export in the previous chapters. User exports are procedures that allow online archiving logs to another directory (non-active log directory) or another media. Apparatus as a full database
The user exit also retrieves them in the active log directory during the ROLLFORWARD operation. To enable user export, configure the useXIT database
The number is set to ON. Once enabled, DB2 will automatically call user export programs as needed. Need to name DB2UEXT2 in Windows, you should store it in SQLLIB / BIN
In the directory, in UNIX, it should be stored in the SQLLIB / BIN directory.
## Database and Table Space Backup
# 数据 数据 Backup Page 2 (6 pages of)
Database backup is a complete copy of the database. In addition to data, backup copies also contain information about tablespace, container, database configuration, log control file, and recovery history files. Note: Backup will not store the database manager profile or registry variable. Only back up the database profile.
To perform a backup, you need sysadm, sysctrl, or sysmaint permissions.
Below is the syntax for the Backup command utility used for this backup:
Backup Database Database-Alias [user username [using password]]
[TableSpace (TBLspace-name [{, tblspace-name} ...] [online]
[Incremental [delta]] [use {tsm | xbsa} [open number-sess sessions]
To Dir / dev [{, DIR / dev} ...] | loading lib-name [open num-sess sessions]]]]]
[WITH NUM-BUFFERS] [Buffer Buffer-size] [parallelism n]
[WITHOUT PROMPTING]
To perform a complete offline backup of the database "Sample" using other backup options, you can use the following command:
(1) Backup Database Sample
(2) TO / DB2BACKUP / DIR1, / DB2BACKUP / DIR2
(3) with 4 buffers
(4) Buffer 4096
(5) Parallelism 2
Let us study this command more carefully:
Indicates the name (or alias) of the database to be backed up.
Specifies one or more locations used to store backups.
It is determined how many buffers in memory can be used during backup operations. Use multiple buffers to improve performance.
Determine the size of each buffer.
Determine how many media reader / logging process / threads are used to perform a backup.
Note: There is no keyword OFFLINE in the syntax, because this is the default method. To perform a full online backup of the Sample database, you must specify the keyword Online as follows: Backup Database Sample
ONLINE
TO / DEV / RDIR1, / DEV / RDIR2
We have previously mentioned that online backup allows other users to access it when the database is backed up. Some changes made by these users are likely to be stored in a backup copy when backed up.
Therefore, it is necessary to restore the online backup and a set of complete archive logs. In addition, the online backup is completed, and DB2 is forced to turn off the current activity log; the result will be archived.
Note: Online backup requirements are enabled for archive logging for databases. DB CFG: Loghead points to the minimum number of the event, less than loghead log
It is an archive log and can be moved. You can use the Archive log command to archive the log.
# Table space backup
In a database with considerable changes in some tablespace, you can choose not to back up the entire database, and only back up specific tablespaces.
To perform a tablespace backup, use the following syntax:
(1) Backup Database Sample
(2) TableSpace (syscatspace, userspace1, userspace2)
(3) Online
(4) TO / DB2TBSP / BACKUP1, / DB2TBSP / BACKUP2
Often, you want to back up the relevant tablespace, such as data, index, lob
Or define the table space of the table in the table.
Note: This backup can only be used in the Archival loggin environment.
# Increment backup
There are two types of incremental backups:
Incremental: DB2 Backup All data change since the last fully database backup.
Delta: DB2 will only back up the data changed since the last successful, incremental, or differential backup.
Note: To perform an incremental backup, the TRACKMOD in the DB CFG must be set to YES (tracking the page that changes in the table space).
Perform a backup in the control center.
#backup file
The naming agreement of the DB2 backup file on the disk contains the following items:
Database alias
Represents a number of backup types (0 means full database backup, 3 means table space backup, 4 means a copy from LOAD)
Instance name
Database node (always node0000 for single partition database)
Directory Joint Number (Always Catn0000 for single partition database)
Backup timestamp
Image serial number
Backup of the partition database:
In The Following Example, The Database WSDB IS Defined on All 4 Partitions,
Numbered 0 THROUGH 3. THE PATH / DEV3 / Backup Is Accessible from ALL
Partitions. Partition 0 Is The Catalog Partition, And Needs to Be Backed-Up
Separately Since This Is An Offline Backup. To Perform An Offline Backup of All
The WSDB Database Partitions To / Dev3 / Backup, Issue The Following
Commands from One of The Database Partitions:
DB2_ALL '<< 0
