DB2 system commands

 

DB2 system commands

1. dasauto

In the $ DB2DIR/das/adm directory, set the Automatic startup status of the DAS. Dasauto [-h |-?] -On |-off.

If the db2fmcd process is not prohibited in the inittab file, the preceding setting is invalid because the dbfmcd process starts db2fmd and db2fmd starts the DAS within a certain period of time.

 

2. dascrt

Create a DAS. The DAS serves the control center and configuration assistant. In the $ DB2DIR/instance directory, dascrt-u dasusr1

On the window platform, you can use the db2admin create command to create

 

3. dasdrop

Delete the DAS in the $ DB2DIR/instance directory.

On the window platform, you can use the db2admin drop command to delete

 

4. dasmigr

If the database is transplanted, you can use this command to port the DAS associated with it. If the database is migrated in the same version, you also need the dasupdt command to update the DAS. Under the $ DB2DIR/instance directory

 

5. dasupdt

Update the DAS. Under the $ DB2DIR/instance directory

 

6. db2_deinstall

Run the following command to uninstall the DB2 product or component: db2_deinstall-F feature_name. Uninstall all components: db2_deinstall-.

 

7. db2_install

Command to install the DB2 product under the Installation Software

 

8. db2audit

The db2audit. cfg and db2audit. log files are both in the sqllib/security directory and are non-text files. If it is the default configuration, you can view the IP addresses and users accessing the database through the log.

Db2audit describe # view Audit Configuration

Db2audit extract file audit. rpt # generate readable audit reports based on audit logs

Db2audit prune all # Clear all audit logs

Db2audit stop # disable Audit

Db2audit start # enable Audit

 

9. db2batch

The Benchmark Testing Tool calculates the cost of each SQL file in batches.

Db2batch-d testdb-a db2inst1/123456-f bt. SQL-r ret. rpt

Bt. SQL file content:

Create table tbbatch (c1 int );

Insert into tbbatch values (2 );

Update tbbatch set c1 = 8 where c1 = 2;

Delete from tbbatch;

Drop table tbbatch;

Ret. rpt file content:

Statement number: 1

Create table tbbatch (c1 int)

Elapsed Time is: 0.091 seconds

Statement number: 2

Insert into tbbatch values (2)

Elapsed Time is: 0.034 seconds

........

 

10. db2cat

Output System dictionary Information

Db2cat-s shen-d testdb-n tblareacode-p table-o cat. rpt # output the tblareacode table Dictionary data of the shen user in the testdb Database

 

11. db2cfexp

Database Connection configuration export tool, the file content includes: Registration variables, DBM configuration parameters, NODE configuration, DB Configuration

Command: db2cfexp cfg. prf backup

 

12. db2cfimp

Database Connection configuration import tool

 

13. db2ckbkp

Check the integrity of the database backup image.

Db2ckbkp-a testdb.0.db2inst1. node).catn).20071023164120.001> chk. rpt

 

14. db2dart-Database analysis and reporting tool

Check whether the database structure is correct.

Db2dart sample

 

15. db2diag-db2diag. log analysis tool

Db2 log diagnostic file analysis tool.

Db2diag-g level = Severe

 

16. db2drdat-DRDA trace

Trace DRDA communication.

Db2drdat on # first turn on trace, you can also specify the trace buffer size

Db2drdat off-t = abc. dmp [if not specified, the default value is db2drdat. dmp] # disable trace and write the trace buffer content to abc. dmp.

 

17. db2empfa-Enable multipage file allocation (MPFA)

When the file space of the SMS tablespace is extended, an extent is extended instead of a page. When creating a database, the default MPFA enable cannot be changed to disable. If the registration variable DB2_NO_MPFA_FOR_NEW_DB = YES is registered, it is MPFA disable when the database is created.

 

18. db2expln-SQL and XQuery Explain

Generate an SQL File Execution Plan. -U followed by the user name and password,-t output to the console

Db2expln-d testdb-u shen-q "select * from tblAreaCode"-t

Db2expln-d testdb-u shen-q "select * from tblAreaCode"-o my. exp

 

19. db2flsn-Find log sequence number

Find the corresponding log file based on the LSN (you can view the LSN through db2pd.

Db2flsn 000000BF0030 # Run the command in the directory containing the SQLOGCTL. LFH file, for example,/home/db2inst1/db2inst1/NODE0000/SQL00001

$ Cd/home/db2inst1/db2inst1/NODE0000/SQL00001

$ Db2flsn 0000281C0000

Given LSN is contained in log page 1025 in log file S0000142.LOG

 

20. db2fm-DB2 fault monitor

Manage FM.

Db2fm-f on # enable FM enable. If disable, The db2fmd service process cannot be started (the db2fm-U command is invalid)

Db2fm-U # Start the db2fmd service process

 

21. db2gcf-Control DB2 instance

Control instances (partitioned database instances), start and stop database instances

Db2gcf-u-p 0-I db2inst1 # Start a partition instance

Db2gcf-s-p 0-I db2inst1 # view the status of the partition instance

Db2gcf-d-p 0-I db2inst1 # disable a partition instance

 

22. db2iauto-Auto-start instance

Set instance auto-start

 

23. db2level-Show DB2 service level

View the current database instance version, patch number, and number of digits (32bit or 64bit)

 

24. db2look-DB2 Statistics and DDL Extraction Tool

Generates DDL statements, permissions DCL, DB, and DBM configuration parameters, and database registration parameters for All database objects.

1) generate all the object DDL created by the shen user

