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

SQL Update a Multi-Table View using INSTEAD OF Triggers

Yesterday I added another SQL trick to my bag ‘o tricks. I am currently working on fixing EDI invoicing process and I needed a way to delete records from four files based on the same key. I could have wrote RPGLE Free Embedded SQL that would have been about 60 lines of code but wheres the fun in that.

I popped open the Google window and searched on “iseries sql delete from multiple tables”. the second hit directed me to IBM and the fourth to Preform the Impossible: Update a Multi-table View using INSTEAD OF Triggers. What did we did before Google, oh yeah, read a manual!

Impossible, that’s for me. It took me a few minutes to understand and a peek at IBM documentation I had to create a view and then the trigger. Once created any time I delete from the view all associated records in the four files are deleted. Very cool.

After view and trigger created I can run this SQL interactive or embedded.

delete from yourlib.ediinvw
where ihedoc = 0000046475

My new view is EDIINVW and I am deleting by EDI document control number.

To accomplish this I created the view with the below code using SQL Explorer.

create view yourlib.ediinvw as
select ih.ihedoc, id.idedoc, ss.iaedoc, si.isedoc
from yourlib.vedinih ih
join yourlib.vedinid id on id.idedoc = ih.ihedoc
join yourlib.vediniss ss on ss.iaedoc = ih.ihedoc
join yourlib.vedinis si on si.isedoc = ih.ihedoc

The four fields in the Select statement is the key field for each of the four files.

I then attempted to create the delete Trigger. SQL Explorer did not like the syntax so I copied and pasted into the green screen iSeries SQL interactive command line. My guess is that SQL Explorer has an issue with INSTEAD OF DELETE ON.

create trigger yourlib.ediinvdel
Instead of delete on yourlib.ediinvw
referencing old as EdiInvwOld
for each row mode DB2SQL
begin atomic
delete from yourlib.vedinih
where ihedoc = EdiInvwOld.ihedoc;
delete from yourlib.vedinid
where idedoc = EdiInvwOld.idedoc;
delete from yourlib.vedinid
where iaedoc = EdiInvwOld.iaedoc;
delete from yourlib.vedinid
where isedoc = EdiInvwOld.isedoc;
end;

I learned another very cool SQL trick, Thanks IT JUNGLE and Four Hundred Guru. This will come in handy over the next few weeks as I fix the EDI processes.

Have your best day!

~Richard

In programming, as in everything else, to be in error is to be reborn.  ~Alan J. Perlis

iSeries SQL Update multiple columns

SQL is cool s**t..I love this stuff.... Below SQL uses file created from spreadsheet and updates the min and max fields in the item balance file from one field from uploaded spreadsheet. 

Spreadsheet fields VENDOR, VMFG, CAPITEM, LOC, MINMAX.

update b50files.vinitmb 

set (ifsmin,ifsmax) = (select minmax, minmax from rbryant.upminmax  

where ifcomp = 1 and ifloc = loc and ifitem = capitem) 

where exists (select * from rbryant.upminmax where ifcomp = 1 and ifloc = loc 

                                 and ifitem = capitem)

Updated 04/29/15

I have a task to update several fields in file with calculated amount based on data from a different file. Since I do not write SQL everyday I do not try to remember and use Google. My google search took me to my blog from 2013. Just what I needed and now expanding complexity for future reference.

I need to take the location item price from item price file and reduce by 50% and update cost fields in item balance file. The real trick in my mind is the "exists". I fumbled around and could not get it work until I added the where select exists. 

vinitmb is item balance file and vinpmat is item price file.

ifavg, ifoth and iflst are cost fields being updated.

ifdiv and ifcls are division class fields.

iflcgd, iflcgt and ifuslc are last change fields in case I screw up and need to back out. Of couse all of this is tested with Select statements and then updated test files to make sure.

update r50files.vinitmb 
set iflst = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifavg = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifoth = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat 
where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
IFLCGD = 20150429,
IFLCGT = 080000,
IFUSLC = 'SQLRB'
where exists (select * from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item) 
and ifdiv = 9999 and ifcls = 2 

~Richard

One man's crappy software is another man's full time job.  ~Jessica Gaston

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

DB2 SQL Where Timestamp field = Date

My SQL challenge this morning required me to write a SQL select to select a date from a Timestamp field. I have started playing around with WDSC 7.0 Data perspective. I did not take to long to figure out, I am really digging SQL on the iSeries!

IVDTE is my Timestamp defined field in MIDINVPF.


SELECT *
  FROM rbryant.MIDINVPF
  where CHAR(DATE(ivdte), ISO) = '2012-04-12'

~Richard

People who look through keyholes are apt to get the idea that most things are keyhole shaped.  ~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