The following article describes the Common commands of DB2 and their actual operation skills, if you are interested in the description of common DB2 commands and their actual operation skills, you can click the following article to view details.
Here we will talk about several DBase commands and techniques commonly used in DB2. We hope these commands and techniques can simplify your work and improve work efficiency.
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;
Common DB2 commands and techniques 8. Database snapshot capturing:
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
The above content is an introduction to frequently-used DB2 commands and techniques. I hope you will find some gains.