< reprint >DB2 Common commands

1. Starting and stopping the database
Db2start --Start
db2stop [Force] --Stop

2. Connection to the database, disconnection
DB2 Connect to DBName [user UserID using PWD] --Establishing a connection
DB2 Connect RESET/DB2 Disconnect current --disconnect

3. Creation and deletion of instances
Db2icrt < Instance name > --Create DB2 instance
Db2idrop < Instance name > --delete DB2 instance
Set DB2INTANCE=DB2 --Sets the current DB2 instance
Db2ilist --show instances owned by DB2

4. Cataloging of nodes, databases
DB2 list node directory --view local node directory
DB2 Catalog TCPIP node <node_name> remote --cataloging a TCP/IP node
DB2 uncatalog node <node_name> --Canceling a node catalog
DB2 catalog DATABASE <db_name> as <db_alias> at node <node_name> --Catalog Database
DB2 uncatalog database <db_name> --de-catalog

5. Table space creation, deletion
DB2 Create Bufferpool stmabmp IMMEDIATE SIZE 25000 PAGESIZE 8K --Creating buffer pool Stmabmp
DB2 Drop tablespace stmabmp --delete table space
DB2 CREATE REGULAR tablespace STMA PAGESIZE 8 K MANAGED by SYSTEM | DATABASE
USING (' D:\DB2Container\Stma ') Extentsize 8 OVERHEAD 10.5
Prefetchsize 8 transferrate 0.14 bufferpool stmabmp DROPPED TABLE RECOVERY OFF
--Create a System/database management table space under D:\DB2Container\Stma STMA
DB2 Backup DATABASE alias tablespace table space name [ONLINE} to Media name -Backup of table space
DB2 List tablespaces Show Detail --list so tablespace information
DB2 list tablespace containers for Spaceid --listing container details for the corresponding table space

--Modify Table space

Alter TABLESPACE tablespace name RESIZE (FILE ' full container name ' Changes the size of the container)

ALTER tablespace tablespace name EXTEND (FILE ' full container name ' ready to increase size) --can be actual size or number of pages

eg:DB2 "Alter tablespace htdc_index EXTEND (file '/home/db2admin/dbback/db2containner/htdc/index/htdc_index ' 1024M) ' --(Unix system) will DMS Htdc_index in the enlarged 1G

6. Creation and deletion of databases
DB2 Create DB DBName [using CodeSet GBK Territory CN] --Creating a database
DB2 Drop DB DBName --delete database
DB2 List DB Directory --List all database directories (including databases for remote catalogs)

DB2 list DB directory on location -such as C: Under Windows,/home/db2inst1 under UNIX (local database)

DB2 list Active Databases --Lists the active database and number of connections

7. Creation and deletion of tables
DROP TABLE TableName; --Delete table
CREATE TABLE TableName
(Field name data type,
PRIMARY KEY (field name)
) in Space1 INDEX in Space2; --Create a table (tablespace Space1 store data, Space2 Store index)

ALTER TABLE TableName VOLATILE cardinality; --Set table TableName as a volatile table

DB2 list tables;
DB2 list tables for User/all/system/schema SchemaName [show detail];
--List tables in the database if no parameters are specified, the default is to list the current user's table

DB2 describe TABLE TableName;

DB2 describe SELECT * from tables; --View table structure

8. Creation and deletion of indexes
DROP INDEX index_name; --Delete index
CREATE INDEX index_name on TableName (F1); --Create an index on the F1 field of the table tablename index_name
DB2 describe indexes for TABLE TableName; --View index of table tablename

DB2 describe indexes for TABLE TableName Show detail;
DB2 SELECT Indname,colnames from syscat.indexes WHERE tabname = ' TableName ';

9. Creation and deletion of views
DROP VIEW ViewName; --delete view
CREATE VIEW ViewName (x,xx) as Selectx,xxfrom TableName wherexxx; --Create a view

10. Database backup and recovery (see the IBM DB2 database backup and recovery article)

--Offline backup
DB2 Force applications All--disconnect all connections

DB2 Force application (H1,H2 ... ) --kill the process connected to the database you want to back up
DB2 Backup DB DBName [to D:\db2_backup] [use TSM] -Offline backups
--Online, incremental backup
--Premise: Change database Parameters Logretain, UserExit, Trackmod is on, after the database is in the backup pending state, to fully offline backup database once, then can be online, online incremental backup.
DB2 Update DB CFG using Logretain on UserExit in Trackmod on -Enable related parameters

--Archive path
DB2 UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 disk:d:\db2\ IMMEDIATE

--logarchmeth1 parameter changed to TSM
DB2 UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 TSM IMMEDIATE
DB2 Backup DB DBName [to D:\db2_backup] [use TSM] -Offline backups
DB2 Backup DB DBName online [to D:\db2_backup] [use TSM] -Online Line Backup
DB2 Backup DB DBName online incremental [to d:\db2_backup] [use TSM] -Online incremental backup

--Database recovery

