Results 1 to 15 of 15
  1. #1
    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

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

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    Correction: I should have said db2ckbkp output instead of backup image.

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

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

  6. #6
    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'

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

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

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

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

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

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

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

  14. #14
    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 Attached Files

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •