Common DB2 commands

1. Open the command line window # db2cmd2, open the control center # db21_db2cc3, open the command editor db21_db2ce to operate on the database command 4, start the database instance # db2start5, stop the database instance # db2stop if you cannot stop the database because of the activated connection, run db2forceapplic before running db2stop

1. Open the command line window # db2cmd 2. Open the control center # db2cmd db2cc 3. Open the command editor db2cmd db2ce === operate database commands === 4. Start the database instance # db2start 5. Stop database instance # db2stop if you cannot stop the database due to the activated connection, run db2 force applic before running db2stop

1. Open the command line window

# Db2cmd

2. Open the Control Center

# Db2cmd db2cc

3. Open the command editor.

Db2cmd db2ce

===== Operation database commands ====

4. Start a database instance

# Db2start

5. Stop a database instance

# Db2stop

If you cannot stop the database because of the activated connection, you can run db2 force application all before running 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 activated Databases

# Db2 list active databases

11. list all database configurations

# Db2 get db cfg

12. delete a database

# Db2 drop database [dbname]

(Be careful when performing this operation)

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]

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. query the table

# Db2 "select * from table name where ..."

22. display table structure

# Db2 describe table tablename

23. Modify columns

# Db2 alter table [tablename] alter column [columname] set data type varchar (24)

===== Script File Operation Command ======

24. Execute the script file

# Db2-tvf scripts. SQL

25. Help commands

* View Command help

# Db2? Db2start

* View error code information

# Db2? 22001

* Memo: For detailed commands, use "db2?.

======================================

26. Back up the database

# Db2 backup db

Note: You must disconnect the database before executing the preceding commands.

27. Online Database Backup

# Db2-v "BACKUP DATABASE ONLINE WITH 2 buffers buffer 1024 include logs without prompting"

28. Restore the database

# Db2 restore db

29. Online database recovery

# Db2 "RESTORE DB TO LOGTARGET Without prompting"

# Db2 "ROLLFORWARD DB To end of logs and stop "...

30. Export data files

# Db2move Export

[-Sn <模式名称，一般为db2admin> ]

[-Tn <表名，多个之间用逗号分隔> ]

31. import data files

# Db2move Import

32. obtain information about the db2 database management configuration Environment

# Db2 get dbm cfg

33. obtain information about the management and configuration environment of a database in db2

# Db2 get db cfg

Or: Execute db2 get db cfg after connecting to a database

34. Change the size of db2 log Space

Note: 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 USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;

If the page size is 4 kb, the preceding command creates 3 MB log files, occupying MB of 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' 10000)

EXTENTSIZE 256

36. Obtain snapshot data of the Database Manager

# Db2-v get snapshot for dbm

37. Enter the itinerary 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 with the specified separator "|"

Db2 "import from btpoper.txt of del modified by coldel | insert into btpoper"

Ii. Uninstall data:

1. unmount 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. unmount data from a table with conditions

Db2 "export to btpoper.txt of del select * from btpoper where brhid = '20140901 '"

Db2 "export to cmcode.txt of del select * from cmcodewhere codtp = '01 '"

Db2 "export to cmkcode.txt of del modified by coldel | select * from cmkcode where codtp = '01 '"

Iii. query data structures 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 prtlines = 0 where brhid = '000000' and jobtp = '02 '"

Db2 "update svmmst set prtlines = 0 where jobtp = '02 'or jobtp = '03 '"

6. Database Connection

Db2 connect to btpdbs

7. Clear database connections

Db2 connect reset disconnects the database

Db2 terminate disconnect Database

Db2 force applications all disconnect all databases

8. Back up databases

1. db2 backup db btpdbs

2. db2move btpdbs export

Db2look-d btpdbs-e-x [-a]-o crttbl. SQL

9. Restore the 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 commands:

Db2?

Db2? Restroe

Db2? Sqlcode (for example: db2? Sql0803) Note: The code must be 4-digit, not 4-digit, followed by 0.

11. bind command: bind the application to the database. We recommend that you bind the application once every time you restore the database.

(1) db2 bind br8200.bnd

(2)/btp/bin/bndall/btp/bnd

/Btp/bin/bndall/btp/tran/bnd

12. view database parameters:

Db2 get dbm cfg

Db2 get db cfg for btpdbs

13. modify Database parameters:

Db2 update db cfg for btpdbs using LOGBUFSZ 20

Db2 update db cfg for btpdbs using LOGFILSIZ 5120

After modification, execute the following command to make it take effect:

Db2 stop

Db2 start

Supplement:

Db2 set schema btp modify current mode to "btp"

Db2 list tablespaces show detail

Db2 list tablespace containers for 2 show detail View tablespace id = 2 use the directory where the container is located

Db2 list application

Db2 list db directory 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 whose schema is btp in the current database

Db2 list tablespaces show detail display database space 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

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

Cancel the compiled destination database btpdbs

$ Db2 uncatalog db btpdbs

View version

# Db2level

Displays the current database management instance

$ Db2 get instance

Set whether the instance is automatically started when the system starts.

$ Db2iauto-on automatic start

$ Db2iauto-off does not start automatically

Database optimization command:

Reorg, runstats

When the database is used for a period of time, the data space will become larger and larger. Delete some

The data is still stored in the database, occupying data space and affecting system performance. Therefore, regular

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

Because there are many tables to be optimized, a sh program runsall is provided in the/btp/bin directory,

Runsall can be run after the business ends on the current day to optimize the database.

During the development of DB2, database maintenance is another important part throughout the development process. It is necessary to maintain a large information system; leave a simple maintenance manual for emergency purposes. Some of the following maintenance commands are collected to assist our maintenance engineers and project managers.

========================================================== ======================================

38. Change the size of the db2 log Space

Note: 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 USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;

If the page size is 4 kb, the preceding command creates 3 MB log files, occupying MB of 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 '10000) EXTENTSIZE 256

40. Create a tablespace

Rem creates a buffer pool space of 8 K

# Db2 connect to gather

# Db2 create bufferpool extends abmp immediate size 25000 PAGESIZE 8 K

Rem creates a 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 limit abmp DROPPED TABLE RECOVERY OFF

# Db2 connect reset

41. Recover temporary data 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: The data added between the last backup (which may be full backup, Incremental backup, or differential backup) and the current backup;

45. update statistics of 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

46. Run statistics on a table

# Db2-v runstatson table TAB_NAMEand indexes all

47. Check whether RUNSTATS is executed on the database.

# Db2-v "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"

48. Change the buffer pool size

In the buffer pool, when the npages of syscat. bufferpools is-1, the Database Configuration Parameter bufferpage controls the buffer pool size.

Change the value of npages to-1:

# 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

The command to change the Database Configuration Parameter BufferPages is as follows:

# Db2-v update db cfgfor dbnameusing BUFFPAGE bigger_value

# Db2-v terminate

49. view the database monitoring content list

# 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. Resetting database snapshots

# Db2-v reset monitor all

53. Calculate the buffer pool hit rate

Ideally, the buffer pool hit rate is above 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 a db2 instance

# Db2icrt <实例名称>

55. delete a db2 instance

# Db2idrop <实例名称>

56. Set the current db2 instance

# Set db2intance = db2

57. Display db2 instances

# Db2ilist

58. commands for restoring the offline Incremental backup database

# DB2 restore database yndc incremental automation from d: \ backup \ autobak \ db2 taken at 20060314232015

59. Create a sample database

On unix platforms, use:

# Sqllib/bin/db2sampl

On windows, OS/2, run: db2sampl e, e is an optional parameter, specifying the drive to create the database

60. Set the apsaradb to available (the default apsaradb 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