Confession of the Month

One-way street in DB2 9

Confession Supplied by Phil Grainger - Cogito/Grainger Database Solutions 

DB2 9 has removed the ability to create new simple table spaces - even though (luckily) the ones we have already in existence will still continue to be supported for the foreseeable future.

This does, however, leave DBAs with a tricky problem - and a problem that doesn't really have a satisfactory solution.

Once you have migrated to DB2 9 you can no longer create simple tables paces. If you try and execute some DDL perhaps that would have created a simple table space in DB2 V8, what you will get is a SEGMENTED table space with a default segment size. (SEGSIZE 4)

However, problems start to arise if you drop a table space in DB2 9 (either deliberately or accidentally - and many of us have dropped "the wrong thing" at some time in our careers). You will be unable to recreate it as a simple table space. You will only be able to recreate it as segmented, partitioned or universal.

If you have dropped it accidentally, this means that no matter HOW many image copies you have around, there is no simple mechanism to recover the data.

Of course, you cannot use the standard DB2 recovery utility (or a third-party equivalent) as the image copies are no longer registered in the SYSCOPY catalog table space.

Neither will you be able to use DSN1COPY to copy the data from an old image copy to the newly created table space - the internal formats are completely different. Even though DSN1COPY will probably complete with a condition code of zero, you will be unable to access any of the data.

The only recourse is to attempt unloading from the image copy and reloading the data into your new table space.

BUT, how can you unload data from an image copy of a table space when the catalog no longer contains the details of the table and table space structures.........?

Confession Supplied by Phil Grainger Cogito/Grainger Database Solutions 

www.cogito.co.uk      www.graingerdatabasesolutions.com