UTILS2

Inspect :

$db2 Inspect rowcompestimate table name prim schema name xkranth results keep primins

c:\programdata/IBM/DB2/db2copy1/DB2/

 it is not readable ..
to make it readable -

$db2inspf c:\programdata/IBM/DB2/db2copy1/DB2/primins c:\programdata/IBM/DB2/db2copy1/DB2/primins.out

i/p                 o/p                 

LOAD:

$db2 load from '/home/db2inst1/copy/empexp.dat' of ixf [lobs from <loc>]

                [modefied by <file>]

                savecount 1000

                rowcount 2000

                messages <msg file loc>

                insert/replace/restart/terminate into table emp

                statistics yes with distribution and detailed indexes all

                copy yes [use tsm/ to <loc>]

                nonrecoverable

                indexing mode rebuild/incremental/differed

                allow no access/ allow read access use tablespace

                check pending cascade immediate/ differed

                lock with force

                exception table <exemp>

Restore Rebuild :

>db2 restore db mydata1 rebuild with tablespace(syscatspace,ts1) from LOCATION taken at TIMESTAMP into mydata2

>C:\Windows\system32>db2 rollforward db mydata2 to PIT overflow log path (C:\DB2\NODE0000\SQL00003\SQLOGDIR)

SQL1271W  Database "MYDATA2" is recovered but one or more table spaces are
off-line on node(s) "0".

>C:\Windows\system32>db2 rollforward db mydata2 complete overflow log path (C:\DB2\NODE0000\SQL00003\SQLOGDIR)
SQL1271W  Database "MYDATA2" is recovered but one or more table spaces are off-line on node(s) "0".

Work sheet ::

restore rebuild :
(restore accidentally droped tablespace)

db2 create db gan
db2 connect to gan
db2 create bufferpool bp4k0 size 200 pagesize 4k
db2 create tablespace tsd4k0 pagesize 4k managed by automatic storage bufferpool bp4k0
db2 create tablespace tsd4k1 pagesize 4k managed by automatic storage bufferpool bp4k0

db2 "create table tabA (id int) in tsd4k0"
db2 "create table tabB (id int) in tsd4k0"

db2 "create table tabC (id int) in tsd4k1"

db2 "insert into tabA values (1)"

db2 "insert into tabB values (10)"

db2 "insert into tabC values (100)"


db2 "restore db gan rebuild with tablespace(syscatspace,tsd4k0) from 

'/home/db2inst2/backups' taken at 20151002010352 into gantemp"

--timestamp may be tablespace leve or db level anything can be accessable(most recent)

db2 list history all for db gan

db2 "rollforward db gantemp to 2015-10-02-01.04.46.000000 using local time overflow log 

path ('/home/db2inst2/arch/db2inst2/GAN/NODE0000/C0000000')"

--give pit timestamp is taken from history command > drop tablespace > just a second 

before of its timestamp

db2 "rollforward db gantemp stop overflow log path 

('/home/db2inst2/arch/db2inst2/GAN/NODE0000/C0000000')"

db2 connect to gantemp

db2look -d gantemp -e -l -o '/home/db2inst2/move/gantemp.ddl'

db2 "export to '/home/db2inst2/move/data/gantempAexp.del' of del messages 

'/home/db2inst2/move/msg/gantempAexp.msg' select * from tabA"
db2 "export to '/home/db2inst2/move/data/gantempBexp.del' of del messages 

'/home/db2inst2/move/msg/gantempBexp.msg' select * from tabB"


--edit gantemp.ddl as reuired

db2 -tvf '/home/db2inst2/move/gantemp.ddl'

db2 "import from '/home/db2inst2/move/data/gantempAexp.del' of del messages 

'/home/db2inst2/move/msg/gantempAimp.msg' insert into tabA'

db2 "import from '/home/db2inst2/move/data/gantempBexp.del' of del messages 

'/home/db2inst2/move/msg/gantempBimp.msg' insert into tabB'


--check



db2 "select * from db2inst2.tabA"

Redirect Restore :

1>Restore with Redirect option

2>set containers for tablespaces

3>Restore with Continue.

$db2 restore db prod from '/home/...'  taken at <ts> into temprod redirect generate script '/home/../prodscrpt.out'

$db2 set tablespace containers for 5 using /home/../ts01.out'

.

.

$db2 restore db prod continue