So, the last blog (Restore Transport Part 1) got as far as restoring one Schema from our LOGBOOK source database (DB1) into our TARGET database (DB3). We’ve fudged our way around the fact that RESTORE TRANSPORT cannot cope with range partitioned tables and now, we need to merge in the objects from the CONTRACT database (DB2).
So our next operation is another RESTORE
which fails SQL2590 reason code 1 again and, when you go through the db2diag.log (remember, you will have to page up through the messages to find this above the SQL2590 errors) , you will see this message:
So this indicates that a tablespace is specified (FACT_TS) and it includes an object (TRANSPORT_TEST) that is not in your schema list (it’s in DB2I1054 not CONTRACT). You can either expand the Schema list that you supply to include DB2I1054 or else you’ll need to take a new backup image once the errant table has been removed.
So let’s say we have that Backup image with no TRANSPORT_TEST table in it and we’re going to do the RESTORE but with the full list of required tablespaces (we didn’t have FACT_BLU_TS in the last one):
Once again we get an SQL2590 and need to make our backwards through the db2diag.log to find this:
I’d say that was a bit of gotcha, even though it’s published in the limitations. It means that any use you’ve made of column-organized tables will have to be re-engineered on your Target database. You don’t actually need anything as complex as ADMIN_MOVE_TABLE but you might need to do a CREATE … LIKE … ORGANIZE BY ROW on your source database, bring over the row-based table in the backup image, and then convert it in situ on the Target database, or do another CREATE … LIKE. Or, if it’s a Shadow Table, you’ll need to re-create it from scratch; it’s just one more thing to think about as your try and merge your data.
So, bearing those restrictions in mind we re-issue our RESTORE command and it works, no errors, and when we interrogate the TARGET database we can see all the tablespaces and schemas we specified from LOGBOOK and CONTRACT, merged into the same database, along with their resident tables:
Mind you: take a look at the Tablespace query I ran earlier for the CONTRACT and LOGBOOK databases, when it’s run against the TARGET database:
Gotcha : It’s stuffed all the tablespaces into the same, default BufferPool. I’m pretty sure I don’t want that to happen. I’m going to need to massage that a bit to avoid some unpleasant memory contention, but that can be done without any major ructions.
And another thing: in the table display you can see that the CONTRACT table resides in the FACT_TS tablespace. But in the tablespace display, FACT_TS isn’t shown. Que? Well if we query the SYSCAT.TABLESPACE view we can see what the BufferPool ID is
And if we want to know what ID 4099 is, we need to take a look using
db2pd –d target –bufferpools
and we can then see that this is a ‘hidden’ or system bufferpool
Gotcha
It’s probably ended up in there because the default bufferpool is now exhausted in terms of memory allocation, so you’ll need to do some re-configuring to enable your Target database to work properly although, in the short-term, you can still interrogate the CONTRACT table without a problem. A few of the commands below, and the tablespaces and bufferpools will be back the way you want them
So, there you go: another little tool to keep in your DB2 toolbox. It’s got its limitations (see Transportable Objects for the current full list) and there’s the odd gotcha, but it can come in very handy when you need to get that merged database up and running without exporting flat-files and transmitting other bulky data formats around. I hope it works for you too.