Detailed descriptions of common DB2 commands and Techniques

This article mainly tells you about several DBase commands commonly used in DB2 and the actual operation skills. I hope you can use these commands and operation skills to improve your work efficiency, the following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

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"."SYSTABLES" 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('2007-03-01'))-days(date('2007-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 do DB2 Common commands define sequences?

 
 

  
  
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 
 
 

The above content is an introduction to frequently-used DB2 commands and techniques. I hope you will find some gains.