Common DB2 commands

Common DB2 commands

Common DB2 commands
I. Basics
1. db2 connect to <Database Name> -- connect to the local database name
Db2 connect to <Database Name> user <user Name> using <password> -- connect to the remote database
2. db2 force application all -- force all applications to disconnect the database
3. db2 backup db db2name <Database Name> -- back up the entire database data and db2 restore db <db2name> -- restore the database
4. db2 list application -- view all connections (you need to connect to a specific database to view them)
5. db2start -- start the database and db2stop -- stop the database
6. create database <database Name> using codeset UTF-8 territory cn -- create a database and use UTF-8 encoding
7. db2 catalog command
Db2 catalog tcpip node <Contact Name> remote <remote database address> server <port number> -- map the remote database to the local contact, which is generally 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 <user Name> using <password> -- connect a local contact to access a remote database
8. Export Databases
Db2look-d <Database Name>-u <user>-e-o <Script Name>. SQL -- export the table structure of the database. The user space is usually db2admin/db2inst1
Db2look-d <Database Name>-u <user>-t <Table 1> <Table 2>-e-o <Script Name>. SQL -- export the table structures of tables 1 and 2 in the database
Db2look-d uibs-u db2inst1-e-o/home/db2inst1/uibsddl2013821. SQL
Db2move <Database Name> export -- export Database Data
Db2move uibs export/home/db2inst1/uibs20130906. SQL
Db2move <Database Name> export-tn <Table 1>, <Table 2> -- export table and table data in the database
9. Database Import
Db2-tvf <Script Name>. SQL -- import the exported table structure to the database table structure
Db2move <Database Name> load-lo replace -- import the data exported from "db2move <Database Name> export" to the database and replace the same data.
In actual use, if you use the db2 auto-incrementing primary key, you need to use by default instead of always. The function is the same, but this will be very convenient during data migration!
10. db2 connect reset or db2 terminate -- disconnect from the database
11. db2set db2codepage = 1208 -- modify the Page code to 1208
12. db2 describe table <table Name> -- View table structure
13. db2 list tables -- View All table structures in the database
14. db2 list tablespaces -- list tablespaces
15. fetch first 10 rows only -- list the first 10 pieces of data in the table
16. db2 describe indexes for table paccount show detail -- View table indexes
17. db2 "select * from paccount"> e: pacountdata.txt -- Query Result redirection
18. select nextval for MACCESSJNLNO from sysibm. sysdummy1; -- query the Sequence Value
Select prevval for ereceiptnumseq from sysibm. sysdummy1;
Example: select * from <Table Name> fetch first 10 rows only
1. load data:
1. load with the default separator. The default value is ",".
Db2 "import from puser.txt of del insert into puser"
2. load with the specified separator "|"
Db2 "import from puser.txt of del modified by coldel | insert into puser"
Db2 "import from e:/rule.txt of del insert into rule"
Ii. Uninstall data:
1. unmount 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. unmount data from a table with conditions
Db2 "export to puser.txt of del select * from puser where brhid = '000000 '"
Db2 "export to cmcode.txt of del select * from cmcodewhere codtp = '01 '"
Iii. query data structures and data:
Db2 "select * from puser"
Db2 "select * from puser where brhid = '000000' and oprid = '000000 '"
Db2 "select oprid, oprnm, brhid, passwd from puser"
4. Delete table data:
Db2 "delete from puser where brhid = '000000' or brhid = '000000 '"
5. Modify Table data:
Db2 "update svmmst set prtlines = 0 where brhid = '000000' and joww = '02 '"
6. Database Connection
Db2 connect to spdb
7. Clear database connections
Db2 connect reset disconnects the database
Db2 terminate disconnect Database
Db2 force applications all disconnect all databases
Database optimization command:
Reorg, runstats
When the database is used for a period of time, the data space will become larger and larger. Some deleted data is still stored in the database, occupying data space and affecting system performance. Therefore, you need to regularly run the reorg and runstats commands to clear Deleted Data and optimize the data structure.
Db2 reorg table name
Db2 runstats on table name with distribution and indexes all
Because there are many tables to be optimized, a sh program runsall is provided in the/ww/bin directory. runsall can be run after the business ends on the current day to optimize the database.
Usage of db2move
I used db2move to get data before, but I think there are some restrictions in use. So sometimes I write my own scripts to count down. In the past few days, due to the upgrade of the bi project, I want to reconstruct the database during testing. I want to modify the table schema and tablespace. The cumbersome method is
1> RUN db2look on the source system to generate the ddl script. Or use db2cc to generate scripts on the GUI;
2> modify the schema and tablespace information in the script;
3> run the modified script on the target system;
4> use SQL to generate Batch scripts for export, import, or load for data migration;
5> in practice, when using load, the table integrity constraints may be damaged. You need to execute set integrity for <tablename> immediate checked to remove the table check pending status;
Now let's talk about the steps to implement db2move. Usually, the db2move operation is used in same schema.
Db2move uibs export-u db2inst1-p db2inst1
Db2move r_uibs export-u db2inst1-p db2inst1
Batch Export
Observe the generated file. There is a db2move. lst file with the following content:
! "Tkpm62". "opb_analyze_dep "! Tab1.ixf! Tab1.msg!
I tried to change the content
! "Tkpm". "opb_analyze_dep "! Tab1.ixf! Tab1.msg!
"Tkpm" is the schema name in the target system. I want to see if the schema name can be converted indirectly.
Db2move uibs import-u ww-p yangyang
Succeeded, as I expected.
However, in some bad cases, the system will automatically create tables when running db2move, but it is also limited to tables. You still need to create views and indexes on the tables)
Therefore, you must use scripts to create indexes, views, and authorizations after importing data. This prevents damage to table integrity.
Db2 backup and recovery database Methods
I. backup Database
1. Full offline backup
1) first, make sure that no user uses db2:
Db2 list applications for db sample
2) Stop the database and restart it to disconnect all connections:
Db2stop force
Db2start
3) execute the BACKUP command: (use tsm as the backup media)
Db2 backup db sample use tsm
If the backup is successful, a timestamp is returned.
4) check that the backup is successful:
The db2 list history backup all for sample shows that this backup record is missing.
The return value can also be seen in the db2adutl query command.
5) Remarks:
First, run the BACKUP command on the master node (the node where the catalog tablespace is located), and then perform this operation on other nodes.
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 archiving logs
Db2 update db cfg for sample using trackmod on enable Incremental Backup
(Each node must be set separately)
After these parameters are enabled, the database is in the backup pending state and requires offline full backup of the database. Perform full offline backup. Refer to the above command.
2) The online backup command is as follows:
Db2 backup db sample online use tsm
If the backup is successful, a timestamp is returned.
3) You can also use db2adutl and db2 list history to view backup records.
4) Remarks:
Similarly, this operation is performed on each node.
3. Online Incremental Backup
1) when three required parameters are enabled, perform Incremental Backup:
Db2 backup db sample online incremental use tsm
If the backup is successful, a timestamp is returned.
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 the two backups is similar to the incremental and cumulative modes of oracle exports. The incremental method of db2 corresponds to the cumulative mode of oracle, while the delta mode of db2 corresponds to the incremental mode of oracle.
4) Remarks:
Similarly, this operation is performed on each node.
2. restore A restore database
1. Create a database
In common management tools, create a database (the alias must be the same as the original database alias ).
2. Restore the database
1): disconnect the network and disconnect all customers. If not, restart. (If a client is connected to the db2 server, it cannot be recovered, including the control center)
2): Restore offline backup:
Settings:
The db2 file you backed up is at: c: \ temp.
Your Database Name: dbname
The format of the backup/recovery file corresponding to db2 is as follows:
C: \ temp \ dbname.0 \ db2ctlsv \ node0000 \ catn0000 \ 20090706 \ hhmmss.001
3): instance
Backup File Path: c: \ temp \ ipmdemo.0 \ db2ctlsv \ node0000 \ catn0000 \ 20090622 \ 164642.001
Where, backup date: 20090622 backup file: 164642.001
Create Database Name: ipmdemo
Database file time stamp: 20090622164642
Recovery command: db2 restore database ipmdemo from c: \ temp taken at 20090622164642
3. Restore online backup
1). Restore. Recovery command: db2 restore database dbname from c: \ temp taken at 20090622164642
Db2 restore database uibs from e:/db2 takenat 20130320074838
Db2 rollforward database uibs to end of logs and stop
2) Roll forward. The rollback command is db2 rollforward db dbname to end of logs (or: Control Center-> all databases-> dbname-> right-click-> Roll back)
3) Stop the rollback. The stop rollback command is db2 rollforward db dbname stop (or: Control Center-> all databases-> dbname-> right-click-> stop rollback)
Iii. Description
1. recovery operations are also online and offline, just like backup operations.
2. Add the tablespace (tablespace-name) clause to perform similar backup and recovery based on the tablespace. Tablespace-level backup/recovery operations require that the database be in the archive log and Incremental backup mode.
3. In the recovery example, only version recovery is performed. If there is an updated full backup and Incremental backup image, you can perform restoration in sequence (pay attention to the recommended restoration sequence and number of times using db2ckrst) and then roll forward.

Using catalog to load remote database information to a local machine is generally divided into two steps:
Step 1: run the following command on the node of the catalog server:
Db2 catalog tcpip node db2node remote hostname server service_port
In this example, db2node is the name of a node you can call. The name cannot be the same as the existing one (you can view it through db2 lsit node directory), and the hostname can also be an ip address, service_port is the port number. The node directory stores all the connectivity information of the remote database.
Step 2: The catalog remote db2 database 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 refers to the client Instance name, And 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 Modification

1. Add Fields

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: There are restrictions on changing the field type. For example, you can change the field to be larger than the previous type length. to change it to a smaller value or modify the decimal point length, you must drop the original column first, and then add 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, you cannot use the preceding statement to modify the column type. You must drop the column type first.

3. Remove Fields

Alter table [table_name] drop column [column_name]

Note: After dropping a field, you may not be able to execute the Table query/insert operation. You Need To reorg the table.

4. Add default values for fields

Alter table [table_name] alter column [column_name] set default [value]

5. Add fields with default values

Alter table [table_name] add column [column_name] [column_type] not null with default [value]

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

Alter table [table_name] alter column [column_name] set default current date;