ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

CA RC/Migrator for Db2 for Z/OS : What do the SQLPOSA/SQLPOSO/SQLPOSB variables do in an RC/Migrator model?

book

Article ID: 226331

calendar_today

Updated On:

Products

RC/Migrator for DB2 for z/OS

Issue/Introduction

What do the SQLPOSA/SQLPOSO/SQLPOSB variables do in an RC/Migrator model?

Environment

DB2 for Z/OS

Resolution

These automatic symbols are used for user defined utilities. Utilities added by users not provided by the product at install.

A user defined utility is always generated no matter what a strategy is doing where as a utility such as CHECK is only generated if the analysis option requesting it is set in the UPDATE OPTIONS.

The SQLPOSA, SQLPOSO and SQLPOSB Automatic Symbolic variables determine the position in the analysis output where user defined utilities are positioned. 

SQLPOSB
The user defined utility is positioned before all SQL to create objects and after the UNLOAD utility.

SQLPOSO
The user defined utility is positioned with the SQL to create an object.

SQLPOSA*
The user defined utility is positioned after all SQL and standard utilities.

1. Consider this utility model to carry out a DCLGEN. It is an OBJ type T which is for tables. 

UTILITY   OBJ DESCRIPTION                SIZE
DCLGEN    T   GENERATED DECLARATIONS     RPI

CMD SYMBOL    ERR  REPLACEMENT VALUE
_  DCLLIB         %USERID..HLQ1.DCLGEN1___________

#IF(%SQLPOSA)
.CALL DSN PARM(%TOSSID)
.DATA
DCLGEN TABLE(%CREATOR..%OBJECT) -
LIBRARY('%DCLLIB') -
ACTION(ADD) -
LANGUAGE(COB2) -
STRUCTURE(%OBJECT)
.ENDDATA
#ENDIF

The above model is utilising the %SQLPOSA. In a migration with this utility present the DCLGEN code is placed at the end after all the required utilities such as REBIND(if selected).

  REBIND PACKAGE(                                                      -
    SQPEQ200_SQE.SQA$PREP.(CAD200_2017-02-25-15.04.59)  -
    )                                                                  -
  QUALIFIER(authid1)                                                 -
    PLANMGMT(OFF)                                                      -
    APREUSE(NO)                                                        -
    APRETAINDUP(YES)                                                   -
    DYNAMICRULES(BIND)                                                 -
    DBPROTOCOL(DRDA)                                                   -
    OWNER(COLPE01)
.ENDDATA

.SYNC 6550      'REBIND PACKAGE'

.CALL DSN PARM(ssid)
.DATA
DCLGEN TABLE(PTI.ACCUM_STRATEGY)                                       -
LIBRARY('authid1.hlq01.DCLGEN1')                                       -
ACTION(ADD)                                                            -
LANGUAGE(COB2)                                                         -
STRUCTURE(ACCUM_STRATEGY)
.ENDDATA

.SYNC 6555      'DCLGEN TABLE PTI.ACCUM_STRATEGY'

2. Consider this utility model to carry out a DCLGEN. It is an OBJ type T which is for tables. 

UTILITY   OBJ DESCRIPTION                SIZE
DCLGEN    T   GENERATED DECLARATIONS     RPI

CMD SYMBOL    ERR  REPLACEMENT VALUE
 _  DCLLIB         %USERID..HLQ1.DCLGEN1___________

#IF(%SQLPOSB)
.CALL DSN PARM(%TOSSID)
.DATA
DCLGEN TABLE(%CREATOR..%OBJECT) -
LIBRARY('%DCLLIB') -
ACTION(ADD) -
LANGUAGE(COB2) -
STRUCTURE(%OBJECT)
.ENDDATA
#ENDIF


The above model is utilising the %SQLPOSB. In a migration with this utility present the DCLGEN code is placed before all SQL but after the initial utilities such as unloads.

.SYNC 960       'UNLOAD TABLE PTI.URA_TBS_1'

.CALL UTIL UNLOAD PARM(ssid)
.DATA
TEMPLATE UNLDDSN DSN 'authid1.PTDB.TID513.U8706.DA'
         SPACE CYL

UNLOAD
  FROM TABLE PTI.URA_VTOC_1
  HEADER     NONE
  LIMIT      ALL
  NOPAD
  UNLDDN     UNLDDSN
  SHRLEVEL   REFERENCE
.ENDDATA

.SYNC 965       'UNLOAD TABLE PTI.URA_VTOC_1'
.CALL DSN PARM(ssid)
.DATA
DCLGEN TABLE(PTI.ACCUM_STRATEGY)                                       -
LIBRARY('authid1.hlq01.DCLGEN1')                                       -
ACTION(ADD)                                                            -
LANGUAGE(COB2)                                                         -
STRUCTURE(ACCUM_STRATEGY)
.ENDDATA