DB2 Restore DB DBname [incremental] [from D:\db2_backup][use TSM] taken at YYYYMMDDHHMMSS

--Recovery (time stamp: YYYYMMDDHHMMSS)
DB2 list History backup [since YYYYMMDDHHMMSS] all for DBName

--view backup of [from YYYYMMDDHHMMSS] to dbname
DB2 Rollforward Database DBName to END of LOGS and complete Noretrieve--Resume pending data to roll forward state
DB2 Rollforward DATABASE DBName to END of LOGS and complete OVERFLOW LOG PATH ("C:\DBName.0\SQLOGDIR")

DB2 CONNECT to DBName
DB2 Prune history YYYYMMDDHHMMSS --deleting backup records before dbname YYYYMMDDHHMMSS

11. Export and import of data
--Export
DB2 EXPORT to D:\TableName.txt of the del SELECT * from SCHEMA. TableName --Text Format

DB2 EXPORT to D:\TableName.csv of the del SELECT * from SCHEMA. TableName --csv can be converted to Excel
DB2 EXPORT to D:\TABLENAME.IXF of IXF SELECT * from SCHEMA. TableName

--Export data (IXF Integrated Universal Interchange Format)
DB2 EXPORT to "D:\TableName.data" of IXF MESSAGES "D:\TableName.msg" select * from SCHEMA. TableName;
--Import
DB2 IMPORT from D:\TableName.txt to Del INSERT into SCHEMA. TableName
DB2 IMPORT from "D:\TableName.data" of IXF [MESSAGES "D:\TableName.msg"] [Commitcount +] insert/create into SCHEMA. TableName;
DB2 IMPORT from "D:\TABLENAME.IXF" of ixf [Commitcount +] insert/insert_update/create/replace/replace_create into SCHEMA. TableName;

IMPORT from file_name of File_type MESSAGES message_file
[INSERT | Insert_update | REPLACE | Replace_create | CREATE]
Into Target_table_name

The *insert option inserts the imported data into the table. The target table must already exist.
*insert_update inserts data into a table, or updates a row in a table that has a matching primary key. The target table must already exist and a primary key is defined.
The *replace option deletes all existing data and inserts the imported data into an existing target table.
* When using the replace_create option, if the target table already exists, the import utility deletes the existing data and inserts the new data, just like the REPLACE option. If the target table is not yet defined, the table and its associated indexes are created first, and then the data is imported. As you might imagine, the input file must be a file in the PC/IXF format, because that format contains a structured description of the exported table. If the target table is a parent table that is referenced by a foreign key, you cannot use Replace_create.
The *create option first creates the target table and its index, and then imports the data into the new table. The only supported file format for this option is PC/IXF. You can also specify the name of the table space where the new table is located (in datatbsp, INDEX in indtbsp).

12. Compilation and operation of stored procedures
DB2 [email protected]-VF PROCEDURENAME.SQL/.DB2 --Compiling
DB2 Call ProcedureName --run

13. batch Files
DB2-TVF Filename.sql

14. Quickly clear and load large table data
ALTER TABLE TableName Activate not logged initially with empty TABLE; --Do not remember the log

DECLARE C1 CURSOR for Selectxxxxfrom TableName1;
LOAD from C1 of the CURSOR INSERT into TableName2 (xxxx) nonrecoverable; --Do not remember the log

DB2 LOAD QUERY TABLE SCHEMA. TableName; --View the target table load status (loading/normal)

15. Optimize the table
Select ' Runstats on table db2admin. ' | | RTrim (name) | | ' and indexes all; '
From Sysibm.systables
where creator = ' db2admin ' and Type = ' T '; --Get optimization statements

DB2 runstats on table Db2admin. TableName and indexes all; --Optimization statements

16. Application Connection
DB2 list application (s) [for DB DBName] [show detail] --Returns information about the currently connected application
DB2 Force application (H1 [, H2,.., HN]) --disconnects from a particular application based on the handle number
DB2 Force application All-disconnects all applications from the database
DB2 Terminate --end Command Line Dialog

17. Set the federated database to be available (the default federated database is unavailable)
DB2 update dbm CFG using federated Yes

18. Create a temporary table space
DB2 CREATE USER Temporary tablespace stmaspace PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2_TAB\STMASPACE. F1 ' 10000) extentsize 256

19. Create a temporary table
DECLARE GLOBAL Temporary table statement to define temporary tables. DB2 temporary tables are session-based and are isolated between sessions. When the session ends, the data for the temporary table is deleted and the temporary table is implicitly removed. The definition of a temporary table does not appear in Syscat.tables.
eg: DECLARE GLOBAL Temporary TABLE SESSION. TEMP_K_CIG (Cig varchar) with REPLACE not logged on commit preserve rows;

20. Modify the log file size, number
DB2 UPDATE DB CFG for DBName USING LOGFILSIZ 6000; --Log file size
DB2 UPDATE DB CFG for DBName USING LOGPRIMARY 5; --Number of log files
DB2 UPDATE DB CFG for DBName USING LOGSECOND 25; --Number of auxiliary log files

