Generate SQL for a specific set of tables and all their dependent objects only for tables that belong to one or two specific creators without generating utility statements in the process

Products

• RC/Migrator for DB2 for z/OS
• RC Compare for DB2 for z/OS

Issue/Introduction

RC/Migrator for Db2 for z/OS (RCM) can be used to generate pure SQL. The SQL may not in fact be executed at all but may be used in order to provide
information about the contents of the Db2 catalog to users who do not have access to it. It could also be manually altered and executed on another location
than it's original source by developers (for example as the starting point of new work.)  In this case do not want any utility statements generated. 
It could also be used as input to a DDL to SSID compare on another subsystem which does not have connectivity to the source subsystem.

Resolution

1. Create a RC/Migrator MIGRATION strategy.
   
   
2. On the "Create Migration Strategy" screen place an "E" next to TABLE in the "PRIMARY OBJECT TYPE SPECIFICATION". "E" stands for EXPLODE. This option will select all of the chosen primary objects and all their dependent objects which include Indexes, Views etc.
   
   
3. Next we need to specify the specific CREATORS that we are targeting. The CREATOR field at the bottom of the screen only provides for one CREATOR but if we use the WHERE field we can get around this restriction. Place a "Y" in the WHERE field.
   
   
4. You will be presented with the "SQL Selection Panel" screen. Here we can enter a WHERE clause that will get the tables we want. See the text that says "Where Clause:"
   
   
5. You will see normally 8 lines of SQL text that we can enter free text into. Then there is an ORDER BY and under that you can see a list of columns on the SYSIBM.SYSTABLES catalog table.
   
   
6. In the first line of text next to "01" we enter these words: AND :2 IN ('creator1', 'creator2'). 'Creator' is going to be two different table creators that you are looking for in this case. Then press enter.
   
   
7. After pressing enter this line is resolved like this " AND A.CREATOR IN ( 'CREATOR1', 'CREATOR2')" The :2 is replaced with A.CREATOR which is the second column on the list at the bottom of the page.
   
   
8. PF3 out of this and the Strategy is saved. You could go back and update the strategy and go in to change the SQL WHERE clause later if required by placing a "Y" in the WHERE clause field again as you did at the start.
   
   
9. It saves the strategy like this because there are no selections to be made manually. The strategy will resolve the where clause when it does the analysis and will pick the tables to process at that time based on that where clause.
   
   
10. Next we do the analysis. We want it to produce only SQL, no utilities or other syntax that's not SQL. In "Update options" we select these options
    

    NO .AUTHS ==> Y 
    
    BND/DAT/STAT/SQL/GRNT/RI ==> S 
    
    COMMIT ASAP ==> Y 

    
    No .Auths set to "Y" will produce no .AUTH authid statements to clutter up our SQL.
    
    Setting BND/DAT/STAT/SQL/GRNT/RI to "S" will produce no utility statements that would also clutter up our SQL. Using COMMIT ASAP set to "Y" will produce "COMMIT WORK;" statements instead of .SYNC statements. We don't want any .SYNC statements in our SQL because these are Batch Processor statements which are not recognized by anything else.
    
    When finished with these settings PF3 back and submit the analysis with these options. The analysis will contain only the tables that have creators that match our IN Clause, all the Indexes, Views and anything else hanging under the tables, no utility statements, no .SYNC's and no .AUTH's. Just pure SQL statements will be generated as we intended.
    
    There will be the usual RC/Migrator header info and Impact Analysis and Change Analysis reports. To further change the result you could also apply Global Changes at analysis time if needed.
    
    The SQL WHERE clause could be augmented to be much more complicated than this example depending on your requirements.
    
    The SQL can then be used by developers as they generally don't want to know or need to know what tablespaces their tables have been created in. The DBA normally worries about such placement and storage allocations.
    
    The other benefit of this pure SQL is that it can be executed by other programs that can run SQL like spufi, DSNTEP2 or QMF on another system without having to do any work to clean out any RC/Migrator specific syntax.

Additional Information

Exclusive Options

Quickly Select Objects for Migration and Analysis