Saturday, March 24, 2012

RPGLE Free array and external data structure

My current task is to write an ETL program to extract on hand inventory from Island  Pacific DB2 table, transform data to XML, load MIDRetail API, process MID job on  MS Server to update MIDRetail tables, retrieve status of MID job to iSeries. Depending on status additional workflow jobs are initiated.

The first challenge is to extract inventory from Island Pacific. This is a little unique and I have not seen inventory stored like this before. Each record contains 100 fields (BSTK01 thru BSTK00) where each field represents a particular store on hand quantity. If there is more than 100 stores for an item the record identifier field(BRID) is incremented.

BRID = 0  BSTK01 thru BSTK00 = Store 001 thru 100
BRID = 1  BSTK01 thru BSTK00 = Store 101 thru 200 
BRID = 2  BSTK01 thru BSTK00 = Store 201 thru 300 

Island Pacific supports a maximum of 900 stores. So record ID only 0 thru 8 could be used. 

After a brief call to my buddy Rick I have an idea of how to pivot the data to the required format. I refreshed my knowledge with the FOR loop earlier in the week but unsure of how. A search of the net revealed a way to use an external data structure to load an array based on pointer. I caught a break in that the inventory fields are contiguous.

I have been working with full procedural files lately staying away from the RPG cycle. Ooops, no *LR = on, dummy! Sometimes the cycle comes in handy and I really never understood why most programmers have moved away from using it.     
     fipbsdtl   ip   e           k disk
     fmidinvpf  o  a e             disk

     d myFileRec     e ds                  extname(ipbsdtl)
     d myFilePtr       s               *   inz(%addr(bstk01))    Pointer start
     d MYFILEMap       ds                  based(MYFILEPtr)
     d storeAry                            like(bstk01) dim(100)
     d strIdx          s              3  0                       Store index

      * Main Routine

         for strIdx = 1 to 100 by 1;
           ivstr = strIdx + (brid * 100);
           ivqty = storeAry(strIdx);
           ivcls = %editc(bcls:'X');
           ivven = %editc(bven:'X');
           ivsty = %editc(bsty:'X');
           ivclr = %editc(bclr:'X');
           ivsiz = %editc(bsiz:'X');
           ivdiv = bdiv;
           ivdep = bdpt;
           write mdinvr;


As you can see my output now has the Store(IVSTR) and On hand quanity(IVQTY) for each item. Item = IVCLS,IVEN,IVSTY, IVCLR,IVSIZ. 

When the record id and item changed(key), it starts all over again. I did notice the quantities looked like the are duplicating but a quick check and they are correct. 

I have to do some more data checking but I think I have the solution. If anyone spot an issue or has question or suggestion please comment.

So I accomplished reeducating myself this week with replacing DO loop from RPGIV to FOR loop in RPG Free, external data structures and array's.

Great fun and I finished of another week successfully advancing my skills and completing another interface program. 


Beta.  Software undergoes beta testing shortly before it's released.  Beta is Latin for "still doesn't work."  ~Author Unknown 


  1. Nice work. Only one question. Do you have to read the input primary file by the key? If not, remove the K in the F Spec and the program will buffer the input and reduce I/Os and speed up the program.

    1. The "style" is made up of class, vendor, style, color and size. I guess I don't need the key, I just nee to read through all the records. I'll try removing ans see what happens. Thanks!