21. How do I restart the database?
DB2 RESTART DB DBName; --Restart the database
DB2 ACTIVATE DB DBName; --Activating the database
DB2 DEACTIVATE DB DBName; --Stop database

22. View the error code
DB2? SQL * * * eg:DB2? SQL803
DB2? Db2-command --help with specifying commands
DB2? Help--Instructions for reading the aid screen
DB2? Options --Help on all command options

23. How to turn off the log for a table
ALTER TABLE table_name ACTIVE not logged inially

24. Test the execution performance of SQL
db2batch-d db_name-f select.sql-r benchmark.txt-o P3 --select.sql is a SELECT statement written in the file

25. View the execution status of the current app number
DB2 get snapshot for application Agentid 299 |grep Row

26. How to modify the buffer pool
DB2 alter Bufferpool IBMDEFAULTBP size 10240

27. How to know the data type of DB2
SELECT * FROM Sysibm.sysdatatypes

28. How to know the Bufferpools condition
SELECT * FROM Sysibm.sysbufferpools

29. Query the User table
SELECT * from SYSIBM. Systables WHERE creator= ' USER '

30. How to know the current DB2 version
SELECT * FROM Sysibm.sysversions

31. How to know the status of Tablespace
SELECT * FROM SYSIBM. Systablespaces

"SQL1032N does not issue a command to start the database manager." sqlstate=57019 "solution
(1). License expires, under the command line with Db2licm-l to see if it expires;
(2). The user name or password used to start the service is wrong, (for example, we changed the password for the login system), workaround (Windows System): Modify the user name or password of the login in the related DB2 service options properties in services, management tools, Control Panel.

33. Table space, table size calculation

tablespace : Page size (bytes) * Total pages
table : (1): After runstats, use select Npages from syscat.tables where tabname= ' TableName ' to npages, then npagesx table Space page size
(2): In the control center, select the table you want to view, select "estimate size" to view, the index size is the same.

34. View a table in a pattern

DB2 list tables for schema <schema name>

Or

All table information are stored in Syscat.tables view
DB2 SELECT * from Syscat.tables where Tabschema=<schema name>
or simply
DB2 SELECT * FROM Syscat.tables ORDER by Tabschema, tabname

Similarly, all column information are stored in Syscat.columns
DB2 SELECT * from Syscat.columns where tabname=<table name>

35. View stored Procedure Information

SELECT * from SYSCAT. procedures;
--See the name, number, settling time, etc. in db2admin mode
SELECT Procname,procedure_id,create_time,text
From SYSCAT. Procedures
where procschema= ' Db2admin '

36. View table, index, tablespace information in system tables

--The name of the index, the owning table, the table space
SELECT Name,creator,tbname,tbcreator,colnames,colcount,tbspaceid,
Uniquerule,iid,create_time
From Sysibm.sysindexes [WHERE NAME like ' idx_% '];
--Index information
SELECT * from Sysibm.sysindexcoluse [where indname like ' idx_% ']

--Table information
SELECT Name,creator,colcount,tbspace,index_tbspace,ctime
From Sysibm.systables [WHERE NAME like '%2007% '];

SELECT * from Syscat.tables;
SELECT * from Sysibm.systables;

--Table space information
SELECT * from Sysibm.systablespaces;
SELECT * from Syscat.tablespaces;

--Find the Database management table space (DMS)
SELECT tbspace from syscat.tablespaces WHERE tbspacetype = ' D ';

--View information
SELECT * from Sysibm.sysviews;
--View the table's data table space, index, Index table space
SELECT C.tbname,c.tbspace,c.indname,d.tbspace
From
(SELECT a.name tbname,a.tbspace tbspace,b.name Indname,b.tbspaceid Tbspaceid
From Sysibm.systables A,
Sysibm.sysindexes b
WHERE A.name=b.tbname [and a.name like ' k_% ']
) as C left JOIN Sysibm.systablespaces D
on c.tbspaceid = D.tbspaceid;

Or
SELECT c.tbname table name, c.tbspace data table space, C.indname Index, d.tbspace Index Table space
From
(SELECT a.name tbname,a.tbspace tbspace,b.name Indname,b.tbspaceid Tbspaceid
From Sysibm.systables a JOIN
Sysibm.sysindexes b
On A.name=b.tbname [and a.name like ' k_% ']) as-C left JOIN sysibm.systablespaces D
on c.tbspaceid = D.tbspaceid;

37.db2look Exporting Script Information

Can be used to export the table, index, attempt, save, trigger and other scripts, can be used under the command line Db2look? View the appropriate parameter information.
Such as:
db2look-d dbtest-z db2admin-e-C >db2look_db2admin.sql
db2look-d dbtest-z db2admin-e-c-o db2look_db2admin.sql
db2look-d dbtest-z db2admin-i db2admin-w db2icss-e-c-o db2look_db2admin.sql

ALTER TABLE name alter COLUMN "field name"
SET DATA TYPE VARCHAR (200);

< reprint >DB2 Common commands