31 tips for using the IBM DB2 database

1. view the local node directory

In the command window, enter:
DB2 list node directory

 
2. cataloguing a TCP/IP Node

Command window:
DB2 catalog TCPIP node <node_name>
Remote <Svcname & brvbar; port_number> ostype <os2 & brvbar;
AIX & brvbar; Win95 & brvbar; NT & brvbar; HPUX & brvbar;
Sun & brvbar; MVS & brvbar; os400 & brvbar;
VM & brvbar; VSE & brvbar; SCO & brvbar; SGI & brvbar;
Linux & brvbar; Dynix>

3. Cancel node Cataloguing
DB2 uncatalog node <node_name>
 

4. view the system database directory
DB2 list database directory
 

5. view the local database directory
DB2 list database directory on <drive letter>
 
If the database is in the local database directory but not in the system database directory, you can right-click <database> in the control center and choose add, enter the name of the database to be added or click the refresh button to select a database. After joining the database, you can access the database.

6. cataloguing Database
DB2 catalog database <db_name>
<Db_alias> at node <node_name>
 

7. Cancel database Cataloguing
DB2 uncatalog database <db_name>
 

8. Test the remote database connection.
DB2 connect to <db_alias> User
<User_id> using <password>
 

9. Any user can set the default mode for a specific database connection by setting the current schema special register. The initial default value is the permission ID of the current session user.

Set schema = can be used interactively or in applications. If you bind a package with the dynamicrules bind option, this statement does not work. This statement is not under transaction control.

10. code page settings

Set character sets when creating databases
Create database <db_name> using
Codeset <codeset> territory <territiry>

 

Example:
Create Database dbtest using codeset
IBM-437 (territory us)

You can also set the code page of the entire database. In Win2000/NT/XP, add the variable db2codepage = to my computer --> properties --> advanced --> environment variable, for example: db2codepage = 437 or db2codepage = 1386. Alternatively, enter db2set db2codepage = 1386 in the IBM DB2 command window. After the settings, restart DB2 to take effect.

11. migration of data from earlier DB2 versions to later versions

First, import the data backup of earlier versions to the database of later versions using the restoration function, and then enter the following in the Command window:
DB2 migrate database <db_name>
 

12. Access the table when the table name or mode contains quotation marks

Command window:
DB2 select * From \ "tabschema \". \ "tabname \"
Command Line processor: DB2 => select * from "tabschema". "tabname"

13. Export the table structure of the database to generate the DDL File

Command window:
Db2look-D <db_name>-e-c-o <file_name>

14. Execute the script file

Command window:
DB2-tvf <file_name>

15. Code Page Conversion

16. Obtain the current DB2 version

Select * From sysibm. sysversions
 

17. Restrictions on modifying fields in the DB2 table?

Only the varchar2 type can be modified and can be increased or not decreased.
Alter table <tb_name> alter Column
<Col_name> set data type varchar (size)
 

18. How to view the table structure?
Describe table <tb_name>
Or
Describe select * from <schema>. <tb_name>
 

19. How to quickly clear a large table?
Alter table table_name active not
Logged initally with empty table
 

20. How can I view the stored procedures of a database?
Select * From syscat. Procedures

 

21. How to view table constraints?
Select * From syscat. Checks
Where tabname = <tb_name>
 

22. How do I view the complete reference constraints of a table?
Select * From syscat. References
Where tabname = <tb_name>
 

23. How do I know the bufferpools status?
Select * From syscat. bufferpools
 

24. How can I view and modify instance and database configuration parameters in the command line?
View instance configuration parameters:
DB2 get dBm cfg
Modify instance configuration parameters:
DB2 update dBm CFG using parameter name New Value
Modify database configuration parameters:
DB2 update dB CFG
<Db_name> new value of using parameter name
 

25. How to modify the buffer?

Add a buffer:
Create bufferpool <buf_name> size
<Number of pages> [pagesize 4096]
{[Not] extended storage}
 
Modify the buffer zone:
Alter bufferpool <buf_name> size
<Number of pages> {[not] extended storage}
 
Delete a buffer:
Drop bufferpool <buf_name>

If the buffer size is set to-1, the number of pages of the buffer pool is determined by the buffpage parameter configured by the database.

Note: The Database Configuration Parameter buffpage only applies to the buffer pool with the buffer size set to-1.

26. How to Use in/not in without using the select clause when multiple fields exist?
Select * From tabschema. tabname where
(Cola, COLB, colc) [not] In (values
(Valuea1, valueb1, valuec1 ),
(Valuea2, valueb2, valuec2 ),
... (Valuean, valuebn, valuecn ))
 

27. view the applications currently connected to the database
DB2 list application [show detail]
 

28. How to confirm the consistency of the DB2 database
Db2dart <db_name>/DB

/DB indicates checking the consistency of the entire database

29. test the performance of SQL statements
Db2batch-D <db_name>-F <file_name>
[-A userid/passwd] [-r <outfile_name>]
 
-R indicates that the query result is output to a file.
 

30. Export the data of a table
Export to <derectry> <filme>

For example, export a user table.
Export to c: \ User. ixf of ixf select * from user
 

31. Import Data
Import from
 
For example, import a user table. You can create a new table directly during import. If the table exists, you can use insert or update.

Execute import:
Import from c: \ User. ixf of ixf
[Create/insert into/update] tablename