Common DB2 database command set: maintenance commands for some databases

During the development of DB2, database maintenance is another important part throughout the development process. It is necessary to maintain a large information system; leave a simple maintenance manual for emergency purposes. Some of the following maintenance commands are collected to assist our maintenance engineers and project managers.

**************************************** *********

* Update: 2006-06-14

* Memo: For detailed commands, use "db2? <Command>.

**************************************** *********
 

 

1. Disable db2

Db2stop or db2stop force.
 

 

In the script, write both of them to avoid using the db2stop force command, for example:

Db2stop
Db2stop force
 

 

2. Start db2

Db2start
 

 

3. Create a database

Db2 create db <db name> or db2 create db using codeset GBK territory CN
 

 

4. delete a database

Be careful when performing this operation.

Db2 drop db <db name>
 

 

If it cannot be deleted, disconnect all databases or restart db2.

5. Disconnect the database

Db2 force application all
 

 

6. Connect to the database

Db2 connect to <db name> user <username> using <passWord>
 

 

7. Disconnect the database

Disconnect the current database:

Db2 connect reset or: db2 disconnect current
 

 

Disconnect all databases:

Db2 disconnect all
 

 

8. Back up the database

Db2 backup db <db name>
 

 

Note: You must disconnect the database before executing the preceding commands.

9. Restore the database

Db2 restore db <source db name>
 

 

10. Export data files

Db2move <db name> export
[-Sn <mode name, usually db2admin>]
[-Tn <table names, separated by commas>]
 

 

11. import data files

Db2move <db name> import
 

 

12. list all databases in the database

Db2 list db directory
 

 

13. Enter the db2 command environment

Run db2cmd in "run ".
 

14. obtain information about the db2 database management configuration Environment

Db2 get dbm cfg
 

 

15. obtain information about the management configuration environment of a database in db2

Db2 get db cfg for <db name>
Or: Execute db2 get db cfg after connecting to a database.
 

 

16. Set the joint database to available (the default joint database is unavailable)

Db2 update dbm cfg using federated yes
 

 

17. Change the size of db2 log Space

Note: The following command is used only for db2 on the developer's own machine to prevent the db2 database from using hard disk space excessively. If it is a server, the parameter needs to be modified.

Db2 update db cfg for <db name> USING logretain OFF
LogPRimary 3 logsecond 2 logfilsiz 25600;
 

 

If the page size is 4 kb, the preceding command creates 3 MB log files, occupying MB of hard disk space. 25600 * 4KB = 102400KB.

18. Create a temporary tablespace

DB2 create user temporary tablespace STMaspACE PAGESIZE 32 K
Managed by database using (file d: DB2_TABSTMASPACE.F1 10000)
EXTENTSIZE 256
 

 

19. Obtain the snapshot data of the Database Manager.

Db2-v get snapshot for dbm
 

 

20. display the travel number

Db2 list applications show detail
 

 

21. Investigation Error

Sqlcode: specific error code of the product;

Sqlstate: common error codes of the DB2 series, which comply with ISO/ANSI 92SQL standards.

Sqlcode: db2? Sql1403n

Sqlstate: db2? 08004
 

 

22. Create a tablespace

Rem creates a buffer pool space of 8 K

Db2 connect to gather

Db2 create bufferpool extends abmp immediate size 25000 PAGESIZE 8 K

Rem creates a tablespace: STMA

Rem must confirm that the path is correct

Rem D: DB2ContainerStma

Db2 drop tablespace stma

Db2 CREATE
Regular tablespace stma pagesize 8 K
MANAGED BY SYSTEM
USING (D: DB2ContainerStma) EXTENTSIZE 8
OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL
STMABMP
DROPPED TABLE RECOVERY OFF

Db2 connect reset
 

23. Restore the temporary data to the roll-forward status

Db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE
 

 

24. Backup tablespace

Backup database yndc tablespace (USERSPACE1)
TO "D: emp" WITH 2 buffers buffer 1024 PARALLELISM 1 WITHOUT PROMPTING
 

 

25. Create a db2 tool Database

Db2 create tools catalog ooools create new database toolsdb
 

 

26. How to perform incremental/differential backup

Incremental: The data added between the last full backup and the current backup;

Delta: The data added between the last backup (which may be full backup, Incremental backup, or differential backup) and the current backup;

27. update statistics for all tables

Db2-v connect to DB_NAME

Db2-v "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"

Db2-v reorgchkupdate statistics on table all

Db2-v "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"

Db2-v terminate
 

 

28. Run statistics on a table

Db2-v runstatson table TAB_NAMEand indexes all
 

 

29. Check whether RUNSTATS is executed on the database

Db2-v "select tbname, nleaf, nlevels,
Stats_timefrom sysibm. sysindexes"
 

 

30. Change the buffer pool size

In the buffer pool, when the npages of syscat. bufferpools is-1, the Database Configuration Parameter bufferpage controls the buffer pool size.

Change the value of npages to-1:

Db2-v connect to DB_NAME

Db2-v select * from syscat. bufferpools

Db2-v alter bufferpoolIBMDEFAULTBP size-1

Db2-v connect reset

Db2-v terminate
 

 

The command to change the Database Configuration Parameter BufferPages is as follows:

Db2-v update db cfgfor dbnameusing BUFFPAGE bigger_value

Db2-v terminate
 

 

31. view the database monitoring content list

Db2-v get monitor switches
 

 

32. Open the monitoring content of a Database

Db2-v update monitor switches using bufferpoolon
 

 

33. Getting database snapshots

Db2-v get snapshot for all databases> snap. out

Db2-v get snapshot for dbm> snap. out

Db2-v get snapshot for all bufferpools> snap. out

Db2-v terminate
 
 

34. Resetting database snapshots

Db2-v reset monitor all
 

 

35. Calculate the buffer pool hit rate

Ideally, the buffer pool hit rate is above 95%. The formula is as follows:

(1-(buffer pool data physical reads + buffer pool index physical reads)
/(Buffer pool data logical reads + pool index logical reads) * 100%
 

 

36. Create a db2 instance

Db2icrt <Instance name>
 

 

37. delete a db2 instance

Db2idrop <Instance name>
 

 

38. Set the current db2 instance

Set db2intance = db2
 

 

39. Display db2 instances

Db2ilist
 

 

40. commands for restoring the offline Incremental backup database

DB2 RESTORE DATABASE YNDC INCREMENTAL AUTOMATIC FROM
D: ackupautobakdb2 taken at 20060314232015
 

 

41. Create a sample database

On unix platforms, use:

Sqllib/bin/db2sampl <path>
 

 

On windows, OS/2, run the following command: db2sampl e, e is an optional parameter, specifying the drive to create the database;

42. list all tables in the database

Db2 list tables
 

 

43. List the data structure of a table

Db2 describe table v_ro_role
 

 

44. add columns to the table

ALTER TABLE STAFF
ADD COLU