List of commonly used DB2 commands (1)

1. Create a database DB2_GCB

Create database DB2_GCB on g: ALIAS DB2_GCB
Using codeset gbk territory cn collate using system DFT_EXTENT_SZ 32

2. Connect to the database

Connect to sample1 user db2admin using 8301206

3. Create an alias

Create alias db2admin. tables for sysstat. tables;
Create alias DB2ADMIN. views for syscat. VIEWS
Create alias db2admin. columns for syscat. columns;
Create alias guest. columns for syscat. columns;

4. Create a table

Create table zjt_tables
(Select * from tables) definition only;
Create table zjt_views
(Select * from views) definition only;

5. Insert records

Insert into zjt_tables select * from tables;
Insert into zjt_views select * from views;

6. Create a view

Create view V_zjt_tables as select tabschema, tabname from zjt_tables;

7. Create a trigger

Create trigger zjt_tables_del
After delete on zjt_tables
REFERENCING OLD AS O
For each row mode DB2SQL
Insert into zjt_tables1 values (substr (o. tabschema,), substr (o. tabname ))

8. Create a unique index

Create unique index I _ztables_tabname
[Size = 3] ON zjt_tables (tabname );
9. view the table

Select tabname from tables
Where tabname = 'zjt _ TABLES ';

10. View Columns

Select SUBSTR (COLNAME, 1, 20) as column name, TYPENAME as type, LENGTH as LENGTH
From columns
Where tabname = 'zjt _ TABLES ';

11. view the table structure

Db2 describe table user1.department
Db2 describe select * from user. tables

12. view the table Index

Db2 describe indexes for table user1.department

13. view the view

Select viewname from views
Where viewname = 'v _ ZJT_TABLES ';

14. View Indexes

Select indname from indexes
Where indname = 'I _ ZTABLES_TABNAME ';

15. view the Stored Procedure

Select substr (PROCSCHEMA, 1, 15), SUBSTR (PROCNAME, 1, 15)
From syscat. PROCEDURES;

16. type conversion (cast)

Ip datatype: varchar
Select cast (ip as integer) + 50 from log_comm_failed

17. Reconnect

Connect reset

18. Database disconnection

Disconnect db2_gcb

19. view application

List application;

20. kill application

Force application (0 );
Db2 force applications all (force all applications to be disconnected from the database)

21. lock table

Lock table test in exclusive mode

22. Share

Lock table test in share mode

23. display all tables of the current user

List tables

24. list all system tables

List tables for system

25. display the current active database

List active databases

26. View Command Options

List command options

27. System database directory

LIST DATABASE DIRECTORY

28. tablespace

List tablespaces

29. tablespace container

LIST TABLESPACE CONTAINERS
Example: list tablespace containers for 1

30. display the user's Database Access Permissions

GET AUTHORIZATIONS