DB2 Common Commands

1. Launch instance (DB2INST1):

Db2start

2. Stop the Instance (DB2INST1):

Db2stop

3. List all instances (DB2INST1)

Db2ilist

3-1. List the current instance:

DB2 Get Instance

3-2. List all databases:

DB2 List Database Directory

4. Look at the sample configuration file:

DB2 Get DBM Cfg|more

5. Update the Database Manager parameter information:

DB2 update dbm CFG using Para_name para_value

6. Create a database:

DB2 Create DB Test

7. View database configuration parameter information

DB2 get DB CFG for Test|more

8. Updating database parameter configuration information

DB2 Update DB CFG for test using Para_name para_value

10. Delete the database:

DB2 Drop DB Test

11. Connect to the database

DB2 Connect to test

12. List all tablespace details.

DB2 List tablespaces Show Detail

13. Query data:

DB2 SELECT * FROM TB1

14. Delete data:

DB2 Delete from TB1 where id=1

15. Create an index:

DB2 CREATE INDEX IDX1 on tb1 (ID);

16. Create a view:

DB2 CREATE VIEW VIEW1 as select ID from TB1

17. Query view:

DB2 SELECT * FROM View1

18. Node Cataloging

DB2 catalog TCP node Node_name remote SERVER_IP Server Server_port

19. Look at the port number

DB2 Get dbm Cfg|grep SVCENAME

20. Attaching the Test node

DB2 Attach to Node_name

21. View Local nodes

DB2 List Node Direcotry

22. Node anti-cataloging

DB2 uncatalog Node Node_name

23. Database Cataloging

DB2 Catalog DB db_name as Db_alias at node Node_name

24. View the catalog of the database

DB2 List DB Directory

25. Connect to the database

DB2 Connect to Db_alias user user_name using User_password

26. Database Anti-cataloging

DB2 Uncatalog DB Db_alias

27. Exporting data

DB2 Export to MyFile of IXF messages MSG select * FROM TB1

28. Import data

DB2 Import from MyFile of IXF messages msg replace into TB1

29. Export all table data for the database

Db2move Test Export

30. Building the definition of a database

db2look-d db_alias-a-e-m-l-x-f-o db2look.sql

31. Create a Database

DB2 Create DB Test1

32. Build Definition

DB2-TVF Db2look.sql

33. Import all data from the database

Db2move Db_alias Import

34. Reorganization Check

DB2 Reorgchk

35. Reorganization Table TB1

DB2 reorg Table TB1

36. UPDATE STATISTICS

DB2 runstats on table TB1

37. Back up the database test

DB2 Backup DB Test

38. Restore the database test

DB2 Restore DB Test

399\. Listing information for a container

DB2 list tablespace containers for tbs_id Show Detail

40. Create a table:

DB2 ceate table tb1 (ID integer not null,name char (10))

41. List all Tables

DB2 List Tables

42. Insert Data:

DB2 INSERT INTO TB1 values (1, ' Sam ');

DB2 INSERT INTO TB2 values (2, ' Smitty ');

. Establish 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. 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) definition only;

5. Inserting records

INSERT INTO Zjt_tables select * from tables;

INSERT INTO Zjt_views select * from views;

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

8. Establishing 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 Long

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. Interrupt Database Connection

Disconnect DB2_GCB

View Application

LIST Application;

. Kill Application

Force application (0);

DB2 force applications all (forcing all applications to disconnect from the database)

. Lock table

Lock table test in exclusive mode

22. Sharing

Lock table test in Share mode

23. Show all tables for the current user

List tables

24. List all the 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. Table Space

List tablespaces

29. Table Space Container

LIST tablespace CONTAINERS for

Example:list tablespace CONTAINERS for 1

30. Displaying access rights to the user's database

GET authorizations

31. Launch an instance

Db2start

32. Stopping an 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 Privileges

GRANT Createin on SCHEMA schema-name to USER

36. Database Privileges

Grant Connect,createtab,dbadm on the database to user

37. Indexing Privileges

Grant control on index index-name to user

38. Information Help (? XXXNNNNN)

Cases:? SQL30081

SQL Help (Description of SQL statement syntax)

Help statement

For example, the Help SELECT

SQLSTATE help (stating the status and category Code of SQL)

? SQLState or? Class-code

41. Change the password associated with the "Management Server"

Db2admin setid username Password

42. Create a SAMPLE database

Db2sampl

Db2sampl F: (Specify installation disk)

43. Using Operating system commands

! Dir

44. Convert data type (CAST)

SELECT EMPNO, CAST (RESUME as VARCHAR (370))

From Emp_resume

WHERE Resume_format = ' ASCII '

UDF.

To run a DB2 Java stored procedure or UDF, you also need to update the DB2 Database Manager configuration on the server to include the path to install the JDK on that machine

DB2 update dbm CFG using Jdk11_path d:sqllibjavajdk

TERMINATE

Update dbm CFG using Spm_name sample

46. Check the DB2 database management program configuration

DB2 get dbm CFG

DB2 Common Commands