Basic db2 knowledge

Connect to the database:
Connect to [database name] user [operation user name] using [Password]

Create a buffer pool (8 K ):

Create bufferpool ibmdefault8k immediate size 5000 PAGESIZE 8 K;
Create a buffer pool (16 K) (OA_DIVERTASKRECORD ):
Create bufferpool ibmdefault16k immediate size 5000 PAGESIZE 16 K;
Create a buffer pool (32 K) (OA_TASK ):
Create bufferpool ibmdefa3232k immediate size 5000 PAGESIZE 32 K;

Create a tablespace:

Create tablespace exoatbs in database partition group limit PAGESIZE 8 k managed by system using (/home/exoa2/exoacontainer) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL limit OVERHEAD 24.10 TRANSFERRATE 0.90 dropped table recovery off;

Create tablespace partition in database partition group ibmdefaultgroup pagesize 16 k managed by system using (/home/exoa2/snapshot) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT16K OVERHEAD 24.1 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

Create tablespace partition in database partition group ibmdefaultgroup pagesize 32 k managed by system using (/home/exoa2/Tables) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT32K OVERHEAD 24.1 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF;

Grant use of tablespace exoatbs to public;
Grant use of tablespace exoatbs16k to public;
Grant use of tablespace exoatbs32k to public;

Create System tablespace:

Create temporary tablespace partition in database partition group ibmtempgroup pagesize 8 k managed by system using (/home/exoa2/snapshot) EXTENTSIZE 32 PREFETCHSIZE 16 bufferpool partition overhead 24.10 TRANSFERRATE 0.90 dropped table recovery off;

Create temporary tablespace partition in database partition group ibmtempgroup pagesize 16 k managed by system using (/home/exoa2/snapshot) EXTENTSIZE 32 PREFETCHSIZE 16 bufferpool partition overhead 24.10 TRANSFERRATE 0.90 dropped table recovery off;

Create temporary tablespace partition in database partition group ibmtempgroup pagesize 32 k managed by system using (/home/exoa2/snapshot) EXTENTSIZE 32 PREFETCHSIZE 16 bufferpool partition overhead 24.10 TRANSFERRATE 0.90 dropped table recovery off;

1. Start the instance (db2inst1 ):

Db2start

2. Stop an instance (db2inst1 ):

Db2stop

3. list all instances (db2inst1)

Db2ilist

5. List the current instance:

Db2 get instance

4. view the sample configuration file:

Db2 get dbm cfg | more

5. Update the Database Manager parameters:

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. Update database parameter configuration information

Db2 update db cfg for test using para_name para_value

10. delete a database:

Db2 drop db test

11. Connect to the database

Db2 connect to test

12. list details of all tablespaces.

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 Cataloguing

Db2 catalog tcp node node_name remote server_ip server server_port

19. Check the port number.

Db2 get dbm cfg | grep SVCENAME

20. Test node attachment

Db2 attach to node_name

21. View local nodes

Db2 list node direcotry directory

22. node deserialization

Db2 uncatalog node node_name

23. Database Cataloguing

Db2 catalog db db_name as db_alias at node node_name

24. view the database catalog

Db2 list db directory

25. Connect to the database

Db2 connect to db_alias user user_name using user_password

26. Database deserialization

Db2 uncatalog db db_alias

27. Export 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 of the database

Db2move test export // db2move test export-u username-p password of the remote connection

30. Generate database Definitions

Db2look-d db_alias-a-e-m-l-x-f-o db2look. SQL
// Remote connection to db2look-d db_alias-a-e-m-l-x-f-o db2look. SQL-I username-w password
31. Create a database

Db2 create db test1

32. Generate Definition

Db2-tvf db2look. SQL

33. Import all data in the database

Db2move db_alias import

34. Reorganization check

Db2 reorgchk

35. reorganize the table tb1

Db2 reorg table tb1

36. update statistics

Db2 runstats on table tb1

37. Backup database test

Db2 backup db test

38. Restore database test

Db2 restore db test

399. List container Information

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 ');

. Create 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