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

RPG V7R1 new features

The following are the new features of RPG in V7R1

1) Open access

2) Sorting and searching data in Data Structure Arrays

3) New operation extenders

4) %Lookup works with Datastructure Array

5) New BIF %SCANRPL

6) No need to have prototypes for subprocedures that are internal to the module

7) New keyword RTNPARM on the (Procedure Interface level) PI level

8)  ALIAS Longer names defined in DDS /SQL can be accessed in RPG

Sorting and searching data in Data Structure Arrays

Open Access

Now using file manipulation opcodes ( READ, WRITE, CHAIN, EXFMT ) we can access the data that is available not only on the 5250 stream. We can access from browser , Handhelp devices..etc..

For that we need to write the program which handles the request made by the RPG program and these are called “ Handlers”

We can have now sort the data in the Data Structure

Example:

 D InvoiceInfo     DS                  Dim(1000)

 D                                           Qualified

 D  Invoice#                       8    

 D  Invdate                          D     

 D  InvAmt                       12p 2  

    // If wanted to SortByName

      SortA  InvoiceInfo(*).Invoice;

    //If wanted to SortBy InvAmt

      SortA  InvoiceInfo(*).InvAmt;

The * indicates the level at which the sorting should occur. Of course, in this example, it’s pretty obvious, since it’s the only level where sorting is possible. But this sorting capability also works with nested Data Structures, so even very complicated structures can be sorted.

Two new operation extenders are available for SORTA

SORTA(A) -  for ascending

SORTA(D) – for descending

%Lookup works with Datastructure Array

Example:

Index = %LookUp( ‘ABC’: InvoiceInfo(*).Invoice#);

New BIF %SCANRPL ( To scan and replace )

This BIF is combination of %SCAN and %REPLACE

ROBOCOPY

ROBOCOPY

Robust File Copy for windows.

For help:
Open a command window > robocopy /?