DB2 Operation Commands

This article summarizes the common operation commands of DB2 and shares them with you. For friends who use DB2, refer to the following.

The DB2 database management client is no longer with the control center since the v9.7 version, but instead 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 have an activation CD, so their licenses are automatically applied during the installation of these products: DB2 express-c and DB2 Connect Personal Edition. In addition, DB2 Personal Edition is also a special case. Although the DB2 Personal Edition has an activation CD, its license is automatically applied during the installation of the product.

Note: You need to enter DB2 's 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 tied to the operating system, as everyone said only need to modify the operating system on the user's password. You want to change that user's password, you change that user's password on the operating system.

1. Open the Command Line window

#db2cmd

2. Open the control center

# Db2cmd DB2CC

　　

3. Open the Command editor

Db2cmd Db2ce

First, load the data:

1, loaded with default delimiter, default to "," number

DB2 "Import from Btpoper.txt of del insert INTO Btpoper"

2. To specify the delimiter "|" Load

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

Second, unload data:

1. Unload 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. Unloading data in a table with conditions

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

Iv. Delete data from the table:

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

Five, modify the table data:

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. Clearing Database joins

DB2 Connect reset Disconnects the database
DB2 Terminate disconnecting the database
DB2 force applications all disconnects all database connections

Viii. backing up the 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 bits, front 0

Xi. bind command: Bind the application to the database, each time the database is restored, it is recommended

