Common DB2 commands and descriptions of their operation skills

Source: Internet
Author: User

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

 
 
  1. 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

 
 
  1. runstats on table afa.yj_jywtk with distribution and detailed indexes all; 

8. Take database snapshots:

Db2 "connect to Instance name"

 
 
  1. 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

 
 
  1. 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

 
 
  1. 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

 
 
  1. describe table tableName  
  2. or  
  3. describe select * from table_name 

15. How to restart the database?

 
 
  1. restart database database_name 

16. How to activate the database?

 
 
  1. activate database database_name 

17. How do I stop a database?

 
 
  1. deactivate database database_name 

18. How to rename a table?

 
 
  1. rename table_old to table_new 

19. How to define a sequence?

 
 
  1. create sequence orderseq start with 1 increment by 1 no maxvalue no cycle cache24 

20. How to view the index of a table

 
 
  1. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.