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
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.
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
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.