Db2 cmd command operation

Db2 cmd command operation 1. Open the command line window # db2cmd 2. Open Control Center www.2cto.com # db2cmd db2cc 3. Open the command editor db2cmd db2ce ==== operate database commands ==== = 4. Start the database instance # db2start 5. Stop the database instance # db2stop if you cannot stop the database due to the activated connection, run db2 force application all before running db2stop./db2stop force 6. create a database # db2 create db [dbname] 7. connect to the database www.2cto.com # db2 connect to [dbname] user [username] using [password] 8. Disconnect a database # db2 connect reset 9. list all databases # db2 list db direc 10. list all activated databases # db2 list active databases 11. list all database configurations # db2 get db cfg 12. delete a database # db2 drop database [dbname] (perform this operation be careful) if it cannot be deleted, disconnect all databases or restart db2 ========= operation data table 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 user tables # db2 list tables for user 18. list specific user tables # db2 list tables for schema [user] 1 9. 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. query table # 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) ======= Script File Operation Command ======= 24. Run the script file # db2-tvf scripts. SQL 25. Help Command * view Command help # db2? Db2start * view error code # db2? 22001 * memo: Use "db2? <Command>. =====================================26. backup database # db2 backup db <db name> remarks: before executing the preceding commands, you must disconnect the DATABASE and back up the database ONLINE # db2-v "backup database <database name> online to <path> WITH 2 buffers buffer 1024 include logs without prompting" 28 recover database # db2 restore db <source db name> 29. Online RESTORE database # db2 "restore DB <database name> TO <db path> LOGTARGET <logpath> without prompting" # db2 "rollforward db <database name> TO END OF LOGS Nd stop "... 30. export data file # db2move <db name> export [-sn <mode name, usually db2admin>] [-tn <Table name, separated by commas>] 31. import the data file # db2move <db name> import 32. Obtain the environment information of the db2 database management configuration # db2 get dbm cfg 33 ,. obtain information about the management and configuration environment of a database in db2 # db2 get db cfg for <db name> or: after connecting to a database, run the db2 get db cfg 34 command to change the size of the db2 log space. Note: The following command is used to prevent the db2 database from using the hard disk space excessively, it is only used for db2 on the developer's own machine. If it is a server, the parameter needs to be modified. # Db2 update db cfg for <db name> USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600; if the page size is 4 kb, the preceding command creates 3 MB log files, MB hard disk space. 25600 * 4KB = 102400KB. 35. CREATE a temporary tablespace # DB2 create user temporary tablespace stmaspace pagesize 32 k managed by database using (FILE 'd: \ DB2_TAB \ STMASPACE. f1 '100) EXTENTSIZE 256 36. Obtain the snapshot data of the Database Manager # db2-v get snapshot for dbm 37. display the travel number # db2 list applications show detail ========== ========================================================== = 1. load data: 1. load with the default separator. The default value is "," db2 "import from btpoper.txt of del insert into btpoper" 2. Load db2 with the specified separator "|" "Import from btpoper.txt of del modified by coldel | insert into btpoper" II. Uninstall data: 1. Uninstall all data in a table: db2 "export to btpoper.txt of del select * from btpoper" db2 "export to btpoper.txt of del modified by coldel | select * from btpoper" 2. Conditional uninstall data in one table: db2 "export to btpoper.txt of del select * from btpoper where brhid = '000000'" db2 "export to cmcode.txt of del select * from cmcodewhere codtp = '01'" d B2 "export to cmcode.txt of del modified by coldel | select * from cmcodewhere codtp = '01'" 3. query the data structure and data: db2 "select * from btpoper" db2 "select * from btpoper where brhid = '000000' and oprid = '000000'" db2 "select oprid, oprnm, brhid, passwd from btpoper "4. delete table data: db2" delete from btpoper "db2" delete from btpoper where brhid = '000000' or brhid = '000000' "5. Modify Table data: db2 "update svmmst set prtlin Es = 0 where brhid = '000000' and jobtp = '02' "db2" update svmmst set prtlines = 0 where jobtp = '02' or jobtp = '03' "6. Join database db2 connect to btpdbs 7. Clear database connection db2 connect reset disconnect database connection db2 terminate disconnect database connection db2 force applications all disconnect all databases 8. Back up database 1. db2 backup db btpdbs 2, db2move btpdbs export db2look-d btpdbs-e-x [-a]-o crttbl. SQL 9. restore database 1. db2 restore db btpdbs without rolling forward 2. db2 -Tvf crtdb. SQL crtdb. SQL file content: create db btpdbs on/db2catalog db2-stvf crttbl. SQL db2move btpdbs import 10. DB2 HELP command: db2? Db2? Restroe db2? Sqlcode (for example: db2? Sql0803) Note: The code must be 4-digit, not 4-digit, followed by the 011 and bind commands: bind the application to the database. After each database restoration, we recommend that you perform one bind (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 XIII. After modifying the database parameters: db2 update db cfg for btpdbs using LOGBUFSZ 20 db2 update db cfg for btpdbs using LOGFILSIZ 5120, execute the following command to make it take effect: db2 stop db2 start supplement: db2 set schema btp change current mode to "btp" db2 l Ist tablespaces show detail View Current Database tablespace allocation status db2 list tablespace containers for 2 show detail View tablespace id = 2 use the container directory db2 list application db2 list db directory to list all databases db2 list active databases list all active databases db2 list tables for all list all tables in the current database db2 list tables for schema btp list tables in the current database where schema is btp db2 list tablespaces show detail display Database spatial usage db2 list packages for all db2 "import from tab76.ixf of 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 for dynaimic SQL on jining deletes an instance: # cd/usr/lpp/db2_07_01/instance #. /db2idrop InstName: # cd/usr/lpp/db2_07_01/bin #. /db2ilist creates a catalog for the database $ Db2 catalog db btpdbs on/db2catalog cancel the compiled destination database btpdbs $ db2 uncatalog db btpdbs view version # db2level displays the current database management instance $ db2 get instance sets whether the instance system starts automatically when it starts. $ Db2iauto-on: $ db2iauto-off: Database optimization commands are not automatically started: reorg and runstats. When the database is used for a period of time, the data space will become larger and larger. Some deleted data is still stored in the database, occupying data space and affecting system performance. Therefore, you need to regularly run the reorg and runstats commands to clear Deleted Data and optimize the data structure. Db2 reorg table name db2 runstats on table name with distribution and indexes all a sh program runsall is provided in the/btp/bin directory because many tables need to be optimized, runsall can be run after the end of the business day to optimize the database. During the development of DB2, it is also important to maintain the database throughout the development process; it is very necessary to maintain a large information system; leave a simple maintenance manual for emergency needs; some of the following maintenance commands are collected, to provide our maintenance engineers and project managers. ========================================================== =====================================38. Modify the db2 log space size remarks: the following command is used only for db2 on the developer's own machine to prevent the db2 database from using hard disk space excessively. If it is a server, the parameter needs to be modified. # Db2 update db cfg for <db name> USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600; if the page size is 4 kb, the preceding command creates 3 MB log files, MB hard disk space. 25600 * 4KB = 102400KB. 39. CREATE a temporary tablespace # DB2 create user temporary tablespace stmaspace pagesize 32 k managed by database using (FILE 'd: \ DB2_TAB \ STMASPACE. f1 '100) EXTENTSIZE 10000 40. CREATE tablespace rem CREATE buffer pool space 8 K # db2 connect to gather # db2 create bufferpool using abmp immediate size 256 PAGESIZE 8 K 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 used abmp dropped table recovery off # db2 connect reset 41. recover the data temporarily TO the roll-forward status # db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE 42. backup tablespace # backup database yndc tablespace (USERSPACE1) TO "D: \ temp" WITH 2 buffers buffer 1024 PARALLELISM 1 without prompting 43. Create a db2 tool database # Db2 create tools catalog systools create new database toolsdb 44. How to perform incremental/differential backup incremental: The data added between the last full backup and the current backup; delta ): data added between the last backup (which may be full backup, Incremental backup, or differential backup) and the current backup; 45. 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 t Erminate 46. Run statistical information on a table # db2-v runstatson table TAB_NAMEand indexes all 47. Check whether RUNSTATS # db2-v "select tbname, nleaf, nlevels are executed on the database, stats_timefrom sysibm. sysindexes "48. Change the size of the buffer pool when syscat. when the npages of bufferpools is-1, the buffer pool size is controlled by the Database Configuration Parameter bufferpage. Change the value of npages to the-1 command: # db2-v connect to DB_NAME # db2-v select * from syscat. bufferpools # db2-v alter bufferpoolibmdefabp BP size-1 # db2-v connect reset # db2-v terminate: # db2-v update db cfgfor dbnameusing BUFFPAGE bigger_value # db2-v terminate 49. Check the list of database monitoring content # db2-v get monitor switches 50. Open the monitoring content of a database # db2-v update monitor switches using bufferpoolon 51. Obtain database snapshots # db2-v get snapshot for all databases> snap. out # db2-v get snapshot for dbm> snap. out # db2-v get snapshot for all bufferpools> snap. out # db2-v terminate 52. reset database snapshot # db2-v reset monitor all 53. The buffer pool hit rate is more than 95% when the buffer pool hit rate is calculated. 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 current db2 instance # set db2intance = db2 57. Display db2 instances # db2ilist 58 command for restoring the offline INCREMENTAL backup DATABASE # DB2 restore database yndc incremental automatic from d: \ backup \ autobak \ db2 taken at 20060314232015 59. Create a sample database on the unix platform. Use: # sqllib/bin/db2sampl <path> On windows, OS/2, and use: db2sampl e, e is an optional parameter, specifying the drive for the database to be created 60, set the joint database to available (the default joint database is unavailable) # db2 update dbm cfg using federated yes 61. list all tables in the database # db2 list tables 62. data migration method 1 export script example # 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 into table1 copy no without prompting" # db2 "load from aa2.ixf of ixf replace into table2 copy no without prompting" # db2 connect reset