Db2look-d testdb-u shen-e-o shen. SQL

2) generate the DDL object created by the shen user that belongs to the abc (schema ).

Db2look-d testdb-u shen-z abc-e-o shen. SQL

3) generate all the object DDL statements created by the shen user, and include the statistical information of the object (use the update statement to modify the dictionary table)

Db2look-d testdb-u shen-m abc-e-o shen. SQL

4) ddl for generating the tbtest table

Db2look-d testdb-u shen-t tbtest-e-o shen. SQL

5) generate DB and DBM configuration parameter modification commands

Db2look-d testdb-u shen-f-o shen. SQL

 

25. db2mtrk-Memory tracker

Trace memory consumed by instances, databases, and proxies.

1) check their watermark values

$ Db2mtrk-I-d-p-w-v

Tracking Memory on: 2007/11/21 at 09:48:51

Memory for instance

Database Monitor Heap has watermark of 180224 bytes

Other Memory has watermark of 2818048 bytes

FCMBP Heap has watermark of 42303488 bytes

Total: 45301760 bytes

 

Memory for database: testdb

Backup/Restore/Util Heap has watermark of 16384 bytes

Package Cache has watermark of 131072 bytes

Catalog Cache Heap has watermark of 65536 bytes

Buffer Pool Heap has watermark of 16728064 bytes

Buffer Pool Heap has watermark of 4325376 bytes

Buffer Pool Heap has watermark of 655360 bytes

Buffer Pool Heap has watermark of 393216 bytes

Buffer Pool Heap has watermark of 262144 bytes

Buffer Pool Heap has watermark of 196608 bytes

Shared Sort Heap has watermark of 0 bytes

Lock Manager Heap has watermark of 21102592 bytes

Database Heap has watermark of 3964928 bytes

Other Memory has watermark of 16384 bytes

Application Control Heap has watermark of 98304 bytes

Application Group Shared Heap has watermark of 18644992 bytes

Total: 66600960 bytes

 

Memory for agent 13852

Application Heap has watermark of 65536 bytes

Total: 65536 bytes

2) check their maximum values.

$ Db2mtrk-I-d-p-m-v

Tracking Memory on: 2007/11/21 at 09:48:42

Memory for instance

Database Monitor Heap has max size of 376832 bytes

Other Memory has max size of 19922944 bytes

FCMBP Heap has max size of 43335680 bytes

Total: 63635456 bytes

 

Memory for database: testdb

Backup/Restore/Util Heap has max size of 249200640 bytes

Package Cache has max size of 2089811968 bytes

Catalog Cache Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Buffer Pool Heap has max size of 2089811968 bytes

Shared Sort Heap has max size of 100220928 bytes

Lock Manager Heap has max size of 21102592 bytes

Database Heap has max size of 11567104 bytes

Other Memory has max size of 12533760 bytes

Application Control Heap has max size of 344064 bytes

Application Group Shared Heap has max size of 37437440 bytes

Total: 17150902272 bytes

 

Memory for agent 13852

Application Heap has max size of 1277952 bytes

Total: 1277952 bytes

 

26. db2pd-Monitor and troubleshoot DB2 database

Monitor the database.

$ Db2pd-inst-alldbs | more

The following information is returned: database version, number of digits, patch number, operating system version, cpu number, physical memory, and DBM parameter value.

$ Db2pd-db sample | more

Return the following information (data can be returned only when the database is activated): database connection information; transaction information; cache pool information (with different pagesize); log file (with LSN information ); locks; tablespace; container; DB parameter value

 

27. db2rfpen-Reset rollforward pending state

Reset the status of the database in rollforward pending.

 

28. db2mongoray

Start the db2 tray in the lower right corner of the window.

 

29. db2trc-Trace

Enable and disable the database trace function

1) Tracking db2

$ Db2trc db2 on-f db2.trc

$ Db2trc db2 off

$ Db2trc fmt db2.trc db2.txt

2) Tracking das

$ Db2trc das on-f das. trc

$ Db2trc das off

$ Db2trc fmt das. trc das.txt

 

30. db2untag-Release container tag

Release unused containers.

$ Db2untag-f/home/db2inst1/db2inst1/NODE0000/SQL00001/userdata01