----------------------------------------------------------------------
-- Name: SAMPLDBD: Z/OS IMS Unload Engine for full Oracle Staging
-- Table Refresh for a single DBD/Instance.
--
-- Client/Project:
----------------------------------------------------------------------
-- RUNTIME PARMS USED IN THIS SCRIPT:
-- IMS_INSTANCE - IMS INSTANCE of Source database
----------------------------------------------------------------------
-- CHANGE LOG:
----------------------------------------------------------------------
-- 2022/12/01 INITIAL RELEASE
----------------------------------------------------------------------
JOBNAME SAMPLDBD;
OPTIONS
CDCOP('I','U','D') -- Set CHANGE OP Constants
,PSEUDO NULL = NO; -- Do NOT simulate NULL for Invalid Source Data
------------------------------------------------------------
-- DATA DEFINITION SECTION
------------------------------------------------------------
------------------------------------------------------------
-- ENGINE PARAMETER DESCRIPTION
------------------------------------------------------------
DESCRIPTION SQLDDL ./ORADDL/ENGPARM.ddl AS S_ENGINE_PARM;
------------------------------------------------------------
-- SOURCE DESCRIPTIONS
------------------------------------------------------------
BEGIN GROUP IMS_DBD;
DESCRIPTION IMSDBD ./IMSDBD/SAMPLDBD.DBD AS SAMPLDBD;
END GROUP
;
BEGIN GROUP IMS_SEG;
-- *********************************************************
-- MUST INCLUDE DESCRIPTIONS FOR ALL SEGMENTS WITH LOG EXITS
-- AND PARENT SEGMENTS WHENEVER THEIR KEY FIELDS FROM LOG
-- RECORDS ARE REFERENCED EVEN WHEN LOG EXIT FOR CHILD
-- SEGMENT DOES NOT INCLUDE PATH DATA
-- *********************************************************
DESCRIPTION COBOL ./IMSSEG/SAMPLDBD/SAMPROOT.cob AS SAMPROOT
FOR SEGMENT SAMPROOT
IN DATABASE SAMPLDBD;
DESCRIPTION COBOL ./IMSSEG/SAMPLDBD/SAMCHLD1.cob AS SAMCHLD1
FOR SEGMENT SAMCHLD1
IN DATABASE SAMPLDBD;
DESCRIPTION COBOL ./IMSSEG/SAMPLDBD/SAMCHLD2.cob AS SAMCHLD2
FOR SEGMENT SAMCHLD2
IN DATABASE SAMPLDBD;
END GROUP
;
------------------------------------------------------------
-- TARGET DESCRIPTIONS
------------------------------------------------------------
BEGIN GROUP ORA_DDL;
DESCRIPTION ORASQL ./ORADDL/SAMPLDBD/SAMPROOT.ddl AS T_SAMPROOT;
DESCRIPTION ORASQL ./ORADDL/SAMPLDBD/SAMCHLD1.ddl AS T_SAMCHLD1;
DESCRIPTION ORASQL ./ORADDL/SAMPLDBD/SAMCHLD2.ddl AS T_SAMCHLD2;
DESCRIPTION ORASQL ./ORADDL/SEGCOUNT.ddl AS T_SEGCOUNT;
END GROUP
;
------------------------------------------------------------
-- DATASTORE SECTION
------------------------------------------------------------
------------------------------------------------------------
-- PARAMETER DATASTORE
------------------------------------------------------------
DATASTORE DD:SQDPARM
OF DELIMITED
COLDEL(',')
CHRDEL('"')
AS ENGINE_PARM
DESCRIBED BY S_ENGINE_PARM;
------------------------------------------------------------
-- SOURCE DATASTORE
------------------------------------------------------------
DATASTORE SAMPLDBD
OF IMSDB
AS CDCIN
EBCDIC
DESCRIBED BY GROUP IMS_SEG;
------------------------------------------------------------
-- TARGET DATASTORE(s)
------------------------------------------------------------
DATASTORE DD:SAMPROOT
OF DELIMITED
COLDEL('~')
CHRDEL('"')
AS T_SAMPROOT
DESCRIBED BY T_SAMPROOT
FOR INSERT;
DATASTORE DD:SAMCHLD1
OF DELIMITED
COLDEL('~')
CHRDEL('"')
AS T_SAMCHLD1
DESCRIBED BY T_SAMCHLD1
FOR INSERT;
DATASTORE DD:SAMCHLD2
OF DELIMITED
COLDEL('~')
CHRDEL('"')
AS T_SAMCHLD2
DESCRIBED BY T_SAMCHLD2
FOR INSERT;
DATASTORE DD:SEGCOUNT
OF DELIMITED
COLDEL('~')
CHRDEL('"')
AS T_SEGCOUNT
DESCRIBED BY T_SEGCOUNT
FOR INSERT;
------------------------------------------------------------
-- FIELD SPECIFICATION SECTION
------------------------------------------------------------
INVALID CDCIN.ALLNUM SETZERO;
-- Retypes for Multi-Byte fields
RETYPE SAMCHLD1.C1-NON-ROMAN-TEXT DTBINARY;
------------------------------------------------------------
-- VARIABLES
------------------------------------------------------------
-- Engine Type variable: C - CDC Apply, U - Unload (zOS)
DECLARE V_ENGINE_TYPE 1 'U';
-- Source/Target Working variables
DECLARE V_DTTM 20 ' ';
DECLARE V_SOURCE_APPLY_DTTM 20 ' ';
DECLARE V_SOURCE_CAPTURE_DTTM 20 ' ';
DECLARE V_SOURCE_CDC_ACTION_CODE 1 ' ';
DECLARE V_SOURCE_SYSTEM_INSTANCE 15 ' ';
DECLARE V_SOURCE_SYSTEM_ORIGIN 10 'GBS';
DECLARE V_SAMPROOT_CNT 6 '0';
DECLARE V_SAMCHLD1_CNT 6 '0';
DECLARE V_SAMCHLD2_CNT 6 '0';
------------------------------------------------------------
-- MAPPING PROCEDURES
------------------------------------------------------------
#INCLUDE ./CDCPROC/SAMPLDBD/SAMPROOT.sqd
#INCLUDE ./CDCPROC/SAMPLDBD/SAMCHLD1.sqd
#INCLUDE ./CDCPROC/SAMPLDBD/SAMCHLD2.sqd
------------------------------------------------------------
-- MAIN SECTION
------------------------------------------------------------
PROCESS INTO T_SAMPROOT
,T_SAMCHLD1
,T_SAMCHLD2
,T_SEGCOUNT
SELECT
{
V_SOURCE_CDC_ACTION_CODE = 'L'
V_SOURCE_SYSTEM_INSTANCE = S_ENGINE_PARM.IMS_INSTANCE
V_DTTM = LOCAL2GMT(TIMESTAMP ())
T_SEGCOUNT.UL_SOURCE_SYSTEM_INSTANCE = V_SOURCE_SYSTEM_INSTANCE
T_SEGCOUNT.UL_SOURCE_SYSTEM_ORIGIN = V_SOURCE_SYSTEM_ORIGIN
T_SEGCOUNT.UL_DATABASE_NAME = 'SAMPLDBD'
T_SEGCOUNT.UL_UNLOAD_DATE = STRING(LEFT(V_DTTM,4),'-',MID(V_DTTM,5,2),'-',MID(V_DTTM,7,2))
}
FROM ENGINE_PARM
UNION
SELECT
{
-- OUTMSG statement(s)for debugging ONLY and should be commented out when not needed
-- OUTMSG(0,STRING(IMSSEGNAME(CDCIN)))
V_DTTM = LOCAL2GMT(TIMESTAMP ())
V_SOURCE_APPLY_DTTM = V_DTTM
V_SOURCE_CAPTURE_DTTM = V_DTTM
CASE IMSSEGNAME(CDCIN)
WHEN 'SAMPROOT' { V_SAMPROOT_CNT = V_SAMPROOT_CNT + 1 CALLPROC(M_SAMPROOT) APPLY(T_SAMPROOT,T_SAMPROOT) }
WHEN 'SAMCHLD1' { V_SAMCHLD1_CNT = V_SAMCHLD1_CNT + 1 CALLPROC(M_SAMCHLD1) APPLY(T_SAMCHLD1,T_SAMCHLD1) }
WHEN 'SAMCHLD2' { V_SAMCHLD2_CNT = V_SAMCHLD2_CNT + 1 CALLPROC(M_SAMCHLD2) APPLY(T_SAMCHLD2,T_SAMCHLD2) }
}
FROM CDCIN
UNION
SELECT
{
-- Create a Segment Count record for each Segment unloaded from the Database
T_SEGCOUNT.UL_SEGMENT_NAME = 'SAMPROOT'
T_SEGCOUNT.UL_SEGMENT_COUNT = V_SAMPROOT_CNT
APPLY(T_SEGCOUNT,T_SEGCOUNT)
T_SEGCOUNT.UL_SEGMENT_NAME = 'SAMCHLD1'
T_SEGCOUNT.UL_SEGMENT_COUNT = V_SAMCHLD1_CNT
APPLY(T_SEGCOUNT,T_SEGCOUNT)
T_SEGCOUNT.UL_SEGMENT_NAME = 'SAMCHLD2'
T_SEGCOUNT.UL_SEGMENT_COUNT = V_SAMCHLD2_CNT
APPLY(T_SEGCOUNT,T_SEGCOUNT)
TERMINATE(0)
}
FROM NOTHING;