DB2 Database Common Command set

In the process of DB2 development, one of the most important tasks throughout the development process is the maintenance of the database, which is necessary to maintain a large information system; Leave a simple maintenance manual for a rainy year; The following parts of the maintenance order are collected 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 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 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 the current database connection:

DB2 Connect reset or: DB2 disconnect current

To disconnect all database connections:

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: After connecting to a database, perform DB2 get DB CFG.

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 investigation 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 Creation Tablespace: Stma REM must confirm that the path is correct rem d:/db2container/stma DB2 drop tablespace stma DB2 CREATE REGULAR Tablespace STMA PAGESIZE 8 K MANAGED by SYSTEM USING (' D:/db2container/stma ') Extentsize 8 overhead 10.5 P Refetchsize 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 reorgchk Update STATISTICS on table all db2-v "Select Tbname, Nleaf, Nlevels, Stats_timefrom sysibm.sysindexes" db2-v Minate

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 alter BUFFERPOOLIBMDEFAULTBP size-1 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 to get db2-v OT for 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 Inde x logical reads)) *100%

36. Create a DB2 instance

Db2icrt < Instance name >

37. Delete DB2 instance

Db2idrop < Instance name >

38. Set the current DB2 instance

Set DB2INTANCE=DB2

39. Show DB2-owned instances

Db2ilist

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

ALTER TABLE STAFF ADD COLUMN pnhone VARCHAR (20)

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 a A2.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 ' ixf replace into table1 COPY NO wi Thout prompting " DB2" load from AA2.IXF to IXF replace into table2 COPY NO without prompting " DB2 Co Nnect Reset