(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 you have changed, you should execute the following command 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 using the directory where the container is located
DB2 List Application
DB2 List DB directory lists all databases
DB2 list Active databases lists all active databases
DB2 list tables for all lists all tables under the current database
DB2 list tables for schema BTP lists tables in the current database with schema BTP
DB2 List tablespaces show detail Show Database space usage
DB2 List packages for all

DB2 "Import from TAB76.IXF of Ixf Commitcount, 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

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

Creating catalogs for databases

$ DB2 Catalog DB Btpdbs On/db2catalog

Canceling a cataloged database Btpdbs

$ DB2 Uncatalog DB Btpdbs

View version

# Db2level

Display the current database management instance

$ DB2 Get Instance

Sets whether the instance system starts automatically when it starts.

$ db2iauto-on Auto-start
$ db2iauto-off does not start automatically

Database Optimization Commands:

Reorg, Runstats

As the database is used over time, the data space becomes more and more large. Some delete out
Data is still stored in the database, occupying data space, affecting system performance. It is therefore necessary to periodically
Run the reorg, runstats command to purge deleted data and optimize data structures.
DB2 reorg Table Name
DB2 runstats on table name with distribution and indexes all
Since the table to be optimized is much more, a SH program runsall is provided under the/btp/bin directory,
Runsall can be run at the end of the day to optimize the database

During the development of DB2, it is important to maintain the database in the whole development process, and it is necessary to maintain a large information system, and to leave a simple maintenance manual for a rainy day; some of the maintenance commands collected below are for our maintenance engineers and project managers.

DB2 Common Command Summary

1. Start the database

Db2start

2. Stopping the database

Db2stop

3. Connecting to a 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
6. Configuration of the Read database

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

7. Configuration of the Write database

DB2 Connect to O_yd user DB2 using PWD

DB2 Update DB CFG for o_yd using parameter name parameter value
8. Close all app connections DB2 force application All
DB2 force application Id1,id2,,, Idn MODE ASYNC
(DB2 list application for DB o_yd Show detail)
9. Backing Up the database

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. Recovering a Database

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

11. Binding stored Procedures

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. Sorting tables

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

DB2 Export to C:/dftz.txt of del select * from DFTZ
DB2 Export to C:/DFTZ.IXF of IXF select * from Dftzimport from C:/123.txt of del insert into Ylbx.czyxx
DB2 import to C:/dftz.txt of Del Commitcount-messages c:/dftz.msg insert INTO DFTZ
DB2 Import to C:/DFTZ.IXF of IXF Commitcount, messages c:/dftz.msg insert INTO DFTZ
DB2 Import to C:/DFTZ.IXF of IXF Commitcount, insert into DFTZ
DB2 Import to C:/DFTZ.IXF of IXF Commitcount insert_update into DFTZ
DB2 Import to C:/DFTZ.IXF of IXF Commitcount, replace into DFTZ
DB2 Import to C:/DFTZ.IXF of IXF Commitcount, create into DFTZ (IXF only)
DB2 Import to C:/DFTZ.IXF of IXF Commitcount-replace_create into DFTZ (IXF only)

15. Execute a batch file
DB2–TF Batch processing file name
(Every command in a document is used; end)
16. Automatically generate batch files
Build text file: Temp.sql

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 table (view) statements
On the server: in the C:/sqllib/misc directory
DB2 Connect to O_yd user DB2 using PWD
Db2look–d o_yd–u db2–e–p–c c:/o_yd.txt18. Other commands
Grant Dbadm on database to user bb
19
SELECT * FROM CZYXX Fetch first 1 rows only
20
Db2look–d ylbx–u Db2admin–w–asd–a–e–o A.txt
21. Show all tables for the current user
List tables
22. List all the system tables
List Tables for system
23. View Table Structure
DB2 describe select * FROM User.tables
24. Viewing the deadlock details
DB2 get snapshot for locks on yourdatdabasename
Use the Df-k command to see if some file systems have no space.

26. Backing Up the database
#db2 Backup DB <db name>
Note: The database connection needs to be disconnected before executing the above command

27. Online BACKUP Database

#db2-v BACKUP DATABASE <database name> ONLINE to <path> with 2 buffers BUFFER 1024x768 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 File

#db2move <db name> Export

[-SN < mode name, generally db2admin>]
[-tn < table name, multiple separated by commas;]

31. Import data Files
#db2move <db name> Import
32. Get DB2 Database management configuration environment information
#db2 get dbm CFG
33. Get DB2 a database Database management configuration environment information
#db2 get db cfg for <db name>

Or: Execute DB2 get DB CFG after connecting to a database

34. Change the size of the DB2 log space
Note: In order to prevent the DB2 database from using hard disk space, the following command is only used 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 that occupy 300MB of hard disk space. 25600*4kb=102400kb.
35. Create a 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 snapshot data for Database Manager
#db2 –v get snapshot for dbm
37. Show the process number
#db2 List Applications Show Detail

38. Change the size of the DB2 log space

Note: In order to prevent the DB2 database from using hard disk space, the following command is only used 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 that occupy 300MB of hard disk space. 25600*4kb=102400kb.

39. Create a 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 TABLE Space

REM Create buffer pool space 8K

#db2 Connect to gather
#db2 Create Bufferpool stmabmp IMMEDIATE SIZE 25000 PAGESIZE 8Krem creating 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 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 1024x768 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 portion of data that was added between the last full backup and the backup;
Difference (Delta): The portion of data that has been added to this backup since the last backup, possibly a full, incremental, or differential 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 Terminate

46. Run statistics on a single table

#db2-v Runstatson table Tab_nameand Indexes all

47. See if Runstats is performed on the database

#db2-V "select Tbname, Nleaf, Nlevels,stats_timefrom sysibm.sysindexes"

48. Change the size of the buffer pool

Buffer pool, when Syscat.bufferpools's npages is-1, the size of the buffer pool is controlled by the configuration parameters of the database bufferpage.
Change the value of npages to the command of-1:
#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
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, look at the database monitoring content list

#db2-V Get monitor switches

50. Open a database monitoring content

#db2-V Update monitor switches using Bufferpoolon

51. Get a snapshot of a database

#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 the Database snapshot

#db2-V Reset Monitor all

53. Calculate the buffer pool hit rate

Ideally, the buffer pool hit rate is above 95% and 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 instances

#db2icrt < Instance name >

55. Delete DB2 Instances

#db2idrop < Instance name >

56. Set the current DB2 instance

#set DB2INTANCE=DB2

57. Display DB2 owned instances

#db2ilist

58. Restore the offline incremental backup DATABASE command

#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 the WINDOWS,OS/2 platform, using: Db2sampl e,e is an optional parameter that specifies the drive that will create the database

60. Set the federated database to be available (the default federated 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 Operation Commands