CA RC/Migrator for Db2 for Z/OS : What do the SQLPOSA/SQLPOSO/SQLPOSB variables do in an RC/Migrator model?
Article ID: 226331
Updated On:
Products
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?
Feedback
