DB2 common command set (1)

1. Disable db2

Db2stop or db2stop force.

In the script, write both of them to avoid using the db2stop force command, for example:

Db2stop

Db2stop force

2. Start db2

Db2start

3. Create a database

Db2 create db

Or db2 create db using codeset GBK territory CN

4. delete a database

Be careful when performing this operation.

Db2 drop db

If it cannot be deleted, disconnect all databases or restart db2.

5. Disconnect the database

Db2 force application all

6. Connect to the database

Db2 connect to user using

7. Disconnect the database

Disconnect the current database: db2 connect reset

Or: db2 disconnect current

Disconnect all databases:

Db2 disconnect all

8. Back up the database

Db2 backup db

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

9. Restore the database

Db2 restore db

10. Export data files

Db2move export [-sn <mode name, usually db2admin>] [-tn <Table Name, separated by commas>]

11. import data files

Db2move import

12. list all databases in the database

Db2 list db directory

13. Enter the db2 command environment

Run db2cmd in "run ".

14. obtain information about the db2 database management configuration Environment
Db2 get dbm cfg

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

Db2 get db cfg

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

16. Set the federated database to available (the default federated database is unavailable)

Db2 update dbm cfg using federated yes

17. 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 using logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;

If the page size is 4 kb, the preceding command creates three 100 MB log files, occupying 300 MB of hard disk space. 25600 * 4KB = 102400KB.

18. Create a temporary tablespace

DB2 create user temporary tablespace stmaspace pagesize 32 k managed by database using (file d: DB2_TABSTMASPACE.F1 10000) EXTENTSIZE 256

19. Obtain the snapshot data of the Database Manager.

Db2-v get snapshot for dbm

20. Enter the itinerary number.

Db2 list applications show detail

21. Investigation Error

Sqlcode: specific error code of the product;

Sqlstate: common error codes of the DB2 series, which comply with ISO/ANSI 92SQL standards.

Sqlcode: db2? Sql1403n

Sqlstate: db2? 08004

22. 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: DB2ContainerStma

Db2 drop tablespace stma

Db2 create regular tablespace stma pagesize 8 k managed by system using (D: DB2ContainerStma) EXTENTSIZE 8 OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL comment abmp DROPPED TABLE RECOVERY OFF

Db2 connect reset

23. Restore the temporary data to the roll-forward status.

Db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE

24. Backup tablespace

Backup database yndc tablespace (USERSPACE1) TO "D: emp" WITH 2 buffers buffer 1024 PARALLELISM 1 WITHOUT PROMPTING

25. Create a db2 tool Database

Db2 create tools catalog ooools create new database toolsdb

26. 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;

27. 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 v