Restore Transport option – mix and match your schemas (Part 1)

I recently had a request from a client to help them build a database with a set of objects from two physical databases and provide a third ‘merged’ database. They’d already established that using 2 successive RESTORE commands with the TABLESPACE option was going to fail (SQL2560N : The target database is not identical to the source database for a restore from a table space level backup), so we were asked to provide another solution.

At this point my colleague Iqbal Goralwalla (whom I’m sure many of you know from IDUG, The DB2 Night Show and numerous other DB2-flavoured gatherings) sent me an email saying “why not try Transportable Schemas”?  To which I thought “what”? I’d never heard of them but they’ve been around since V9.7 FP2 apparently and they worked a treat for our customer. There’s a few gotchas (there always are) but let me walk you through how it works, and I’ll point out some of the stumbling blocks as we go.

This is what I’m trying to do

It’s a bit more complicated than that (of course); some of the objects I want to have in DB3 are in more than one tablespace (many of the tables have data in one tablespace, indexes in another and the large objects in a third), but all objects can be identified as residing in a specific schema or schemas.

There’s quite a bit to cover, so I’m dividing it into two parts, with this first part giving you the background and getting to the point where we’ve got the required tables from the DB2 database transported into DB3.

Let’s look at the specifics.

DB1 is actually called CONTRACT and it has the tablespaces shown below

What I want from this are the tables in the CONTRACT schema

There are going to be a few problems here, but we’ll come to those. Note that

• Our CONTRACT schema objects are in recognizable Tablespaces and these are associated with distinct BufferPools
• There are objects which are not in the CONTRACT schema, but which are in the associated tablespaces (e.g. TRANSPORT_TEST is in the DB2I1054 schema, and resides in the FACT_TS tablespace

The database is backed up on a regular basis so I’ve got a backup image ready to use.

The other source database (labelled DB2 in the diagram at the start) is really called LOGBOOK, and it’s the objects in the LOGBOOK schema that I want to merge into my DB3 database, which is actually called TARGET. If we look at the same displays of Tablespace and Table details in LOGBOOK, we can see these details

There’s a couple of things to note here:

• All the LOGBOOK tables reside in more than one tablespace
• The LOGBOOK table has data in more than one tablespace (DATA_TS1 and DATA_TS2) because it’s a range partitioned table

Again, there’s a readily available backup image to work from.

So, I create my TARGET database and issue this command

You can see the list of Tablespaces I’ve specified as well as the schema I want to migrate. The keyword TRANSPORT enables the selection of specific Schema(s). This will complete successfully but if you do a LIST APPLICATIONS you can see this

Indicating that there’s still some work outstanding. A Staging Database (SYSTG000) has been built to allow this action to be processed and the RESTORE is waiting for the CONTINUE command…..  and that’s when you get your first error

Not hugely informative so you have to go looking in the db2diag.log for the details. You’ll probably see a few of these messages

But keep paging up and you’ll find this

Which is a nice helpful message and tells you all you need to know: your specified schema (LOGBOOK) includes a table (AIRCRAFT) that stores data in a tablespace (IMAGE_TS) that you didn’t include in your list. If you modify your command to include that tablespace in the list

followed by the RESTORE … CONTINUE command, you’ll get a clean SQL return code and you can interrogate the TARGET database to see what has now appeared. Here are the tablespace details :

and the tables:

Notice anything untoward? Although the requested tablespaces DATA_TS1 and DATA_TS2 have been migrated across; there’s nothing in them (the NUMBER_OF_TABLES figure in the first display is 0). And there is no LOGBOOK table showing in the second display. That’s one of the restrictions with this option

Gotcha : you cannot TRANSPORT range partitioned tables. It’s one of a number of documented limitations (search on DB2  Transportable Objects for the full list) but the reason I reckon it’s a gotcha is that there is no warning or error message to say this table cannot be transported; the tablespaces are created but the table is just left behind, somewhere in the works.

There are some ways I can suggest to deal with this, but none of them are pretty. You could use ADMIN_MOVE_TABLE

• create a non-partitioned version of this table

• run a BACKUP to create an image that will allow the table to be transported
• and then use ADMIN_MOVE_TABLE on the source database to return it to range partitioned format

• Then go to the Target database, do your RESTORE from the image that includes the “de-partitioned” table and use ADMIN_MOVE_TABLE to change it to range partitioned format, as you just did on the Source database

Sounds like some major hassle, but at least all of these operations can be done on-line without interrupting user access, so it’s possible. But clumsy.

That’s probably enough for one session; we’ve got the contents we need from our LOGBOOK database, and we’ve even managed to get some range partitioned tables in there. We’ll look at merging in the next set of data, from the CONTRACT database, and wrapping up the RESTORE in the next blog.