Http://hi.baidu.com/wader2006/blog/item/78406b60b51f8b47ebf8f8f0.html
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
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 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
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 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 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
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
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)
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
