Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    15

    Unanswered: Question about DB2 v10 and simple table spaces

    Hi! I have a DB2 database with some simple table spaces that were created using DB2 v8.1. We're migrating to DB2 v10 and I need to convert the table spaces from simple to universal. I have one table per table space.

    How can I tell if a given table space is simple, universal, partitioned, etc., in DB2 v10? Is this recorded in a DB2 table, like SYSIBM.SYSTABLESPACE or must I use a utility to determine the type of a table space?

    Thanks!

    Peace...

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    You are correct, sysibm.tablespace is what you need to look. The column "TYPE" has the information you need. I am pretty sure it does not put a value for a simple tablespace, it has been a little while and unfortunately I do not have any to check.

    "The type of table space:
    blank
    The table space was created without the LOB or MEMBER CLUSTER options. If the DSSIZE column is zero, the table space is not greater than 64 gigabytes.
    G
    The table space was defined with the MAXPARTITIONS option (a partitioned-by-growth table space) with the underlying structure of a universal table space.
    L
    The table space can be greater than 64 gigabytes.
    O
    The table space was defined with the LOB option (the table space is a LOB table space).
    P
    Implicit table space created for XML columns.
    R
    Range-partitioned universal table space."

  3. #3
    Join Date
    Nov 2008
    Posts
    15
    Thanks for that info! I've been having problems accessing IBM's online DB2 manuals and I don't have any local copies readily available. This info is very helpful!

    Now, I've got another related question. lol

    I've been able to successfully create a table space in DB2 v10 and when I look at it in SYSIBM.SYSTABLESPACE in my DB2 v10 subsystem, the 'TYPE' column is blank and the PARTITIONS column is 0. I haven't checked the 'DSSIZE" column. I understand in DB2 v10, "simple" table spaces can't be created so I'm wondering if the 'TYPE' column is blank, does that mean a "simple" table space was created or not.

    Any thoughts?

    Thanks!

    Peace...

  4. #4
    Join Date
    Apr 2012
    Posts
    156
    If you are v10, it did not create a simple tablespace. Check what the segsize and partitions values are for the tablespace you created. For a simple tablespace I believe the segsize and partitions will both be 0.

    Also, if you are moving tables to a new tablespace, make sure you do not drop the old one until you are 100% sure will not have to recover. We had one that we moved, everything was good from a db standpoint, so we dropped the old tablespace. Then a few days later the application team need it recovered to 2 days before we moved the table. We were able to get the data back but it was not fun. Better option is to rename, keep it until you know 100% sure you will not need to recover to a point in time before your migration.

  5. #5
    Join Date
    Nov 2008
    Posts
    15
    Thanks again for the info. I just dropped and recreated my database in v10 and ran a query against SYSIBM.SYSTABLESPACE and found the SEGSIZE column is 4 and the PARTITIONS column is 0 for my table space. The TYPE column is still blank.

    Now, I need to see what those values are in my DB2 v8 subsystem.

    Thanks again!

    Peace...

Posting Permissions

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