DB2 Common Commands Daquan

1. Establishing 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. Connecting to the database

Connect to Sample1 user db2admin using 8301206

3. Creating aliases

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 as

(SELECT * from tables) definition only;

CREATE TABLE Zjt_views as

(SELECT * from views) definition only;

5. Insert Record

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. Setting up triggers

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,1,8), substr (o.tabname,1,10))

8. Establishing a Uniqueness Index

CREATE UNIQUE INDEX I_ztables_tabname

[Size=3] On Zjt_tables (tabname);

9. View Table

Select TabName from tables

where tabname= ' zjt_tables ';

10. View Column

Select SUBSTR (colname,1,20) as column name, TYPENAME as type, length as

From columns

where tabname= ' zjt_tables ';

11. View Table Structure

DB2 describe table User1.department

DB2 describe select * FROM User.tables

12. View the index of a table

DB2 describe indexes for table user1.department

13. View View

Select ViewName from views

where viewname= ' v_zjt_tables ';

14. View Index

Select Indname from Indexes

where indname= ' i_ztables_tabname ';

15. View the storage process

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. Break Database connection

Disconnect DB2_GCB

View Application

LIST Application;

Kill application

FORCE application (0);

DB2 force applications All (forces all applications to disconnect from the database)

Lock table

Lock table test in exclusive mode

22. Share

Lock table test in Share mode

23. Display all tables for the current user

List tables

24. List all system tables

List Tables for system

25. Display the currently active database

List Active databases

26. View command Options

List command options

27. System Database Directory

LIST DATABASE DIRECTORY

28. Table Space

List tablespaces

29. Table Space Containers

LIST tablespace containers for

Example:list tablespace containers for 1

30. Show access rights to user databases

Get authorizations

31. Startup instance

Db2start

32. Stop instance

Db2stop

33. Table or view privileges

Grant Select,delete,insert,update on tables to user

Grant all on tables to user with Grant OPTION

34. Package Privileges

GRANT EXECUTE

On PACKAGE Package-name

to public

35. Mode privilege

GRANT Createin on SCHEMA schema-name to USER

36. Database Privileges

Grant Connect,createtab,dbadm on database to user

37. Indexing privilege

Grant control on index index-name to user

38. Information Help (?) XXXNNNNN)

Cases:? SQL30081

SQL Help (Description of SQL statement syntax)