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

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).

Correction: I should have said db2ckbkp output instead of backup image.

using option -t in db2ckbkp should help

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).

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'

-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.

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.

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.

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)

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

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.

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.

the one you attached looks good. thanks !