Moving on to next contract....

After four and half months I have completed 99% of the interfaces between iSeries Island Pacific ERP and MidRetial Allocation application running on SQL.

I  created over 52 programs and/or files that either send or retrieve data in XML using RPGLE, SQLRPGLE and IFS API’s. One of the best aspects of this project is that I was able to teach myself how to read and write directly to the windows file shares. Utilizing embedded SQL is also a lot of fun, the more I coded without the RPG cycle the simpler tasks became.

I think the best aspect of using SQL is the ability quickly adjust the SQL select statements as needs change.

The generic interface map.




The project went live over the weekend with some minor tweaks but overall a success!

Today I start a new contract as EDI Lead / Developer for a large retail chain. I am really looking forward to increasing my EDI skills in a retail environment. My previous EDI experience revolves around distribution and manufacturing supplying the retail vendors with ASN and Warehousing data.

~Richard

There are two ways to write error-free programs; only the third one works.  ~Alan J. Perlis

Two more weeks to go.....

We are almost done with implementing Midretail, I have been extremely busy coding and testing. The SQL works beautifully and is absolutely amazing. Learning embedded SQL and RPG Free has been exciting and challenging. I would have never been able to pull this off without the help of Google and all the wonderful people posting hints, tricks and code examples. I hope to find time to pay it forward and post more snip-its of code I created for interfacing the systems.

Special thanks to -
www.scottklement.com/
www.itjungle.com
www.iprodeveloper.com
www.code400.com
www.rpg-xml.com
www.bmeyers.net
www.ilerpgprogramming.com
www.winautomation.com


and many more....

I am starting to look forward to my new position as EDI Lead/Developer at a large retailer here in Jacksonville. I am hopeful for a long term relationship with challenging opportunities to work with different hardware / software platforms in a fast paced environment.

~Richard

A handful of patience is worth more than a bushel of brains.  ~Dutch Proverb

RPGLE SQL0511 for Update error.

Just when I think I have a handle on RPGLE Embedded SQL, SQL reaches out and slaps me silly.

I created a daily sales retrieval program using RPGLE Free and Embedded SQL which writes XML data to the /QNTC file system (windows share). I wanted to update the status and date field  after I write out the XML. I thought a simple to do, I have already done something similar with no problem. Then.....

SQL0511

SQL0511N

The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.
Explanation:
The result table of the SELECT or VALUES statement cannot be updated.
On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:

• The DISTINCT keyword
• A column function in the SELECT list
• A GROUP BY or HAVING clause
• A FROM clause that identifies one of the following:
• More than one table or view
• A read-only view
• An OUTER clause with a typed table or typed view
• A set operator (other than UNION ALL).
• A FROM clause that identifies one of the following:
• More than one table or view
• A read-only view
• An OUTER clause with a typed table or typed view
• A data change statement

Note that these conditions do not apply to subqueries of the SELECT statement.
The statement cannot be processed.
User response:
Do not perform updates on the result table as specified.
Federated system users: isolate the problem to the data source failing the request (see the problem determination guide for procedures to follow to identify the failing data source). If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -511
sqlstate: 42829

Oh me oh my. I don’t have any more time to spend on this and decided to skip the update of the status and data field at this time. The program is called remotely from a batch file that will also run a step that processes the XML into the Midretail allocation program. I will capture the result of the update to Midretail in the batch program and if successful will send another remote command to update the status and date of the records processed.

Exec SQL declare mainCursor cursor
         for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vstp, vdtp
             from MidSalesPF
             where vdte = :yesterday
             group by stat, vstr, vdte, vcls, vven, vsty, vclr, vstp, vdtp
             order by vstr, vdte, vdte, vcls, vven, vsty, vclr, vstp;

      Exec SQL open mainCursor;                 // SQL open cursor

      Exec SQL fetch next from mainCursor       // Load data Structure
            into :mainds;

      exsr ClrHdr;                    // Clear or create XML header record

      dow sqlstt = '00000' ;           // Continue if no SQL error [DO Loop]
       exsr  Load_SSColSR;             // Write detail XML records to IFS

       Exec SQL fetch next from mainCursor      // Get next record
             into :mainds;
      enddo ;                                                      
      Exec SQL close mainCursor;  // Close SQL file   

~Richard

The road to success is dotted with many tempting parking places.  ~Author Unknown

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 

Embedded SQL SQLSTT 42703 and 42618 errors...

Great news, found out yesterday that my contract is extended to the end of May. The company has decided to keep me on to help with implementation of the MIDRetail.

I ran into a problem with my daily sales XML output. I added date selection as a host variable to the Where clause in my SQL Select statements. I ran the program and no output, WTF!

I ran the program in debug and found SQLSTT is throwing an error 42703. After browsing Google I determined that the host variable is the Where clause is suspect. I used sub-procedures to make the SQL as generic as possible. I have a couple of SQL statements in the code and am changing the statements by evaluating a temporary field SQLV as needed.

* Standalone Variables
    d sqlV            s          32000a   varying                              varying as needed
    d sql1            s          32000a   varying                              Store/style/color
    d sql2            s          32000a   varying                              Update sales type
    d sql3            s          32000a   varying                              Store/style/color/sz   


begsr CrtSQLSR;
        // Daily Sales summerized by store, style, color = MAINDS
        sql1 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vstp +
             from MidSalesPF +
             where vdte = :yesterday and 1 = 1 +
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr +
             order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr' );

        // Daily Sales summerized by store, style, color, size = MAIN1DS
        sql3 = ('Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vsiz, vstp +
             from MidSalesPF +
             where vdte = :yesterday and 1 = 1 +
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, +
             vsiz +
             order by vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz' );

       endsr;                                                      
-------------------------------------------------
begsr Day_SscsizSR;

      clear sqlV; // Clear temp field
      sqlV = sql3; // SQL statement to run
      first = 'Y';                              // First pass flag
      declare();                                // SQL declare
      openCursor();                             // SQL open cursor
      Exec SQL fetch next from mainCursor       //Load data Structure
            into :main1ds;                 
blah,blah,blah.......

------------------------------------------------

p declare         b
    d declare         pi

    c/exec sql
    c+ declare mainCursor cursor
    c+     for mainStatement
    c/end-exec
    c/exec sql
    c+ prepare mainStatement
    c+    from :sqlV My problem child
    c/end-exec

    p declare         e
     *-------------------------------------------------------------------
    p openCursor      b
    d openCursor      pi

    c/exec sql
    c+ open mainCursor
    c/end-exec

    p openCursor      e
     *-------------------------------------------------------------------
    p closeCursor     b
    d closeCursor     pi

    c/exec sql
    c+ close mainCursor
    c/end-exec

    p closeCursor     e             
------------------------------------------------------------------------------------------------------

From what I can tell SQL does not like a variable inside a variable. The Where clause :yesterday inside :SQLV.

So I took it all apart and got rid of the sub-procedures.

begsr Day_SscsizSR;

      Exec SQL declare mainCursor2 cursor
         for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vsiz, vstp
             from MidSalesPF
             where vdte = :yesterday
             group by stat, vstr, vdte, vstp, vcls, vven, vsty, vclr, vsiz
             order by vstr, vdte, vdte, vstp, vcls, vven, vsty, vclr, vsiz;

      Exec SQL open mainCursor2;                             // SQL open cursor

      Exec SQL fetch next from mainCursor2       //Load data Structure
            into :main1ds;                        

blah,blah,blah........

So I am on the hunt for why my XML is no longer being written. I must have cut something out by mistake while getting rid of the sub-procedures.

~Richard

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