DB2 common command set (2)

28. Run statistics on a table

Db2-v runstatson table TAB_NAMEand indexes all

29. Check whether RUNSTATS is executed on the database.

Db2-v "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"

30. Change the buffer pool size.

In the buffer pool, when the npages of syscat. bufferpools is-1, the Database Configuration Parameter bufferpage controls the buffer pool size.

Change the value of npages to-1:

Db2-v connect to DB_NAME

Db2-v select * from syscat. bufferpools

Db2-v alter bufferpoolIBMDEFAULTBP size-1

Db2-v connect reset

Db2-v terminate

The command to change the Database Configuration Parameter BufferPages is as follows:

Db2-v update db cfgfor dbnameusing BUFFPAGE bigger_value

Db2-v terminate

31. view the database monitoring content list

Db2-v get monitor switches

32. Open the monitoring content of a database.

Db2-v update monitor switches using bufferpoolon

33. Obtain database snapshots

Db2-v get snapshot for all databases> snap. out

Db2-v get snapshot for dbm> snap. out

Db2-v get snapshot for all bufferpools> snap. out

Db2-v terminate

34. Reset database snapshots

Db2-v reset monitor all

35. Calculate the buffer pool hit rate

Ideally, the buffer pool hit rate is above 95%. The formula is as follows:

(1-(buffer pool data physical reads + buffer pool index physical reads )/

(Buffer pool data logical reads + pool index logical reads) * 100%

36. Create a db2 instance

Db2icrt <Instance name>

37. delete a db2 instance

Db2idrop <Instance name>

38. Set the current db2 instance

Set db2intance = db2

39. Display db2 instances

Db2ilist

40. commands for restoring the offline Incremental backup database

DB2 restore database yndc incremental automatic from d: ackupautobakdb2 taken at 20060314232015

41. Create a sample database

On unix platforms, use: sqllib/bin/db2sampl

On windows, OS/2, run the following command: db2sampl e, e is an optional parameter, specifying the drive to create the database;

42. list all tables in the database

Db2 list tables

43. List the data structure of a table

Db2 describe table v_ro_role

44. add columns to the table

Alter table staff add column pnhone varchar (20)

45. data migration method 1

Export script example

Db2 connect to testdb user test password test

Db2 "export to aa1.ixf of ixf select * from table1"

Db2 "export to aa2.ixf of ixf select * from table2"

Db2 connect reset

Import script example

Db2 connect to testdb user test password test

Db2 "load from aa1.ixf of ixf replace into table1 copy no without prompting"

Db2 "load from aa2.ixf of ixf replace into table2 copy no without prompting"

Db2 connect reset v