Method to backup DDL and Data from the System Catalog using Database Management solutions
Article ID: 63081
Updated On:
Products
Issue/Introduction
In case of accidental update (or drop) of e.g. a table, a fast recreation of the object requires the original DDL which may not always be easily available.
Is there a way to easily backup DDL using Database Management for Db2 for z/OS solutions?
Resolution
Use ACM (Alternate Catalog Mapping) to make a shadow copy of your DB2 catalog.
The process of creating an ACM will create a copy of each catalog table and load the data from it which means it is a snapshot in time of the main Db2 catalog.
While ACM is mostly used for performance reasons it can also serve as a backup of DDL.
When the ACM is activated on the main menu:
ACM ==> ON ACMID ==> MYACM01
the Database Management for DB2 for Z/OS tools use the ACM instead of the main SYSIBM Db2 catalog when functions are used.
If an ACM of the catalog is regularly updated , the TEMPLATE, DDL or HDDL functions in RC/Update for Db2 for z/OS (RCU) can or an RC/Migrator
for Db2 for z/OS (RCM) Analysis be used to generate DDL from the shadow copy.
When an RC/Migrator Analysis is generated, the option of using an ACM is there in the Analysis Options : ACM ANALYSIS ===> Y
When the ACM is reviewed .......
----------------- Alternate Catalog Mapping ----------------- yyyy/mm/dd hh:mm
Command ===> SCROLL ===> CSR
Define ID ==> MYACM01 Copy from ID ==>
Description ==> My ACM
FROM: All: Define ID. tablename TO: All --> .
To copy Define ID or standard NAME, type an '=' in the respective area above.
---------------------------------------------------------------------- AUTHID1
SYSIBM . SYSCOLAUTH MYACM01 . SYSCOLAUTH
SYSIBM . SYSCOLUMNS MYACM01 . SYSCOLUMNS
SYSIBM . SYSCOPY MYACM01 . SYSCOPY
SYSIBM . SYSDATABASE MYACM01 . SYSDATABASE
SYSIBM . SYSDBAUTH MYACM01 . SYSDBAUTH
SYSIBM . SYSDBRM MYACM01 . SYSDBRM
SYSIBM . SYSFIELDS MYACM01 . SYSFIELDS
SYSIBM . SYSFOREIGNKEYS MYACM01 . SYSFOREIGNKEYS
SYSIBM . SYSINDEXES MYACM01 . SYSINDEXES
SYSIBM . SYSINDEXPART MYACM01 . SYSINDEXPART
the SYSIBM tables are mapped to the ACM tables.
For example, when using RC/Query, issuing a TABLE/LIST report.
Entering the SQL command when there is a list of tables returned shows that the ACM is used.
SELECT A.NAME , A.CREATOR , A.DBNAME , A.TSNAME , CASE WHEN A.CARDF >
9223372036854775807 THEN 9223372036854775807 ELSE BIGINT ( A.CARDF )
END , A.COLCOUNT , A.TYPE , A.DBID , A.OBID , A.CLUSTERTYPE , A.EDPROC
, A.VALPROC , A.PCTPAGES , A.PARENTS , A.CHILDREN , A.KEYCOLUMNS ,
A.RECLENGTH , A.STATUS , A.KEYOBID , A.CHECKFLAG , A.AUDITING ,
A.CREATEDBY , A.LOCATION , A.TBCREATOR , A.TBNAME , A.CREATEDTS ,
A.ALTEREDTS , A.DATACAPTURE , A.RBA1 , A.RBA2 , A.PCTROWCOMP ,
A.STATSTIME , A.CHECKS , A.CHECKRID5B , A.ENCODING_SCHEME ,
A.TABLESTATUS , A.NPAGESF , A.SPACEF , A.AVGROWLEN , A.RELCREATED ,
A.NUM_DEP_MQTS , A.VERSION , A.PARTKEYCOLNUM , A.SPLIT_ROWS ,
A.SECURITY_LABEL , A.OWNER , A.APPEND , A.OWNERTYPE , A.CONTROL ,
A.VERSIONING_SCHEMA , A.VERSIONING_TABLE , A.HASHKEYCOLUMNS , A.LABEL
, A.REMARKS , A.CHECKRID , A.CLUSTERRID , A.ARCHIVING_SCHEMA ,
A.ARCHIVING_TABLE , A.STATS_FEEDBACK , A.REGENERATETS
FROM MYACM01.SYSTABLES A <<<<<----MYACM01.SYSTABLES instead of SYSIBM.SYSTABLES
WHERE A.TYPE IN ( 'T' , 'G' , 'X' , 'M' , 'C' , 'P' , 'H' , 'R' , 'D' )
AND A.CREATOR = 'AUTHID1'
ORDER BY A.NAME , A.CREATOR WITH UR
Additional Information
Feedback
