If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > how to find tablespace autostorage clause from backup image

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: California,USA
Posts: 72
how to find tablespace autostorage clause from backup image

HAPPY CHRISTMAS to all.

OS: AIX 5.3
DB2: 9.5 FP 4

I would like to know how to determine whether a tablespace in the backup image has been autostorage enabled or not.
I need this to be determined from db2ckbkp command so that I can generate a redirected restore script more accurately to restore a database from a backup image having tablespaces with both autostorage enabled and disabled.

Any help is highly appreciated.

Thanks in advance.
-Ram
Reply With Quote
  #2 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
The backup image contains info about whether the db is using automatic storage / its storage path. I don't think it has info about whether each tablespace is defined using automatic storage or not, but you might be able to tell from the container name. If you can't tell, then specify the ON parameter on the restore command (to redefine automatic storage tablespaces) and do set tablespace containers for all (set containers will fail with sql20319n for those that are not using automatic storage).
Reply With Quote
  #3 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
Correction: I should have said db2ckbkp output instead of backup image.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
using option -t in db2ckbkp should help
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #5 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
How do you tell if a tablespace is defined using automatic storage or not from db2ckbkp -t (or -T) output?

The way I tell is by looking at the container name, but it may not be 100% accurate (ie. I can create a non-automatic storage tablespace but use the naming convention of automatic storage for the container definition).
Reply With Quote
  #6 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
If the history file still contains create tablespace info, you can try extracting it from the backup image to see the DDL or try:
strings <backup image> | grep -i 'create tablespace'
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
-t should give you info about the tablespace ( containers, pagesize, TSID, number of pages : usagble or used, extentsz, prefetchsz etc). Just to check autostorage yes or no, the -S option should help. You should see something like the following:

db2ckbkp -S SAMPLE.0.db2com01.NODE0000.CATN0000.20101226030006 .001


Automatic storage enabled: Yes

Number of storage paths: 1

storage path: /db2/db2_tables01/auto

Header:

flavour: 1
version: 0

Storage Group:

flavour: 2
version: 0
stateFlags: 0x0
lastLSN: 0000000000000000
initialLSN: 0000000000000000



Storage group information dumped -- NO VERIFICATION PERFORMED.
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #8 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
The -S option will tell us if the db is created using automatic storage option, but it won't tell if the tablespaces are defined using automatic storage.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
I think this is what he is looking for to avoid issues related to restore the database with automatic storage path to different server where the path does not exist. with db2ckbkp -S the DBA will see whether automatic storage is enabled or not and then will go for further drill down on the original database on the specific path and tablespace by db2pd -db SAMPLE -tablespaces (from the output he will look into the "AUTO" field). I hope this should help him.
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #10 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
In some cases, the original db doesn't exist and all they have is the backup image. If the original db is available, then the "AS" field from db2pd -tablespaces will give that info ("Auto" is for prefetchsize)
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
Yes, db2pd will help him while his original DB is available in the source system.

Regarding the "AUTO" field, this is what I got when i executed the command on my system. You can try it once too: We should look for the AUTO field to be YES to find out those TS with autostorage ON.

[jadtttaS::db2inst1::/db2ins01] db2pd -db SAMPLE -tablespaces

Database Partition 0 -- Database SAMPLE -- Active -- Up 8 days 21:01:28

Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x07000001D9B5E000 0 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 SYSCATSPACE
0x07000001D9B5E820 1 SMS UsrTmp 4096 64 No 32 1 1 On 1 0 63 SAMPTMP
0x07000001D9B5F040 2 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 USERSPACE1
0x07000001D9B5F860 3 DMS Regular 32768 64 No 32 3 3 On 4 0 63 TS32K
0x07000001DDD266E0 4 DMS Regular 8192 64 No 32 2 2 On 1 0 63 IXSPACESMPLE
0x07000001DDD26F60 5 DMS Regular 8192 64 No 32 2 2 On 1 0 63 IXSPACEMLDR
0x07000001DDD277E0 6 DMS Regular 4096 64 No 32 1 1 On 5
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #12 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
Quote:
Originally Posted by JAYANTA_DATTA View Post
We should look for the AUTO field to be YES to find out those TS with autostorage ON.

We should look at the AS field under "Tablespace Autoresize Statistics:" heading to find out if the tablespaces are defined with automatic storage. The Auto field is for prefetchsize (automatic or not).
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
Quote:
Originally Posted by BELLO4KA View Post
We should look at the AS field under "Tablespace Autoresize Statistics:" heading to find out if the tablespaces are defined with automatic storage. The Auto field is for prefetchsize (automatic or not).
Can you show me the AS field on the above output I posted ? As both the tablespaces I created on my SAMPLE is with AUTO storage and they are showing "Yes" against the AUTO field. what you are mentioning is very much there in public boulder, but while finding the TS with auto storage on from the above or similar output of db2pd the Auto column shows if the tablespace uses the automatic storage.
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
  #14 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
The output you pasted doesn't show the AS field. I'm attaching from one of my dbs - TS ID 4 was created without automatic storage.
Attached Files
File Type: txt db2pd_tab_AS.txt (7.5 KB, 65 views)
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: DELHI INDIA
Posts: 338
the one you attached looks good. thanks !
__________________

Jayanta Datta
DB2 UDB DBA
IBM India, Global Delivery
New Delhi
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On