Db2 database commands

Db2 database commands

This article summarizes commonly used DB2 operation commands and shares them with you. For friends who use db2, refer.

The DB2 database management Client no longer has a control center since v9.7, but uses the Data Studio Client. The license key is not applied during the installation of the DB2 database product. However, the following products do not activate CD, so their licenses will be automatically applied when these products are installed: DB2 Express-C and DB2 Connect Personal Edition. DB2 Personal Edition is also a special case. Although DB2 Personal Edition has activated CD, its license will be automatically applied when the product is installed.

Note: Before executing the command, enter the DB2 account: su db2inst1

Change Password: Change the corresponding operating system password. In windows, you can change the db2admin password. in linux, you can change the db2inst1 password. The db2 database password is bound to the operating system, as you said, you only need to modify the user password on the operating system. You can change the password of the user on the operating system.

1. Open the command line window
Copy codeThe Code is as follows: # db2cmd
2. Open the Control Center
Copy codeThe Code is as follows: # db2cmd db2cc
3. Open the command editor.
Copy codeThe Code is as follows: db2cmd db2ce

1. load data:

1. load with the default separator. The default value is ",".
Copy codeThe Code is as follows: db2 "import from btpoper.txt of del insert into btpoper"
2. load with the specified separator "|"
Copy codeThe Code is as follows: db2 "import from btpoper.txt of del modified by coldel | insert into btpoper"

Ii. Uninstall data:

1. unmount all data in a table
Copy codeThe Code is as follows: 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
Copy codeThe Code is as follows: 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 '"
Db2 "export to cmkcode.txt of del modified by coldel | select * from cmkcode where codtp = '01 '"

Iii. query data structures and data:

Copy codeThe Code is as follows: 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:

Copy codeThe Code is as follows: db2 "delete from btpoper"
Db2 "delete from btpoper where brhid = '000000' or brhid = '000000 '"

5. Modify Table data:

Copy codeThe Code is as follows: 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

Copy codeThe Code is as follows: 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
Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: 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:

Copy codeThe Code is as follows: db2 get dbm cfg
Db2 get db cfg for btpdbs

13. modify Database parameters:

Copy codeThe Code is as follows: 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:
Copy codeThe Code is as follows: 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:

Copy codeThe Code is as follows: # cd/usr/lpp/db2_07_01/instance
#./Db2idrop InstName

List all DB2 instances:

Copy codeThe Code is as follows: # cd/usr/lpp/db2_07_01/bin
#./Db2ilist

Create a catalog for a database

Copy codeThe Code is as follows: $ db2 catalog db btpdbs on/db2catalog

Cancel the compiled destination database btpdbs

Copy codeThe Code is as follows: $ db2 uncatalog db btpdbs

View version

Copy codeThe Code is as follows: # db2level

Displays the current database management instance

Copy codeThe Code is as follows: $ 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.

Summary of common DB2 commands

