Common commands and techniques that DB2 must understand

The string connection inside the 1.DB2 is available "| |" This to connect

2. How to quickly delete large quantities of data tables (test for database tables)

The most commonly used and most frequently used statements: Delete from test, but this practice is less efficient and takes too long because the database log is remembered when data is deleted.

Import from/dev/null of Del Replace into test//first empty, then import the data (because the imported file is empty, it is equivalent to emptying the table data), this deletion is faster

ALTER table test Activate not logged initially with EMPTY table, delete data from tables, do not log, this processing is the fastest

3. Import and export data, supported files are Ixf,del files

DB2 "Export to Filename.del of del select * from TableName"//DB2 import data from tables into a file

DB2 "Import from Filename.del of del insert INTO TableName"//DB2 importing data from a file into a table

Check whether a table already exists in 4.DB2

SELECT * from "SYSIBM". " Systables "where lower (name) = ' Afa_maintransdtl '

5. Handling DB2 Lock List issues:

DB2 "Connect to AFA"

DB2 "Get snapshot for Locks on instance name"

DB2 "Terminate"

Then look at the relevant information, locate the table that is locked, and execute the following statement:

DB2 "Force application (application handle) Note: Application handle corresponds to an integer

6. When you create a stored procedure on the server:

You should specify a specific line break for the stored procedure, and then execute the following statement:

db2-td@-VF Filename.sql (where @ is the line break specified in the stored procedure)

7. See if the index works

Runstats on table AFA.YJ_JYWTK with distribution and detailed indexes all;

8. Capturing a Database snapshot:

DB2 "Connect to Instance name"

DB2 "Update monitor switches using lock on sort on bufferpool on UOW on table on statement"

DB2 "Get snapshot for all on instance name"

9. View stored Procedures

Select Procname,text from Sysibm.sysprocedures;

10.list Tables View database tables

11. Find the position of the character in the string

Locate (' Y ', ' xyz ')

Find the position of ' Y ' in ' xyz '.

12. Calculate the difference in days between two dates

Days (Date (' 2007-03-01 '))-days (date (' 2007-02-28 '))

Days returned from 0001-01-01 onwards

13. Create table mappings for one user to access another instance

Create alias TableName for instance name. TableName

14. How to view the table structure

Describe table TableName

Or

Describe SELECT * FROM table_name

15. How do I restart the database?

Restart database database_name

16. How do I activate a database?

Activate database database_name

17. How do I stop a database?

Deactivate database database_name

18. How do I rename a table?

Rename Table_old to Table_new

19. How do I define a sequence?

Create sequence orderseq start with 1 increment by 1 no MaxValue no cycle cache24

20. How to view the index of a table

Describe indexes for Table TableName Show Detail

Article transfer from: http://database.51cto.com/art/200905/122272.htm