Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "summary of common operation commands in db2 database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This article summarizes the common operation commands of DB2 in detail and shares them to you. Friends who use db2 can refer to it.

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

Note: before executing the command, you need to enter the DB2 account: su db2inst1

Modify password: change the corresponding operating system password, change the password of db2admin on windows, change the password of db2inst1 on linux, and the database password of db2 is bound to the operating system, as everyone said, you only need to modify the password of the user on the operating system. If you want to change that user's password, you can change that user's password on the operating system.

1. Open a command line window

The copy code is as follows:

# db2cmd

2. Open the control center

The copy code is as follows:

# db2cmd db2cc

3. Open the command editor

The copy code is as follows:

Db2cmd db2ce

1. Load data:

1. Load with the default delimiter, which defaults to the "," sign

The copy code is as follows:

Db2 "import from btpoper.txt of del insert into btpoper"

2. Load with the specified delimiter "|"

The copy code is as follows:

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

2. Uninstall the data:

1. Unload all data in a table

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

The copy code is 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, query data structure and data:

The copy code is 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:

The copy code is as follows:

Db2 "delete from btpoper"

Db2 "delete from btpoper where brhid='907020000' or brhid='907010000'"

5. Modify the data in the table:

The copy code is as follows:

Db2 "update svmmst set prtlines=0 where brhid='907010000' and jobtp='02'"

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

VI. Connect the database

The copy code is as follows:

Db2 connect to btpdbs

Clear the database connection

Db2 connect reset disconnects the database

Db2 terminate disconnects the database

Db2 force applications all disconnects all database connections

8. Back up the database

1 、 db2 backup db btpdbs

2 、 db2move btpdbs export

The copy code is as follows:

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

IX. Restore the database

1 、 db2 restore db btpdbs without rolling forward

2. Db2-tvf crtdb.sql

Contents of crtdb.sql file:

The copy 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 (example: db2? Sql0803) Note: code must be 4 digits, less than 4 digits, preceded by 0

11. Bind command: bundle the application with the database. It is recommended to do a bind every time the database is restored.

(1) db2 bind br8200.bnd

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

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

12. View database parameters:

The copy code is as follows:

Db2 get dbm cfg

Db2 get db cfg for btpdbs

XIII. Modify database parameters:

The copy code is as follows:

Db2 update db cfg for btpdbs using LOGBUFSZ 20

Db2 update db cfg for btpdbs using LOGFILSIZ 5120

After the amendment is completed, the following order shall be executed to make it effective:

The copy code is as follows:

Db2 stop

Db2 start

Add:

Db2 set schema btp changes the current mode to "btp"

Db2 list tablespaces show detail to view the current database table space allocation

Db2 list tablespace containers for 2 show detail View the directory where tablespace id=2 uses the container

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 the tables under the current database

Db2 list tables for schema btp lists the tables in the current database whose schema is btp

Db2 list tablespaces show detail displays 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:

The copy code is as follows:

# cd / usr/lpp/db2_07_01/instance

#. / db2idrop InstName

List all DB2 instances:

The copy code is as follows:

# cd / usr/lpp/db2_07_01/bin

#. / db2ilist

Create a catalog for the database

The copy code is as follows:

$db2catalog db btpdbs on / db2catalog

Cancel the cataloged database btpdbs

The copy code is as follows:

$db2 uncatalog db btpdbs

View version

The copy code is as follows:

# db2level

Show the current database management instance

The copy code is as follows:

$db2 get instance

Sets whether the instance starts automatically when the instance system starts.

$db2iauto-on starts automatically

$db2iauto-off does not start automatically

Database tuning commands:

Reorg 、 runstats

When the database is used for a period of time, the data space becomes larger and larger. Drop some delete

The data is still stored in the database, which occupies data space and affects the performance of the system. So it needs to be on a regular basis.

Run reorg and runstats commands to clear the delete data and optimize the data structure.

Db2 reorg table table name

Db2 runstats on table table name with distribution and indexes all

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

You can run runsall at the end of the day to optimize the database.

In the DB2 development process, throughout the development process, there is a very important part of the work is the maintenance of the database; for the maintenance of a huge information system is very necessary; keep a simple maintenance manual for a rainy day; the following collection of some maintenance commands to entertain our maintenance engineers and project managers.

Summary of common commands in DB2

1. Start the database

The copy code is as follows:

Db2start

2. Stop the database

The copy code is as follows:

Db2stop

3. Connect to the database

The copy code is as follows:

Db2 connect to o_yd user db2 using pwd

4. Read the database manager configuration

The copy code is as follows:

Db2 get dbm cfg

5. Write database manager configuration

Db2 update dbm cfg using parameter name parameter value

6. Read the configuration of the database

The copy code is as follows:

Db2 connect to o_yd user db2 using pwd

Db2 get db cfg for o_yd

7. Write the configuration of the database

The copy 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

The copy 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

The copy 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

The copy code is as follows:

Db2 restore db o_yd from d: to d:

