DB2 Common Command Set----DB2 maintenance (reprint)

Source: Internet
Author: User
Tags db2 db2 connect db2 connect to db2 describe table switches
DB2 Common Command Set----DB2 maintenance 2007-09-09 10:10 in the DB2 development process, the whole development process also has a very important part of the work is the maintenance of the database, for the maintenance of a huge information system is very necessary; Leave a simple maintenance manual for a rainy night. Some of the maintenance commands collected below are used to treat our maintenance engineers and project managers.


* UPDATE:2006-06-14

* Memo: Please use "DB2" for detailed command? <command> "for viewing.


1. Close DB2

Db2stop or

Db2stop Force.

Write two of the scripts in general, avoiding the use of Db2stop force commands, such as:


Db2stop Force

2. Start DB2


3. Create a Database

DB2 Create DB <db name>

or DB2 create DB using CodeSet GBK territory CN

4. Delete Database

Be careful to do this.

DB2 Drop DB <db name>

If you cannot delete, disconnect all database connections or restart DB2.

5. Disconnect the database

DB2 Force application All

6. Connecting to the database

DB2 Connect to <db name> user <username> using <password>

7. Disconnect the database

Disconnect Current Database connection: DB2 connect reset

Or: DB2 Disconnect current

Disconnect all databases: DB2 disconnect All

8. Backing Up the database

DB2 Backup DB <db name>

Note: You need to disconnect the database before you perform the above command

9. Restoring the Database

DB2 Restore DB <source db name>

10. Export Data Files

Db2move <db name> Export [-sn < mode name, generally db2admin>] [-tn < table name, multiple comma separated;]

11. Import Data File

Db2move <db name> Import

12. List all db in database

DB2 List DB Directory

13. Enter the DB2 command environment

Executing in "Run": db2cmd

14. Get DB2 Database management configuration environment information

DB2 get dbm CFG

15. Get DB2 a database Database manage configuration environment information

DB2 get DB CFG for <db name>

Or: Perform DB2 get db CFG after connecting to a database.

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

DB2 update dbm CFG using federated Yes

17. Change the size of the DB2 log space

Note: The following command is designed to prevent the DB2 database from using hard disk space, only for DB2 on the developer's own machine, and if it is a server, the parameters need to be modified.

DB2 UPDATE DB CFG for <db name> USING logretain off logprimary 3 logsecond 2 logfilsiz 25600;

If the page size is 4KB, the above command creates 3 100M log files, taking up 300MB of hard disk space. 25600*4kb=102400kb.

18. Create a temporary table space

DB2 CREATE USER Temporary tablespace stmaspace PAGESIZE K MANAGED by DATABASE USING (FILE ' D:/db2_tab/stmaspace. F1 ' 10000) extentsize 256

19. Get snapshot data for the database manager

Db2–v Get snapshot for dbm

20. Show the process number

DB2 List Applications Show Detail

21. Survey Errors

Sqlcode: Product specific error code;

SQLSTATE:DB2 series of common error codes, in line with the Iso/ansi 92SQL standard.

Investigating SQLCODE:DB2? sql1403n

Investigating SQLSTATE:DB2? 08004

22. Create a table space

REM Create buffer pool space 8K

DB2 Connect to gather

DB2 CREATE Bufferpool stmabmp IMMEDIATE SIZE 25000 PAGESIZE 8K

REM Create tablespace: STMA

REM must confirm that the path is correct


DB2 Drop Tablespace STMA

DB2 CREATE REGULAR tablespace STMA PAGESIZE 8 K MANAGED by SYSTEM USING (' D:/db2container/stma ') Extentsize 8 overhead 10 .5 prefetchsize 8 transferrate 0.14 bufferpool stmabmp dropped TABLE RECOVERY off

DB2 Connect Reset

23. Restore pending data to roll forward status

DB2 Rollforward DATABASE TestDB to end of LOGS and COMPLETE Noretrieve

24. Backup table Space

BACKUP DATABASE YNDC tablespace (USERSPACE1) to "D:/temp" with 2 buffers BUFFER 1024 PARALLELISM 1 without prompting

25. Create the DB2 tool database

DB2 Create tools Catalog SysTools Create new Database Toolsdb

26. How to make incremental/differential backups

Increment: The last full backup to the additional data portion between this backup;

Difference (Delta): The portion of data that has increased since the last backup (possibly full, incremental, or differential) to this 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. See if Runstats has been executed on the database

Db2-v "Select Tbname, Nleaf, Nlevels, Stats_timefrom sysibm.sysindexes"

30. Change the size of the buffer pool

In the buffer pool, when the Syscat.bufferpools npages is-1, the configuration parameters of the database Bufferpage control the size of the buffer pool.

Change the value of the npages to-1 command:

Db2-v Connect to Db_name

Db2-v SELECT * from Syscat.bufferpools


Db2-v Connect Reset

Db2-v Terminate

To change the database configuration parameters bufferpages commands are as follows:

DB2-V Update db cfgfor dbnameusing BUFFPAGE bigger_value

Db2-v Terminate

31. View the list of database monitoring contents

DB2-V Get Monitor Switches

32. Open a database monitoring content

DB2-V Update monitor switches using Bufferpoolon

33. Get Database Snapshots

Db2-v get snapshot to all databases > Snap.out

Db2-v get snapshot for dbm>> snap.out

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

Db2-v Terminate

34. Reset Database Snapshots

Db2-v Reset Monitor All

35. Calculate buffer pool hit ratio

Ideally, the buffer pool hit more than 95%, the formula is as follows:

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

36. Create a DB2 instance

Db2icrt < Instance name >

37. Delete DB2 Instance

Db2idrop < Instance name >

38. Set the current DB2 instance


39. Show DB2-owned instances


40. Restore the offline incremental backup DATABASE command

DB2 RESTORE DATABASE yndc incremental AUTOMATIC from d:/backup/autobak/db2 taken at 20060314232015

41. Create a sample database

On UNIX platforms, use: Sqllib/bin/db2sampl <path>

On the WINDOWS,OS/2 platform, use: Db2sampl e,e is an optional parameter that specifies the drive that will 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


Add multiple columns at once: ALTER TABLE Wb_productcell add prod_addr varchar (30)
Add prod_com varchar (30);

45. Data Migration Method 1

Export Script Sample
DB2 Connect to TESTDB user test password test
DB2 "Export to AA1.IXF of IXF select * FROM table1"
DB2 "Export to AA2.IXF of IXF select * from Table2"
DB2 Connect Reset
Import Script Example
DB2 Connect to TESTDB user test password test
DB2 "Load from AA1.IXF of IXF to replace into table1 COPY NO without prompting"
DB2 "Load from AA2.IXF of IXF to replace into table2 COPY NO without prompting"
DB2 Connect Reset

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.