Advanced Schema Management Migration Procedures

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,
  LOBs, UDTs .
• 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 1718 COMMAND gt
SCROLL
gt CSR
DB2 SSID
gt D81A
STRATEGY gt IDUG
CREATOR gt RASST02 TYPE gt SRC SSID
gt ----------------------------------------
------------------------------ 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 lt
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 1722 COMMAND gt


Strategy
gt IDUG001 Description gt MIGRATE TO
IDUG01 QA ENV. Creator gt
RASST02 Share Option gt U (U,Y,N,X,L) SRC
SSID gt 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 gt dsn8d gt Creator gt
gt Where gt 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 1727 Command gt

SCROLL gt PAGE

Name gt IDUG001 Share
gt N Default gt N Description gt
Panel RMDATABS View SQL
gt N Confirm Replace gt 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
VARgt 2 CREATOR VARgt 3 STGROUP
VARgt 4 BPOOL CHAR 5 DBID
SMAgt 6 IBMREQD CHAR 7
CREATEDBY VARgt 8 ROSHARE CHAR
9 TIMESTAMP TIMgt 10 TYPE
CHAR 11 GROUP_MEMBER VARgt 12 CREATEDTS
TIMgt
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 1813 COMMAND gt



STRATEGY gt IDUG001 DESCRIPTION gt
MIGRATE TO IDUG ENV. CREATOR gt
RASST02 SHAREOPTION gt U
------------------------------------
---------------------------------- RASST02

EXECUTION SPECIFICATIONS
ACCESS SPECIFICATIONS
EXECUTION MODE gt ( O - Online, B - Batch )
ACM ANALYSIS gt N OVERRIDE gt N
PDS ANALYSIS gt N
RECOVERY gt N


OPTION SPECIFICATIONS
DB2 SSID SPECIFICATIONS UPDATE
OPTIONS gt y SOURCE
SSID gt D81A GLOBAL CHANGES gt s
TARGET SSID gt D81a
SET NAME gt iduggbl1 SET CREATOR gt
rasst02

OUTPUT
DATASET SPECIFICATIONS
EDIT DATASET gt N

