Common DB2 commands and descriptions of their operation skills

This article mainly describes common DB2 commands and their usage skills in actual operations. It is also the most commonly used statement in actual operations: delete from test, the efficiency is relatively low and it takes too long because database logs are required when data is deleted.

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

Common DB2 commands 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"

DB2 Common commands 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.

Commonly used DB2 commands 12. Calculate the number of days between two 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 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 
 
 

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