advanced schema management migration procedures l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Advanced Schema Management – Migration Procedures PowerPoint Presentation
Download Presentation
Advanced Schema Management – Migration Procedures

Loading in 2 Seconds...

play fullscreen
1 / 40

Advanced Schema Management – Migration Procedures - PowerPoint PPT Presentation


  • 404 Views
  • Uploaded on

Advanced Schema Management – Migration Procedures Abstract

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Advanced Schema Management – Migration Procedures' - Gideon


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
abstract
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.
agenda
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
schema management challenges
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
schema management the basics
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
schema management migration
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.

schema management migration7
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.

schema management migration8
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’

schema management migration9
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.

schema management migration10
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 )

global changes partial snapshot
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 ____________ ____________

schema management migration12
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
migration in a 1 m environment
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
copy group services
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 *******************************

copy group services15
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%

copy group services16
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)
copy group services17
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

copy group services output
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 *******************************

execution of scripts
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”
execution of script in parallel
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

implementing environment specific processes
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

cloning consolidating db2 s
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
cloning consolidating db2 s23
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
cloning consolidating db2 s24
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

cloning consolidating db2 s25
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
special migration scenarios
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
special migration scenarios27
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 ********************************

special migration scenarios28
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

synchronizing environments
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.
synchronizing environments30
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
synchronizing environments31
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)
synchronizing environments32
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

synchronizing environments33
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

synchronizing environments34
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

synchronizing environments35
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

synchronizing environments36
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 ;

synchronizing environments37
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
synchronizing environments38
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)
synchronization tool necessary
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
chuck sodowsky
Chuck Sodowsky

Advanced Schema Management – Migration procedures

CA

Charles.sodowsky@ca.com