x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Thursday, December 03, 2009

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                                
;  


posted @ Thursday, December 03, 2009 1:32 PM | Feedback (0) | Filed Under [ DB2 ]

Powered by:
Powered By Subtext Powered By ASP.NET