* $$ JOB JNM=POL501,DISP=D,CLASS=E,USER=OP1PSSS
* $$ PRT DISP=D,CLASS=I,DEST=(,DC02)
// JOB POL501 UNLOAD ACTIVE POLICIES AND MEMBERS FROM QUERY TABLES
// DLBL MUTPL,'POL501.MUT.PLCY.MEMBERS',0,VSAM,CAT=UCAT4, X
RECSIZE=31,RECORDS=131000
// DLBL FIRPL,'POL501.FIM.PLCY.MEMBERS',0,VSAM,CAT=UCAT4, X
RECSIZE=31,RECORDS=160000
// DLBL OTAPL,'POL501.OTA.PLCY.MEMBERS',0,VSAM,CAT=UCAT4, X
RECSIZE=31,RECORDS=40000
// EXEC ARIDBS,SIZE=AUTO
CONNECT SQLOPRT IDENTIFIED BY "DBAPSWD" TO DB2PROD;
DATAUNLOAD
SELECT PLCY_NMBR, MMBRS_NMBR, ASGND_AGENT_CD, SRVC_CNTY
FROM SQL.MUT_TABLES_P1
WHERE PLCY_STTS_CD = 'A' ;
OUTFILE (MUTPL PDEV(DASD));
DATAUNLOAD
SELECT POLICY_NUMBER, MMBRS_NMBR,
AGENT_CD , SRVCN_CNTY
FROM SQL.FIM_PLCY_P1
WHERE STTS_CD = 'A' ;
OUTFILE (FIRPL PDEV(DASD));
DATAUNLOAD
SELECT POLICY_TYPE||POLICY_NUMBER, MEMBER_NUMBER,
AGENT_CODE, COUNTY_CODE
FROM SQL.OTA_POLICY_P1
WHERE POLICY_STATUS IN ('J', 'M', 'K', '8', '9') ;
OUTFILE (OTAPL PDEV(DASD));
/*
/&
* $$ EOJ
* $$ JOB JNM=POL503,CLASS=E,DISP=D,USER=OP1PSSS
* $$ LST LST=SYSLST,CLASS=I,DISP=D,DEST=(,DC02)
// JOB POL503 SORT POLICY MEMBER FILES ON MEMBER NUMBER
// DLBL SORTIN1,'POL500.AUT.PLCY.MEMBERS',0,VSAM,CAT=UCAT4
// DLBL SORTIN2,'POL501.MUT.PLCY.MEMBERS',0,VSAM,CAT=UCAT4
// DLBL SORTIN3,'POL501.FIM.PLCY.MEMBERS',0,VSAM,CAT=UCAT4
// DLBL SORTIN4,'POL501.OTA.PLCY.MEMBERS',0,VSAM,CAT=UCAT4
// DLBL SORTOUT,'POL503.ALL.PLCY.MEMBERS',0,VSAM,CAT=UCAT4, X
RECORDS=500000,RECSIZE=31
// EXEC SORT,SIZE=400K
SORT FIELDS=(30,2,A,24,5,A,12,11,A,1,10,A),FORMAT=BI,WORK=9,FILES=4
RECORD TYPE=F,LENGTH=31
INPFIL BLKSIZE=31
OUTFIL BLKSIZE=3100
OPTION PRINT=ALL,LABEL=(S,S,S,S,S,S)
END
/*
// EXEC LISTLOG
/*
/&
* $$ EOJ
* $$ JOB JNM=POL504,DISP=D,CLASS=E,USER=OP1PSSS
* $$ PRT DISP=D,CLASS=I,DEST=(,DC02)
// JOB POL504 CREATE AND LOAD POLFILE TABLE IN QUERY
// DLBL FILEA,'POL503.ALL.PLCY.MEMBERS',0,VSAM,CAT=UCAT4
// EXEC ARIDBS,SIZE=AUTO
CONNECT SQLOPRT IDENTIFIED BY "DBAPSWD" TO DB2PROD;
SET AUTOCOMMIT (ON) ;
DROP DBSPACE PUBLIC.POLFILE ;
ACQUIRE PUBLIC DBSPACE NAMED POLFILE
(NHEADER=1,PAGES=30080,PCTINDEX=30,LOCK=PAGE,STORPOOL=5,PCTFREE=0) ;
COMMIT WORK ;
CREATE TABLE SQL.POL_FILE
(MEMBER_NUMBER INTEGER,
POLICY_NUMBER CHAR(10),
AGENT_CODE CHAR(05),
COUNTY_CODE CHAR(02))
IN POLFILE ;
SET ERRORMODE CONTINUE ;
DATALOAD TABLE (SQL.POL_FILE)
MEMBER_NUMBER 12-22
POLICY_NUMBER 1-10
AGENT_CODE 24-28
COUNTY_CODE 30-31
INFILE (FILEA BLKSZ(3100) PDEV(DASD) RECSZ(31) RECFM(FB))
COMMITCOUNT(20000) ;
COMMIT WORK ;
CREATE INDEX SQL.POL_FILE_I1 ON SQL.POL_FILE (MEMBER_NUMBER)
PCTFREE = 0 ;
CREATE INDEX SQL.POL_FILE_I2 ON SQL.POL_FILE (POLICY_NUMBER)
PCTFREE = 0 ;
GRANT SELECT ON SQL.POL_FILE TO SQLDYL ;
GRANT SELECT ON SQL.POL_FILE TO PUBLIC ;
INSERT INTO SQL.MIS_TABLE_DATA_P1 (LOAD_TS, TNAME)
VALUES (CURRENT TIMESTAMP, 'POL_FILE') ;
/*
/&
* $$ EOJ
* $$ JOB JNM=QPOLFLEB,DISP=D,CLASS=E,USER=OP1PSSS
* $$ PRT DISP=D,CLASS=I,DEST=(,DC02),JNM=QPOLFLB
/*
// JOB QPOLFLEB BACKUP POL_FILE FROM QUERY
// OPTION LOG
// TLBL QPOLFLE,'QPOLFLE,U'
// EXEC ARIDBS,SIZE=AUTO
CONNECT SQLOPRT IDENTIFIED BY "DBAPSWD" TO DB2PROD;
SET AUTOCOMMIT (ON) ;
SET ISOL (UR) ;
UNLOAD TABLE (SQL.POL_FILE)
OUTFILE (QPOLFLE BLKSZ(24723) PDEV(TAPE)) ;
/*
// EXEC LISTLOG
/*
/&
* $$ EOJ
* $$ JOB JNM=POLDEL,DISP=D,CLASS=E,USER=OP1PCPD
* $$ LST LST=SYSLST,CLASS=R,DISP=D,DEST=(%VM,%DEST),RBS=0
// JOB POLDEL DELETE WORK FILES
// EXEC IDCAMS,SIZE=AUTO
DELETE (POL500.AUT.PLCY.MEMBERS) CAT(VS004UC.USERCAT4)
DELETE (POL501.FIM.PLCY.MEMBERS) CAT(VS004UC.USERCAT4)
DELETE (POL501.MUT.PLCY.MEMBERS) CAT(VS004UC.USERCAT4)
DELETE (POL501.OTA.PLCY.MEMBERS) CAT(VS004UC.USERCAT4)
DELETE (POL503.ALL.PLCY.MEMBERS) CAT(VS004UC.USERCAT4)
/*
/&
* $$ EOJ
Return to the Sample Job Stream List
//POL501 JOB (POL501),' ',
// CLASS=A,
// MSGCLASS=X,
// MSGLEVEL=(1,1),
// REGION=6M
//*
//JOBLIB INCLUDE MEMBER=JOBLIB
//*
//* STEP0010 PGM=DSNTIAUL ARIDBS
//*
//STEP0010 EXEC PROC=DB2BATCH,
// PROGRAM=DSNTIAUL,
// PLAN=PLAN2,
// PARMS=SQL,
// SYSTEM=DB2P
//* MODULE ARIDBS PROCESSED BY PLUG-IN JCLTDBSU
//* SD VS O UCAT4 MUTPL 'POL501.MUT.PLCY.MEMBERS'
//SYSREC00 DD DSN=POL501.MUT.PLCY.MEMBERS,
// DISP=(NEW,CATLG,DELETE),
// LRECL=31,
// RECFM=FB,
// SPACE=(TRK,(82,20),RLSE),
// UNIT=SYSDA
//* SD VS O UCAT4 FIRPL 'POL501.FIM.PLCY.MEMBERS'
//SYSREC01 DD DSN=POL501.FIM.PLCY.MEMBERS,
// DISP=(NEW,CATLG,DELETE),
// LRECL=31,
// RECFM=FB,
// SPACE=(TRK,(100,25),RLSE),
// UNIT=SYSDA
//* SD VS O UCAT4 OTAPL 'POL501.OTA.PLCY.MEMBERS'
//SYSREC02 DD DSN=POL501.OTA.PLCY.MEMBERS,
// DISP=(NEW,CATLG,DELETE),
// LRECL=31,
// RECFM=FB,
// SPACE=(TRK,(25,6),RLSE),
// UNIT=SYSDA
//* PR O SYSLST 00E 0001
//SYSPRINT DD SYSOUT=I,
// DEST=(N1,DC02)
//* CD O
//SYSPUNCH DD SYSOUT=*
//* CD I
//SYSIN DD *
SELECT PLCY_NMBR, MMBRS_NMBR, ASGND_AGENT_CD, SRVC_CNTY
FROM SQL.MUT_TABLES_P1
WHERE PLCY_STTS_CD = 'A' ;
SELECT POLICY_NUMBER, MMBRS_NMBR,
AGENT_CD , SRVCN_CNTY
FROM SQL.FIM_PLCY_P1
WHERE STTS_CD = 'A' ;
SELECT POLICY_TYPE||POLICY_NUMBER, MEMBER_NUMBER,
AGENT_CODE, COUNTY_CODE
FROM SQL.OTA_POLICY_P1
WHERE POLICY_STATUS IN ('J', 'M', 'K', '8', '9') ;
/*
//*
//* STEP0020 PGM=SORT
//*
//STEP0020 EXEC PGM=SORT
//* MODULE SORT PROCESSED BY PLUG-IN JCLTSORT
//* SD VS I UCAT4 SORTIN1 'POL500.AUT.PLCY.MEMBERS'
//SORTIN DD DSN=POL500.AUT.PLCY.MEMBERS,
// DISP=SHR
//* SD VS I UCAT4 SORTIN2 'POL501.MUT.PLCY.MEMBERS'
// DD DSN=POL501.MUT.PLCY.MEMBERS,
// DISP=OLD
//* SD VS I UCAT4 SORTIN3 'POL501.FIM.PLCY.MEMBERS'
// DD DSN=POL501.FIM.PLCY.MEMBERS,
// DISP=OLD
//* SD VS I UCAT4 SORTIN4 'POL501.OTA.PLCY.MEMBERS'
// DD DSN=POL501.OTA.PLCY.MEMBERS,
// DISP=OLD
//* SD VS O UCAT4 'POL503.ALL.PLCY.MEMBERS'
//SORTOUT DD DSN=POL503.ALL.PLCY.MEMBERS,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(310,77),RLSE),
// UNIT=SYSDA
//* PR O
//SYSOUT DD SYSOUT=*
//* CD I
//SYSIN DD *
SORT FIELDS=(30,2,A,24,5,A,12,11,A,1,10,A),FORMAT=BI
RECORD TYPE=F,LENGTH=31
*INPFIL BLKSIZE=31
*OUTFIL BLKSIZE=3100
*OPTION PRINT=ALL,LABEL=(S,S,S,S,S,S)
END
/*
//*
//* STEP0030 PROC=DSNUPROC ARIDBS
//*
//STEP0030 EXEC PROC=DSNUPROC,PARM='DB2P'
//* MODULE ARIDBS PROCESSED BY PLUG-IN JCLTDBSU
//* SD VS I UCAT4 FILEA 'POL503.ALL.PLCY.MEMBERS'
//SYSREC DD DSN=POL503.ALL.PLCY.MEMBERS,
// DISP=OLD
//* PR O SYSLST 00E 0003
//SYSPRINT DD SYSOUT=I,
// DEST=(N1,DC02)
//* PR O
//UTPRINT DD SYSOUT=*
//* CD I
//SYSIN DD *
LOAD INDDN(SYSREC) RESUME(YES)
INTO TABLE SQL.POL_FILE
(MEMBER_NUMBER POSITION(12:22),
POLICY_NUMBER POSITION(1:10),
AGENT_CODE POSITION(24:28),
COUNTY_CODE POSITION(30:31))
/*
//*
//* STEP0040 PROC=DSNTEP2 ARIDBS
//*
//STEP0040 EXEC PROC=DSNTEP2
//* MODULE ARIDBS PROCESSED BY PLUG-IN JCLTDBSU
//* PR O SYSLST 00E 0003
//SYSPRINT DD SYSOUT=I,
// DEST=(N1,DC02)
//* CD I
//SYSIN DD *
INSERT INTO SQL.MIS_TABLE_DATA_P1 (LOAD_TS, TNAME)
VALUES (CURRENT TIMESTAMP, 'POL_FILE') ;
/*
//*
//* STEP0050 PROC=DSNUPROC ARIDBS
//*
//STEP0050 EXEC PROC=DSNUPROC,PARM='DB2P'
//* MODULE ARIDBS PROCESSED BY PLUG-IN JCLTDBSU
//* MT O SYS005 UA 'QPOLFLE,U'
//QPOLFLE DD DSN=QPOLFLE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=TAPE
//* PR O SYSLST 00E 0004
//SYSPRINT DD SYSOUT=I,
// DEST=(N1,DC02)
//* PR O
//UTPRINT DD SYSOUT=*
//* CD I
//SYSIN DD *
UNLOAD DATA FROM TABLE(SQL.POL_FILE) UNLDDN(QPOLFLE)
/*
//*
//* STEP0060 PGM=IDCAMS
//*
//STEP0060 EXEC PGM=IDCAMS
//* MODULE IDCAMS PROCESSED BY PLUG-IN JCLTAMS
//* VS I UCAT4 A 'POL500.AUT.PLCY.MEMBERS' DELETE
//#DELETE DD DUMMY,DSN=POL500.AUT.PLCY.MEMBERS
//* PR O
//SYSPRINT DD SYSOUT=*
//* CD I
//SYSIN DD *
DELETE (POL500.AUT.PLCY.MEMBERS)
IF LASTCC=8 THEN IF MAXCC=8 THEN SET MAXCC=0
/*
//*
//SCRATCH EXEC PGM=IEFBR14
//*
//DEL001 DD DSN=POL501.MUT.PLCY.MEMBERS,DISP=(MOD,DELETE)
//DEL002 DD DSN=POL501.FIM.PLCY.MEMBERS,DISP=(MOD,DELETE)
//DEL003 DD DSN=POL501.OTA.PLCY.MEMBERS,DISP=(MOD,DELETE)
//DEL004 DD DSN=POL503.ALL.PLCY.MEMBERS,DISP=(MOD,DELETE)
//
Return to the Sample Job Stream List