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

I need more hours in a day.....

Decoupling RPG database IO using Rational Open Access: RPG Edition

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 

The flu season blah

Last week was not as productive as should have been. I starting running into some big walls, lack of test partition disk space and user out sick the week before and needs time to catch up her responsibilities. I did finish the inventory ETL and tested with small set of records.


I am currently working on the capture of product being received.

By the end of the day Friday I accomplished modifying Island Pacific program and  adding records to the file I will use to track and create XML receiving of product.


I caught the nasty bug that the user had a couple of weeks ago and been in bed all weekend. Taking today off and hopefully be able to go to work tomorrow.


~Richard

A positive attitude may not solve all your problems, but it will annoy enough people to make it worth the effort.  ~Herm Albright, quoted in Reader's Digest, June 1995

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