1. Start the database
Copy codeThe Code is as follows: db2start
2. Stop the database
Copy codeThe Code is as follows: db2stop
3. Connect to the database
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
4. Read Database Management Program Configuration
Copy codeThe Code is as follows: db2 get dbm cfg
5. Write Database Management Program Configuration
Db2 update dbm cfg using parameter name parameter value
6. Read Database Configuration
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
Db2 get db cfg for o_yd
7. Write Database Configuration
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
Db2 update db cfg for o_yd using parameter name parameter value
8. Close all application connections
Copy codeThe Code is as follows: db2 force application all
Db2 force application ID1, ID2, Idn MODE ASYNC
(Db2 list application for db o_yd show detail)
9. Back up the database
Copy codeThe Code is as follows: db2 force application all
Db2 backup db o_yd to d:
(Db2 initialize tape on //./tape0)
(Db2 rewind tape on //./tape0)
Db2 backup db o_yd to //./tape0
10. Restore the database
Copy codeThe Code is as follows: db2 restore db o_yd from d: to d:
Db2 restore db o_yd from //./tape0 to d:
11. Bind a stored procedure
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
Db2 bind c:/dfplus. bnd
Copy the stored procedure to the C:/sqllib/function directory on the server.
12. Sort the table
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
Db2 reorg table ydd
Db2 runstats on table ydd with distribution and indexes all
13. Export table data
Copy codeThe Code is as follows: db2 export to c:/dftz.txt of del select * from dftz
Db2 export to c:/dftz. ixf of ixf select * from dftz
14. Import table data
Copy codeThe Code is as follows: import from c:/123.txt of del insert into ylbx. czyxx
Db2 import to c:/dftz.txt of del commitcount 5000 messages c:/dftz. msg insert into dftz
Db2 import to c:/dftz. ixf of ixf commitcount 5000 messages c:/dftz. msg insert into dftz
Db2 import to c:/dftz. ixf of ixf commitcount 5000 insert into dftz
Db2 import to c:/dftz. ixf of ixf commitcount 5000 insert_update into dftz
Db2 import to c:/dftz. ixf of ixf commitcount 5000 replace into dftz
Db2 import to c:/dftz. ixf of ixf commitcount 5000 create into dftz (IXF only)
Db2 import to c:/dftz. ixf of ixf commitcount 5000 replace_create into dftz (IXF only)

15. Execute a batch file
Db2-tf batch processing file name
(Use and end each command in the file)
16. automatically generate a batch file
Document: temp. SQL
Copy codeThe Code is as follows: select 'runstats on table DB2. '| tabname |' with distribution and detailed indexes all; 'from syscat. tables where
Tabschema = 'db2 'and type = 'T ';
Db2-tf temp. SQL> runstats. SQL
17. automatically generate the table creation (View) Statement
On the server: C:/sqllib/misc directory
Copy codeThe Code is as follows: db2 connect to o_yd user db2 using pwd
Db2look-d o_yd-u db2-e-p-c:/o_yd.txt
18. Other commands
Copy codeThe Code is as follows: grant dbadm on database to user bb
19
Copy codeThe Code is as follows: select * from czyxx fetch first 1 rows only
20
Copy codeThe Code is as follows: db2look-d ylbx-u db2admin-w-asd-a-e-o a.txt
21. display all tables of the current user
Copy codeThe Code is as follows: list tables
22. list all system tables
Copy codeThe Code is as follows: list tables for system
23. view the table structure
Copy codeThe Code is as follows: db2 describe select * from user. tables
24. View deadlock details
Copy codeThe Code is as follows: db2 get snapshot for locks on yourdatdabasename
Run the df-k Command to check whether some file systems have no space.

26. Back up the database
# Db2 backup db <db name>
Note: You must disconnect the database before executing the preceding commands.

27. Online Database Backup
Copy codeThe Code is as follows: # db2-v "backup database <database name> online to <path> WITH 2 buffers buffer 1024 include logs without prompting"
28. Restore the database
Copy codeThe Code is as follows: # db2 restore db <source db name>

29. Online database recovery
Copy codeThe Code is as follows: # 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

Copy codeThe Code is as follows: # db2move <db name> export

[-Sn <mode name, usually db2admin>]
[-Tn <table names, separated by commas>]
31. import data files
Copy codeThe Code is as follows: # db2move <db name> import
32. obtain information about the db2 database management configuration Environment
Copy codeThe Code is as follows: # db2 get dbm cfg
33. obtain information about the management and configuration environment of a database in db2
Copy codeThe Code is as follows: # db2 get db cfg for <db name>

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.

Copy codeThe Code is as follows: # 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, occupying MB of hard disk space. 25600 * 4KB = 102400KB.
35. Create a temporary tablespace
Copy codeThe Code is as follows: # 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
Copy codeThe Code is as follows: # db2-v get snapshot for dbm
37. Enter the itinerary number
Copy codeThe Code is as follows: # db2 list applications show detail

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 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, occupying MB of hard disk space. 25600 * 4KB = 102400KB.

39. Create a temporary tablespace

Copy codeThe Code is as follows: # 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
Copy codeThe Code is as follows: # 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

Copy codeThe Code is as follows: # db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE

42. Backup tablespace

Copy codeThe Code is as follows: # backup database yndc tablespace (USERSPACE1) TO "D: \ temp" WITH 2 buffers buffer 1024 PARALLELISM 1 WITHOUT PROMPTING

43. Create a db2 tool Database

Copy codeThe Code is as follows: # db2 create tools catalog ooools 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

Copy codeThe Code is as follows: # 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

Copy codeThe Code is as follows: # db2-v runstatson table TAB_NAMEand indexes all

47. Check whether RUNSTATS is executed on the database.

Copy codeThe Code is as follows: # 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

Copy codeThe Code is as follows: # db2-v get monitor switches

50. Open the monitoring content of a Database

Copy codeThe Code is as follows: # db2-v update monitor switches using bufferpoolon

51. Obtain database snapshots

Copy codeThe Code is as follows: # 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

Copy codeThe Code is as follows: # 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%

54. Create a db2 instance

# Db2icrt <Instance name>

55. delete a db2 instance

# Db2idrop <Instance name>

56. Set the current db2 instance

# Set db2intance = db2

57. Display db2 instances

# Db2ilist

58. commands for restoring the offline Incremental backup database

Copy codeThe Code is as follows: # DB2 restore database yndc incremental automatic from d: \ backup \ autobak \ db2 taken at 20060314232015

59. Create a sample database

On unix platforms, use:
# Sqllib/bin/db2sampl <path>
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)

Copy codeThe Code is as follows: # 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
Copy codeThe Code is as follows: # 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
Copy codeThe Code is as follows: # 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"

I hope this article will be helpful for you to further master DB2 database operations.

Who has the full list of IBMDB2 database operation commands?

Common DB2 commands
1. Start the database
Db2start
2. Stop the database
Db2stop
3. Connect to the database
Db2 connect to o_yd user db2 using pwd
4. Read Database Management Program Configuration
Db2 get dbm cfg
5. Write Database Management Program Configuration
Db2 update dbm cfg using parameter name parameter value
For more information, see kushu.blog.ccidnet.com/...4.html.

Common db2 commands

Connect to the database:

Connect to [database name] user [operation user name] using [Password]

Create a buffer pool (8 K ):

Create bufferpool ibmdefault8k immediate size 5000 PAGESIZE 8 K;
Create a buffer pool (16 K) (OA_DIVERTASKRECORD ):
Create bufferpool ibmdefault16k immediate size 5000 PAGESIZE 16 K;
Create a buffer pool (32 K) (OA_TASK ):
Create bufferpool ibmdefa3232k immediate size 5000 PAGESIZE 32 K;

Create a tablespace:

Create tablespace exoatbs in database partition group limit PAGESIZE 8 k managed by system using ('/home/exoa2/exoainer iner') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL limit OVERHEAD 24.10 TRANSFERRATE 0.90 dropped table recovery off;

Create tablespace partition in database partition group ibmdefaultgroup pagesize 16 k managed by system using ('/home/exoa2/Shanghai') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL overflow OVERHEAD 24.1 TRANSFERRATE 0.90 dropped table recovery off;

Create tablespace partition in database partition group ibmdefaultgroup pagesize 32 k managed by system using ('/home/exoa2/Shanghai') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL overflow OVERHEAD 24.1 TRANSFERRATE 0.90 dropped table recovery off;

Grant use of tablespace exoatbs to public;
Grant use of tablespace exoatbs16k to public;
Grant use of tablespace exoatbs32k to public;

Create System tablespace:

Create temporary tablespace exoasystmp in database partition group ibmtempgroup pagesize 8 k managed by system using ('/home/exoa2/exoasystmp') EXTENTSI ...... the remaining full text>