DB2 export and import database

DB2 Export Database: db2move ncdb export-u db2admin-P 1

Ncdb indicates the database instance db2admin as the username 1 as the password

DB2 recovery: db2move instance import-u username-P Password
DB2 export: db2move instance export-u username-P Password

-- DB2 backup:
-- Connect to the database: Start --- run ---- db2cmd and then enter the following command
DB2 connnect to ncdb user db2admin using 1
-- Explanation: ncdb indicates the Instance name db2admin. Username 1 indicates the password.
-- Execute the command to export the table structure
Db2look-D ncdb-a-e-x-o ncdb. SQL
-- Export command
Db2move ncdb Export
-- Export completed

-- DB2 recovery:
-- Create a database:
Create Database ncdb on 'C: 'Using codeset GBK territory CN collate using system catalog tablespace managed by database using (File 'C: \ data \ ncdb \ catalogdata1 '128000) user tablespace managed by database using (File 'C: \ data \ ncdb \ userdata1 '512000)
Temporary tablespace managed by database using (File 'C: \ data \ ncdb \ tempspace1 '768000)
-- Connect to the database
Connect to ncdb user db2admin using 1
-- Execute the create tablespace statement DB2 will not automatically expand. Therefore, we recommend that you directly create a big point when creating the tablespace. The default value here is 8 GB, which can be modified by yourself.
Create bufferpool ncused4 size 102400 pagesize 4 K

Create bufferpool ncused16 size 38400 pagesize 16 K

Create regular tablespace nnc_data01 pagesize 16 K managed by database using (File 'C: \ data \ ncdb \ nnc_data01 '8g) extentsize 8 overhead 24.1 prefetchsize 8 transferrate 0.9 bufferpool ncused16

Create regular tablespace nnc_index01 pagesize 4 K managed by database using (File 'C: \ data \ ncdb \ nnc_index01 '8g) extentsize 32 overhead 24.1 prefetchsize 32 transferrate 0.9 bufferpool ncused4

Create regular tablespace nnc_data02 pagesize 16 K managed by database using (File 'C: \ data \ ncdb \ nnc_data02 '8g) extentsize 8 overhead 24.1 prefetchsize 8 transferrate 0.9 bufferpool ncused16

Create regular tablespace nnc_index02 pagesize 4 K managed by database using (File 'C: \ data \ ncdb \ nnc_index02 '8g) extentsize 32 overhead 24.1 prefetchsize 32 transferrate 0.9 bufferpool ncused4

Create regular tablespace nnc_data03 pagesize 16 K managed by database using (File 'C: \ data \ ncdb \ nnc_data03 '8 GB) extentsize 8 overhead 24.1 prefetchsize 8 transferrate 0.9 bufferpool ncused16

Create regular tablespace nnc_index03 pagesize 4 K managed by database using (File 'C: \ data \ ncdb \ nnc_index03 '8g) extentsize 32 overhead 24.1 prefetchsize 32 transferrate 0.9 bufferpool ncused4

Create user temporary tablespace usertemp pagesize 16 K managed by system using ('C: \ data \ ncdb \ usertemp1 ') extentsize 32 overhead 10.67 prefetchsize 32 transferrate 0.04 bufferpool ncused16

Create System temporary tablespace tempspace2 pagesize 16 K managed by system using ('C: \ data \ ncdb \ tempspace2') extentsize 32 overhead 10.67 prefetchsize 32 transferrate 0.04 bufferpool ncused16

-- Execute the delegate command
DB2 connect to ncdb

Grant Use of tablespace nnc_data01 to user db2admin with grant option
Grant Use of tablespace nnc_data02 to user db2admin with grant option
Grant Use of tablespace nnc_data03 to user db2admin with grant option
Grant Use of tablespace nnc_index01 to user db2admin with grant option
Grant Use of tablespace nnc_index02 to user db2admin with grant option
Grant Use of tablespace nnc_index03 to user db2admin with grant option
Grant Use of tablespace usertemp to user db2admin with grant option
-- Reconnect
Connect Reset

--- Recover the database first, and enter the directory of the backup file.
-- Create a table structure
DB2-tvf ncdb. SQL> ncdb. Log
-- Import backup files
Db2move ncdb load> load. Log

-- Connect to the database
DB2 connnect to ncdb
-- Check whether the table is suspended
DB2 "select 'set integrity for db2admin. '| substr (tabname, 1, 30) | 'immediate checked;' from syscat. tables where status = 'C '"

-- If the following statement is suspended (several times can be executed ):

DB2 "select 'set integrity for db2admin. '| substr (tabname, 1, 30) | 'immediate checked;' from syscat. tables where status = 'C'"> set. SQL

DB2-tvf set. SQL

-- Troubleshooting of Common Errors sql0668n does not allow operations on table xxx. The cause code is 3 sqlstate = 57016.
-- Solution: (Note that the tablespace size may be insufficient. Remember to query the execution results of each statement-log files)
-- View the table status. db2admin. sm_busi_navigation indicates the table name.
DB2 load query table db2admin. sm_busi_navigation
-- Fix the temporary mounting:
DB2 load from D: \ x.txt of del terminate into db2admin. sm_busi_navigation
-- Solve the Problem of temporary failure in setting integrity:
DB2 set integrity for db2admin. sm_busi_navigation immediate checked