DB2 Common Commands

DB2 Common Commands
First, the basic article
1. DB2 Connect to < database name >--Connect to local database name
DB2 Connect to < database name > user < username > Using < password >--connect to the remote database
2. DB2 force application All--forcing all applications to disconnect from the database
3. DB2 Backup DB db2name< database name >--Backing up entire database data, DB2 restore DB <db2name>--Restoring database
4, DB2 list application--View all connections (need to connect to a specific database to view)
5, Db2start--Start the database, Db2stop--Stop the database
6. Create databases < database name > Using codeset Utf-8 Territory CN--Create database with UTF-8 encoding
7. DB2 Catalog Command
DB2 catalog TCPIP node < contact name > Remote < address > server < port number >--Mapping a remote database to a local contact is typically 50000
DB2 Catalog DB < remote database name > as < contact name > at node PUB11--remote database name to local contact
DB2 Connect to < contact name > user < username > Using < password >--connect local contact to access remote database
8. Database Export
db2look-d < database name >-u < user >-e-o < script name >.sql--Export the table structure of the database, where user space is generally db2admin/db2inst1
db2look-d < database name >-u < user >-T < table 1> < table 2>-e-o < script name >.sql--Export the table structure of table 1 and table 2 in the database
db2look-d uibs-u db2inst1-e-o/home/db2inst1/uibsddl2013821.sql
Db2move < database name > Export--Exporting database data
Db2move Uibs Export/home/db2inst1/uibs20130906.sql
Db2move < database name > EXPORT-TN < table 1>,< Table 2>--Export tables and table data in the database
9. Database Import
DB2-TVF < script name >.sql--Import the above exported table structure into the database table structure
Db2move < database name > Load-lo Replace--Import the data exported from "Db2move < database name > export" above into the database and replace the same data
In the actual use of the process, if you use the DB2 self-increment primary key, you need to use by default, not always, the function is the same, but it is very convenient for data migration!
10, DB2 connect reset or DB2 terminate--disconnect from the database
11, Db2set db2codepage=1208--Modified page encoding is 1208
12. DB2 describe table < name >--View table structure
13. DB2 List Tables--View all table structures in the database
14. DB2 List tablespaces--List table space
15. Fetch first rows only--list the top 10 data in the table
16, DB2 Describe indexes for table Paccount Show detail--View table index
17, DB2 "SELECT * from Paccount" > e:pacountdata.txt--query result redirect
18, select Nextval for Maccessjnlno from Sysibm.sysdummy1; --The value of the query sequence
Select Prevval for ereceiptnumseq from Sysibm.sysdummy1;
Example: SELECT * FROM < table name > fetch First rows only
First, load the data:
1, loaded with default delimiter, default to "," number
DB2 "Import from Puser.txt of del insert INTO Puser"
2. To specify the delimiter "|" Load
DB2 "Import from Puser.txt of Del Modified by coldel| INSERT INTO Puser "
DB2 "Import from E:/rule.txt of del insert into rule"
Second, unload data:
1. Unload all data in a table
DB2 "Export to Puser.txt of del select * from Puser"
DB2 "Export to Puser.txt of Del Modified by coldel| SELECT * FROM Puser "
2. Unloading data in a table with conditions
DB2 "Export to Puser.txt of del select * from Puser where brhid= ' 907020000 '"
DB2 "Export to Cmmcode.txt of del select * from Cmmcode where codtp= ' 01 '"
Third, query data structure and information:
DB2 "SELECT * FROM Puser"
DB2 "SELECT * from Puser where brhid= ' 907020000 ' and oprid= ' 0001 '"
DB2 "Select OPRID,OPRNM,BRHID,PASSWD from Puser"
Iv. Delete data from the table:
DB2 "Delete from Puser where brhid= ' 907020000 ' or brhid= ' 907010000 '"
Five, modify the table data:
DB2 "Update svmmst set prtlines=0 where brhid= ' 907010000 ' and joww= ' 02 '"
Vi. joining the database
DB2 Connect to Spdb
VII. Clearing Database joins
DB2 Connect reset Disconnects the database
DB2 Terminate disconnecting the database
DB2 force applications all disconnects all database connections
Database Optimization Commands:
Reorg, Runstats
As the database is used over time, the data space becomes more and more large. Some of the deleted data is still stored in the database, occupying data space, affecting system performance. Therefore, it is necessary to run reorg, runstats commands periodically, purge deleted data, and optimize data structures.
DB2 reorg Table Name
DB2 runstats on table name with distribution and indexes all
Because the table to optimize more, so in the/ww/bin directory provides a SH program runsall, can be run at the end of the day, the operation of Runsall, the database optimization.
About the use of Db2move
Previously used Db2move to the data, but feel that there are some restrictions on the use. So sometimes you write your feet backwards. These days because of the BI project upgrade, testing the time to refactor the database. The main is to modify the table schema and tablespace, the more cumbersome way is
1> run Db2look on the source system to generate the DDL script. or use db2cc to generate a script with a graphical interface;
2> Modify the information about schema and tablespace in the script;
3> Run the modified script on the target system;
4> using SQL to generate export, import, or load batch scripts for data migration;
5> actual operation, the use of load may occur when the table integrity constraints, need to execute set integrity for <tablename> immediate checked eliminate the status of the table check pending;
Now say the steps implemented with Db2move, usually the db2move operation is used in same schema case.
Db2move uibs export-u db2inst1-p db2inst1
Db2move r_uibs export-u db2inst1-p db2inst1
Bulk Export
Observe the generated file, which has a db2move.lst file with the following contents:
!" Tkpm62 "." OPB_ANALYZE_DEP "!tab1.ixf!tab1.msg!
I tried to change the content into
!" TKPM "." OPB_ANALYZE_DEP "!tab1.ixf!tab1.msg!
"TKPM" is my schema name in the target system, I want to see if this can indirectly implement the schema name conversion
Db2move uibs import-u ww-p Yangyang
Succeeded, as I had expected.
However, there are some undesirable places, while running Db2move, the system will automatically build the table, but also limited to tables, the views and indexes on the table need to be created on their own)
So you need to use scripts to create indexes and views, as well as authorization, after the data is finished. This prevents damage to the table's integrity.
DB2 Backup and Recovery (restore) Database methods
A BACKUP Database
1. Offline Full backup
1), first ensure that no users use DB2:
DB2 list applications for DB sample
2), shut down the database and reboot to disconnect all connections:
Db2stop Force
Db2start
3), execute Backup command: (use TSM as backup media)
DB2 backup DB Sample Use TSM
The backup succeeds and a timestamp is returned.
4), check the backup success:
DB2 List History Backup all for sample can see more records of this backup.
The DB2ADUTL query command can also see the return value.
5), Remark:
The backup command is performed on the primary node (the node in the catalog table space), and this is done for the other node.
2. Online Backup:
1), first open the database configuration parameters that support Online backup:
DB2 Update DB CFG for sample using UserExit on Enable user exit
DB2 Update DB CFG for sample using Logretain on enable archive logging
DB2 Update DB CFG for sample using TRACKMOD on Enable incremental backup feature
(requires each node to be set separately)
When these parameters are turned on, the database is in backup pending state and requires an offline full backup of the database. Make an offline full backup, and refer to the commands above.
2), the online Backup command is as follows:
DB2 Backup DB Sample online use TSM
The backup succeeds and returns a timestamp.
3), you can also use DB2ADUTL and DB2 list history to view backup records.
4), remark:
Again, this is done for each node.
3. Online Incremental backup
1), when the necessary three parameters are turned on, do an incremental backup:
DB2 Backup DB Sample online incremental use TSM
The backup succeeds and returns a timestamp.
2), you can also use DB2ADUTL and DB2 list history to view backup records.
3), there is also a Delta backup:
DB2 Backup DB Sample online incremental delta use TSM
The difference between these two backups, similar to the incremental and cumulative methods of Oracle Exports, DB2 incremental corresponds to the cumulative mode of Oracle, DB2 's delta approach corresponds to Oracle's incremental approach.
4), remark:
Again, this is done for each node.
Ii. Recovery (Restore) database
1. Create a new database
In the General Administration tool, create the database (aliases are consistent with the original database alias).
2. Restore the database
1): Disconnect: Disconnect the network, cut off all customer connections, if not, restart first. (If a client connects to the DB2 server, recovery is not possible, including the control center)
2): Restore offline Backup:
Set
Your backup of that DB2 file in: C:\Temp
Your database name: dbname
Then the format of the backup/restore file corresponding to DB2 is this:
c:\temp\dbname.0\db2ctlsv\node0000\catn0000\20090706\hhmmss.001
3): Example
Backup file path: c:\temp\ipmdemo.0\db2ctlsv\node0000\catn0000\20090622\164642.001
Where, Backup date: 20090622 Backup files: 164642.001
New database name: Ipmdemo
Database file timestamp: 20090622164642
The RESTORE command is: DB2 RESTORE Database Ipmdemo from C:\Temp taken at 20090622164642
3. Restore Online Backup
1), recovery. The RESTORE command is: DB2 RESTORE Database dbname from C:\Temp taken at 20090622164642
DB2 RESTORE Database Uibs from e:/db2 taken at 20130320074838
DB2 Rollforward database Uibs to end of logs and stop
2), roll forward. The Roll Forward command is: DB2 Rollforward DB dbname to end of logs (or, all databases, control center, right-click, roll Forward)
3), stop roll forward. The Stop Roll Forward command is: DB2 Rollforward DB dbname Stop (or, all databases, control center,->dbname, right-click to stop roll forward)
Iii. Related Notes
1, recovery operation also has online and offline, the difference is like the operation of backup.
2, according to the table space backup and restore similar, add clause tablespace (tablespace-name) can. A table-space-level backup/restore operation requires that the database be in archive logs and enabled in incremental backup mode.
3. Only version recovery is done in the recovery example. If you have an image of an updated full backup and an incremental backup, you can restore it in turn (pay attention to using Db2ckrst's recommended recovery order and number of times), then roll forward.
http://www.iteye.com/topic/611797
Using catalog, loading remote database information into a local is generally a two-step operation:
The first step, the catalog server side of node, commands the following:
DB2 catalog TCPIP node Db2node remote hostname server Service_port
Where Db2node is a node name that you randomly start, the node name cannot be duplicated with the existing node name (can be viewed through DB2 lsit node directory), hostname can also be IP address,service_port as the port number. The node directory is used to store all connectivity information for the remote database.
The second step, the catalog remote DB2 database, the command is as follows:
DB2 Catalog Database db_name as alias_name at node Db2node.
Db_name refers to the name of the remote database, Alias_name is the client instance name, Db2node refers to the node! you specified above
DB2 catalog TCPIP node Db2node remote hostname server Service_port
DB2 catalog TCPIP Node r_uibs remote 10.102.90.1 Server 60000
DB2 Catalog Database Uibs as r_uibs at node R_uibs

DB2 Table Structure Changes

1. Add a field

ALTER TABLE [table_name] add [column_name] [Column_type]

2. Change the field type

ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] Set data type [Column_type]

Note: Changes to the field type are limited, such as changing the field to be larger than the previous type, if you want to change the size or change the decimal length, you must first drop the original column and then add it again.

For example, I want to change the column of a varchar (10) to varchar (6) or change the column of a decimal (16, 2) to Decimal (16, 4), and so on, all of which cannot be modified using the above statement, but also to a different type. You also need to drop the column first.

3. Remove the field

ALTER TABLE [TABLE_NAME] drop column [column_name]

Note: After dropping the field, it may cause the table query/insert operation to not execute, and the table needs to be reorg.

4. Add a default value for a field

ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] SET default [value]

5. Add a field with default values

ALTER TABLE [TABLE_NAME] Add column [column_name] [column_type] NOT NULL with default [value]

6. Set the default time for the field to the current time

ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] set default current date;

DB2 Common Commands