Basic DB2 database operations

Basic DB2 database operations
Start the DB2 service: db2start
Disable the DB2 service: db2stop




1. load data:
1. load with the default separator. The default value is ",".
Db2 "import from btpoper.txt of del insert into btpoper"
2. load with the specified separator "|"
Db2 "import from btpoper.txt of del modified by coldel | insert into btpoper"


Ii. Uninstall data:
1. unmount all data in a table
Db2 "export to btpoper.txt of del select * from btpoper"
Db2 "export to btpoper.txt of del modified by coldel | select * from btpoper"


2. unmount data from a table with conditions
Db2 "export to btpoper.txt of del select * from btpoper where brhid = '20140901 '"
Db2 "export to cmcode.txt of del select * from cmcodewhere codtp = '01 '"
Db2 "export to cmkcode.txt of del modified by coldel | select * from cmkcode where codtp = '01 '"


Iii. query data structures and data:
Db2 "select * from btpoper"
Db2 "select * from btpoper where brhid = '000000' and oprid = '000000 '"
Db2 "select oprid, oprnm, brhid, passwd from btpoper"


4. Delete table data:
Db2 "delete from btpoper"
Db2 "delete from btpoper where brhid = '000000' or brhid = '000000 '"


5. Modify Table data:
Db2 "update svmmst set prtlines = 0 where brhid = '000000' and jobtp = '02 '"
Db2 "update svmmst set prtlines = 0 where jobtp = '02 'or jobtp = '03 '"


6. Database Connection
Db2 connect to btpdbs


7. Clear database connections
Db2 connect reset disconnects the database
Db2 terminate disconnect Database
Db2 force applications all disconnect all databases

8. Back up databases
1. db2 backup db btpdbs
2. db2move btpdbs export
Db2look-d btpdbs-e-x [-a]-o crttbl. SQL

9. Restore the database
1. db2 restore db btpdbs without rolling forward
2. db2-tvf crtdb. SQL
Crtdb. SQL file content: create db btpdbs on/db2catalog
Db2-stvf crttbl. SQL
Db2move btpdbs import

10. DB2 help commands:
Db2?
Db2? Restroe
Db2? Sqlcode (for example: db2? Sql0803) Note: The code must be 4-digit, not 4-digit, followed by 0.

11. bind command: bind the application to the database. We recommend that you bind the application once every time you restore the database.
(1) db2 bind br8200.bnd
(2)/btp/bin/bndall/btp/bnd
/Btp/bin/bndall/btp/tran/bnd

12. view database parameters:
Db2 get dbm cfg
Db2 get db cfg for btpdbs

13. modify Database parameters:
Db2 update db cfg for btpdbs using LOGBUFSZ 20
Db2 update db cfg for btpdbs using LOGFILSIZ 5120
After modification, execute the following command to make it take effect:
Db2 stop
Db2 start

Supplement:

Db2 set schema btp modify current mode to "btp"

Db2 list tablespaces show detail
Db2 list tablespace containers for 2 show detail View tablespace id = 2 use the directory where the container is located
Db2 list application
Db2 list db directory list all databases
Db2 list active databases list all active databases
Db2 list tables for all list all tables in the current database
Db2 list tables for schema btp list tables whose schema is btp in the current database
Db2 list tablespaces show detail display database space usage
Db2 list packages for all

Db2 "import from tab76.ixf of ixf commitcount 5000 insert into achact"

Db2 "create table achact_t like achact"

Db2 "rename table achact_t to achact"

Db2 "insert into achact_t select * from achact where txndt> = (select lstpgdt from
Acmact where actno = achact. actno )"

Db2 get snapshot for dynaimic SQL on jining


Delete an instance:
# Cd/usr/lpp/db2_07_01/instance
#./Db2idrop InstName

List all DB2 instances:
# Cd/usr/lpp/db2_07_01/bin
#./Db2ilist

Create a catalog for a database
$ Db2 catalog db btpdbs on/db2catalog
Cancel the compiled destination database btpdbs
$ Db2 uncatalog db btpdbs

View version
# Db2level

Displays the current database management instance
$ Db2 get instance

Set whether the instance is automatically started when the system starts.
$ Db2iauto-on automatic start
$ Db2iauto-off does not start automatically


Database optimization command:
Reorg, runstats
When the database is used for a period of time, the data space will become larger and larger. Delete some
The data is still stored in the database, occupying data space and affecting system performance. Therefore, regular
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/btp/bin directory,
Runsall can be run after the business ends on the current day to optimize the database.

How to import a DB2 database
Keywords: db2 db2move

1. Import the database to the new db2 database
Take the database of xukuang as an example. The exported database files are ncdata.tar.gzand iufodata.tar.gz. decompress the files and place them in the ncdata and iufodata directories.
1.1 create a table structure
In the ncdata and iufodata folders, there are ncdb. SQL and iufodb. SQL, which are the SQL scripts used for database creation ....
Note that you need to check whether the tablespace name needs to be modified before executing the script...
Otherwise run: db2cmd db2-tvf ncdb. SQL; db2-tvf iufodb. SQL
1.2 import data
Use db2move ncdb load to import data to the corresponding table

1.3 check lock table records
Db2 Select tabname from syscat. tables where status = 'C' // find the locked table
Execute the following statement for each queried Table Name:
Db2 connect to <Database Name> user <user> using <password>
SET INTEGRITY FOR TABLNAME ALL IMMEDIATE UNCHECKED
1.4 recreate the table structure
Db2 "reorgchk update statistics on table all"

2. update existing databases

Db2cmd db2move ncdb import-io replace_create