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?
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:
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.
The table space was defined with the MAXPARTITIONS option (a partitioned-by-growth table space) with the underlying structure of a universal table space.
The table space can be greater than 64 gigabytes.
The table space was defined with the LOB option (the table space is a LOB table space).
Implicit table space created for XML columns.
Range-partitioned universal table space."
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.
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.
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.