I am noob to CL just started to dig deep into getting record from a text file.
How can I read the date field from a csv file and validate it if the date is equal to current date then insert the csv record into a table.
ABC_Rate.csv file contains the following information:
RIC,Ask Price,Bid Price,Universal Close Price Date
ABC,.1234,.123,20200107
If the Price Date (20200107) is equal to current date the record will be inserted into a table if not it will exit.
Here is my code:
I appreciate any help.
How can I read the date field from a csv file and validate it if the date is equal to current date then insert the csv record into a table.
ABC_Rate.csv file contains the following information:
RIC,Ask Price,Bid Price,Universal Close Price Date
ABC,.1234,.123,20200107
If the Price Date (20200107) is equal to current date the record will be inserted into a table if not it will exit.
Here is my code:
START: PGM
/* Declare required variables */
DCL VAR(&DAY) TYPE(*CHAR) LEN(2)
DCL VAR(&MONTH) TYPE(*CHAR) LEN(2)
DCL VAR(&YEAR) TYPE(*CHAR) LEN(2)
DCL VAR(&TRADEDATE) TYPE(*CHAR) LEN(8)
/* Declare required files */
DCLF FILE(QTEMP/FTPOUTPUT)
DCLF FILE(QTEMP/ABC) OPNID(A)
/* Check for any existing FTPINPUT file in QTEMP then ignore */
CHKOBJ OBJ(QTEMP/FTPINPUT) OBJTYPE(*FILE)
MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPINPUT) RCDLEN(112) OPTION(*NOSRC +
*NOLIST))
CLRPFM FILE(QTEMP/FTPINPUT)
CPYF FROMFILE(TSTSRCXX/S19031A) TOFILE(QTEMP/FTPINPUT) FROMMBR(ABCKINFILE) +
MBROPT(*REPLACE) FMTOPT(*CVTSRC)
OVRDBF FILE(INPUT) TOFILE(QTEMP/FTPINPUT) OVRSCOPE(*CALLLVL)
/* Create output file for monitoring the FTP status */
CHKOBJ OBJ(QTEMP/FTPOUTPUT) OBJTYPE(*FILE)
MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPOUTPUT) RCDLEN(132) OPTION(*NOSRC +
*NOLIST))
CLRPFM FILE(QTEMP/FTPOUTPUT)
OVRDBF FILE(OUTPUT) TOFILE(QTEMP/FTPOUTPUT) OVRSCOPE(*CALLLVL)
/* Connect to ABC FTP */
FTP RMTSYS(*INTNETADR) INTNETADR(HOSTED.DATASCOPE.REUTERS.COM) PORT(21)
/* Copy the output file */
CPYF FROMFILE(QTEMP/FTPOUTPUT) TOFILE(TSTSRCXX/S19031A) TOMBR(ABCOUTFILE) +
MBROPT(*REPLACE) FMTOPT(*CVTSRC)
/* Scan FTPLOG, if SUCCESSFUL TRANSFER (226) found, clear FILE1; */
/* else send operator message to check FTP status */
RCVF: RCVF
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
IF COND(%SST(&FTPOUTPUT 1 3) *EQ '226') THEN(DO)
GOTO CMDLBL(STEP1)
ENDDO
ELSE CMD(DO)
GOTO CMDLBL(RCVF)
ENDDO
ERROR: SNDSMTPEMM RCP((me@test.com *PRI)) SUBJECT('ABC ftp failed') NOTE('<p>Dear +
Support, <br /> <br />The daily ABC file has failed to be downloaded from +
ABC ftp site.<br/><br/><br/>Please do not reply.<br/> This is +
an automated mailing system </p>') CONTENT(*HTML)
GOTO CMDLBL(EXIT)
/* Check is content date match with current date */
STEP1: RTVSYSVAL SYSVAL(QDAY) RTNVAR(&DAY)
RTVSYSVAL SYSVAL(QMONTH) RTNVAR(&MONTH)
RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&YEAR)
CHGVAR VAR(&TRADEDATE) VALUE('20' *CAT &YEAR *CAT &MONTH *CAT &DAY)
/* Creat a temp table to insert the record into QTEMP/ABC THE BELOW PART CONTAIN THE ISSUE */
RUNSQLSTM SRCFILE(TSTSRCXX/S19031A) SRCMBR(CBKKQTEMP)
CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(QTEMP/ABC) RCDDLM(*CRLF) +
FROMRCD(2) RPLNULLVAL(*FLDDFT)
RCVF OPNID(A)
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EXIT))
STEP2: IF COND(%SST(&A_ABC 21 8) *EQ &TRADEDATE) THEN(GOTO CMDLBL(STEP3))
ELSE CMD(GOTO CMDLBL(EXIT))
/* Clear after input/output files being processed */
STEP3: DLTOVR FILE(*ALL)
MONMSG MSGID(CPF0000)
/* Insert ABC Rate into permanent table ABC */
CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(TSTDTAXX/ABC) RCDDLM(*CRLF) +
FROMRCD(2) RPLNULLVAL(*FLDDFT)
EXIT: ENDPGM
/* Declare required variables */
DCL VAR(&DAY) TYPE(*CHAR) LEN(2)
DCL VAR(&MONTH) TYPE(*CHAR) LEN(2)
DCL VAR(&YEAR) TYPE(*CHAR) LEN(2)
DCL VAR(&TRADEDATE) TYPE(*CHAR) LEN(8)
/* Declare required files */
DCLF FILE(QTEMP/FTPOUTPUT)
DCLF FILE(QTEMP/ABC) OPNID(A)
/* Check for any existing FTPINPUT file in QTEMP then ignore */
CHKOBJ OBJ(QTEMP/FTPINPUT) OBJTYPE(*FILE)
MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPINPUT) RCDLEN(112) OPTION(*NOSRC +
*NOLIST))
CLRPFM FILE(QTEMP/FTPINPUT)
CPYF FROMFILE(TSTSRCXX/S19031A) TOFILE(QTEMP/FTPINPUT) FROMMBR(ABCKINFILE) +
MBROPT(*REPLACE) FMTOPT(*CVTSRC)
OVRDBF FILE(INPUT) TOFILE(QTEMP/FTPINPUT) OVRSCOPE(*CALLLVL)
/* Create output file for monitoring the FTP status */
CHKOBJ OBJ(QTEMP/FTPOUTPUT) OBJTYPE(*FILE)
MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPOUTPUT) RCDLEN(132) OPTION(*NOSRC +
*NOLIST))
CLRPFM FILE(QTEMP/FTPOUTPUT)
OVRDBF FILE(OUTPUT) TOFILE(QTEMP/FTPOUTPUT) OVRSCOPE(*CALLLVL)
/* Connect to ABC FTP */
FTP RMTSYS(*INTNETADR) INTNETADR(HOSTED.DATASCOPE.REUTERS.COM) PORT(21)
/* Copy the output file */
CPYF FROMFILE(QTEMP/FTPOUTPUT) TOFILE(TSTSRCXX/S19031A) TOMBR(ABCOUTFILE) +
MBROPT(*REPLACE) FMTOPT(*CVTSRC)
/* Scan FTPLOG, if SUCCESSFUL TRANSFER (226) found, clear FILE1; */
/* else send operator message to check FTP status */
RCVF: RCVF
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
IF COND(%SST(&FTPOUTPUT 1 3) *EQ '226') THEN(DO)
GOTO CMDLBL(STEP1)
ENDDO
ELSE CMD(DO)
GOTO CMDLBL(RCVF)
ENDDO
ERROR: SNDSMTPEMM RCP((me@test.com *PRI)) SUBJECT('ABC ftp failed') NOTE('<p>Dear +
Support, <br /> <br />The daily ABC file has failed to be downloaded from +
ABC ftp site.<br/><br/><br/>Please do not reply.<br/> This is +
an automated mailing system </p>') CONTENT(*HTML)
GOTO CMDLBL(EXIT)
/* Check is content date match with current date */
STEP1: RTVSYSVAL SYSVAL(QDAY) RTNVAR(&DAY)
RTVSYSVAL SYSVAL(QMONTH) RTNVAR(&MONTH)
RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&YEAR)
CHGVAR VAR(&TRADEDATE) VALUE('20' *CAT &YEAR *CAT &MONTH *CAT &DAY)
/* Creat a temp table to insert the record into QTEMP/ABC THE BELOW PART CONTAIN THE ISSUE */
RUNSQLSTM SRCFILE(TSTSRCXX/S19031A) SRCMBR(CBKKQTEMP)
CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(QTEMP/ABC) RCDDLM(*CRLF) +
FROMRCD(2) RPLNULLVAL(*FLDDFT)
RCVF OPNID(A)
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EXIT))
STEP2: IF COND(%SST(&A_ABC 21 8) *EQ &TRADEDATE) THEN(GOTO CMDLBL(STEP3))
ELSE CMD(GOTO CMDLBL(EXIT))
/* Clear after input/output files being processed */
STEP3: DLTOVR FILE(*ALL)
MONMSG MSGID(CPF0000)
/* Insert ABC Rate into permanent table ABC */
CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(TSTDTAXX/ABC) RCDDLM(*CRLF) +
FROMRCD(2) RPLNULLVAL(*FLDDFT)
EXIT: ENDPGM
Comment