Common commands and their usage in DB2

First, high (important degree)

1. Start a DB 2 instance using:
net start InstanceName

2. Stop a DB 2 instance using:
net stop InstanceName

3. Start the Configuration assistant:
Db2=>!db2ca

4. Manually configure the connection:
Db2=>catalog node/db

5. Delete the view:
Db2=>drop View View_name

6. Create a table:
Db2=>create Table TableName

7. List the number of active databases and connections:
Db2=>list Active databases

8. Disconnect all applications from the database:
Db2=> Force Application All

9. Connect with the remote instance identified by the user by using a password:
Db2=> attach to user using

10. Create a database named:
Db2=> CREATE Database

11. Explicitly invalidate the database:
Db2=> Deactivate Database

12. Explicitly connect to the database with the user and password as needed:
Db2=> connect to [[user] using]

13. Disconnect from the current database:
Db2=> Connect Reset

14. List the tables in the database. If no parameters are specified, the default is to list the current user's table:
Db2=> list Tables[for {user | all | system | Schema}][show detail]

15. Display column information for a table or view:
Db2=> describe table

16. Determine if the table needs to be reorganized. This is useful for automating runstats on all tables:
db2=> Reorgchk on table all

17. Reorganize the table by refactoring the rows to eliminate the "fragmented" data and compress the information:
Db2=> reorg Table

18. Extract the database data into a flat file:
Db2=> Export

19. Import data into the database by using the Import utility:
Db2=> Import

20. Return to the LOAD utility's progress:
db2=> load Query table [to Local-message-file][nosummary | summaryonly] [SHOWDELTA]

21. Create an index:
Db2=>create Index indexname on table (collumn)

22. Create a bidirectional index:
Db2=>create Index indexname on table (collumn) Allow reverse scans

23. Perform a database backup:
db2=> backup DATABASE [to]

24. Perform database recovery:
db2=> restore database [from]

25. Returns a connection of two strings:
Db2=>concat ()

26. Return the uppercase of the string:
Db2=>upper () or UCase ()

27. Create a trigger:
Db2=>create Trigger Triggername

28. List the basic or detailed information about the tablespace:
Db2=>list tablespace[show Detail]

29. List Basic or detailed information about the Tablespace container for the specified tablespace:
Db2=>list tablespace containers for Tablespacename

30. export files from table names to del type Name.del:
Db2=>export to Name.del of del Select * from names

31. NAMES from the Del type. DEL file into an empty table named names:
Db2=>import from Names.del to del insert into names

32. Load all data from the IXF type file product.ixf into the table product:
Db2=>load form product.ixf of IXF insert into product

33. When loading the sales table, view the file sales.msg to monitor the loading process:
Db2=>load query table sales to D:\ SALES.M

34. Copy the database Db2cert to a new database Newcert on another server:
Db2=>db2move Db2cert Export

35. Rebuild the database structure in the new database Newcert and import the data from the table into the new database:
Db2=>db2move Newcert Import–io replace_create

36. Reorganization of a particular table:
db2=>reorg Table TableName

37. Index to reorganize a specific table:
db2=>reorg Table TableName Index IndexName

38. Back up the database Db2cert to the C:\dbbackup directory:
Db2=>backup database Db2cert to C:\dbbackup

39. Restore the database Db2cert from the directory C:\dbbackup:
Db2=>restore database Db2cert from C:\dbbackup

40. Restore the database to a new database called newdb and allocate two buffers, each with a size of 512:
Db2=>restore database Db2cert from C:\dbbackup
Db2=>into newdb
Db2=>with 2 Buffers
Db2=>buffer 512
Db2=>without Rolling ForWord

41. Backup Database db2cert The catalog table space syscatspace and user tablespace filets to the C:\dbbackup directory:
Db2=>backup database Db2cert tablespace (syscatspace,filets) to C:\dbbackup

42. Restore the database Db2cert tablespace filets from the C:\dbbackup directory in online mode:
Db2=>restore database Db2cert tablespace (filets) online from C:\dbbackup

43. Roll forward by the restore command causes the database Db2cert in the roll-forward pending state to roll forward to the end of the log:
Db2=>rollforward database Db2cert to end of logs

44. Create a consistent point so that it can be used for future roll-forward recovery:
DB2=>QUIESCE tablespace for table

45. Define the cursor:
Db2=> declare cursor1 cursor with hold
(If there is no option to add with, the cursor will be closed at commit and rollback)
For select Market_code from Tb_market_code for update
(Cursor1 is defined as a modifiable cursor)

46. Kill the deadlock process:
db2=> DB2 Force application (handle)

47. Call the stored procedure:
Db2=>call stored procedure name (parameter list)

48. Export the stored procedure:
Db2=>get routine into filename from procedure stored procedure name

49. Import the stored procedure:
Db2=>put routine from file name

50. Connect to the database:
Db2=>connect to database_name user username using password

51. Create aliases:
Create alias Db2admin.tables for Sysstat.tables
Create alias Db2admin.views fro syscat.views
Create alias Db2admin.columns for Syscat.columns
Create alias Guest.columns for Syscat.columns

52. Insert Record:
Db2=>insert to zjt_tables select * from tables

53. Create a unique index:
db2=> CREATE UNIQUE INDEX i_ztables_tabname on Zjt_tables (tabname)