DATASET NAME gt 'PTIDEVL.VIRTUEL.DB2(IDUG001)'
VOLUME SERIAL gt
( 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 1816 COMMAND gt

RO039I Analysis Model has been
selected.
----Control Options----------
------Utility Options---- ----Output
Options---- AUX IMPLODE gt N STATS
(Y,N,S,A) gt s ANALYSIS HEADER gt
Y RI(LOCAL/GLOBAL) gt l CHECK DATA
gt y IMPACT ANALYSIS gt Y SECURITY
gt n IMAGE COPY (Y,P,N) gt N RPT IN
DDLFILE gt Y DROP OBJECTS gt N
(B)IND/(R)EBIND gt c
DEFAULT SQLID gt RASST02 RECOVER INDEX
gt N Dataset Delete Options ALIAS SQLID
gt UNLOAD
gt Y LOB OBJECTS gt y
TEMPORARY gt Y RTN
IMPLODE gt (T,V,A,blank)
RTN INTERACTION gt N
(Y,N,A,O,E,M,S)
--Data Unload Options-------- -------Model
Options------ ALL ROWS
gt y MODEL ID gt _at_DEFAULT
----IDCAMS Options---- NUMBER ROWS gt
MODEL CREATOR gt R115BPTI VSAM DEFINES gt
N DATA STATISTICS gt N UPDATE MODEL gt
N VSAM DELETES gt N TRUNCATE
gt N
-----Exclusive Options--------
NO
.AUTHS gt Y
BND/DAT/STAT/SQL/GRNT/R
I gt n ( B nd, D at, R -Stat, S QL, G rnt, R I
or N ) COMMIT ASAP gt n ( If SQL
only specified )
11
Global changes (partial snapshot)
--------------------- RC/M Global
Changes------------------

SET NAME gt IDUGGBL1 SHARE OPTION gt
U CREATOR gt
RASST02 DESCRIPTION gt MIGRATE TO IDUGQA

CODE
CHANGE DESCRIPTION FROM TO
AL ALL OBJECT TYPES
ALVS _ VCAT gt
STOGROUP (MO) ___________ _____________ ALSV
_ STOGROUP gt 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 gt IDUGGBL1 SHARE OPTION gt U
  
• CREATOR gt RASST02 DESCRIPTION gt 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 ____________
  ____________

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 1306 COMMAND gt

SCROLL gt CSR

SET SSID gt D81A
SET NAME gt I
CREATOR gt RASST02
---------------------------------------
------------------------------- RASST02


S ---- LAST UPDATE -----
O SETNAME DESCRIPTION CREATOR O
USER DATE TIME _ ________
_________________________ RASST02 _ lt GLOBAL
SET CREATION _ INTEG01 change issue
133214 RASST02 U RASST02 05/01/14
0906 _ IDUGGBL1 MIGRATE TO IDUG01 D81A
RASST02 U RASST02 06/01/25 1648 _
IDUGGBL2 MIGRATE TO IDUG02 D81B RASST02 U
RASST02 06/01/26 1303 _ IDUGGBL3 MIGRATE
TO IDUG03 D81C RASST02 U RASST02 06/01/26
1305 _ IDUGGBL4 MIGRATE TO IDUG07 D81C
RASST02 U RASST02 06/01/26 1306 _ IXXXX01
MOVE PAY APPL RI RASST02 U RASST02
05/10/04 2012
BOTTOM OF DATA
15
Copy Group Services
-------------- RC/M Global Changes
-----------------------

SET NAMEgt IDUGGBL1 SHARE OPTIONgtU
CREATOR gt RASST02
DESCRIPTION gtMIGRATE TO IDUG01 D81A

CODE
CHANGE DESCRIPTION FROM TO
AL ALL OBJECT TYPES
ALVS VCAT gt
STOGROUP (MO)
ALSV STOGROUP gt VCAT (MO)
ALCR
CREATOR DSN8810 IDUG01
ALCR
IDUG02 ALDB DATABASE NAME
IDUG06DB ALTS
TABLESPACE NAME DSN8S IDUGS
-------------- RC/M Global Changes
-----------------------

SET NAMEgt IDUGGBL3 SHARE OPTIONgtU
CREATOR gt RASST02
DESCRIPTION gtMIGRATE TO IDUG03 D81C

CODE
CHANGE DESCRIPTION FROM TO
AL ALL OBJECT TYPES
ALVS VCAT gt
STOGROUP (MO)
ALSV STOGROUP gt VCAT (MO)
ALCR
CREATOR DSN8810 IDUG03
ALCR
IDUG03 ALDB DATABASE NAME
IDUG26DB ALTS
TABLESPACE NAME DSN8S IDUGS
-------------- RC/M Global Changes
-----------------------

SET NAMEgt IDUGGBL2 SHARE OPTIONgtU
CREATOR gt RASST02
DESCRIPTION gtMIGRATE TO IDUG02 D81B

CODE
CHANGE DESCRIPTION FROM TO
AL ALL OBJECT TYPES
ALVS VCAT gt
STOGROUP (MO)
ALSV STOGROUP gt VCAT (MO)
ALCR
CREATOR DSN8810 IDUG02
ALCR
IDUG02 ALDB DATABASE NAME
IDUG16DB ALTS
TABLESPACE NAME DSN8S IDUGS
-------------- RC/M Global Changes
-----------------------

SET NAMEgt IDUGGBL7 SHARE OPTIONgtU
CREATOR gt RASST02
DESCRIPTION gtMIGRATE TO IDUG07 D81C

CODE
CHANGE DESCRIPTION FROM TO
AL ALL OBJECT TYPES
ALVS VCAT gt
STOGROUP (MO)
ALSV STOGROUP gt 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 1513 COMMAND gt

SCROLL gt CSR

Groupname gt IDUG4ENV Description
gt 01020307 QA ENV. Creator gt
RASST02 Share Option gt 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 1700 COMMAND gt
SCROLL
gt CSR
DB2 SSID
gt D81A
STRATEGY gt IDUG
CREATOR gt RASST02 TYPE gt SRC SSID
gt ----------------------------------------
------------------------------ RASST02


T S SRC ---- LAST UPDATE ----
O STRATEGY DESCRIPTION CREATOR P
O SSID USER DATE TIME _ ________
_________________________ RASST02 _ N ____ lt
STRATEGY CREATION _ IDUG001 MIGRATE TO IDUG
ENV RASST02 M U D81A RASST02 06/01/24
1306 _ MANAGED OUTPUT
RASST02 06/01/24 1309 _ CGRP
MANAGED OUTPUT (IDUGQA01)
RASST02 06/01/27 1621 _ CGRP MANAGED OUTPUT
(IDUGQA02) RASST02 06/01/27
1621 _ CGRP MANAGED OUTPUT (IDUGQA03)
RASST02 06/01/27 1621 _ CGRP
MANAGED OUTPUT (IDUGQA04)
RASST02 06/01/27 1621
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
• Unloads 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 ALIASs 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
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

• 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(OBJTYPE,,TABLE) IF(OBJDISP,,CREATE)
GRANT SELECT ON TABLE CREATOR..OBJECT to
DBAP1, DBAP2 IF(TOSSID,,DB2P) CREATE ALIAS
DW001.AOBJECT for CREATOR..OBJECT ENDIF END
IF ENDIF
IF(SQLPOSA) .CALL MYPGMA1
PARM(DB2P,OBJDISPOBJTYPEDBNAMETSNAME) ENDI
F
22
Cloning / Consolidating DB2s

• Scenarios where ALL data need to be copied/moved
• Data Sharing consolidation or simply
  consolidating two DB2s
• 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 DB2s

• RC/Merger component
• Use existing (or new) RC/Migrator definition
• Global Changes can be applied to target
• BINDs 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 DB2s
RMA11M R11.5 ------- RC/M Strategy Move
Analysis Options ------ 06/01/30 1742 COMMAND
gt


Enter SAVE to save settings. END to return.
"?" in field for field level help.

----Control Options----------
------Utility Options----- ----Output
Options---- AUX IMPLODE gt N STATS
(Y,N,S,A) gt N ANALYSIS HEADER gt Y
RI(LOCAL/GLOBAL) gt L CHECK DATA gt
N IMPACT ANALYSIS gt Y SECURITY
gt N IMAGE COPY gt N RPT IN
DDLFILE gt Y DROP OBJECTS gt N
(B)IND/(R)EBIND gt N
DEFAULT SQLID gt RASST02
Dataset Delete Options ALIAS SQLID
gt UNLOAD
gt Y LOB OBJECTS gt Y
TEMPORARY gt Y RTN
IMPLODE gt (T,V,A,blank)
RTN INTERACTION gt N
(Y,N,A,O,E,M,S)
NO .AUTHS gt Y
-------Model Options------
--Data Unload Options-- MODEL ID gt
IDUGGBL ----IDCAMS Options---- ALL ROWS
gt Y MODEL CREATOR gt RASST02 VSAM
DEFINES gt N DATA STATISTICS gt N
UPDATE MODEL gt N VSAM DELETES gt N
------------------------------RC/Merger
Options--------------------------------
(M)OVE/(C)OPY gt c MAXTASKS gt 3
CONVERT IX gt N (E)XCP/(V)SAM
gt E RESERVE OBIDS gt Y REORG TS
gt N RESET PAGE RBA gt Y
COLLISION RPT gt Y RECOVER IX ALL gt N
DATA ONLY COPY gt N SHARE LEVEL gt N
ALLMSGS gt N
25
Cloning / Consolidating DB2s

• RC/Merger process
• Placeholder tables created in placeholder
  tablespace attempting to reserve OBIDs
• 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
• Lets see an example

27
Special Migration Scenarios
DFCHG R11.5 -- Dataset Facility Change Dataset
Allocations 2006/02/01 1722 COMMAND gt
SCROLL
gt PAGE
SSID
D81A LOC LOCAL --------------------------- ACM
OFF ACMID STEEN01 gt Use 'S' and press ENTER
to change datasets.

S C DATABASE
SPACENAM PART PRIQTY SECQTY UT USING
VOLUME ALLOC _
_______ _______ __ ________ ______
_   IDUG06DB IDUGS81B           88       -1  SG 
SYSDEFLT DB3107       96  s IDUG06DB
IDUGS81P 88 -1 SG SYSDEFLT
DB3059 720
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 1722 COMMAND gt
SCROLL
gt PAGE
SSID
D81A LOC LOCAL --------------------------- ACM
OFF ACMID STEEN01 gt Use 'S' and press ENTER
to change datasets.

S C DATABASE
SPACENAM PART PRIQTY SECQTY UT USING
VOLUME ALLOC _
_______ _______ __ ________ ______
_   IDUG06DB IDUGS81B           88       -1  SG 
SYSDEFLT DB3107       96  s C IDUG06DB
IDUGS81P 7200 1440 SG SYSDEFLT
DB3059 720
BOTTOM OF DATA

28
Special Migration Scenarios

• 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

PTBPDI R11.5 -------- Batch Processor Display
Input -------- 2006/02/01 1729 .CALL DFLMOVE
INDDN(PTIIN) OUTDDN(PTIPRINT)
.DATA

SSID(D81A) TABLESPACE(IDUG06DB.IDUGS81P)
VCAT(D81A)

PRIQTY(720)

SECQTY(1440)
.ENDDATA

.SYNC 5 'MOVE TABLESPACE(IDUG06DB.IDUGS81
P)' .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 -gt DB2 compare)
• Later this environment is compared to
  all other targets (systems test, QA, production)
  (DB2 -gt 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
---------------- RC/M Compare Automapping Masks
--------------- 06/02/02 1737 COMMAND gt

SCROLL gt CSR

Set Name gt IDUG06TP Description gt MAP
TEST TO PROD Creator gt
RASST02 Share Option gt 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

• Mapping source and target names is necessary
  when names and creators are different or a RENAME
  is necessary

33
Synchronizing Environments

• Compare Rules for mapped objects

RMR3 R11.5 ------------ RC/M Rule Database
Services ----------- 06/02/03 0942

Rulename gt 00TO06
Description gt COMPARE 00 TO 06 ENV
Creator gt RASST02 Share Option gt
U (U,Y,N) TRG SSID gt
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 1015 COMMAND gt


Strategy
gt IDUG002 Description gt SYNC
IDUG00 TO IDUG01 Creator gt RASST02
Share Option gt U (U,Y,N,X,L)
-------------------------------------------
------------------------------------ Object
gt db (SG,DB,TS,T,I,V,S,A,TG - Initial primary
object type. May be
changed during object selection.)
SOURCE SQL SPECIFICATION
Dataset gt
'ptidevl.virtuel.db2'
Member gt idugddl
Volser gt (If not cataloged) Obj
Name gt gt Obj Creator gt
gt


TARGET
SUBSYSTEM SPECIFICATION
SSID gt d81a
Location gt LOCAL
Obj Name gt idug06db gt Obj Creator
gt gt ACM gt N
ID gt Where gt N

RULE
SET SPECIFICATION MASK SPECIFICATION
Rule Set gt 00to06
Mask Set gt idug06tp
Creator gt RASST02 Creator
gt 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 1029 COMMAND gt


PRIMARY
OBJECT PAIR
SOURCE DATABASE gt
IDUG00DB TARGET DATABASE gt IDUG06DB
SOURCE CREATOR gt RASST02 gt
TARGET CREATOR gt RASST02 gt
-------------------------------------------------
-------------------- RASST02 EXCLUDE DEPENDENT
OBJECTS
A - exclude all unmatched
dependent objects of this type
S - display a selectable list of unmatched
dependent objects

SOURCE TABLESPACES gt _
TARGET TABLESPACES gt _
SOURCE TABLES gt _ TARGET
TABLES gt _ SOURCE
INDEXES gt _ TARGET INDEXES
gt a SOURCE VIEWS gt _
TARGET VIEWS gt _
SOURCE SYNONYMS gt _ TARGET
SYNONYMS gt _ SOURCE
ALIASES gt a TARGET ALIASES
gt _ SOURCE TRIGGERS gt _
TARGET TRIGGERS gt _

DISPLAY DEPENDENT
OBJECTS
Display all Dependent Objects
gt _ 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_at_ca.com