IBM/DB2 Common Command Set ZZ

1, close DB2.
Db2stop or db2stop force.
Write two of the scripts in general, avoiding the use of Db2stop force commands, such as:
Db2stop
Db2stop Force

2, start DB2
Db2start

3, create the database
DB2 Create DB
or DB2 create DB using CodeSet GBK territory CN

4, delete the database
Be careful to do this.
DB2 Drop DB
If you cannot delete, disconnect all database connections or restart DB2.

5, disconnect the database
DB2 Force application All

6, connecting to the database
DB2 Connect to user using

7, disconnect the database
Disconnect Current Database connection: DB2 connect reset
Or: DB2 Disconnect current
To disconnect all database connections:
DB2 Disconnect All

8, back up the database
DB2 Backup DB
Note: You need to disconnect the database before you perform the above command

9, restore the database
DB2 Restore DB

10, export the data file
Db2move Export [-sn < mode name, generally db2admin>] [-tn < table name, multiple comma separated;]

11, Import data file
Db2move Import

12, listing all db in the database
DB2 List DB Directory

13, enter DB2 command environment
Executing in "Run": db2cmd

14, get DB2 Database management configuration environment information
DB2 get dbm CFG

15, get DB2 a database Database management configuration environment information
DB2 Get DB CFG for
Or: Perform DB2 get db CFG after connecting to a database.

16, set the federated database to be available (default federated database is unavailable)
DB2 update dbm CFG using federated Yes

17, change the size of the DB2 log space
Note: The following command is designed to prevent DB2 databases from using hard disk space, only for developers on their own machines
DB2, if it is a server, the parameters need to be modified.
DB2 UPDATE DB CFG for USING logretain off logprimary 3 logsecond 2 logfilsiz 25600;
If the page size is 4KB, the above command creates 3 100M log files, taking up 300MB of hard disk space.
25600*4kb=102400kb.

18, create a temporary table space

DB2 CREATE USER Temporary tablespace stmaspace PAGESIZE K MANAGED by DATABASE
USING (FILE ' D:db2_tabstmaspace. F1 ' 10000) extentsize 256

19, get the database Manager snapshot data
Db2–v Get snapshot for dbm

20, show the process number
DB2 List Applications Show Detail

21, investigating errors
Sqlcode: Product specific error code;
SQLSTATE:DB2 series of common error codes, in line with the Iso/ansi 92SQL standard.
Investigating SQLCODE:DB2? sql1403n
Investigating SQLSTATE:DB2? 08004

22, create a table space
REM Create buffer pool space 8K
DB2 Connect to gather
DB2 CREATE Bufferpool stmabmp IMMEDIATE SIZE 25000 PAGESIZE 8K
REM Create tablespace: STMA
REM must confirm that the path is correct
REM D:DB2CONTAINERSTMA
DB2 Drop Tablespace STMA
DB2 CREATE REGULAR tablespace STMA PAGESIZE 8 K MANAGED by SYSTEM USING
(' D:db2containerstma ') Extentsize 8 Overhead 10.5 prefetchsize 8 transferrate 0.14
Bufferpool Stmabmp dropped TABLE RECOVERY off
DB2 Connect Reset

23, restore the pending data to the roll forward state
DB2 Rollforward DATABASE TestDB to end of LOGS and COMPLETE Noretrieve

24, Backup table space
BACKUP DATABASE YNDC tablespace (USERSPACE1) to "D:temp" with 2 buffers BUFFER
1024 PARALLELISM 1 without prompting
25, create the DB2 tool database

DB2 Create tools Catalog SysTools Create new Database Toolsdb

26. How to make incremental/differential backups
Increment: The last full backup to the additional data portion between this backup;
Difference (Delta): From the last backup (possibly full, incremental, or differential) to this backup
increased data portion;

27, UPDATE STATISTICS for all tables
Db2-v Connect to Db_name
Db2-v "Select Tbname, Nleaf, Nlevels, Stats_timefrom sysibm.sysindexes"
Db2-v reorgchkupdate statistics on table all
Db2-v "Select Tbname, Nleaf, Nlevels, Stats_timefrom sysibm.sysindexes"
Db2-v Terminate V

28, running statistics on a table
Db2-v Runstatson Table Tab_nameand Indexes all

29, to see if the database has been executed RUNSTATS
Db2-v "Select Tbname, Nleaf, Nlevels, Stats_timefrom sysibm.sysindexes"


30, change the size of the buffer pool
In the buffer pool, when the Syscat.bufferpools npages is-1, the configuration parameters of the database are Bufferpage
Controls the size of the buffer pool.

Change the value of the npages to-1 command:
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

To change the database configuration parameters bufferpages commands are as follows:
DB2-V Update db cfgfor dbnameusing BUFFPAGE bigger_value
Db2-v Terminate
31, view the list of database monitoring contents
DB2-V Get Monitor Switches

32, open a database monitoring content
DB2-V Update monitor switches using Bufferpoolon

33, get the database snapshot
Db2-v get snapshot to all databases > Snap.out
Db2-v get snapshot for dbm>> snap.out
Db2-v get snapshot to all bufferpools>> snap.out
Db2-v Terminate

34, reset the database snapshot
Db2-v Reset Monitor All

35, calculate buffer pool hit ratio
Ideally, the buffer pool hit more than 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 DB2 instance
Db2idrop < Instance name >

38, set the current DB2 instance
Set DB2INTANCE=DB2

39, showing DB2-owned instances
Db2ilist

40, restore offline incremental backup of the database command
DB2 RESTORE DATABASE yndc incremental AUTOMATIC from D:BACKUPAUTOBAKDB2 taken at
20060314232015

41, create the sample database
On UNIX platforms, using: Sqllib/bin/db2sampl
On the WINDOWS,OS/2 platform, use: Db2sampl e,e is an optional parameter that specifies the drive that will create the database;

42, listing all the 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 Sample
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 to replace into table1 COPY NO without prompting"
DB2 "Load from AA2.IXF of IXF to replace into table2 COPY NO without prompting"
DB2 Connect Reset V