Advertisement
Guest User

Untitled

a guest
Jul 27th, 2017
286
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SAS 9.59 KB | None | 0 0
  1. //PY9754JM JOB (00000-01644692-004-H6-238S,),                           J0056925
  2. //         'DECRYPT RQST',
  3. //*        TYPRUN=SCAN,
  4. //*        RESTART=BFCNTL.DELETE,      <-- RESTART BFCNTL
  5. //*        CLASS=W,                    <-- WEEKEND CLASS
  6. //*        CLASS=L,                    <-- OVERNIGHT CLASS
  7. //         REGION=8M,                  <-- MEMORY SIZE
  8. //         MSGCLASS=S,                 <-- HOLDS JOB OUTPUT
  9. //         NOTIFY=&SYSUID,             <-- NOTIFIES SUBMITTER
  10. //         TIME=30                     <-- TIME ALLOCATED TO JOB
  11. //*
  12. /*JBS BIND DB2BIND.DGP5
  13. //*
  14. //*********************************************************************
  15. //*  09754
  16. //*  DECRYPT PRSN_CDH
  17. //*********************************************************************
  18. //IOFCOPY  OUTPUT JESDS=ALL,DEST=ARCHIVE,CLASS=S
  19. //SARCOPY  OUTPUT JESDS=ALL,DEST=ARCHIVE,CLASS=A,DEFAULT=YES
  20. //PROFILE  SET CLIENT=09754,           <-- CLIENT NUMBER
  21. //         DBNODE=P509754E,            <-- DATABASE NODE
  22. //         DB2SYS=DBP5,                <-- DB SUBSYS (B)
  23. //         DBSYS=DGP5,                 <-- DB DATA SHARING SUBSYS (G)
  24. //         LBL=@YJM,                   <-- LABEL
  25. //         BRELEASE=4,                 <-- RELEASE NODE
  26. //         CLNTPRF=BA4                 <-- CLIENT/RELEASE PREFIX
  27. //*
  28. //ENVIRO   SET BENVIRO=NDVR.P.V4.P0BASE,  <-- BASE ENVIRONMENT LABEL
  29. //         CENVIRO=NDVR.P.V4.P&CLIENT, <-- CLIENT ENVIRONMENT LABEL
  30. //         TORP=P,                     <-- TEST OR PRODUCTION NODE
  31. //         CE=P.PRD                    <-- CALC ENGINE FILE OVRD
  32. //*
  33. //PRDLIBS  JCLLIB ORDER=(BASNDVR.P.V4.P&CLIENT..PROCLIB,
  34. //         BASNDVR.P.V4.P0BASE.PROCLIB,
  35. //         HRONDVR.P.P0BASE.PROCLIB)
  36. //*
  37. //LOADLIBS INCLUDE MEMBER=PRD          <-- PROD TBA CODE
  38. //*
  39. //*********************************************************************
  40. //* CNTL PROC - 000009500
  41. //*********************************************************************
  42. //BFCNTL   EXEC BFCNTL,                <-- BATCH FLOW CONTROL
  43. //         CNTL=.&LBL                  <-- JOB IDENTIFIER
  44. //*
  45. //*********************************************************************
  46. //* BFSAS
  47. //*********************************************************************
  48. //SAS1     EXEC BFSAS                  <-- SAS
  49. //PROFILE  DD DSN=BAS&CLIENT..&TORP.0.BFCNTL.&LBL..PROFILE,
  50. //         DISP=(SHR,,)
  51. //MACRO    DD DSN=BASNDVR.P.V4.P&CLIENT..SASMACS.SOURCE,
  52. //         DISP=(SHR,,)
  53. //SASCBTBL DD DISP=SHR,
  54. //         DSN=BASNDVR.P.V4.F0BASE.PARMLIB(SASCBTBL)
  55. //PARMS    DD DISP=SHR,
  56. //         DSN=BAS&CLIENT..P.V4.PARMLIB
  57. //IN1      DD DISP=SHR,
  58. //         DSN=BAS09754.P.P509754E.DCRDLKUP.ACCTNMBR
  59. //SASSYSIN DD *
  60.   /**/
  61.   %MACPREP
  62.   %PROFILE
  63.   %INCLUDE MACRO(DCPTDCRD);
  64.   %INCLUDE PARMS(PY9754JM);
  65.  
  66. %MACRO QUERYTBA(SRCHBY = );
  67. %*--------------------------------------------------------------------*
  68.  | Read in Flat File containing Debit Card Account Numbers or Internals.
  69.  *--------------------------------------------------------------------*;
  70.  DATA FILE;
  71.    INFILE IN1 MISSOVER;
  72.  
  73.    %IF(&SRCHBY = ACCT) %THEN %DO;
  74.  
  75.    INPUT @001 ACCT   $CHAR16.;
  76.  
  77.    OUTPUT FILE;
  78.  
  79.    %END;
  80.    %IF(&SRCHBY = INTN) %THEN %DO;
  81.  
  82.    INPUT @001 INTN   $CHAR09.;
  83.  
  84.    OUTPUT FILE;
  85.  
  86.    %END;
  87.  
  88.  RUN;
  89.  
  90. %PRINTEM(FILE);
  91.  
  92. %*--------------------------------------------------------------------*
  93.  | Sort the FILE dataset by search by input.
  94.  *--------------------------------------------------------------------*;
  95.  PROC SORT DATA = FILE; BY &SRCHBY; RUN;
  96. %*--------------------------------------------------------------------*
  97.  | Proc SQL
  98.  *--------------------------------------------------------------------*;
  99.  PROC SQL; CONNECT TO DB2 (SSID=&DGSYS); %PUT &SQLXRC &SQLXMSG;
  100.  
  101. %*--------------------------------------------------------------------*
  102.  | Query TBA for Acct, SSN, Client ID, and Internals.
  103.  *--------------------------------------------------------------------*;
  104.  CREATE TABLE ACCTS AS SELECT * FROM CONNECTION TO DB2
  105.  (SELECT CAST(A.PRSN_INTN_ID AS CHAR(09)) AS INTN
  106.        , A.TXPY_ID                        AS SSN
  107.        , A.EM_CLNT_ID                     AS CLNTID
  108.        , B.CDH_DATA_ROW_TX                AS ACCT
  109.     FROM &DATABASE..PRSN            A
  110.     JOIN &DATABASE..PRSN_CDH        B
  111.       ON B.PRSN_INTN_ID       = A.PRSN_INTN_ID
  112.      AND B.CDD_FLD_SET_DFN_ID = 40
  113.      AND B.PRSN_CDH_EFENDDT   = '2299-12-31'
  114.  )ORDER BY &SRCHBY;
  115.  
  116. %PUT &SQLXRC &SQLXMSG;
  117.     ALTER TABLE ACCTS MODIFY ACCT CHAR(16);
  118. %PUT &SQLXRC &SQLXMSG;
  119.  
  120. %*--------------------------------------------------------------------*
  121.  | Disconnect from DB2.
  122.  *--------------------------------------------------------------------*;
  123.   DISCONNECT FROM DB2; %PUT &SQLXRC &SQLXMSG; QUIT;
  124.  
  125. %PRINTEM(ACCTS);
  126.  
  127. %*--------------------------------------------------------------------*
  128.  | Decrypt the sub-stringed card numbers
  129.  *--------------------------------------------------------------------*;
  130.  %DCPTDCRD(IDSN=ACCTS
  131.           ,ODSN=DCPTACCTS
  132.           ,ENCRFLD=ACCT
  133.           ,DCPTFLD=ACCT
  134.           ,DROP=N
  135.           );
  136.  
  137. %*--------------------------------------------------------------------*
  138.  | Sort the DCPTACCTS dataset by search by input.
  139.  *--------------------------------------------------------------------*;
  140.  PROC SORT DATA = DCPTACCTS; BY &SRCHBY; RUN;
  141.  
  142. %PRINTEM(DCPTACCTS);
  143.  
  144. %*--------------------------------------------------------------------*
  145.  | Merge datasets by SRCHBY variable.
  146.  *--------------------------------------------------------------------*;
  147.  DATA LKUPOUT;
  148.   MERGE FILE     (IN=IN1)
  149.         DCPTACCTS(IN=IN2);
  150.      BY &SRCHBY;
  151.  
  152.      SELECT;
  153.       WHEN (IN1 AND IN2) DO;
  154.            OUTPUT LKUPOUT;
  155.       END;
  156.       OTHERWISE DO;
  157.            DELETE;
  158.       END;
  159.      END;
  160.  RUN;
  161.  
  162. %PRINTEM(LKUPOUT) RUN;
  163.  
  164. %MEND QUERYTBA;
  165.  
  166.  
  167. %MACRO REFRESHDB(RUNCHECK = "N");
  168.  
  169. %LET RUNCHECKB = "N";
  170.  
  171. %IF &RUNCHECK. = "A" %THEN %DO;
  172.  
  173. %LET RUNCHECK  = "Y";
  174. %LET RUNCHECKB = "Y";
  175.  
  176. %END;
  177. %IF &RUNCHECK. = "Y" %THEN %DO;
  178.  
  179. %*--------------------------------------------------------------------*
  180.  | Connect to DB2: Drop table if it already exists, then recreate it.
  181.  *--------------------------------------------------------------------*;
  182.  PROC SQL EXEC NOERRORSTOP;CONNECT TO DB2 (SSID=&DGSYS);
  183.                                                   %PUT &SQLXRC &SQLXMSG;
  184.  
  185.   %IF &RUNCHECKB. = "Y" %THEN %DO;
  186.    EXECUTE( DROP TABLE &SYSUID..DCRDLKUP) BY DB2
  187.    ;
  188.   %END;
  189.  
  190.    EXECUTE
  191.    (
  192.     CREATE TABLE &SYSUID..DCRDLKUP
  193.     (INTN                    CHAR(09) NOT NULL
  194.     ,SSN                     INTEGER  NOT NULL
  195.     ,ACCT                    INTEGER  NOT NULL
  196.     ,CLNTID                  CHAR(5)  NOT NULL
  197.     ,PRIMARY KEY (INTN,SSN,CLNTID)
  198.     ) BY DB2
  199.     ;
  200.  
  201.     EXECUTE
  202.    (
  203.       CREATE INDEX DCRD_INTN
  204.             ON &SYSUID..DCRDLKUP (INTN)
  205.     ) BY DB2
  206.     ;
  207.  
  208.     EXECUTE
  209.    (
  210.       CREATE INDEX DCRD_SSN
  211.             ON &SYSUID..DCRDLKUP (SSN)
  212.     ) BY DB2
  213.     ;
  214.  
  215.     EXECUTE
  216.    (
  217.       CREATE INDEX DCRD_CLNTID
  218.             ON &SYSUID..DCRDLKUP (CLNTID)
  219.     ) BY DB2
  220.     ;
  221.  
  222.     EXECUTE
  223.    (
  224.      GRANT ALL ON TABLE &SYSUID..DCRDLKUP TO @C2683B
  225.     ) BY DB2
  226.     ;
  227.  
  228. %*--------------------------------------------------------------------*
  229.  | Disconnect from DB2.
  230.  *--------------------------------------------------------------------*;
  231.   %PUT &SQLXRC &SQLXMSG; DISCONNECT FROM DB2; QUIT;
  232.  
  233. %MEND REFRESHDB;
  234.  
  235. %*--------------------------------------------------------------------*
  236.  | Beginning of Program - Execute Macros via Call.
  237.  *--------------------------------------------------------------------*;
  238. %QUERYTBA(SRCHBY = &SRCHBY);
  239. %REFRESHDB(RUNCHECK=&REFRESHDB.);
  240.  
  241. %*--------------------------------------------------------------------*
  242.  | Setup LIBNAME DB2UP in production database.
  243.  *--------------------------------------------------------------------*;
  244. LIBNAME DB2UP DB2       SSID=&DGSYS             AUTHID="P09754H"
  245.                         PRESERVE_COL_NAMES=YES  PRESERVE_TAB_NAMES=YES
  246.                         READ_LOCK_TYPE=NOLOCK   READ_ISOLATION_LEVEL=UR
  247.                         DBSLICEPARM=(ALL,8)
  248.                         SPOOL=NO
  249.                         UPDATE_LOCK_TYPE = TABLE
  250.                         DBCOMMIT=0
  251.                         ;
  252. %*--------------------------------------------------------------------*
  253.  |PROC SQL: Insert LKUPOUT dataset onto tables.
  254.  *--------------------------------------------------------------------*;
  255.  PROC SQL;
  256. %*--------------------------------------------------------------------*
  257.  |Insert the LKUPOUT dataset into the DCRDLKUP table in production.
  258.  *--------------------------------------------------------------------*;
  259.  INSERT INTO DB2UP.DCRDLKUP
  260.    SELECT *
  261.      FROM LKUPOUT;
  262. %*--------------------------------------------------------------------*
  263.  |Quit PROC SQL.
  264.  *--------------------------------------------------------------------*;
  265. %PUT &SQLXRC &SQLXMSG;
  266.  QUIT;
  267. %PUT &SQLXRC &SQLXMSG;
  268. %*--------------------------------------------------------------------*
  269.  |Email Reports.
  270.  *--------------------------------------------------------------------*;
  271. %PUTFILE(DSN=LKUPOUT
  272.          ,FILEDD=OUT1
  273.          ,DELIMITR=','
  274.          ,HEADER=Y
  275.          ,DBLQUOTE=N
  276.          ,LABEL=Y
  277.          ,VAR=INTN
  278.               SSN
  279.               ACCT
  280.               CLNTID
  281.          )
  282.  
  283. %BFMAIL(TO=&MAILTO
  284.         ,SUBJECT=Debit Card Lookup
  285.         ,FROM=&SYSJOBID
  286.         ,FILEDD=OUT1
  287.         ,FILETYPE=TEXT
  288.         ,FILENAME=DCRDLKUP1071.txt
  289.         );
  290. %*--------------------------------------------------------------------*
  291.  | End of Program
  292.  *--------------------------------------------------------------------*;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement