1 / 40

Advanced Schema Management – Migration Procedures

Advanced Schema Management – Migration Procedures Abstract

Gideon
Download Presentation

Advanced Schema Management – Migration Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Schema Management – Migration Procedures

  2. Abstract • Schema management today is more than just moving a table from test to production. One issue is all the new objects and features as well as dependences introduced by DB2 for z/OS. Another issue is the demand or need to have more and more “identical” or “cloned” environments and environment specific features need to be maintained and synchronized. • This presentation will illustrate how these processes can be automated and maintained with minimal manual intervention using Unicenter RC/Migrator.

  3. Agenda • Schema Migration – the basics • Schema Migration in a one-to-many environment • Implementation of environment specific processes • Cloning an environment with / without data (without the expensive Unload / Load process) • Alternate migration processes • To handle “out of extent” or volume migration fast • VCAT  STOGROUP conversion • Schema synchronization of different environments – which method to chose

  4. Schema management challenges • Why automate migration processes ? • Do we really know all the objects any more • Do we know the dependent objects like triggers, LOB’s, UDT’s ……. • Navigating the catalog is not getting any easier • The “static world” has become VERY dynamic • Which new objects exist • Do we remember all the naming conventions, environment specific characteristics …. • Loading target tables in the correct order when RI and cyclical RI involved • Decision whether Online Schema Changes or “good old” drop – create • Most important – regulatory requirements (like SOX) to have documented and automated processes in place. • Latest change can be viewed in the catalog • No history provided for older changes • No history provided for dropped objects

  5. Schema management – the basics • First scenario – basic migration process • Migrate all databases with a generic name of “DSN8D%” created by “STEEN” from subsystem D81A • Target environment could be a future QA environment • Apply global changes to names, creators and attributes to apply to target environments characteristics. • Use SQL to migrate statistics instead of Runstats • Include LOB objects and data • Include local RI and generate the appropriate check utility statements • Generate BIND statements for the most current versions

  6. Schema management – migration RMS1 R11.5 -------------- RC/M Strategy Services -------------- 05/12/14 17:18 COMMAND ===> SCROLL ===> CSR DB2 SSID ===> D81A STRATEGY ===> IDUG% CREATOR ===> RASST02 TYPE ===> * SRC SSID ===> * ---------------------------------------------------------------------- RASST02 T S SRC +---- LAST UPDATE ----+ O STRATEGY DESCRIPTION CREATOR P O SSID USER DATE TIME c idug001 migrate to idug01 QA env_ RASST02 m u d81a <== STRATEGY CREATION ******************************** BOTTOM OF DATA ******************************* Valid O Cmds For Strats : A,C,D,G,I,L,M,T,U,X,! Press END to go back Valid O Cmds For Analyses: A,B,C,D,E,G,M,O,P,R,S,U,X,Z,! Press ENTER to process First a “strategy” is created to define the scope.

  7. Schema management – migration ---------------- RC/M Create Migration Strategy --------------- 05/12/14 17:22 COMMAND ===> Strategy ===> IDUG001 Description ===> MIGRATE TO IDUG01 QA ENV. Creator ===> RASST02 Share Option ===> U (U,Y,N,X,L) SRC SSID ===> D81A ------------------------------------------------------------------------------- PRIMARY OBJECT TYPE SPECIFICATION. ( Select one type with 'A' , 'S', or 'E' ) _ Storage Group a Database _ Tablespace _ Table _ Index _ View _ Synonym/Alias _ Trigger _ Routine PRIMARY OBJECT SELECTION SPECIFICATION. Name ==> dsn8d% > Creator ==> * > Where ==> y Instead of selecting the “starting objects” individually, the Autobuild option is used in conjunction with the WHERE-clause.

  8. Schema management – migration R11.5 ----------------- SQL Selection Panel ----------------- 2005/12/14 17:27 Command ==> SCROLL ===> PAGE Name ==> IDUG001 Share => N Default => N Description => Panel : RMDATABS View SQL => N Confirm Replace ==> Y Userid : RASST02 Where Clause: 01 and :7 = ‘STEEN’ 02 03 04 05 06 07 08 A.NAME A A.CREATOR A SYSIBM.SYSDATABASE Object Type: T (Correlation variable A ) 1 NAME VAR> 2 CREATOR VAR> 3 STGROUP VAR> 4 BPOOL CHAR 5 DBID SMA> 6 IBMREQD CHAR 7 CREATEDBY VAR> 8 ROSHARE CHAR 9 TIMESTAMP TIM> 10 TYPE CHAR 11 GROUP_MEMBER VAR> 12 CREATEDTS TIM> The CREATEDBY predicate is applied in order to only migrate databases created by ‘STEEN’

  9. Schema management – migration r11.5 ----------- RC/M Migration Strategy Analysis ----------- 05/12/14 18:13 COMMAND ===> STRATEGY ===> IDUG001 DESCRIPTION ===> MIGRATE TO IDUG ENV. CREATOR ===> RASST02 SHAREOPTION ===> U ---------------------------------------------------------------------- RASST02 EXECUTION SPECIFICATIONS ACCESS SPECIFICATIONS EXECUTION MODE ===> ( O - Online, B - Batch ) ACM ANALYSIS ===> N OVERRIDE ===> N PDS ANALYSIS ===> N RECOVERY ===> N OPTION SPECIFICATIONS DB2 SSID SPECIFICATIONS UPDATE OPTIONS ===> y SOURCE SSID ===> D81A GLOBAL CHANGES ===> s TARGET SSID ===> D81a SET NAME ===> iduggbl1 SET CREATOR ===> rasst02 OUTPUT DATASET SPECIFICATIONS EDIT DATASET ===> N DATASET NAME ===> 'PTIDEVL.VIRTUEL.DB2(IDUG001)' VOLUME SERIAL ===> ( If not cataloged ) Prior to generating the script for the migration, we will specify the options to use and what kind of changes are needed to conform to the naming convention at the target environment.

  10. Schema management – migration Please see notes section for option details r11.5 ------------ RC/M Strategy Analysis Options ------------ 05/12/14 18:16 COMMAND ===> RO039I: Analysis Model has been selected. ----Control Options---------- ------Utility Options---- ----Output Options---- AUX IMPLODE ==> N STATS (Y,N,S,A) ==> s ANALYSIS HEADER ==> Y RI(LOCAL/GLOBAL) ==> l CHECK DATA ==> y IMPACT ANALYSIS ==> Y SECURITY ==> n IMAGE COPY (Y,P,N) ==> N RPT IN DDLFILE ==> Y DROP OBJECTS ==> N (B)IND/(R)EBIND ==> c DEFAULT SQLID ==> RASST02 RECOVER INDEX ==> N Dataset Delete Options ALIAS SQLID ==> UNLOAD ==> Y LOB OBJECTS ==> y TEMPORARY ==> Y RTN IMPLODE ==> (T,V,A,blank) RTN INTERACTION ==> N (Y,N,A,O,E,M,S) --Data Unload Options-------- -------Model Options------ ALL ROWS ==> y MODEL ID ==> @DEFAULT ----IDCAMS Options---- NUMBER ROWS ==> MODEL CREATOR ==> R115BPTI VSAM DEFINES ==> N DATA STATISTICS ==> N UPDATE MODEL ==> N VSAM DELETES ==> N TRUNCATE ==> N -----Exclusive Options-------- NO .AUTHS ==> Y BND/DAT/STAT/SQL/GRNT/RI ==> n ( B nd, D at, R -Stat, S QL, G rnt, R I or N ) COMMIT ASAP ==> n ( If SQL only specified )

  11. Global changes (partial snapshot) --------------------- RC/M Global Changes------------------ SET NAME ===> IDUGGBL1 SHARE OPTION ===> U CREATOR ===> RASST02 DESCRIPTION ===> MIGRATE TO IDUGQA CODE CHANGE DESCRIPTION FROM TO AL ALL OBJECT TYPES ALVS _ VCAT > STOGROUP (MO) ___________ _____________ ALSV _ STOGROUP > VCAT (MO) ___________ _____________ ALCB _ CREATED BY ___________ _____________ ALCR _ CREATOR DSN8810____ IDUG01_______ ALCR _ *__________ IDUG02_______ ALDB _ DATABASE NAME *__________ IDUG06DB_____ ALGT _ GRANTOR (NC) ___________ _____________ ALGE _ GRANTEE (NC) ___________ _____________ ALSG _ STOGROUP ___________ _____________ ALTS _ TABLESPACE NAME DSN8S%_____ IDUGS%_______ ALVC _ VCATNAME ___________ _____________ SG STORAGE GROUP SGNM _ NAME ___________ _____________ SGCB _ CREATED BY ___________ _____________ SGCR _ CREATOR ___________ _____________ SGGT _ GRANTOR (NC) ___________ _____________ SGGE _ GRANTEE (NC) ___________ _____________ SGVC _ VCATNAME ___________ _____________ SGVP _ VSAM PASSWORD ___________ _____________ SGVL _ VOLUME ___________ _____________ DB DATABASE DBNM _ NAME ___________ _____________ DBCB _ CREATED BY ___________ _____________ DBCR _ CREATOR ___________ _____________ DBGT _ GRANTOR (NC) ___________ _____________ DBGE _ GRANTEE (NC) ___________ _____________ DBSG _ STOGROUP ___________ _____________ DBBP _ BUFFERPOOL BP0________ BP1__________ DBCS _ CCSID ___________ _____________ DBIB _ INDEXBP BP0________ BP2__________ --------------------- RC/M Global Changes ------------------- SET NAME ===> IDUGGBL1 SHARE OPTION ===> U CREATOR ===> RASST02 DESCRIPTION ===> MIGRATE TO IDUGQA CODE CHANGE DESCRIPTION FROM TO TBTP _ TYPE ____________ ____________ TBDC _ DATA CAPTURE *___________ CHANGES_____ IX INDEX IXNM _ NAME ____________ ____________ IXCR _ CREATOR ____________ ____________ IXCB _ CREATED BY ____________ ____________ IXTN _ TBNAME ____________ ____________ IXDB _ DBNAME ____________ ____________ IXSP _ SUBPAGES ____________ ____________ IXTC _ TBCREATOR *___________ IDUG01______ IXUR _ UNIQUERULE ____________ ____________ IXBP _ BUFFERPOOL ____________ ____________ IXCL _ CLOSE RULE ____________ ____________ IXDP _ DATASET PASSWORD ____________ ____________ IXVC _ VCAT ____________ ____________ IXVO _ VOLUMES ____________ ____________ IXSG _ STOGROUP ____________ ____________ IXSV _ STOGROUP > VCAT (MO) ____________ ____________ IXVS _ VCAT > STOGROUP (MO) ____________ ____________ IXPQ _ PRIMARY QUANTITY *___________ CALC(720)___ IXSQ _ SECONDARY QUANTITY *___________ CALC(720)___ IXER _ ERASE RULE ____________ ____________ IXFP _ FREEPAGE ____________ ____________ IXPF _ PCTFREE ____________ ____________ IXTY _ TYPE ____________ ____________ IXGC _ GROUP BUFFER CACHE ____________ ____________ IXPS _ PIECESIZE 2G__________ 512M________ IXPS _ 1G__________ 512M________ IXPS _ *___________ 64M_________ IXCP _ COPY ____________ ____________ IXDF _ DEFINE ____________ ____________ VW VIEW VWNM _ NAME ____________ ____________

  12. Schema management – migration • The generated output based on options selected will be ready for execution at the target site: • Unload generated for every table in the scope and unload dataset dynamically allocated • DDL extracted and altered to reflect the naming convention from Global Changes • Create statements for the target and catalog update statements (as opposed to runstats) • Load, Copy, Check utilities generated (all datasets dynamically allocated) • Bind statements for packages referenced (in this case only latest version found from source) • Parallel processing for utilities if desired (more later) • Sync points for easy restartability

  13. Migration in a 1-M environment • If more than one target environment exists • The same strategy could be analyzed multiple times with different sets of Global Changes • Unicenter RC/Migrator Copy Group Services is the answer to address this issue • Analyze the scope of objects included in the strategy once • Apply target specific Global Changes for every target specified in the Copy Group • One script created for every target included in the Copy group

  14. Copy Group Services • Each Global Change set describes one target • In this case FOUR Global Change Set (targets) have been defined(please see next slide for a snippet of the four sets) RMR1 R11.5 ------------ RC/M Global Change Services ----------- 06/01/26 13:06 COMMAND ===> SCROLL ===> CSR SET SSID ===> D81A SET NAME ===> I% CREATOR ===> RASST02 ---------------------------------------------------------------------- RASST02 S +---- LAST UPDATE -----+ O SETNAME DESCRIPTION CREATOR O USER DATE TIME _ ________ _________________________ RASST02 _ <== GLOBAL SET CREATION _ INTEG01 change issue 133214 RASST02 U RASST02 05/01/14 09:06 _ IDUGGBL1 MIGRATE TO IDUG01 D81A RASST02 U RASST02 06/01/25 16:48 _ IDUGGBL2 MIGRATE TO IDUG02 D81B RASST02 U RASST02 06/01/26 13:03 _ IDUGGBL3 MIGRATE TO IDUG03 D81C RASST02 U RASST02 06/01/26 13:05 _ IDUGGBL4 MIGRATE TO IDUG07 D81C RASST02 U RASST02 06/01/26 13:06 _ IXXXX01 MOVE PAY APPL RI RASST02 U RASST02 05/10/04 20:12 ******************************** BOTTOM OF DATA *******************************

  15. Copy Group Services -------------- RC/M Global Changes ----------------------- SET NAME==> IDUGGBL1 SHARE OPTION==>U CREATOR ==> RASST02 DESCRIPTION ==>MIGRATE TO IDUG01 D81A CODE CHANGE DESCRIPTION FROM TO AL ALL OBJECT TYPES ALVS VCAT > STOGROUP (MO) ALSV STOGROUP > VCAT (MO) ALCR CREATOR DSN8810 IDUG01 ALCR * IDUG02 ALDB DATABASE NAME * IDUG06DB ALTS TABLESPACE NAME DSN8S% IDUGS% -------------- RC/M Global Changes ----------------------- SET NAME==> IDUGGBL3 SHARE OPTION==>U CREATOR ==> RASST02 DESCRIPTION ==>MIGRATE TO IDUG03 D81C CODE CHANGE DESCRIPTION FROM TO AL ALL OBJECT TYPES ALVS VCAT > STOGROUP (MO) ALSV STOGROUP > VCAT (MO) ALCR CREATOR DSN8810 IDUG03 ALCR * IDUG03 ALDB DATABASE NAME * IDUG26DB ALTS TABLESPACE NAME DSN8S% IDUGS% -------------- RC/M Global Changes ----------------------- SET NAME==> IDUGGBL2 SHARE OPTION==>U CREATOR ==> RASST02 DESCRIPTION ==>MIGRATE TO IDUG02 D81B CODE CHANGE DESCRIPTION FROM TO AL ALL OBJECT TYPES ALVS VCAT > STOGROUP (MO) ALSV STOGROUP > VCAT (MO) ALCR CREATOR DSN8810 IDUG02 ALCR * IDUG02 ALDB DATABASE NAME * IDUG16DB ALTS TABLESPACE NAME DSN8S% IDUGS% -------------- RC/M Global Changes ----------------------- SET NAME==> IDUGGBL7 SHARE OPTION==>U CREATOR ==> RASST02 DESCRIPTION ==>MIGRATE TO IDUG07 D81C CODE CHANGE DESCRIPTION FROM TO AL ALL OBJECT TYPES ALVS VCAT > STOGROUP (MO) ALSV STOGROUP > VCAT (MO) ALCR CREATOR DSN8810 IDUG07 ALCR * IDUG07 ALDB DATABASE NAME * IDUG76DB ALTS TABLESPACE NAME DSN8S% IDUGS%

  16. Copy Group Services • Re-use existing Global Change definitions • One Copy Group can hold one or many Global Change definitions = target environments • Provide the ability to generate scripts for multiple environments in one execution • Saves CPU and time • Can be used for the synchronization process too (compare environments using Unicenter RC/Compare will be covered later)

  17. Copy Group Services • Copy Group IDUG4ENV is created referencing the four Global Change Sets recently created. RMCG3 R11.5 -------------- RC/M Copy Group Update ------------- 06/01/26 15:13 COMMAND ===> SCROLL ===> CSR Groupname ===> IDUG4ENV Description ===> 01+02+03+07 QA ENV. Creator ===> RASST02 Share Option ===> U (U,Y,N) ---------------------------------------------------------------------- RASST02 GLOBAL GLOBAL-CHANGE-SET TRG UTILITY O NUM COPY-ID DESCRIPTION CHANGES CREATOR NAME SSID OPTIONS _ 1 QA01 IDUG01 ENV. Y RASST02 IDUGGBL1 D81A N _ 2 QA02 IDUG02 ENV. Y RASST02 IDUGGBL2 D81B N _ 3 QA03 IDUG03 ENV. Y RASST02 IDUGGBL3 D81C N _ 4 QA04 IDUG07 ENV. Y RASST02 IDUGGBL4 D81C N _ 5 ____ _________________________ N ________ ________ ____ N ******************************* BOTTOM OF DATA ******************************** Valid O Commands: D, I, R Press END to save group Each target can have individual UTILITY OPTIONS - like which ones to execute and different naming conventions

  18. Copy Group Services - output • Generating the scripts using Copy Group results in four individual executable scripts. • First 4 bytes taken from strategy • Next four bytes is the Copy Group Copy-id RMS1 R11.5 -------------- RC/M Strategy Services -------------- 06/01/27 17:00 COMMAND ===> SCROLL ===> CSR DB2 SSID ===> D81A STRATEGY ===> IDUG% CREATOR ===> RASST02 TYPE ===> * SRC SSID ===> * ---------------------------------------------------------------------- RASST02 T S SRC +---- LAST UPDATE ----+ O STRATEGY DESCRIPTION CREATOR P O SSID USER DATE TIME _ ________ _________________________ RASST02 _ N ____ <== STRATEGY CREATION _ IDUG001 MIGRATE TO IDUG ENV RASST02 M U D81A RASST02 06/01/24 13:06 _ * MANAGED OUTPUT * RASST02 06/01/24 13:09 _ CGRP * MANAGED OUTPUT * (IDUGQA01) RASST02 06/01/27 16:21 _ CGRP * MANAGED OUTPUT * (IDUGQA02) RASST02 06/01/27 16:21 _ CGRP * MANAGED OUTPUT * (IDUGQA03) RASST02 06/01/27 16:21 _ CGRP * MANAGED OUTPUT * (IDUGQA04) RASST02 06/01/27 16:21 ******************************** BOTTOM OF DATA *******************************

  19. Execution of scripts • Each script generated can be executed as is (serial process) or using a parallel process • Specify MAX number of parallel tasks • Unload’s executed in parallel • DDL executed as single task after unloads • Load, Runstats, Copy, Bind, Check etc. executed in parallel after DDL executed. • The utilities which are decided to execute in parallel are customized via Model Services using “eye-catchers”

  20. Execution of script in parallel UNLOAD TABLE1 UNLOAD TABLE2 UNLOAD TABLE3 SERIAL EXECUTION UNLOAD TABLE1 UNLOAD TABLE2 UNLOAD TABLE3 CREATE DB2 CREATE TABLESPACE SPX1 CREATE TABLESPACE SPX2 CREATE TABLESPACE SPX3 CREATE TABLE TBX1 CREATE TABLE TBX2 CREATE TABLE TBX3 CRAETE VIEWS CREATE ALIAS’s CREATE INDEXES ALTER TABLE ADD FK LOAD TABLE TBX1 LOAD TABLE TBX2 LOAD TABLE TBX3 RUNSTATS SPX1 RUNSTATS SPX2 RUNSTATS SPX3 COPY SPX1 COPY SPX2 COPY SPX3 BIND PACKAGES CREATE DDL and GRANT STATEMENTS LOAD TBX1 LOAD TBX2 LOAD TBX3 COPY SPX1 COPY SPX2 COPY SPX3 RUNSTATS STATEMENTS BIND PACKAGES This case illustrates only UNLOAD, LOAD and COPY have been specified to be executed in parallel

  21. Implementing environment specific processes • Can be almost anything which is specific to a DB2 site, a specific environment etc. • Every time CREATE TABLE is executed • GRANT specific users • CREATE ALIAS • Generate a DCLGEN • Call a User Program (e.g. REXX to insert information into a table) #IF(%SQLPOSA) #IF(%OBJDISP,=,CREATE) -- UPDATE TRAILER TABLE FOR processing .CONNECT %TOSSID INSERT INTO DBAP.DBA_TRAILER_OBJECT ( OBJECT1_ID, OBJECT2_ID, FUNCTION, OBJECTTYPE, USER_ID, CREATE_TS) VALUES ( '%CREATOR', '%OBJECT', 'XLAT', '%OBJTYPE', USER, CURRENT TIMESTAMP); SET CURRENT SQLID = USER; .DISCONN #ENDIF #ENDIF #IF(%OBJTYPE,=,TABLE) #IF(%OBJDISP,=,CREATE) GRANT SELECT ON TABLE %CREATOR..%OBJECT to DBAP1, DBAP2 ; #IF(%TOSSID,=,DB2P) CREATE ALIAS DW001.A%OBJECT for %CREATOR..%OBJECT #ENDIF #ENDIF #ENDIF #IF(%SQLPOSA) .CALL MYPGMA1 + PARM(DB2P,%OBJDISP¤%OBJTYPE¤%DBNAME¤%TSNAME) #ENDIF

  22. Cloning / Consolidating DB2’s • Scenarios where ALL data need to be copied/moved • Data Sharing consolidation – or simply “consolidating two DB2’s” • Move/Copy structures to other environments OR subsystems where all data is needed • Move a tablespace to another database • Cloning entire environments • Data Unload / Load often not an option due to time constraint and outage • Recovery of “new environment” from “source environment image copies” can be too time consuming too • RC/Merger which is an integral part of Unicenter RC/Migrator is using different high speed techniques to address these issues and limit the outage

  23. Cloning / Consolidating DB2’s • RC/Merger component • Use existing (or new) RC/Migrator definition • Global Changes can be applied to target • BIND’s can be migrated • AUTH can be migrated • DATA ONLY possible if target environment already in place • Compared to regular migration Analysis – Move analysis provides a set of additional parameters

  24. Cloning / Consolidating DB2’s RMA11M R11.5 ------- RC/M Strategy Move Analysis Options ------ 06/01/30 17:42 COMMAND ===> Enter SAVE to save settings. END to return. "?" in field for field level help. ----Control Options---------- ------Utility Options----- ----Output Options---- AUX IMPLODE ==> N STATS (Y,N,S,A) ==> N ANALYSIS HEADER ==> Y RI(LOCAL/GLOBAL) ==> L CHECK DATA ==> N IMPACT ANALYSIS ==> Y SECURITY ==> N IMAGE COPY ==> N RPT IN DDLFILE ==> Y DROP OBJECTS ==> N (B)IND/(R)EBIND ==> N DEFAULT SQLID ==> RASST02 Dataset Delete Options ALIAS SQLID ==> UNLOAD ==> Y LOB OBJECTS ==> Y TEMPORARY ==> Y RTN IMPLODE ==> (T,V,A,blank) RTN INTERACTION ==> N (Y,N,A,O,E,M,S) NO .AUTHS ==> Y -------Model Options------ --Data Unload Options-- MODEL ID ==> IDUGGBL ----IDCAMS Options---- ALL ROWS ==> Y MODEL CREATOR ==> RASST02 VSAM DEFINES ==> N DATA STATISTICS ==> N UPDATE MODEL ==> N VSAM DELETES ==> N ------------------------------RC/Merger Options-------------------------------- (M)OVE/(C)OPY ==> c MAXTASKS ==> 3 CONVERT IX ==> N (E)XCP/(V)SAM ==> E RESERVE OBIDS ==> Y REORG TS ==> N RESET PAGE RBA ==> Y COLLISION RPT ==> Y RECOVER IX ALL ==> N DATA ONLY COPY ==> N SHARE LEVEL ==> N ALLMSGS ==> N

  25. Cloning / Consolidating DB2’s • RC/Merger process: • “Placeholder” tables created in “placeholder” tablespace attempting to reserve OBID’s • Target environment created • Data copy/move • If MOVE requested, VSAM datasets are renamed • If COPY requested, VSAM content copied to target VSAM dataset • OBID translation if necessary • Page RBA being reset

  26. Special Migration Scenarios • Converting from VCAT to STOGROUP • “Out of Extent” situation (MGEXTSZ will help to avoid) • Move dataset to another volume • All scenarios require either Reorg or Load replace where outage and resources is a challenge • Dataset Facility can minimize the outage and resources needed • High speed data movement (not row processing) • DB2 Catalog Maintenance if necessary • Let’s see an example

  27. Special Migration Scenarios DFCHG R11.5 -- Dataset Facility Change Dataset Allocations 2006/02/01 17:22 COMMAND ===> SCROLL ===> PAGE SSID: D81A LOC: LOCAL --------------------------- ACM: OFF ACMID: STEEN01 > Use 'S' and press ENTER to change datasets. S C DATABASE SPACENAM PART PRIQTY SECQTY UT USING VOLUME ALLOC _ * * _______ _______ __ ________ ______ _IDUG06DB IDUGS81B  88       -1  SGSYSDEFLT DB310796 s IDUG06DB IDUGS81P88 -1 SG SYSDEFLT DB3059720 ******************************* BOTTOM OF DATA ******************************** A tablespace has more space allocated than what is specified (could be extent failed). The NEW parameters specified. If the pageset was VCAT defined – we could change it to be STOGROUP defined as well – or move it to another volume. DFCHG R11.5 -- Dataset Facility Change Dataset Allocations 2006/02/01 17:22 COMMAND ===> SCROLL ===> PAGE SSID: D81A LOC: LOCAL --------------------------- ACM: OFF ACMID: STEEN01 > Use 'S' and press ENTER to change datasets. S C DATABASE SPACENAM PART PRIQTY SECQTY UT USING VOLUME ALLOC _ * * _______ _______ __ ________ ______ _IDUG06DB IDUGS81B  88       -1  SGSYSDEFLT DB310796 s CIDUG06DB IDUGS81P72001440 SG SYSDEFLT DB3059720 ******************************* BOTTOM OF DATA ********************************

  28. 1) The pageset is stopped 2) The necessary DB2 ALTER statements executed 3) VSAM DEFINE the “new” pageset modelling the “old” using alloc parameters 4) VSAM ALTER RENAME “old” to be “temp” 5) VSAM ALTER RENAME “new” to be “old” 6) VSAM ALTER DELETE “temp” 7) The pageset is started again Special Migration Scenarios PTBPDI R11.5 -------- Batch Processor Display Input -------- 2006/02/01 17:29 .CALL DFLMOVE INDDN(PTIIN) OUTDDN(PTIPRINT) .DATA SSID(D81A) TABLESPACE(IDUG06DB.IDUGS81P) VCAT(D81A) PRIQTY(720) SECQTY(1440) .ENDDATA .SYNC 5 'MOVE TABLESPACE(IDUG06DB.IDUGS81P)' .CALL DFLMOVE INDDN(PTIIN) OUTDDN(PTIPRINT) .DATA SSID(D81A) TABLESPACE(DSNDB07.DSN4K04) VCAT(D81A) USING(SG,D81A) VOLUMES( DB3051 ) .ENDDATA .SYNC 5 'MOVE TABLESPACE(DSNDB07.DSN4K04)' Can be executed ONLINE or BATCH, and more changes can be grouped in one execution

  29. Synchronizing Environments • Goal of synchronizing • Make target environment adopt certain differences from the source environment • Typical differences / changes: • New or changed columns • New or altered indexes • RI changes • New or dropped objects • Many possible methods to control and administer changes and how to synchronize • Which method to use depends on your local environment – how the “new structure” is born.

  30. Synchronizing Environments • A couple of examples: • New structure comes in from modeling tools (like Erwin) and test environment will have to adopt “changes” (DDL -> DB2 compare) Later this environment is compared to all other targets (systems test, QA, production) (DB2 -> DB2 compare) • Changes are implemented in “dictionary environment” in DB2 (objects exist with DEFINE NO) This environment is then compared to each target DB2 • “Current” production environment exists as a DDL-file (aka. Old baseline). The “new look” (can be a DDL-file or DB2 defined structure) is then compared to “old baseline” and a pseudo-DDL file is created (Incremental Change Language). This file only holds the changes – and then is compared to every target

  31. Synchronizing Environments • Prior to going live with a solid and automated DB2 change Management process – some initial issues need to be considered and defined : • Naming convention differences between the source(s) and target(s) – MAPPING / MASKING • When differences found between source and target attributes, which one should take precedence – COMPARE RULES • For NEW objects (not on the target and by then not mapped), naming convention/standard applied – GLOBAL CHANGES • Does the target and/or source have objects which should be excluded from comparison – EXCLUDE OBJECTS (target might have additional indexes) • To use or not to use ICL – that is the question(advantages / disadvantages covered later)

  32. Synchronizing Environments Mapping source and target names is necessary when names and creators are different or a RENAME is necessary ---------------- RC/M Compare Automapping Masks --------------- 06/02/02 17:37 COMMAND ===> SCROLL ===> CSR Set Name ===> IDUG06TP Description ===> MAP TEST TO PROD Creator ===> RASST02 Share Option ===> U (U,Y,N) ---------------------------------------------------------------------- RASST02 OBJECT SOURCE TARGET STOGROUP _ CREATOR ________ ________ NAME ________ ________ DATABASE _ CREATOR % % NAME IDUG00% IDUG06% TABLESPACE _ DBNAME IDUG00% IDUG06% NAME = = TABLE _ CREATOR %00 %01 NAME = = _ COLNAME = = _ CREATOR %00 %01 NAME EMP EMP _ COLNAME COMMISSION COMM INDEX _ CREATOR %00 %01 NAME = = VIEW _ CREATOR %00 %01 Valid Commands: D, I, R Enter END to save

  33. Synchronizing Environments • Compare Rules for mapped objects RMR3 R11.5 ------------ RC/M Rule Database Services ----------- 06/02/03 09:42 Rulename ===> 00TO06 Description ===> COMPARE 00 TO 06 ENV Creator ===> RASST02 Share Option ===> U (U,Y,N) TRG SSID ===> * OBJECT ATTRIBUTE RULE TABLESPACE CREATEDBY N CREATOR N NAME N DATABASE N BUFFERPOOL N LOCKSIZE Y CLOSE N PASSWORD N PARTED Y SEGSIZE Y LOCKMAX N CCSID N TYPE N MAXROWS Y LOCKPART N DSSIZE N LOG Y DEFINE Y PARTITIONS Y VCAT Y STOGROUP Y OBJECT ATTRIBUTE RULE TABLE COL_LABEL Y COL_NUMBER Y COL_FIELDPROC Y COL_FIELDPARM Y COL_COMMENT Y DELETE_UNPAIRED_TARGET Y MOVE_UNPAIRED_SOURCE Y COLUMN_DEFAULT Y TYPESCHEMA Y START Y INCREMENT Y CACHE Y MAXVALUE Y MINVALUE Y CYCLE Y FOREIGN_KEY Y FK_TBCREATOR Y FK_TBNAME Y FK_DELRULE Y FK_COLNAME Y UNIQUE_CONSTRAINT Y

  34. Synchronizing Environments • Define source and target as well as Rules and Mask to use ----------------- RC/M Create Compare Strategy ---------------- 06/02/03 10:15 COMMAND ===> Strategy ===> IDUG002 Description ===> SYNC IDUG00 TO IDUG01 Creator ===> RASST02 Share Option ===> U (U,Y,N,X,L) ------------------------------------------------------------------------------- Object ===> db (SG,DB,TS,T,I,V,S,A,TG - Initial primary object type. May be changed during object selection.) SOURCE SQL SPECIFICATION: Dataset ===> 'ptidevl.virtuel.db2' Member ===> idugddl Volser ===> (If not cataloged) Obj Name ===> * > Obj Creator ===> * > TARGET SUBSYSTEM SPECIFICATION: SSID ===> d81a Location ===> LOCAL Obj Name ===> idug06db> Obj Creator ===> * > ACM ===> N ID ===> Where ===> N RULE SET SPECIFICATION: MASK SPECIFICATION: Rule Set ===> 00to06 Mask Set ===> idug06tp Creator ===> RASST02 Creator ===> RASST02 Press ENTER to process selections Enter END to go back Scroll is possible if LONG NAMES is exploited

  35. Synchronizing Environments • Now - source and target is mapped • Rules - whether source/target attribute takes precedence • Global changes applied to non-mapped objects • Exclude parameter (below) – which objects NOT to map ---------------- RC/M All Type Exclude Options ---------------- 06/02/03 10:29 COMMAND ===> PRIMARY OBJECT PAIR: SOURCE DATABASE ===> IDUG00DB TARGET DATABASE ===> IDUG06DB SOURCE CREATOR ===> RASST02 > TARGET CREATOR ===> RASST02 > --------------------------------------------------------------------- RASST02 EXCLUDE DEPENDENT OBJECTS: A - exclude all unmatched dependent objects of this type S - display a selectable list of unmatched dependent objects SOURCE TABLESPACES ===> _ TARGET TABLESPACES ===> _ SOURCE TABLES ===> _ TARGET TABLES ===> _ SOURCE INDEXES ===> _ TARGET INDEXES ===> a SOURCE VIEWS ===> _ TARGET VIEWS ===> _ SOURCE SYNONYMS ===> _ TARGET SYNONYMS ===> _ SOURCE ALIASES ===> a TARGET ALIASES ===> _ SOURCE TRIGGERS ===> _ TARGET TRIGGERS ===> _ DISPLAY DEPENDENT OBJECTS: Display all Dependent Objects ===> _ Y - display list of all dependents under this primary pair

  36. Synchronizing Environments • ICL analysis as opposed to target analysis is one method to compare, where output is pseudo-DDL • Can be imported to any target and analyzed ALTER TABLE IDUG01.EMP ALTER COLUMN COMM COLNAME COMMISSION ADD COLUMN TITLE FOLLOWS LASTNAME VARCHAR (45) NOT NULL DEFAULT 'unknown' FOR SBCS DATA ; CREATE TABLE IDUG00.NEW_TB001 ( TABLE_NAME CHAR ( 128 ) NOT NULL FOR SBCS DATA , INDEX_NAME CHAR ( 128 ) NOT NULL FOR SBCS DATA ) IN IDUG06DB.IDUGS81P CCSID EBCDIC ;

  37. Synchronizing Environments • A regular analysis (as apposed to ICL) will create a real script to ALTER if possible or unload, drop, create, load etc. the target objects to be in sync with the source dependent on the RULE SET in use. • The generated script is for a specific environment only • The output is ready to execute

  38. Synchronizing Environments • ICL advantages / disadvantages • When source is huge – perhaps 100,000 lines of DDL, and “DELTA” is small – easier to see impact • When many targets, comparing small “delta” to targets will execute a lot faster • Solid change management process need to be in place to avoid “invalid” ICL (like column placements)

  39. Synchronization tool necessary ? • DB2 V8 provides new ALTER capabilities • Only a few attribute changes possible • Many limitations • When the same changes need to be applied to many targets • Making the “wrong” changes can lead to outage • Compliance / regulatory requirements to document all the processes

  40. Chuck Sodowsky Advanced Schema Management – Migration procedures CA Charles.sodowsky@ca.com

More Related