DB2 Creating a library data recovery

Cases:
Database: PRODB2
Users: Db2admin/db2admin
Backup Library Path: D:/bank

I. Recovering a database
1. Start the database
Run-"Db2cmd
-"DB2
Db2=>start DB Manager
Db2=>force Application All
Db2=>drop Database Tjns

DB2 =
2. Create a database
CREATE DATABASE ppdev0309 on D:\ USING codeset GBK TERRITORY CN

3. Connect to the database
Connect to ppdev0309 user db2admin using Db2admin

3. Create a buffer pool/table space

DB2 CREATE Bufferpool BUFFER4 SIZE 102400 PAGESIZE 4K;

DB2 CREATE Bufferpool BUFFER16 SIZE 38400 PAGESIZE 16K;

DB2 CREATE REGULAR tablespace nnc_data01 PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_DAT A01 ' 198144) autoresize YES extentsize + OVERHEAD 12.67 prefetchsize + transferrate 0.18 bufferpool BUFFER16 DROPPED TA BLE RECOVERY OFF;

DB2 CREATE REGULAR tablespace nnc_data02 PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_DAT A02 ' 105536) autoresize YES extentsize + OVERHEAD 12.67 prefetchsize + transferrate 0.18 bufferpool BUFFER16 DROPPED TA BLE RECOVERY OFF;

DB2 CREATE REGULAR tablespace nnc_data03 PAGESIZE K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_DAT A03 ' 105536) autoresize YES extentsize + OVERHEAD 12.67 prefetchsize + transferrate 0.14 bufferpool BUFFER16 DROPPED TA BLE RECOVERY OFF;

DB2 CREATE REGULAR tablespace nnc_index01 PAGESIZE 4 K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_IND EX01 ' 262144) autoresize YES extentsize + OVERHEAD 12.67 prefetchsize + transferrate 0.18 bufferpool BUFFER4 DROPPED TA BLE RECOVERY OFF;

DB2 CREATE REGULAR tablespace nnc_index02 PAGESIZE 4 K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_IND EX02 ' 202144) autoresize YES extentsize OVERHEAD 12.67 prefetchsize transferrate 0.18 bufferpool BUFFER4 DROPPED TAB LE RECOVERY OFF;

DB2 CREATE REGULAR tablespace nnc_index03 PAGESIZE 4 K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\ppdev0309\NNC_IND EX03 ' 262144) autoresize YES extentsize + OVERHEAD 12.67 prefetchsize + transferrate 0.18 bufferpool BUFFER4 DROPPED TA BLE RECOVERY OFF;

DB2 CREATE USER Temporary tablespace usertemp PAGESIZE 16K MANAGED by Database USING (FILE ' D:\DB2\NODE0000\ppdev0309\use Rtemp ' 162144) autoresize YES extentsize OVERHEAD 12.67 prefetchsize, transferrate 0.18 bufferpool BUFFER16;

DB2 CREATE SYSTEM Temporary tablespace tempspace2 PAGESIZE K MANAGED by Database USING (FILE ' D:\DB2\NODE0000\ppdev030 9\tempspace2 ' 65536) autoresize YES extentsize OVERHEAD 12.67 prefetchsize, transferrate 0.18 bufferpool BUFFER16;

4. Assigning Permissions
GRANT Dbadm, Createtab, Bindadd, CONNECT, Create_not_fenced_routine, Implicit_schema, LOAD, Create_external_routine, Quiesce_connect on DATABASE to USER Tjns;
Grant use of tablespace nnc_data01 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace nnc_data02 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace nnc_data03 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace nnc_index01 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace nnc_index02 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace nnc_index03 to USER PPDEV0309 with GRANT OPTION;
Grant use of tablespace usertemp to USER PPDEV0309 with GRANT OPTION;

5. Database Optimization---Guide library no need to do
Update dbm CFG using ASLHEAPSZ 1024;
Update dbm CFG using sheapthres 40000;
Update dbm CFG using maxagents 300;
Update dbm CFG using num_poolagents 100;
Update database configuration for PRODB2 using Dbheap 10240;
Update database configuration for PRODB2 using LOGBUFSZ 2048;
Update database configuration for PRODB2 using CATALOGCACHE_SZ 3072;
Update database configuration for PRODB2 using Locklist 10240;
Update database configuration for PRODB2 using APP_CTL_HEAP_SZ 4096;
Update database configuration for PRODB2 using sortheap 4096;
Update database configuration for PRODB2 using Stmtheap 3072;
Update database configuration for PRODB2 using Applheapsz 16384;
Update database configuration for PRODB2 using Pckcachesz 20480;
Update database configuration for PRODB2 using Maxlocks 18;
Update database configuration for PRODB2 using Num_iocleaners 8;
Update database configuration for PRODB2 using Num_ioservers 5;
Update database configuration for PRODB2 using MAXAPPLS 300;
Update database configuration for PRODB2 using AVG_APPLS 200;
Update database configuration for PRODB2 using Logfilsiz 204800;
Update database configuration for PRODB2 using Logprimary 50;
Update database configuration for PRODB2 using Logsecond 150;
Update database configuration for PRODB2 using MINCOMMIT 2;
Update database configuration for PRODB2 using blk_log_dsk_ful OFF;

6. Modify the user name
Change the original user name to Db2admin in Db2move.lst (uppercase, Aix strict control)
Change the original user name to Db2admin in Credb.sql (uppercase, Aix strict control)
Change the database Join command on the file header to

CONNECT to PRODB2 USER db2inst1 USING db2inst1;

7. Split table Structure
The foreign keys query distinguishes the Credb.sql into Credb1.sql (tables and indexes), credb2.sql (foreign keys and views).

8. Import the Library cmd console
Mode 1:
--Import library data (load mode requires importing tables and indexes first, import not required)
Db2move Tjns import-u db2admin-p db2admin>import.log
Mode 2:
--Import tables and indexes (you need to connect to the Connect database first)
DB2-TVF credb1.sql > Credb1.log
--Import library data (load mode requires importing tables and indexes first, import not required)
Db2move Tjns load-u db2admin-p db2admin>load.log
--Import foreign keys and views
DB2-TVF credb2.sql > Credb2.log

9. Do database collation
--Querying database tables for hangs
Select Tabschema,tabname from Syscat.tables where status= ' C '
--If yes, resolve the table
Set INTEGRITY for Db2inst1. Bd_defdoclist IMMEDIATE CHECKED
--can also reorgchk the whole table and the library
--Analysis of single sheets:
Runstats on table db2inst1.gl_detail with distribution and detailed indexes all
--Analyze all tables below the entire user:
Connect to PPDEV0309 user db2admin using admin
REORGCHK UPDATE STATISTICS on table all

10. Export the database cmd console
Connect to PPDEV0309;
--Export table structure
db2look-d ppdev0309-u db2admin-e-o credb.sql-i db2admin-w db2admin
--Export Table data
Db2move protest EXPORT-TC db2admin-u db2admin-p db2admin>export.sql

Import from D:\20150101\tab1.ixf to del insert into Rm_port

11. Disconnect the connection
Disconnect PRODB2

12. Deleting a database
Deleting a database
With Run-"db2cmd
DB2 Drop DB database name

13. Restart the server
Db2stop Force
Db2start

DB2 Creating a library data recovery