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

      ******************************************************************

      /free

         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;

         endfor;

      /end-free                                                

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. 

~Richard

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

Read multi-member file using EXTMBR Keyword

The below code is the extraction of my sales records from a multi-member file. Currently hard coded to member 04 for testing purposes. The member name as variable with EXTMBR( ).

I am thinking I could have combined this code with my extract and load program but decided to go with a two step process instead. I also thought about using SQL and creating a table but don't have the time to figure it out right now.

If anyone has a suggestions please pass them on.

*************************************************************************

     Fvog012d3  if   e             disk    usropn extmbr(@mbr)
     Fmidslyrspfuf a e             disk

     D @mbr            s              5a                                        Member name
     D mbrNum          s              2a                                        Member number
     D mbrCnt          s              2s 0 inz(04)                              Member number


      ************************************************************************
      * Main Routine
      ************************************************************************
      /free

       // dou mbrCnt = 13;
       mbrNum = %editc(mbrCnt:'X');   // Convert month counter to alpha
       @mbr = 'R0M' + mbrNum;         // Member name variable

       if not %open(vog012d3);
         open vog012d3;              // Sales history open the file
       endif;

       read vog012d3;                // Read all records
       dow not %eof(vog012d3);

        if dscde <> '3' or dscde <> '4';   // Exclude discount codes 3 and 4

         vstr = str;                       // Store
         vdte = date;                      // Transaction date
         vseq = seq;                       // transaction sequece
         vcls = cls;                       // Class code
         vtr# = tran;                      // Register ID
         vqty = qty;                       // Quantity
         vpri = price;                     // Price
         vdsc = dscnt;                     // Discount
         vven = vend;                      // Vendor
         vsty = styl;                      // Style
         vclr = color;                     // Color
         vsiz = size;                      // Size
         write mdslsr;
        endif;
       read vog012d3;
       enddo;

       close vog012d3;                    // Close file

        // mbrcnt = mbrCnt + 1;           // Add 1 to counter to advance to next
                                          // member
        // enddo;                         // Loop through all the members

       *inlr = *on;                      // See ya!

      /end-free                                        

Have a great Friday!

 ~Richard

RPGLE EXTFILE and EXTMBR keywords

Great day, hit it out of the park. I started the morning wondering how I was going to combine 12 members of a multi-member file into one XML document and ended the day with one RPGLE Free, one RPGSQLLE and one CL program. Processed 25 million records, creating a five gigabyte file in the IFS in 20 minutes.

The requirement is to extract last 12 months sales history from the iSeries DB2 database, convert to XML document per API guide, transfer to PC server and load to SQL database. This process is an initial load and only be used a few times.

I called my good friend and excellent RPG programmer Rick and asked how would he combine all the records in a multi-member file. He suggested OVRDBF in a CL DO LOOP or QCMDEXC and construct the OVRBDF in a RPG program. Sounds doable, I have seen working code through debugging, but never written my own.

I could smack myself for not remembering the member parameter of the OVRDBF.  I never liked working or found use with that type of file, just pain. I am sure there must be good use somewhere.

With Rick’s suggestion and few searches with Google I discovered you no longer need QCMDEXC in your RPGLE to over ride database file and member. Two additional file specifications keywords EXTFILE( ) and EXTMBR( ) have been added.

I quickly hammered out the RPGLE Free program to extract the sales records to a temporary file created in library QTEMP.

Cloned one of my RPGLE programs, that creates daily sales XML output. Deleted all but what is needed for the yearly and massaged as needed.

Created a CL with three lines of code, one to create the empty file in QTEMP and two to call the RPGLE programs.

First time through I hard coded only one member to be extracted. This way I can test with a smaller set of data. Went over the output, the took out the hard coding, recompiled and ran again. Done in 20 minutes with output that looks pretty good. I need a little fine tuning and will have it completed in the morning. I will post some code if I get a chance.

Sleepy-nite-nite...

~Richard

Mapping the receiving and shipping process

With all the master file update/sync programs completed I am at a point where I have to dig into the business processes. It is relatively easy to take data from here and put in over there in a different format.

This is a welcomed break from programming. Some of the business processes have not been ironed out and I can not program the interface without further testing of the Island Pacific, MidRetail processes and business decisions agreed on.

Since Wednesday I am working on identifying the current process and exactly what the issues are. Here is the basic flow of the process and modifications. The yellow and green are new processes.

This is the first time I am working with a JIT operation. The current process is extremely manual and a ton of opportunities exists. I am pretty excited at the idea of helping the IT department integrate and update the current business systems.


I forgot to copy data files and programs to my thumb drive Friday afternoon so I will not be able to do some testing I planned. Oh well guess I will have to take the dogs to the park instead. ;)
Have a great weekend!

~Richard


If opportunity doesn't knock, build a door.  ~Milton Berle