Db2 restore db o_yd from / /. / tape0 to d:

11. Bind stored procedures

The copy 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. Organize the table

The copy 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

The copy 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

The copy 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 file name

(for each command in the file; end)

16. Automatically generate batch files

Build text file: temp.sql

The copy 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 table (view) statements

On the server: C:/sqllib/misc directory

The copy code is 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 orders

The copy code is as follows:

Grant dbadm on database to user bb

nineteen

The copy code is as follows:

Select * from czyxx fetch first 1 rows only

twenty

The copy code is as follows:

Db2look-d ylbx-u db2admin-w-asd-a-e-o a.txt

21. Show all tables for the current user

The copy code is as follows:

List tables

twenty-two。 List all system tables

The copy code is as follows:

List tables for system

23. View table structure

The copy code is as follows:

Db2 describe select * from user.tables

24. View deadlock details

The copy code is as follows:

Db2 get snapshot for locks on yourdatdabasename

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

26. Back up the database

# db2 backup db

Note: you need to disconnect the database before executing the above command

27. Backup the database online

The copy code is as follows:

# db2-v "BACKUP DATABASE ONLINE TO WITH 2 BUFFERS BUFFER 1024 INCLUDE LOGS WITHOUT PROMPTING"

28. Restore the database

The copy code is as follows:

# db2 restore db

29. Restore the database online

The copy code is as follows:

# db2 "RESTORE DB TO LOGTARGET WITHOUT PROMPTING"

# db2 "ROLLFORWARD DB TO END OF LOGS AND STOP"...

30. Export data files

The copy code is as follows:

# db2move export

[- sn]

[- tn]

31. Import data files

The copy code is as follows:

# db2move import

32. Get db2 database management configuration environment information

The copy code is as follows:

# db2 get dbm cfg

33 、. Get the database management configuration environment information of a database in db2

The copy code is as follows:

# db2 get db cfg for

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

34. Change the size of db2 log space

Note: the following command is set to prevent the db2 database from using too much hard disk space. It is only used for db2 on the developer's own machine. If it is a server, the parameters need to be modified.

The copy code is as follows:

# db2 UPDATE DB CFG FOR USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600

If the page size is 4KB, the above command creates three 100m log files that take up 300MB hard disk space. 25600*4KB=102400KB .

Create temporary tablespaces

The copy 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. Get snapshot data of database manager

The copy code is as follows:

# db2-v get snapshot for dbm

37. Display the progress number

The copy code is as follows:

# db2 list applications show detail

38. Change the size of db2 log space

Note: the following command is set to prevent the db2 database from using too much hard disk space. It is only used for db2 on the developer's own machine. If it is a server, the parameters need to be modified.

# db2 UPDATE DB CFG FOR USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600

If the page size is 4KB, the above command creates three 100m log files that take up 300MB hard disk space. 25600*4KB=102400KB .

39. Create temporary tablespace

The copy 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

Create tablespaces

Rem create buffer pool space 8K

The copy code is as follows:

# db2 connect to gather

# db2 CREATE BUFFERPOOL STMABMP IMMEDIATE SIZE 25000 PAGESIZE 8K

Rem creates tablespaces: 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 STMABMP DROPPED TABLE recovery OFF

# db2 connect reset

41. Restore suspended data to roll forward

The copy code is as follows:

# db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE

42. Backup tablespace

The copy 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

The copy code is as follows:

# db2 create tools catalog systools create new database toolsdb

44. How to make incremental / differential backups

Increment: the part of data that was added between the last full backup and this backup

Delta: the part of data that has been added between the last backup (which may be a full backup, an incremental backup, or a differential backup) and this backup

45. Update statistics for all tables

The copy 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

The copy code is as follows:

# db2-v runstatson table TAB_NAMEand indexes all

47. Check to see if RUNSTATS has been executed on the database

The copy code is 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 npages of syscat.bufferpools is-1, the size of the buffer pool is controlled by the database configuration parameter bufferpage.

Command to change the value of npages to-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 list of database monitoring contents

The copy code is as follows:

# db2-v get monitor switches

50. Open a database to monitor content

The copy code is as follows:

# db2-v update monitor switches using bufferpoolon

51. Take a database snapshot

The copy 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. Reset database snapshot

The copy code is as follows:

# db2-v reset monitor all

53. Calculate the buffer pool hit rate

Ideally, the hit rate of the buffer pool is above 95%. The calculation 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

55. Delete db2 instance

# db2idrop

56. Set the current db2 instance

# set db2intance=db2

57. Show the instances owned by db2

# db2ilist

58. Command to restore offline incremental backup database

The copy code is as follows:

# 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

On the windows,os/2 platform, using: db2sampl eBook e is an optional parameter that specifies the drive on which the database will be created

60. Set the federated database to be available (the default federated database is not available)

The copy code is 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

Sample export script

The copy 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

Sample import script

The copy 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"

This is the end of the summary of common operation commands in db2 database. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.