AS400 export data to IFS file

AS400 Export data to IFS file - A simple way

Sample Query file: GLAQRY.SQL

insert into qtemp/exp16z
select trim(coalesce(log_id,' ')) || '|' ||
trim(char(coalesce(rec_voucher,0))) || '|' ||
trim(char(coalesce(inv_voucher,0))) || '|' ||
trim(char(coalesce(depot,0))) || '|' ||
trim(coalesce(depot_name,' ')) || '|' ||
trim(char(coalesce(vendor_number,0))) || '|' ||
trim(coalesce(vendor_name,' ')) || '|' ||
trim(coalesce(vendor_code,' ')) || '|' ||
trim(coalesce(department,' ')) || '|' ||
trim(coalesce(activity_type,' ')) || '|' ||
trim(coalesce(type_id,' ')) || '|' ||
trim(coalesce(reference,' ')) || '|' ||
replace(char(coalesce(process_Date,date'0001-01-01')),'.','/') || '|' ||
replace(char(coalesce(activity_date,date'0001-01-01')),'.','/') || '|' ||
trim(coalesce(po_number,' ')) || '|' ||
trim(coalesce(kimball5,' ')) || '|' ||
trim(coalesce(incoterm,' ')) || '|' ||
trim(coalesce(shipping_terms,' ')) || '|' ||
trim(char(coalesce(carton_count,0))) || '|' ||
trim(char(coalesce(units_per_carton,0))) || '|' ||
trim(char(coalesce(cost_in_doc_currency,0))) || '|' ||
trim(coalesce(doc_currency,' ')) || '|' ||
trim(coalesce(depot_currency,' ')) || '|' ||
trim(char(coalesce(doc_fx_rate,0))) || '|' ||
trim(char(coalesce(cost_in_depot_currency,0))) || '|' ||
trim(char(coalesce(po_retail_value,0))) || '|' ||
trim(coalesce(suspend,' ')) || '|' ||
trim(coalesce(matched,' ')) || '|' ||
trim(coalesce(allocated,' ')) || '|' ||
trim(char(coalesce(cost_category,0))) || '|' ||
trim(char(coalesce(bingo_no,0))) || '|' ||
trim(char(coalesce(bingo_year,0))) || '|' ||
trim(char(coalesce(bingo_week,0))) || '|' ||
replace(char(coalesce(sent_to_sun,date'0001-01-01')),'.','/') || '|' ||
trim(coalesce(vat_code,' ')) || '|' ||
trim(coalesce(transport_mode,' ')) || '|' ||
trim(coalesce(exit_port,' ')) || '|' ||
trim(coalesce(exit_port_country,' ')) || '|' ||
trim(coalesce(factory,' ')) || '|' ||
trim(coalesce(factory_country,' ')) || '|' ||
trim(coalesce(vendor_country,' ')) || '|' ||
trim(coalesce(commodity_code,' ')) || '|' ||
trim(coalesce(item_name,' ')) || '|' ||
trim(char(coalesce(item_weight,0))) || '|' ||
trim(char(coalesce(gross_weight,0))) || '|' ||
trim(char(coalesce(net_weight,0)))
from GL_LOG_V15
where sent_to_sun = cast(now() as date) - 1 days
or (inv_voucher is null and process_date =  cast(now() as date) - 1 days)

CL Program to run the sql query and export the result to IFS

             PGM

             DCL        VAR(&FRMFILE) TYPE(*CHAR) LEN(100)/* FROM FILE*/
             DCL        VAR(&TOFILE) TYPE(*CHAR) LEN(100)/* TO FILE*/
             DCL        VAR(&PATH) TYPE(*CHAR) LEN(100) /* PATH */
             DCL        VAR(&LIBRARY) TYPE(*CHAR) LEN(10) /* LIBRARY*/
             DCL        VAR(&NRCD) TYPE(*DEC) LEN(10 0) /* Job ID */
             DCL        VAR(&DATETIME) TYPE(*CHAR) LEN(20)
             DCL        VAR(&TR_YEAR) TYPE(*CHAR) LEN(4)
             DCL        VAR(&TR_MONT) TYPE(*CHAR) LEN(2)
             DCL        VAR(&TR_DAY) TYPE(*CHAR) LEN(2)
             DCL        VAR(&TR_HOUR) TYPE(*CHAR) LEN(2)
             DCL        VAR(&TR_MINS) TYPE(*CHAR) LEN(2)
             DCL        VAR(&TR_SECS) TYPE(*CHAR) LEN(2)
             DCL        VAR(&TR_MSEC) TYPE(*CHAR) LEN(6)

             DLTF       FILE(QTEMP/EXP16Z)
             MONMSG     MSGID(CPF0000 CPF2105)

             CRTPF      FILE(QTEMP/EXP16Z) RCDLEN(2000) SIZE(*NOMAX)

             CHGVAR     VAR(&PATH) VALUE('/IFS' *TCAT &LIBRARY *TCAT +
                          '/GLOG/')

             CHGVAR     VAR(&FRMFILE) VALUE(&PATH *TCAT 'QRY/GLAQRY.SQL')

             RUNSQLSTM  SRCSTMF(&FRMFILE) COMMIT(*NC) DATFMT(*EUR)

             RTVMBRD    FILE(QTEMP/EXP16Z) NBRCURRCD(&NRCD)

             IF         COND(&NRCD *EQ 0) THEN(GOTO CMDLBL(ENDCMD))

             RTVSYSVAL  SYSVAL(QDATETIME) RTNVAR(&DATETIME)
             CHGVAR     VAR(&TR_YEAR) VALUE(%SST(&DATETIME 1 4))
             CHGVAR     VAR(&TR_MONT) VALUE(%SST(&DATETIME 5 2))
             CHGVAR     VAR(&TR_DAY) VALUE(%SST(&DATETIME 7 2))
             CHGVAR     VAR(&TR_HOUR) VALUE(%SST(&DATETIME 9 2))
             CHGVAR     VAR(&TR_MINS) VALUE(%SST(&DATETIME 11 2))
             CHGVAR     VAR(&TR_SECS) VALUE(%SST(&DATETIME 13 2))
             CHGVAR     VAR(&TR_MSEC) VALUE(%SST(&DATETIME 15 6))
             CHGVAR     VAR(&TOFILE) VALUE(&PATH *TCAT 'GL_ACTIVITY' +
                          *TCAT '_' +
                          *TCAT &TR_YEAR *TCAT '-' *TCAT &TR_MONT *TCAT +
                          '-' *TCAT &TR_DAY *TCAT '-' *TCAT &TR_HOUR +
             *TCAT      '.' *TCAT &TR_MINS *TCAT '.' *TCAT &TR_SECS *TCAT +
                          '.' *TCAT &TR_MSEC *TCAT '.TXT')

             CPYTOIMPF  FROMFILE(QTEMP/EXP16Z) TOSTMF(&TOFILE) +
                          MBROPT(*REPLACE) RCDDLM(*LF) DTAFMT(*FIXED)


ENDCMD:      ENDPGM