DB2 Database Basic Operation Instruction 30 article

1. View the Local node directory

Command Window Input: DB2 (DB2 certified DB2 Training) List node directory

2. Cataloging a TCP/IP node

Command window: DB2 catalog TCPIP node remote server OSType

3. Canceling a Node catalog

DB2 uncatalog Node

4. View the system database (database training database certification) directory

DB2 List Database Directory

5. View the local database directory

DB2 List database directory on < drive letter >

A database in the local database directory that is not in the System database directory cannot be accessed, you can select < database in the control center > Right click to select Add, then enter the name of the database you want to add or click the Refresh button to choose a database to access after you join the database.

6. Cataloging database

DB2 catalog database as at node

7. Canceling the database catalog

DB2 Uncatalog Database

8. Test the connection to the remote database

DB2 Connect to user using

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

Set schema =

It can be used interactively by the user or in an application, and if the package is bound with the Dynamicrulesbind option, the statement will not work. This statement is not under transaction control.

10. Settings for code pages

Setting the character set when creating a database

Create DATABASE using CodeSet territory

Example: Create database dbtest using CodeSet IBM-437 territory US

You can also set up a code page for the entire database, in WIN2000/NT/XP, in My Computer---------------------- 1386. Or, after you enter Db2set db2codepage=1386 in the IBM DB2 Command window, the settings need to be restarted DB2 take effect.

11.DB2 low-version data to a higher-version migration

Import the low-version data backup using the recovery feature into the high-version database, and then enter DB2 migrate in the command window.

12. Accessing tables when the table name or pattern 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-e-c-O

14. Execute the script file

Command window: DB2-TVF

15. Conversion of code pages

16. Get the version of the current DB2 select * from Sysibm.sysversions

17.DB2 the field of the table is modified?

Only the VARCHAR2 type can be modified and can only be increased without reducing

ALTER TABLE ALTER COLUMN SET data type varchar (SIZE)

18. How do I view the structure of a table?

Describe table or describe select * from.

19. How to quickly clear a large table?

ALTER TABLE table_name ACTIVE not logged initally with EMPTY TABLE

20. How do I view the stored procedures for a database?

SELECT * from SYSCAT. Procedures

21. How do I view the constraints of a table?

SELECT * from SYSCAT. CHECKS WHERE tabname =

22. How do I view a table's referential full constraints?

SELECT * from SYSCAT. REFERENCES WHERE tabname =

23. How do I know bufferpools status?

SELECT * from SYSCAT. Bufferpools

24. How do I view the Modify instance and database configuration parameters at the command line?

View instance configuration parameters: DB2 get dbm CFG

Modify instance Configuration parameters: DB2 update dbm CFG using parameter name new value

View Database configuration parameters: DB2 get DB CFG for

Modify Database Configuration parameters: DB2 update db CFG for using parameter name new value

25. How do I modify the buffer?

Add buffer: Create Bufferpool size [pagesize 4096] {[NOT] EXTENDED STORAGE} Modify buffer: Alter Bufferpool size {[NOT] EXTENDED STORAGE }

Delete buffer: Drop Bufferpool

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

Note: The database configuration parameter buffpage only works for buffer pools with buffer size set to-1.

26. When multiple fields are not used by using the SELECT clause in/not in the SELECT * from Tabschema.tabname where (ColA, ColB, COLC) [not] in (VALUES (valueA1, VA LueB1, ValueC1), (valueA2, ValueB2, valueC2), ... (Valuean, VALUEBN, VALUECN))

27. View the apps currently connected to the database

DB2 list application [show detail]

28. How to confirm the consistency of the DB2 database

Db2dart/db/db means checking the consistency of the entire database

29. Export data from a table

Export to

such as: Export user table

Export to C:\USER.IXF of IXF select * from user

30. Import data

Import from

such as: Import user tables. You can create a new table directly when you import it. If the table exists, insert it with INSERT, or update with update

DB2 Database Basic Operation Instruction 30 article