We're not in Kansas anymore....

Web Services, SOA, SOAP, HTTP, C++, WSDL, DISCO, Lions, Tigers and Bears. I’m used to diving into the deep end of the pool but after two days I think I have fallen into the Marianas Trench still waiting to hit bottom.

My current challenge is to send Name, Order and Email for new orders to LISTRAK Email Marketing Services utilizing API and SOAP formatted XML from our IBMi.

A little lite reading.....

developerWorks SOA and web services

Listrak integration services

Consuming Web services from RPG or COBOL programs on System i

~Richard


If we are facing in the right direction, all we have to do is keep on walking.  ~Buddhist Saying

Whooohooo! IBMi V7R1 installed and working with no problems.

Good to be back and up to date, was at V5R4 and I was missing some of the enhancements that were in V6R1. I upgraded several machines three years ago to V6R1 and did not like going back and working with V5R4.

Since I am now essentially a one man IT shop (software/IBMi) and buried deep in fixing and supporting a failed ERP implementation I outsourced the upgrade to Source Data Products. Over the years I have done about 15 installs/upgrades but has never been my focus and it takes planning to do it right. I did not have the time to plan and do the upgrade myself.

Bob and Mark did a fantastic job. Bob from the standpoint of providing a price that could not be beat and Mark for performing a flawless upgrade. As a bonus he configured ASMI port. Now I have full remote control access to the IBMi. From a powered off status I can start machine without manual intervention at the CO-LO and see status of machine before IPL.

Total time for upgrade excluding software downloads and third party software upgrades was around 7 hours. One small bump with credit card processing software, they missed sending be the bulletin to change JAVA path. IBM changed the JAVA path to QOPNSYS which is where it should have been in the first place.

For under $3k Source Data Products cannot be beat. Now I have to quickly get up to speed with RDi Development Tools and V7R1 enhancements. Can't wait to utilize RUNSQL in CLLE, SDA and RLU in RDi.

EDI done wrong...

The last few months have been interesting to say the least. I have been in discovery mode for a medium size Jacksonville based retailer evaluating their EDI processes. The contract called for extracting and supplying EDI statistics to EDI providers and learning how the business does EDI.

This was my second contract in the retail industry and the experience is extremely beneficial. Most of my experience comes from manufacturing/distribution operations where I was either hired to implement, fix or re-implement ERP software solutions and provide IT management based on the IBM midrange systems.

What I discovered are EDI processes set up by RPG programmers who did not fully understand EDI. Instead of using Inovis TrustedLink translator on the iSeries to provide validation of the EDI documents, based on the published guidelines, RPG programming was created.

At very basic level this setup allows invalid data to populate into user files and then program failure farther down the line when code is not created for every variation of potential data error. I actually saw this happen during an invoicing run where missing data was allowed through the translator and validation programs. This required programming effort to back out data that had partially processed through the back end systems and delayed payments to suppliers.

I have seen this scenario several times before and successfully changed EDI operations to run with minimal intervention and staff. In my humble opinion if an EDI operation requires anything more than a EDI coordinator to maintain processing it is not implemented correctly.

Another interesting find is all element qualifiers are translated into the user files. Not that it’s wrong, just useless. Unless, the user file fields are so generic that the previous field denotes what the current field represents. Oh wait but how are you going to have to create programming to validate field contents? :) NOT!

If the wheel exists why reinvent, EDI software is a tool already written why not use of it?

I wish the company would have hired me as a permanent employee. I would have enjoyed re-implementing EDI and being more involved in the business side of IT.

I feel bad that I did not give any notice but felt that I had not integrated into the daily activities so I would not be missed. I needed health insurance and security of a full time position without the fear of working my way out of job.

~Richard

Obstacles are those frightful things you see when you take your eyes off your goal.  ~Henry Ford     

Next challenge, EDI integration....

My intended daily blog has turned into a when I can blog. This is a good thing since it means I am getting paid.

My current contract analyzing and documenting EDI processes for a major retailer in preparation for replacement of software or full outsourcing. The software currently being used is Trusted Link on an iSeries with a flat file IFS interface to Retail merchandising System based on Oracle.

If kept in house the software mandate is to run on Microsoft platform and all processes discontinued on the iSeries. Interface architecture is to be replaced with an Enterprise Bus Service package that will be used across the organization. I am also being asked to help with implementation new WMS from Highjump and TMS from Mercurygate software which requires interoperability with EDI processes.

Two potential software candidates that can fill the EDI and Enterprise Bus Service requirements are Extol EBI and Microsoft BizTalk.

This is exciting stuff and I am being given the opportunity to use my iSeries, EDI, integration, warehousing, distribution and management skills to help move a fast paced environment from a single ERP based system to a best of breed software using the cloud where possible environment. I will be getting exposure to Oracle, Cloud processing and Microsoft products such as SSIS, BizTalk and .Net.

I do still want to keep my iSeries programming skills up and am fortunate to have active clients requiring programming development and IT assistance. I am currently working on updating a System 2000 tax processes.

Have a great week,

~Richard

A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila.  ~Mitch Ratcliffe
 

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

Extended contract and new task coming

