Daily practical DB2 operations-part

 

DB2 database
1. load data:
Export to tempfile of del select * From tablename where not clearing condition;
Load from tempfile of del modified by delprioritychar replace into tablename nonrecoverable;
Note:
When the export data of unrelated data tables is export, it can be in the form of concurrency to improve efficiency;
Tablename indicates the name of the table to be cleared;
Modified by delprioritychar prevents the database records from being loaded due to line breaks;
Replace into replaces the content in the current database with the content of the current data file;
Nonrecoverable load without logs;

2. Search for the current application:
DB2 list application | grep btpdbs;

3. Delete the application currently in use:
DB2 "Force Application (id1, Id2, ID3 )"
Id1, Id2, and ID3 are the application numbers displayed in the list;

4. view the execution status of the current application number:
Dbget snapshot for application agentid 299 | grep row

5. view database parameters:
DB2 get dB CFG for // The current database can be omitted
6. Modify the Database Log Data:
DB2 update dB CFG using

7. Usage of db2stop force:
the following error occurs during BIND:
sql0082c an error has occurred which has terminated processing.
sql0092n no package was created because of previous errors.
sql0091n binding was ended with "3" errors and "0" warnings.
the table file is locked and cannot be used any more.
An error is returned during the stop operation: db2stop
8/03/2005 21:46:53 0 0 sql1025n the database manager was not stopped because databases are still active.
Sql1025n the database manager was not stopped because databases are still active.
the following command is required to solve this problem: db2stop force
08/03/2005 21:47:49 0 0 sql1064n db2stop processing was successful.
sql1064n db2stop processing was successful.
Start the database db2start, connect to the database db2s, and re-run the bind.

8. Modify the buffer pool parameters:
DB2 alter bufferpool ibmdefaultbp size 10240
View the data in this table as follows:
DB2 "select * From syscat. bufferpools ";

9. DB2 log processing:
DB2 logs are stored in the file system in the form of files. There are two modes: cyclic logs and archive logs. When creating a new database, the default log mode is circular log. In this mode, you can only back up and restore the database offline. To implement online backup and recovery, you must set the log archiving mode. Currently, the archive log mode is set in the integrated business system. Other systems (such as post-event monitoring, business decision-making, and intermediate business) are generally set to the circular log mode. As for the mode used, you can modify the database configuration parameters (logretain) to achieve: archive log mode: DB2 update dB CFG for using logretain on note: After changing to on, when you view the value of the Database Configuration Parameter logretain, the recovery is displayed. After this parameter is changed, the database is in the backup pending status when you connect to the database again. In this case, an offline database backup (DB2 backup dB) is required to make the database status normal. Cyclic log mode: DB2 update dB CFG for using logretain off

10. DB2 log processing:
follow these steps: & #1048698; the DB2 command prune must be used for cleanup, we do not recommend that you use the RM command to delete it. & #1048698; before deletion, ensure that the application has been stopped (that is, the application has been online ). & #1048698; view the currently used log file directory and the first active log file & #1048766; use the "DB2 get dB CFG for" command to view the log file directory (path to log files) parameter to determine the log file directory currently used by the database. For example, path to log files =/db2log/indicates that the DB2 log storage directory is/db2log & #1048766; run the "DB2 get dB CFG for" command to view the parameters of the first active log file. All the log files before the corresponding log files are archived log files, you can delete it if it is confirmed that it is useless. For example, first active log file = s0015913.log indicates that the current first active log file is s0015913.log. & #1048698; back up the archive log to be deleted & #1048698; Delete the archive log and log on to the Application User (such as BTP). Execute:
$ DB2 connect to
$ DB2 prune logfile prior to s ???????. Log
Note: s ???????. Log is the first activity log file. This command can delete all archived log files before the first active log file.

11. How to clear the db2diag. Log File
Db2diag. log is a file used to record information about running DB2 databases. You can use this file to view detailed error information about the DB2 database. This file is also growing and needs to be cleaned regularly. You can check the instance Configuration Parameter diagpath to determine db2diag. under which directory is the log file: DB2 get dBm cfg if diagnostic data directory path (diagpath) =/home/db2inst1/sqllib/db2dump, the file is stored in the/home/db2inst1/sqllib/db2dump directory. When the file system/home usage reaches 80%-90%, the db2diag. log file should be deleted in time. Follow these steps: & #1048698; Make sure that the application (such as BTP) and DB2 have been stopped. & #1048698; back up the original db2diag. log file to another file system. & #1048698; Delete the db2diag. log file. After deletion, DB2 automatically creates a new file.

12. Load Operation
During Load
DB2 "load from acmmst.txt of del modified by coldel | replace into acmmst nonrecoverable"
An error occurs due to irregular data. The following error occurs during operations after a forced interruption:
Sql0668n operation not allowed for Reason code "3" on table "BTP. acmmst ".
Sqlstate = 57016
In this case, perform the following operations:
DB2 "load from/dev/null of del terminate into acmmst nonrecoverable ".

If the nonrecoverable parameter is not used, the database status may be incorrect. Use:
DB2 list tablesapces show detail to check the status. If the status is not normal, back up the status offline.

Update between two table files:
DB2 "Update cdmcrd Set offset = (select cdmlsl. offset from cdmlsl where cdmlsl. crdno = cdmcrd. crdno) Where cdmcrd. crdno in (select cdmlsl. crdno from cdmlsl)

13. Multi-field condition query and modification:
The fields in Table A include actno, cnlno, Bal, PWD. The fields in Table B are actno, cnlno, and txnamt. The purpose is to change the BAL in Table A to txnamt in Table B. The command is as follows:
DB2 "update a set Bal = (select txnamt from B where actno =. actno and cnlno =. cnlno) where. actno |. cnlno in (select actno | cnlno from B );

14. Multi-condition matching Query
The condition for querying a table is B? AAA record:
DB2 "select * from a where actno like 'B _ AAA % '".
Query records with certain characters in the data:
DB2 "select * from a where actno like '%-aaa % ".

15/database restoration
use the following command to recover a database:
restore dB db1 to/tstdb2/CATALOG into DB newlogpath/tstdb2/db2log buffer 2048
replace existing redirect parallelism 16;
set tablespace containers for 1 using (path '/tstdb2/db2tmp ');
set tablespace containers for 2 using
(device '/dev/rtstcontlv00' 2621440, devic'/dev/rtstcontlv01 '2621440,
device '/dev/rtstcontlv02 '2014, devic'/dev/rtstcontlv03' 2621440);
restore dB db1 continue;

After the restoration is complete, run the command db2s and report the following error:
P570:> db2s
Sql1117n a connection to or activation of Database "DB" cannot be made
Because of roll-forward pending. sqlstate = 57019
Db21034e the command was processed as an SQL statement because it was not
Valid command line processor command. During SQL processing it returned:
Sql1024n a database connection does not exist. sqlstate = 08003
The solution is as follows:
P570:> DB2 rollforward dB to end of logs and complete

Rollforward status

Input Database alias = DB
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed =-
Last committed transaction = 2005-11-20-10.59.23.000000

Db20000i the rollforward command completed successfully.