.

.
then after the last DCLGEN utility there are the first SQL statements....
.

.

.SYNC 1940      'DCLGEN TABLE PTI.URA_TBS_1'

.CALL DSN PARM(ssid)
.DATA
DCLGEN TABLE(PTI.URA_VTOC_1)                                           -
LIBRARY('authid1.hlq01.DCLGEN1')                                       -
ACTION(ADD)                                                            -
LANGUAGE(COB2)                                                         -
STRUCTURE(URA_VTOC_1)
.ENDDATA

.SYNC 1945      'DCLGEN TABLE PTI.URA_VTOC_1'


.SYSTEM SQLDDL
.CONNECT ssid

-- **************************************************************
-- *                                                            *
-- * DATABASE CREATE AND ALTER STATEMENTS                       *
-- *                                                            *
-- **************************************************************

-- PTDB WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.

  SET CURRENT SQLID = 'authid01';

CREATE  DATABASE PTDB
        STOGROUP PTSG
           BUFFERPOOL BP1
           INDEXBP    BP2
           CCSID EBCDIC
    ;

3. Consider this utility model to carry out a DCLGEN. It is an OBJ type T which is for tables. 

UTILITY   OBJ DESCRIPTION                SIZE
DCLGEN    T   GENERATED DECLARATIONS     RPI

CMD SYMBOL    ERR  REPLACEMENT VALUE
 _  DCLLIB         %USERID..HLQ1.DCLGEN1___________
#IF(%SQLPOSO)
.CALL DSN PARM(%TOSSID)
.DATA
DCLGEN TABLE(%CREATOR..%OBJECT) -
LIBRARY('%DCLLIB') -
ACTION(ADD) -
LANGUAGE(COB2) -
STRUCTURE(%OBJECT)
.ENDDATA
#ENDIF


The above model is utilising the %SQLPOSO. In a migration with this utility present the DCLGEN code is placed after the SQL to create an object

In this case the user defined utility is a (T) or TABLE type model utility so it is positioned right after the last create table statements in the analysis. 

.SYNC 2735      'CREATE TABLE PTI.PTPLA_DOT_DDL_0101'

    SET CURRENT SQLID = USER;

-- PTI.PTPLA_DOT_DDL_0102 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.

    SET CURRENT SQLID = 'authid1';

    CREATE AUXILIARY TABLE PTI.PTPLA_DOT_DDL_0102
        IN PTDB.PTITSLAL
        STORES PTI.PTPLA_DOT_SYSTABLES_0102
        APPEND NO
        COLUMN DDL;

.SYNC 2740      'CREATE TABLE PTI.PTPLA_DOT_DDL_0102'

    SET CURRENT SQLID = USER;

.CALL DSN PARM(ssid)
.DATA
DCLGEN TABLE(PTI.ACCUM_STRATEGY)                                       -
LIBRARY('authid1.hlq01.DCLGEN1')                                       -
ACTION(ADD)                                                            -
LANGUAGE(COB2)                                                         -
STRUCTURE(ACCUM_STRATEGY)
.ENDDATA
.

.

.then after the last DCLGEN utility there would be the create index statements for the tables.

.

.

.SYNC 3715      'DCLGEN TABLE PTI.PTPLA_DOT_DDL_0101'

.CALL DSN PARM(ssid)
.DATA
DCLGEN TABLE(PTI.PTPLA_DOT_DDL_0102)                                   -
LIBRARY('authid1.hlq01.DCLGEN1')                                       -
ACTION(ADD)                                                            -
LANGUAGE(COB2)                                                         -
STRUCTURE(PTPLA_DOT_DDL_0102)
.ENDDATA

.SYNC 3720      'DCLGEN TABLE PTI.PTPLA_DOT_DDL_0102'


-- **************************************************************
-- *                                                            *
-- * INDEX CREATE AND ALTER STATEMENTS                          *
-- *                                                            *
-- **************************************************************

-- PTI.OLM_LIST_IX2 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.

  SET CURRENT SQLID = 'authid1';

CREATE  UNIQUE INDEX PTI.OLM_LIST_IX2 ON PTI.OLM_LIST
        ( LIST_ID ASC
          )
        USING STOGROUP PTSG
                                    ERASE NO
           FREEPAGE 0
           PCTFREE 10
           BUFFERPOOL BP2
           CLOSE YES
           PIECESIZE 2G
 ;
SYNC 3725      'CREATE INDEX PTI.OLM_LIST_IX2'

Additional Information

Conditional Automatic Symbols for All Objects

Regarding how to add a user defined utility such as DCLGEN, see also How do I create/add the DCLGEN Utility to a model to ONLY generate the DCLGEN in a migration?