Scenario: Deploying and verifying database changes across multiple subsystems

The database administrator of a large IT organization needs to register, track, and deploy changes to database objects across multiple subsystems.

DB2® Administration Tool can store data directly into an DB2 Configuration Manager repository database. The repository database stores information about schema and authorization changes. The tight integration of the tools provides a single view of schema and authorization changes that are committed to DB2 for z/OS® for one or more DB2 systems.

Database administrators can use DB2 Configuration Manager user interface to view the DB2 information that is stored in Optim™ Configuration Manager. The interface provides various flexible filtering and sorting capabilities. For example, you can filter by statement type, object name, object type, user ID. The type of information that is stored for each change includes the following:

DB2 subsystem information
Unique change ID (determined by DB2 Admin)
Change name and comment about the change (determined by the user)
Status of the change beginning from when it first starts to run until it completes
What DDL and DCL statements were committed to DB2 for the change
The user that made the change

DB2 Administration Tool makes it possible to modify one or more databases on different subsystems, and then view the changes by using DB2 Configuration Manager.

Recording database changes that were made using DB2 Administration Tool change management in the DB2 Configuration Manager repository database has the following benefits:

DB2 Administration Tool data can be linked with other data that is stored in DB2 Configuration Manager. For example, DB2 Configuration Manager stores a snapshot of the DB2 catalog that shows differences (regardless of how they were made) between two points in time. This snapshot allows linking between DB2 Configuration Manager configuration changes and the DB2 Administration Tool viewer in DB2 Configuration Manager.
You can view the status of a change starting from when it begins running for the first time. The status of a change can be viewed across subsystems.
DDL, GRANT, and REVOKE statements that are applied to DB2 for a change are recorded.
Problem diagnostics and reporting by showing the ID of the person who ran the statement, the approximate run time, the change name and comment, and more. To determine why the change was made, you can customize the change name and comment so that it records your internal project and work order number of the change.

Example

The following example illustrates how a schema change that is made to three database subsystems using the change management batch interface is then viewed by using the DB2 Administration change management batch interface. The DB2 Configuration Manager front-end interface is used to display DB2 Administration data that is recorded in the DB2 Configuration Manager repository database.

For this example, assume data set SAMPLE.AOCOCM.DDL(W001) contains the following DDL:

SET CURRENT SCHEMA = 'OCMTEST';   
                                  
CREATE DATABASE OCMTEST;          
                                  
CREATE TABLE TB01 (C1 INT)        
  IN DATABASE OCMTEST;            
                                  
CREATE UNIQUE INDEX TB01X1        
  ON TB01 (C1)                    
  BUFFERPOOL BP1;

The following JCL provides an example of using the change management batch interface. This change management batch job will, for each of the three DB2 subsystems, create a change entry in the DB2 Administration Tool change management database, analyze the change to determine what is needed to apply the change on the respective DB2 subsystem, and apply the change statements to DB2.

//P001W001 JOB (SAMPLE,ICE,ICE,ICE),'SAMPLE',CLASS=B,     
//   MSGCLASS=H,MSGLEVEL=(1,1),NOTIFY=SAMPLE,TIME=(,30),          
//   REGION=0M                                                     
//*                                                                
/*JOBPARM S=SY4A                                                   
//*                                                                
//LSCLIBS JCLLIB ORDER=SAMPLE.AOCOCM.PROCLIB                      
//*                                                                     
//**********************************************************************
//* INPUT STATEMENTS                                                    
//**********************************************************************
//INPUT    EXEC PGM=IEFBR14                                             
//STMTS    DD DISP=(SHR,PASS),DSN=SAMPLE.AOCOCM.DDL(W001)              
//*                                                                     
//*************************************************                     
//DSNA     EXEC GOCCM,SSID=DSNA,PLAN=ADB                             
//PARMS  DD *                                                           
  CHANGE_NAME = 'OCMTEST: &PRJ#.:&WORK#.'                               
  CHANGE_COMMENT = 'CREATE OBJECTS'                                     
                                                                        
  ACTION_RUN_CHANGE = 'Y'                                               
                                                                        
  PREFIX_FOR_DATA_SETS = '&USERID..OCMTEST'                             
                                                                        
  SYMBOL_NAME = '&PRJ#.'  SYMBOL_VALUE = 'P001';                        
  SYMBOL_NAME = '&WORK#.' SYMBOL_VALUE = 'W001';                        
/*                                                                      
//IMCHG001 DD DISP=(SHR,PASS),DSN=*.INPUT.STMTS                         
//*************************************************                     
//DBAC     EXEC GOCCM,SSID=DBAC,PLAN=ADB                             
//PARMS  DD *                                                           
  CHANGE_NAME = 'OCMTEST: &PRJ#.:&WORK#.'                               
  CHANGE_COMMENT = 'CREATE OBJECTS'                                     
                                                                        
  ACTION_RUN_CHANGE = 'Y'                                               
                                                                        
  PREFIX_FOR_DATA_SETS = '&USERID..OCMTEST'                             
                                                                        
  SYMBOL_NAME = '&PRJ#.'  SYMBOL_VALUE = 'P001';                        
  SYMBOL_NAME = '&WORK#.' SYMBOL_VALUE = 'W001';                        
/*                                                                      
//IMCHG001 DD DISP=(SHR,PASS),DSN=*.INPUT.STMTS                         
//************************************************* 
//DB9C     EXEC GOCCM,SSID=DB9C,PLAN=ADB         
//PARMS  DD *                                       
  CHANGE_NAME = 'OCMTEST: &PRJ#.:&WORK#.'           
  CHANGE_COMMENT = 'CREATE OBJECTS'                 
                                                    
  ACTION_RUN_CHANGE = 'Y'                           
                                                    
  PREFIX_FOR_DATA_SETS = '&USERID..OCMTEST'         
                                                    
  SYMBOL_NAME = '&PRJ#.'  SYMBOL_VALUE = 'P001';    
  SYMBOL_NAME = '&WORK#.' SYMBOL_VALUE = 'W001';    
/*                                                  
//IMCHG001 DD DISP=(SHR,PASS),DSN=*.INPUT.STMTS

This example assumes that the objects were registered in the local change management database and created in DB2 as expected. The job completed with return code 0:

STEP:INPUT    PROC:         COND_CODE:   0
STEP:GOCCM    PROC:DSNA     COND_CODE:   0
STEP:GOCCM    PROC:DBAC     COND_CODE:   0
STEP:GOCCM    PROC:DB9C     COND_CODE:   0

Start DB2 Configuration Manager and select DB2 Admin Tool Explorer from the Open menu.
On the DB2 Admin Tool Explorer tab, the change entry data for each change on each subsystem is displayed. Notice that the status of each change is COMPLETE.
Click the change ID value for the DSNA location to show the details of one of the subsystem changes.
To view all of the changes, return to the summary view by clicking DB2 Admin Tool Explorer Summary, then, on the summary view, click Show All.
The details for all are displayed in a single view. You can apply even more filters to show only the changes for a table, a statement type, and so on.