Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //PY9754JM JOB (00000-01644692-004-H6-238S,), J0056925
- // 'DECRYPT RQST',
- //* TYPRUN=SCAN,
- //* RESTART=BFCNTL.DELETE, <-- RESTART BFCNTL
- //* CLASS=W, <-- WEEKEND CLASS
- //* CLASS=L, <-- OVERNIGHT CLASS
- // REGION=8M, <-- MEMORY SIZE
- // MSGCLASS=S, <-- HOLDS JOB OUTPUT
- // NOTIFY=&SYSUID, <-- NOTIFIES SUBMITTER
- // TIME=30 <-- TIME ALLOCATED TO JOB
- //*
- /*JBS BIND DB2BIND.DGP5
- //*
- //*********************************************************************
- //* 09754
- //* DECRYPT PRSN_CDH
- //*********************************************************************
- //IOFCOPY OUTPUT JESDS=ALL,DEST=ARCHIVE,CLASS=S
- //SARCOPY OUTPUT JESDS=ALL,DEST=ARCHIVE,CLASS=A,DEFAULT=YES
- //PROFILE SET CLIENT=09754, <-- CLIENT NUMBER
- // DBNODE=P509754E, <-- DATABASE NODE
- // DB2SYS=DBP5, <-- DB SUBSYS (B)
- // DBSYS=DGP5, <-- DB DATA SHARING SUBSYS (G)
- // LBL=@YJM, <-- LABEL
- // BRELEASE=4, <-- RELEASE NODE
- // CLNTPRF=BA4 <-- CLIENT/RELEASE PREFIX
- //*
- //ENVIRO SET BENVIRO=NDVR.P.V4.P0BASE, <-- BASE ENVIRONMENT LABEL
- // CENVIRO=NDVR.P.V4.P&CLIENT, <-- CLIENT ENVIRONMENT LABEL
- // TORP=P, <-- TEST OR PRODUCTION NODE
- // CE=P.PRD <-- CALC ENGINE FILE OVRD
- //*
- //PRDLIBS JCLLIB ORDER=(BASNDVR.P.V4.P&CLIENT..PROCLIB,
- // BASNDVR.P.V4.P0BASE.PROCLIB,
- // HRONDVR.P.P0BASE.PROCLIB)
- //*
- //LOADLIBS INCLUDE MEMBER=PRD <-- PROD TBA CODE
- //*
- //*********************************************************************
- //* CNTL PROC - 000009500
- //*********************************************************************
- //BFCNTL EXEC BFCNTL, <-- BATCH FLOW CONTROL
- // CNTL=.&LBL <-- JOB IDENTIFIER
- //*
- //*********************************************************************
- //* BFSAS
- //*********************************************************************
- //SAS1 EXEC BFSAS <-- SAS
- //PROFILE DD DSN=BAS&CLIENT..&TORP.0.BFCNTL.&LBL..PROFILE,
- // DISP=(SHR,,)
- //MACRO DD DSN=BASNDVR.P.V4.P&CLIENT..SASMACS.SOURCE,
- // DISP=(SHR,,)
- //SASCBTBL DD DISP=SHR,
- // DSN=BASNDVR.P.V4.F0BASE.PARMLIB(SASCBTBL)
- //PARMS DD DISP=SHR,
- // DSN=BAS&CLIENT..P.V4.PARMLIB
- //IN1 DD DISP=SHR,
- // DSN=BAS09754.P.P509754E.DCRDLKUP.ACCTNMBR
- //SASSYSIN DD *
- /**/
- %MACPREP
- %PROFILE
- %INCLUDE MACRO(DCPTDCRD);
- %INCLUDE PARMS(PY9754JM);
- %MACRO QUERYTBA(SRCHBY = );
- %*--------------------------------------------------------------------*
- | Read in Flat File containing Debit Card Account Numbers or Internals.
- *--------------------------------------------------------------------*;
- DATA FILE;
- INFILE IN1 MISSOVER;
- %IF(&SRCHBY = ACCT) %THEN %DO;
- INPUT @001 ACCT $CHAR16.;
- OUTPUT FILE;
- %END;
- %IF(&SRCHBY = INTN) %THEN %DO;
- INPUT @001 INTN $CHAR09.;
- OUTPUT FILE;
- %END;
- RUN;
- %PRINTEM(FILE);
- %*--------------------------------------------------------------------*
- | Sort the FILE dataset by search by input.
- *--------------------------------------------------------------------*;
- PROC SORT DATA = FILE; BY &SRCHBY; RUN;
- %*--------------------------------------------------------------------*
- | Proc SQL
- *--------------------------------------------------------------------*;
- PROC SQL; CONNECT TO DB2 (SSID=&DGSYS); %PUT &SQLXRC &SQLXMSG;
- %*--------------------------------------------------------------------*
- | Query TBA for Acct, SSN, Client ID, and Internals.
- *--------------------------------------------------------------------*;
- CREATE TABLE ACCTS AS SELECT * FROM CONNECTION TO DB2
- (SELECT CAST(A.PRSN_INTN_ID AS CHAR(09)) AS INTN
- , A.TXPY_ID AS SSN
- , A.EM_CLNT_ID AS CLNTID
- , B.CDH_DATA_ROW_TX AS ACCT
- FROM &DATABASE..PRSN A
- JOIN &DATABASE..PRSN_CDH B
- ON B.PRSN_INTN_ID = A.PRSN_INTN_ID
- AND B.CDD_FLD_SET_DFN_ID = 40
- AND B.PRSN_CDH_EFENDDT = '2299-12-31'
- )ORDER BY &SRCHBY;
- %PUT &SQLXRC &SQLXMSG;
- ALTER TABLE ACCTS MODIFY ACCT CHAR(16);
- %PUT &SQLXRC &SQLXMSG;
- %*--------------------------------------------------------------------*
- | Disconnect from DB2.
- *--------------------------------------------------------------------*;
- DISCONNECT FROM DB2; %PUT &SQLXRC &SQLXMSG; QUIT;
- %PRINTEM(ACCTS);
- %*--------------------------------------------------------------------*
- | Decrypt the sub-stringed card numbers
- *--------------------------------------------------------------------*;
- %DCPTDCRD(IDSN=ACCTS
- ,ODSN=DCPTACCTS
- ,ENCRFLD=ACCT
- ,DCPTFLD=ACCT
- ,DROP=N
- );
- %*--------------------------------------------------------------------*
- | Sort the DCPTACCTS dataset by search by input.
- *--------------------------------------------------------------------*;
- PROC SORT DATA = DCPTACCTS; BY &SRCHBY; RUN;
- %PRINTEM(DCPTACCTS);
- %*--------------------------------------------------------------------*
- | Merge datasets by SRCHBY variable.
- *--------------------------------------------------------------------*;
- DATA LKUPOUT;
- MERGE FILE (IN=IN1)
- DCPTACCTS(IN=IN2);
- BY &SRCHBY;
- SELECT;
- WHEN (IN1 AND IN2) DO;
- OUTPUT LKUPOUT;
- END;
- OTHERWISE DO;
- DELETE;
- END;
- END;
- RUN;
- %PRINTEM(LKUPOUT) RUN;
- %MEND QUERYTBA;
- %MACRO REFRESHDB(RUNCHECK = "N");
- %LET RUNCHECKB = "N";
- %IF &RUNCHECK. = "A" %THEN %DO;
- %LET RUNCHECK = "Y";
- %LET RUNCHECKB = "Y";
- %END;
- %IF &RUNCHECK. = "Y" %THEN %DO;
- %*--------------------------------------------------------------------*
- | Connect to DB2: Drop table if it already exists, then recreate it.
- *--------------------------------------------------------------------*;
- PROC SQL EXEC NOERRORSTOP;CONNECT TO DB2 (SSID=&DGSYS);
- %PUT &SQLXRC &SQLXMSG;
- %IF &RUNCHECKB. = "Y" %THEN %DO;
- EXECUTE( DROP TABLE &SYSUID..DCRDLKUP) BY DB2
- ;
- %END;
- EXECUTE
- (
- CREATE TABLE &SYSUID..DCRDLKUP
- (INTN CHAR(09) NOT NULL
- ,SSN INTEGER NOT NULL
- ,ACCT INTEGER NOT NULL
- ,CLNTID CHAR(5) NOT NULL
- ,PRIMARY KEY (INTN,SSN,CLNTID)
- ) BY DB2
- ;
- EXECUTE
- (
- CREATE INDEX DCRD_INTN
- ON &SYSUID..DCRDLKUP (INTN)
- ) BY DB2
- ;
- EXECUTE
- (
- CREATE INDEX DCRD_SSN
- ON &SYSUID..DCRDLKUP (SSN)
- ) BY DB2
- ;
- EXECUTE
- (
- CREATE INDEX DCRD_CLNTID
- ON &SYSUID..DCRDLKUP (CLNTID)
- ) BY DB2
- ;
- EXECUTE
- (
- GRANT ALL ON TABLE &SYSUID..DCRDLKUP TO @C2683B
- ) BY DB2
- ;
- %*--------------------------------------------------------------------*
- | Disconnect from DB2.
- *--------------------------------------------------------------------*;
- %PUT &SQLXRC &SQLXMSG; DISCONNECT FROM DB2; QUIT;
- %MEND REFRESHDB;
- %*--------------------------------------------------------------------*
- | Beginning of Program - Execute Macros via Call.
- *--------------------------------------------------------------------*;
- %QUERYTBA(SRCHBY = &SRCHBY);
- %REFRESHDB(RUNCHECK=&REFRESHDB.);
- %*--------------------------------------------------------------------*
- | Setup LIBNAME DB2UP in production database.
- *--------------------------------------------------------------------*;
- LIBNAME DB2UP DB2 SSID=&DGSYS AUTHID="P09754H"
- PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
- READ_LOCK_TYPE=NOLOCK READ_ISOLATION_LEVEL=UR
- DBSLICEPARM=(ALL,8)
- SPOOL=NO
- UPDATE_LOCK_TYPE = TABLE
- DBCOMMIT=0
- ;
- %*--------------------------------------------------------------------*
- |PROC SQL: Insert LKUPOUT dataset onto tables.
- *--------------------------------------------------------------------*;
- PROC SQL;
- %*--------------------------------------------------------------------*
- |Insert the LKUPOUT dataset into the DCRDLKUP table in production.
- *--------------------------------------------------------------------*;
- INSERT INTO DB2UP.DCRDLKUP
- SELECT *
- FROM LKUPOUT;
- %*--------------------------------------------------------------------*
- |Quit PROC SQL.
- *--------------------------------------------------------------------*;
- %PUT &SQLXRC &SQLXMSG;
- QUIT;
- %PUT &SQLXRC &SQLXMSG;
- %*--------------------------------------------------------------------*
- |Email Reports.
- *--------------------------------------------------------------------*;
- %PUTFILE(DSN=LKUPOUT
- ,FILEDD=OUT1
- ,DELIMITR=','
- ,HEADER=Y
- ,DBLQUOTE=N
- ,LABEL=Y
- ,VAR=INTN
- SSN
- ACCT
- CLNTID
- )
- %BFMAIL(TO=&MAILTO
- ,SUBJECT=Debit Card Lookup
- ,FROM=&SYSJOBID
- ,FILEDD=OUT1
- ,FILETYPE=TEXT
- ,FILENAME=DCRDLKUP1071.txt
- );
- %*--------------------------------------------------------------------*
- | End of Program
- *--------------------------------------------------------------------*;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement