DB2 OS/390 Database COPY and RESTORE
Hey there!!!!!
In the SQL Server world, if you want to create a database on a same server instance, from an existing database, all you have to do is something like:
RESTORE DATABASE <db_name>
FROM DISK = '\<file path><file name>'
WITH MOVE '<logical data file name>_data' TO '\<file path><file name>.MDF'
, MOVE '<logical log file name>_log' TO '\<file path><file name>.LDF'
, REPLACE
GO
In DB2? OH NO!!! SORRY!!!
I guess you could use Platinum or some other tool, but that's like a mainframe wizard, and they always (wizards in general) are never 100% bullet proof.
At least not for my liking.
Anyway….first thing you gotta do is to make sure that your destination database is completely created with th DDL from the source Database
From here, there are a couple of ways to go.
One requires the involvement of the DB2 Management system less than the other.
The latter (which is easier, straight forward, easier to understand, and takes WAY longer) , requires you to unload all of the tables, check/convert DB2 Load cards, put all of the tables spaces in utility pending, set up all of the load JCL for each, then making sure the dfata is then loaded in the correct RI order, submit the JCL, Run Statics, and take the tables out of Copy Pending.
The Other requires you to Create Image Copies (This is like a SQL Server Dump, but imagine that each table is a file).
From that you will use a DSN1COPY to put everything back.
The DB2MS Doesn't know about any of the contsraints, because it's basically "under the covers".
The only Complication, is that you have to tell The Copy the actual Object ID's of each object, mapped from the source, to the target…which is a royal pain….
UNTIL NOW!
All of the Id's are in the System Catalogs…if Both of the DB's are on the same subsystem (and assuming you've built the target DB with all of the DDL from the First), you can run the following SQL and it will create the mapping for you.
You can the take the output and type in all the mappings, or like I would do, is take the results, and generate everything I need.
I will post more about the IMAGECOPY, DSN1COPY, and the Code to create the mapping for the copy in future posts.
Anyway, I hope I gave a glimpse into 2 different worlds, and some help for us poor z/OS 390 DB2 Souls.
And HEY, if anyone has a better solution, I AM NOT PROUD. Please post it.
Thanks
SQL QUERY BXHRSPDB.COMPARE_TABLES LINE 1
SELECT CREATOR1
, CREATOR2
, DBNAME1
, DBID1
, DBNAME2
, DBID2
, TSNAME1
, TSID1
, TSNAME2
, TSID2
, TBNAME1
, TBID1
, TBNAME2
, TBID2
FROM (
SELECT SUBSTR(TS2.CREATOR,1,8) AS CREATOR2
, SUBSTR(TS2.DBNAME,1,8) AS DBNAME2
, TS2.DBID AS DBID2
, SUBSTR(TS2.NAME,1,8) AS TSNAME2
– DSN1COPY WANT PSID (PAGE SET ID), NOT OBID (FILE ID)
, TS2.PSID AS TSID2
, SUBSTR(TB2.NAME,1,18) AS TBNAME2
, TB2.OBID AS TBID2
FROM SYSIBM.SYSTABLESPACE TS2
LEFT JOIN SYSIBM.SYSTABLES TB2
ON TS2.DBNAME = TB2.DBNAME
AND TS2.NAME = TB2.TSNAME
WHERE TS2.CREATOR = 'BXHRSPDC'
AND TS2.DBNAME = 'SBD000DC'
AND TB2.TYPE = 'T'
) AS C1
FULL JOIN (
SELECT SUBSTR(TS1.CREATOR,1,8) AS CREATOR1
, SUBSTR(TS1.DBNAME,1,8) AS DBNAME1
, TS1.DBID AS DBID1
, SUBSTR(TS1.NAME,1,8) AS TSNAME1
– DSN1COPY WANT PSID (PAGE SET ID), NOT OBID (FILE ID)
, TS1.PSID AS TSID1
, SUBSTR(TB1.NAME,1,18) AS TBNAME1
, TB1.OBID AS TBID1
FROM SYSIBM.SYSTABLESPACE TS1
LEFT JOIN SYSIBM.SYSTABLES TB1
ON TS1.DBNAME = TB1.DBNAME
AND TS1.NAME = TB1.TSNAME
WHERE TS1.CREATOR = 'BXHRSPDB'
AND TS1.DBNAME = 'SBD000DB'
AND TB1.TYPE = 'T'
) AS C2
– ON DBNAME1=DBNAME2
ON TSNAME1=TSNAME2
AND TBNAME1=TBNAME2
;