Common commands and techniques that DB2 must understand)

1. The string connection in DB2 is available "| ".

2. How to quickly delete a large number of data tables (test is a database table)

The most common and most commonly used statement: delete from test. However, this method is inefficient and takes too long, because database logs must be recorded when data is deleted.

Import from/dev/null of del replace into test // first clear and then import data (because the imported file is empty, it is equivalent to Clearing table data). This deletion speed is faster

Alter table test activate not logged initially with empty table; Delete the data in the table without logging. This process is the fastest.

3. Import and export data. Supported files include ixf and Del.

DB2 "export to filename. Del of del select * From tablename" // DB2 imports table data to a file

DB2 "import from filename. Del of del insert into tablename" // import the data in the file to the table in DB2

4. Check whether the table exists in DB2

Select * from "sysibm". "variables" where lower (name) = 'afa _ maintransdtl'

5. Solve the DB2 lock table problem:

DB2 "connect to AFA"

DB2 "Get snapshot for locks on Instance name"

DB2 "Terminate"

View the related information, find the locked table, and execute the following statement:

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

6. When creating a stored procedure on the server:

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. Check whether the index works

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

8. Take database snapshots:

DB2 "connect to Instance name"

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

DB2 "Get snapshot for all on Instance name"

9. View stored procedures

Select procname, text from sysibm. sysprocedures;

10. List tables viewing database tables

11. Find the position of the character in the string

Locate ('y', 'xyz ')

Find the location of 'y' in 'xyz.

12. Calculate the number of days for different dates

Days (date ('2017-03-01 ')-days (date ('2017-02-28 '))

Days returns the number of days calculated from 0001-01-01.

13. Create Table ing 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 to restart the database?

Restart database database_name

16. How to activate the database?

Activate database database_name

17. How do I stop a database?

Deactivate database database_name

18. How to rename a table?

Rename table_old to table_new

19. How to 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