Great day, contract extended for another 12 weeks. I have been really busy finishing up my current project which I am a week behind schedule. I hope to make up some time this weekend. The heavy coding is completed just need to tie up the loose ends and automate.

I am using Winautomation on the Windows server to watch for data in folders and initiate jobs on the iSeries based on responses from the jobs executed on the Windows server.

The next project sounds exciting, creating a data warehouse on the iSeries. I have never had the chance to work on putting together a data warehouse but understand the concepts. I may take a closer look at what Rodin has to offer.

Any other suggestions will be greatly appreciated.

Have your best day,

Richard

Try not to become a man of success, but rather try to become a man of value.  ~Albert Einstein

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 

RPGLE Free FOR op code replaced DO....

I discovered the DO operation code does not exist in RPGLE Free, it has been replaced with the FOR operation code. I have not had to code a DO loop in some time so this is a pleasant surprise.

Old way -

C         2             Do      20                  Index
C                       Eval    Array(Index) = Index;
C         Index         Chain   SubfileRec
C                       If      %found
C                       Eval    SF_Field_1 = 44
C                       Endif
C                       Enddo   2

RPGLE Free -

/free
For Index = 2 to 20 by 2;      // Set up a controlled loop
Array(Index) = Index;            // Set Array element
Chain Index SubfileRec;        // Get subfile record
  If %found;                           // If found
  SF_Field_1 = 44;                //   Set subfile field
  Endif;
Endfor;
/End-free

For my purpose it is real easy to change the starting point for my sales history ETL process. This process is a onetime load so when we are ready to go live I can easily set the starting member name. Currently I am testing with sales from 04/2011 through today. Each member of the multi-member file represents a month of sales.

     ** MID112R: Extract sales from history and identify type of sales and    **
     **          identify week ending date. Update VWEK and VSTP accordingly. **
     **          Output file midslyrspf is used in MID0113R to create XML     **
     **          Document for MID plan/history load.                           **
     **          Intended to only run at initial load of MID.                 **
     **                                                                       **
     ** Richard Bryant - Tek Systems   Mar. 15, 2012                          **
     ********************** M O D I F I C A T I O N S *************************
     ** Date       Programmer   Description                                   **
     ** ---------- ------------ -------------------------------------------- **
     **                                                                       **
     **************************************************************************
    fvog012d3  if   e             disk    usropn extmbr(@mbr)
    fmidslyrspfo  a e             disk

    D @mbr            s              5a                                        Member name
    D mbrCnt          s              2s 0                                      Member number
    D D_Date          s               d   DATFMT(*ISO)
    D DayofWeek       s              1s 0
    D WK_date         s              8p 0                                      Week Ending date
    D Cents           S              2S 2                                      cents
    D Centsa          S              2S 2                                      cents absolute
     ************************************************************************
     * Main Routine
     ************************************************************************
     /free
      for mbrCnt = 4 to 12 by 1;      // Start at member 4 = sales month april 2011

      @mbr = 'R0M' + %editc(mbrCnt:'X');  // 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
       exsr SetDate;
       exsr SalesSR;

        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
        vwek = %int(WK_date);
        write mdslsr;
       endif;
      read vog012d3;
      enddo;

      close vog012d3;                    // Close file

      endfor;

      *inlr = *on;                      // See ya!

       //************************************************************************
       // * Sub Routines
       //************************************************************************
       //************************************************************************
       // SetDate - update date field VWEK with week end date. Saturday is the
       //           current sales cut-off.
       //************************************************************************
      begsr setDate;

       D_Date = %date(%int(date):*YMD);        // Convert sales date to real date
       DayofWeek = %Rem(%Diff(d_date:d'2001-12-16':*days):7); // Get day of week
                                                              // 2001-12-16 = Sun
       select;
         when DayofWeek = 0;                  // Sunday
           D_date = D_date + %days(6);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 1;                  // Monday
           D_date = D_date + %days(5);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 2;                  // Tuesday
           D_date = D_date + %days(4);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 3;                  // Wednesday
           D_date = D_date + %days(3);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 4;                  // Thursday
           D_date = D_date + %days(2);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 5;                  // Friday
           D_date = D_date + %days(1);
           WK_date = %dec(d_date: *iso);
         when DayofWeek = 6;                  // Saturday
           D_date = D_date + %days(0);
           WK_date = %dec(d_date: *iso);

        endsl;

       endsr;

       //************************************************************************
       // SalesSR - Identify type of sales to update field VSTP
       //************************************************************************

       begsr SalesSR;

        centsa = %int(price) - price;          // Strip out cents
        centsa = %abs(cents);                  // Remove negitive convert to absolute
         select;
           when centsa <> .99 and dscnt = 0;   // Sales Regular
             vstp = 'Sales Reg';
           when centsa = .99 and dscnt = 0;    // Sales Mkdn
             vstp = 'Sales Mkdn';
           when dscnt <> 0;                    // Sales Promo
             vstp = 'Sales Promo';
         endsl;

       endsr;

     /end-free                                          

Have a great day!

~Richard

Common sense is instinct.  Enough of it is genius.  ~George Bernard Shaw