DB2 database Common Operations Command Encyclopedia _ database Other

This article is a detailed summary of DB2 Common operation commands, to share with you. For the use of DB2 friends can refer to the following.

The DB2 database management client does not have a control center since the v9.7 version, but instead uses data Studio client. License keys are not applied during the installation of the DB2 database product. However, the following products do not have a CD activated, so their licenses are automatically applied during the installation of these products: DB2 express-c and DB2 Connect Personal Edition. In addition, the DB2 Personal Edition is also a special case. Although the DB2 Personal Edition has an activation CD, its licenses are automatically applied during the installation of the product.

Note: You need to enter the DB2 account before executing the command: Su db2inst1

Modify Password: Change the corresponding operating system password, Windows can change db2admin password, linux change db2inst1 password can be, DB2 database password is and operating system binding, like everyone said only need to modify the operating system on the user's password on it. If you want to change the user's password, you can change that user's password on the operating system.

1, open the Command line window

Copy Code code as follows:

#db2cmd

2, open the control center

Copy Code code as follows:

# Db2cmd DB2CC

3. Open Command Editor

Copy Code code as follows:

Db2cmd Db2ce

One, loading data:

1, with the default separator load, the default is "," number

Copy Code code as follows:

DB2 "Import from Btpoper.txt to del insert into Btpoper"

2, with the specified separator "|" Load

Copy Code code as follows:

DB2 "Import from Btpoper.txt to Del Modified by coldel| INSERT INTO Btpoper "

Second, unload data:

1, unload a table all the data

Copy Code code 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, with the condition of unloading a table data

Copy Code code as follows:

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:

Copy Code code as follows:

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:

Copy Code code as follows:

DB2 "Delete from Btpoper"
DB2 "Delete from Btpoper where brhid= ' 907020000 ' or brhid= ' 907010000 '"

Modify the data in the table:

Copy Code code as follows:

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

Copy Code code as follows:

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

Copy Code code as follows:

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:

Copy Code code as follows:

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:

Copy Code code as follows:

DB2 get dbm CFG
DB2 get DB CFG for Btpdbs

13, modify the database parameters:

Copy Code code as follows:

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:

Copy Code code as follows:

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:

Copy Code code as follows:

# cd/usr/lpp/db2_07_01/instance
#./db2idrop Instname

List all DB2 instances:

Copy Code code as follows:

# Cd/usr/lpp/db2_07_01/bin
#./db2ilist

Create a catalog for a database

Copy Code code as follows:

$ DB2 Catalog DB Btpdbs On/db2catalog

Canceling a cataloged database Btpdbs

Copy Code code as follows:

$ DB2 Uncatalog DB Btpdbs

View version

Copy Code code as follows:

# Db2level

Show current Database Admin instance

Copy Code code as follows:

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

Summary of DB2 Common commands

1. Start the database

Copy Code code as follows:

Db2start

2. Stop database

Copy Code code as follows:

Db2stop

3. Connecting to a database

Copy Code code as follows:

DB2 Connect to O_yd user DB2 using PWD

4. Read Database management program configuration

Copy Code code as follows:

DB2 get dbm CFG

5. Write database Management program configuration


DB2 update dbm CFG using parameter Name argument value


6. Read the configuration of the database

Copy Code code as follows:

DB2 Connect to O_yd user DB2 using PWD
DB2 get DB CFG for O_yd

7. Write a database configuration

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

DB2 Force application All
DB2 force application Id1,id2,,, Idn MODE ASYNC
(DB2 list application for DB o_yd Show detail)

9. Backing up a database

Copy Code code 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 Database

Copy Code code as follows:

DB2 Restore DB O_yd from D:to D:
DB2 Restore DB O_yd from//./TAPE0 to D:

11. Binding stored Procedures

Copy Code code as follows:

DB2 Connect to O_yd user DB2 using PWD
DB2 Bind C:/DFPLUS.BND

Copy stored procedures to the C:/sqllib/function directory on the server


12. Organizing tables

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

Import from C:/123.txt of Del inserts 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 file name
(Every command used in a document; end)
16. Automatically generate batch files
Build text file: Temp.sql

Copy Code code 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 a Build table (view) statement


On the server: C:/sqllib/misc directory

Copy Code code as follows:

DB2 Connect to O_yd user DB2 using PWD
Db2look–d o_yd–u db2–e–p–c C:/o_yd.txt

18. Other commands

Copy Code code as follows:

Grant Dbadm on database to user bb

19

Copy Code code as follows:

SELECT * FROM Czyxx fetch a 1 rows only

20

Copy Code code as follows:

Db2look–d ylbx–u Db2admin–w–asd–a–e–o A.txt

21. Display all tables for the current user

Copy Code code as follows:

List tables

22. List all system tables

Copy Code code as follows:

List Tables for system

23. View Table Structure

Copy Code code as follows:

DB2 describe select * FROM User.tables

24. View deadlock Details

Copy Code code as follows:

DB2 get snapshot for locks on yourdatdabasename

Use the Df-k command to see if some file systems have no space.

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

Copy Code code as follows:

#db2-V "BACKUP DATABASE <database name> ONLINE to <path> with 2 buffers BUFFER 1024 INCLUDE LOGS without PROM Pting "

28, restore the database

Copy Code code as follows:

#db2 Restore DB <source db name>

29. Online Recovery Database

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

#db2move <db name> Export

[-SN < mode name, generally for DB2ADMIN>]
[-tn < table name, multiple comma separated;]

31. Import data File

Copy Code code as follows:

#db2move <db name> Import

32, get DB2 database management configuration environment information

Copy Code code as follows:

#db2 get dbm CFG

33. Get DB2 A database Database manage configuration environment information

Copy Code code as follows:

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

Copy Code code as follows:

#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

Copy Code code as follows:

#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

Copy Code code as follows:

#db2 –v get snapshot for dbm

37. Show the process number

Copy Code code as follows:

#db2 List Applications Show Detail

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

Copy Code code as follows:

#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

Copy Code code as follows:

#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

Copy Code code as follows:

#db2 Rollforward DATABASE TestDB to end of LOGS and COMPLETE Noretrieve

42. Backup Table Space

Copy Code code as follows:

#BACKUP DATABASE YNDC tablespace (USERSPACE1) to "D:\temp" with 2 buffers BUFFER 1024 PARALLELISM 1 without prompting

43. Create DB2 Tool database

Copy Code code as follows:

#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

Copy Code code 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 statistical information on a table

Copy Code code as follows:

#db2-v Runstatson table Tab_nameand Indexes all

47, to see if the database has been executed runstats

Copy Code code as follows:

#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

Copy Code code as follows:

#db2-V Get monitor switches

50, open a database monitoring content

Copy Code code as follows:

#db2-V Update monitor switches using Bufferpoolon

51. Get Database Snapshots

Copy Code code as follows:

#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

Copy Code code as follows:

#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%

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

Copy Code code as follows:

#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)

Copy Code code as follows:

#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

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

#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"

I hope this article will help you to further firmly grasp the DB2 database operation.