54. View the index of the table:
db2=> DB2 describe indexes for table user1.department

55. Reconnect:
Db2=> Connect Reset

56. Interrupt Database Connection
Db2=>disconnect DB2_GCB

Second, medium (degree of importance)
1. Return all normal snapshots of the database (for V8 only):
Db2=> get health snapshot for all on

2. Return to the configuration settings for the Management Server:
db2=> Get admin cfg

3. Set the Management Server configuration parameters

Update to Value:
Db2=> Update admin CFG using

4. Create TABLE spaces:
Db2=>create tablespace Tablespacename

5. Displays the statistics and rules for the table, as well as information about the table's indexes and related rules:
DB2=>REORGCHK UPDATE STATISTICS on table tablename

6. Analyze the statistics of the current table:
Db2=>reorgchk Current statistics on table tablename

7. Analyze the statistics of the tables in the current database:
Db2=>reorgchk Current statistics on table all

8. Enumerate the log Files DB2 roll forward, the next required archive, and the time the transaction was committed after the roll-forward process started:
Db2=>rollforward database Db2cert Query status]

9. Report information about all the recovery history files in the database Db2cert:
Db2=>list History all for Db2cert

10. Modify the configuration of the database manager, and the SQL information in the application that accesses all databases for this instance is captured:
Db2=>update dbm configuration using dft_mon_stmt on

11. SQL information for the application capturing the activation switch:
Db2=>update monitor switches using statement on

12. Show All connection processes:
Db2=> DB2 List Applications

13. Displays the details of the current connection process:
Db2=> DB2 list Applications Show Detail

14. Display information for all locks:
db2=> DB2 get snapshot for locks on sample

15. View the table structure:
db2=> DB2 describe table user1.department
DB2=>DB2 describe select * from User.tables

16. Sharing:
Db2=>lock table test in Share mode

17. Displays all tables for the current user:
Db2=>list tables

18. List all the system tables:
Db2=>list Tables for System

19. Displays a list of all DB 2 commands:
Db2=>!db2?

20. Displays information about a command:
Db2=>!db2? Command

21. Display the explanatory information for a sqlcode:
Db2=>!db2? sqlnnnn

22. Displays an explanation of a DB2 error:
Db2=>!db2? db2nnnn

23. Return to the Database Manager configuration settings:
Db2=>get dbm CFG

24. Configure the Database Manager parameters

Update to Value:
Db2=>update dbm CFG using

25. Explicitly activate the database:
Db2=> Activate database

26. Configure the Database Manager parameters

Update to Value:
db2=> 1update dbm CFG using

27. Return database configuration settings for the database:
Db2=> Get DB CFG for

28. Display the identity, name, type, content, and status of the tablespace:
db2=> list tablespaces [show detail]

29. Displays container information with the specified tablespace:
Db2=> list tablespace containers for [show detail]

30. Create an entry in the database directory for the database:
Db2=> Catalog Database

31. Return the contents of the database directory:
Db2=> List Database directory [on]

32. Return to the status of the session monitor switch:
Db2=> Get Monitor Switches

33. To set the status of the session monitor switch:
Db2=> Update Monitor switches using

34. Reset Performance Monitor Value:
Db2=> Reset Monitor All

35. Return the performance information at the instance level:
Db2=> Get snapshot for dbm

36. Return all performance information for the database at the database level:
Db2=> get snapshot for all on

37. Return the contents of the dynamic SQL cache:
Db2=> get snapshot for dynamic SQL on

38. Collect statistics for the tables. The table name must be fully qualified:
db2=> runstats on table.

Three, low (important degree)
1. Return the normal snapshot information for the instance (V8 only):
Db2=> get health snapshot for dbm

2. Extract the SQL procedure into a binary file:
Db2=> get routine into from [specific] procedure [hide body]

3. Deploy the SQL procedure from a binary file:
Db2=> put routine from [owner [use registers]

4. Collect and UPDATE statistics for tables and indexes:
Db2=>runstats on table tablename

5. rebind all the packages on the database Db2cert and record the results in the file Lizhi.log:
Db2=>db2rebind DB2CERT/1 Lizhi.log

6. Delete files in the active log path so that the file name is less than S0000100.log:
Db2=>prune logfile prior to S0000100.log

7. View the settings for the current command line processor:
Db2=>list command Options

8.DB2 the database Manager to obtain row-level locks:
Db2=>alter table (tablename) locksize table

9. Forcing the DB2 database Manager to get table-level locks:
Db2=>lock table (tablename) in (share/exclusive) mode

10. Display the current and latency values for the database manager parameters:
Db2=>get dbm CFG Show Detail

11. Return the value of the db2instance environment variable:
Db2=>get instance

12. Return information about the currently connected application:
Db2=>list application [Show detail]

13. Disconnect from a specific application based on the handle number:
Db2=> Force application (H1 [, H2,.., HN])

14. Display the current and latency values for the database configuration parameters (V8 only):
Db2=> get db cfg Show detail

15. Database configuration parameters for the database

Update to Value:
db2=> Update db CFG for using

16. Reset the table space status to normal
db2=> quiesce tablespaces for table reset

From:

Http://blog.sina.com.cn/s/blog_4c451e0e01012v82.html

Common commands and their usage in DB2