1, open the Command line window
#db2cmd
2, open the control center
# Db2cmd DB2CC
3. Open Command Editor
Db2cmd Db2ce
===== Operations Database Command =====
4, start the database instance
#db2start
5. Stop database instance
#db2stop
If you can't stop the database because of the active connection, execute DB2 force application all on the db2stop before running the/db2stop force
6. Create a database
#db2 Create DB [dbname]
7, connect to the database
#db2 connect to [dbname] user [username] using [password]
8, disconnect the database
#db2 Connect Reset
9. List all databases
#db2 List db Directory
10. List all active databases
#db2 List Active databases
11. List all database configurations
#db2 Get DB cfg
12, delete the database
#db2 drop database [dbname]
(Be careful to do this)
If you cannot delete, disconnect all database connections or restart DB2
========= Operation Datasheet Command ==========
13. List all user tables
#db2 List Tables
14. List all system tables
#db2 List Tables for system
15. List all Tables
#db2 List Tables for all
16. List system tables
#db2 List Tables for system
17, List the user table
#db2 List Tables for user
18. List specific user tables
#db2 list tables for schema [user]
19. Create a new table (T1) with the same structure as a table (T2) in the database
#db2 CREATE table T1 like T2
20. Import data from one table T1 to another table T2
#db2 "INSERT INTO T1 select * from T2"
21. Inquiry Form
#db2 "SELECT * from table name where ..."
22. Display table Structure
#db2 describe table tablename
23, modify the column
#db2 ALTER TABLE [TABLENAME] ALTER COLUMN [COLUMNAME] SET data type varchar (24)
= = = = = = = = = = = = = =======
24. Execute script File
#db2-TVF Scripts.sql
25. Help Order
* View command Help
#db2? Db2start
* View error code information
#db2? 22001
* Memo: Please use "DB2" for detailed command? <command> "for viewing.
=========================
26. Backup Database
#db2 Backup DB <db name>
Note: You need to disconnect the database before you perform the above command
27, Online Backup Database
#db2-V "BACKUP DATABASE <database name> ONLINE to <path> with 2 buffers BUFFER 1024 INCLUDE LOGS without PROM Pting "
28, restore the database
#db2 Restore DB <source db name>
29. Online Recovery Database
#db2 "RESTORE DB <database name> to <db path> logtarget <logpath> without prompting"
#db2 "Rollforward DB <database name> to end of LOGS and STOP" ...
30. Export Data Files
#db2move <db name> Export
[-SN < mode name, generally for DB2ADMIN>]
[-tn < table name, multiple comma separated;]
31. Import data File
#db2move <db name> Import
32, get DB2 database management configuration environment information
#db2 get dbm CFG
33. 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
34, 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.
35, create temporary table space
#DB2 CREATE USER Temporary tablespace stmaspace PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2_TAB\STMASPACE. F1 ' 10000)
Extentsize 256
36, get the database Manager snapshot data
#db2 –v get snapshot for dbm
37. Show the process number
#db2 List Applications Show Detail
===================================================
One, loading data:
1, with the default separator load, the default is "," number
DB2 "Import from Btpoper.txt to del insert into Btpoper"
2, with the specified separator "|" Load
DB2 "Import from Btpoper.txt to Del Modified by coldel| INSERT INTO Btpoper "
Second, unload data:
1, unload a table all the data
DB2 "Export to Btpoper.txt of del select * from Btpoper"
DB2 "Export to Btpoper.txt of Del Modified by coldel| SELECT * FROM Btpoper "
2, with the condition of unloading a table data
DB2 "Export to Btpoper.txt of del select * from Btpoper where brhid= ' 907020000 '"
DB2 "Export to Cmmcode.txt of del select * from Cmmcode where codtp= ' 01 '"
DB2 "Export to Cmmcode.txt of Del Modified by coldel| SELECT * from Cmmcode where codtp= ' 01 '
Third, the query data structure and information:
DB2 "SELECT * FROM Btpoper"
DB2 "SELECT * from Btpoper where brhid= ' 907020000 ' and oprid= ' 0001 '"
DB2 "Select OPRID,OPRNM,BRHID,PASSWD from Btpoper"
Delete the data in the table:
DB2 "Delete from Btpoper"
DB2 "Delete from Btpoper where brhid= ' 907020000 ' or brhid= ' 907010000 '"
Modify the data in the table:
DB2 "Update svmmst set prtlines=0 where brhid= ' 907010000 ' and jobtp= ' 02 '"
DB2 "Update svmmst set prtlines=0 where jobtp= ' or jobtp= ' 03 '"
Vi. joining the database
DB2 Connect to Btpdbs
Vii. Clear Database joins
DB2 Connect Reset Disconnect Database
DB2 Terminate Disconnect Database
DB2 force applications All disconnect all database connections
Viii. Backup Database
1. DB2 Backup DB Btpdbs
2, Db2move Btpdbs Export
db2look-d btpdbs-e-X [-a]-o crttbl.sql
ix. Recovery of the database
1. DB2 restore DB Btpdbs without rolling forward
2, DB2-TVF Crtdb.sql
Crtdb.sql file contents: Create DB Btpdbs On/db2catalog
DB2-STVF Crttbl.sql
Db2move Btpdbs Import
Ten, DB2 help command:
DB2?
DB2? Restroe
DB2? Sqlcode (example: DB2? sql0803) Note: code must be 4 digits, not enough 4 digits, front 0
The bind command: Bind the application to the database, and each time you restore the database, it's recommended
(1) DB2 bind BR8200.BND
(2)/btp/bin/bndall/btp/bnd
/btp/bin/bndall/btp/tran/bnd
12, view the database parameters:
DB2 get dbm CFG
DB2 get DB CFG for Btpdbs
13, modify the database parameters:
DB2 Update DB CFG for Btpdbs using LOGBUFSZ 20
DB2 Update DB CFG for Btpdbs using LOGFILSIZ 5120
After the change is completed, the following command should be executed to make it effective:
DB2 stop
DB2 start
Add:
DB2 Set schema BTP modify the current mode to "BTP"
DB2 List tablespaces Show Detail view current database table space allocation status
DB2 list tablespace containers for 2 show Detail view tablespace id=2 use container directory
DB2 List Application
DB2 List DB directory lists all databases
DB2 list Active databases lists all active databases
DB2 List table for all lists all tables under the current database
DB2 List table for schema BTP lists tables with schema BTP in the current database
DB2 List tablespaces Show detail display database space usage
DB2 List packages for all
DB2 "Import from TAB76.IXF to IXF Commitcount 5000 insert INTO Achact"
DB2 "CREATE table achact_t like Achact"
DB2 "Rename table achact_t to Achact"
DB2 INSERT INTO achact_t SELECT * from Achact where txndt>= (select Lstpgdt from
Acmact where Actno=achact.actno) "
DB2 get snapshot to dynaimic SQL on Jining
To delete an instance:
# cd/usr/lpp/db2_07_01/instance
#./db2idrop Instname
List all DB2 instances:
# Cd/usr/lpp/db2_07_01/bin
#./db2ilist
Create a catalog for a database
$ DB2 Catalog DB Btpdbs On/db2catalog
Canceling a cataloged database Btpdbs
$ DB2 Uncatalog DB Btpdbs
View version
# Db2level
Show current Database Admin instance
$ DB2 Get Instance
Sets whether the instance system starts automatically when it is started.
$ db2iauto-on Auto Start
$ db2iauto-off does not start automatically
Database Tuning Command:
Reorg, Runstats
When the database is used for a period of time, the data space becomes bigger and larger. Some delete
Data is still stored in the database, occupying data space, affecting system performance. It is therefore necessary to periodically
Run reorg, runstats commands, purge deleted data, and optimize data structures.
DB2 reorg Table Name
DB2 runstats on table name with distribution and indexes all
Because to optimize the table more, so in the/btp/bin directory provides a SH program runsall,
You can run Runsall and optimize the database after the end of the day
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.
=================================================================
38, 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.
39, create temporary table space
#DB2 CREATE USER Temporary tablespace stmaspace PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2_TAB\STMASPACE. F1 ' 10000) extentsize 256
40. 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 1 0.5 prefetchsize 8 transferrate 0.14 bufferpool stmabmp dropped TABLE RECOVERY off
#db2 Connect Reset
41. Restore the pending data to roll forward status
#db2 Rollforward DATABASE TestDB to end of LOGS and COMPLETE Noretrieve
42. Backup Table Space
#BACKUP DATABASE YNDC tablespace (USERSPACE1) to "D:\temp" with 2 buffers BUFFER 1024 PARALLELISM 1 without prompting
43. Create DB2 Tool database
#db2 Create Tools Catalog SysTools Create new Database Toolsdb
44. How to make incremental/differential backup
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;
45, update all the table statistics
#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
46. Run statistical information on a table
#db2-v Runstatson table Tab_nameand Indexes all
47, to see if the database has been executed runstats
#db2-V "select Tbname, Nleaf, Nlevels,stats_timefrom sysibm.sysindexes"
48, 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
49. Look at the list of database monitoring contents
#db2-V Get monitor switches
50, open a database monitoring content
#db2-V Update monitor switches using Bufferpoolon
51. 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
52. Reset Database Snapshots
#db2-V Reset Monitor all
53, calculate the buffer pool hit rate
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 logical reads)) *100%
========= Database Instance ========================
54. Create DB2 Instance
#db2icrt < Instance name >
55. Delete DB2 Instance
#db2idrop < Instance name >
56, set the current DB2 instance
#set DB2INTANCE=DB2
57, show DB2 have the instance
#db2ilist
58, restore offline incremental backup of the database command
#DB2 RESTORE DATABASE yndc incremental AUTOMATIC from D:\backup\autobak\db2 taken at 20060314232015
59. Create 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
60. Set the federated database to be available (default federated database is unavailable)
#db2 update dbm CFG using federated Yes
61, List all the tables in the database
#db2 List Tables
62. 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 replace to table1 COPY NO without prompting"
#db2 "Load from AA2.IXF of IXF replace to table2 COPY NO without prompting"
#db2 Connect Reset
