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

Top 10 Reasons to Switch from IBM iSeries to Oracle and .Net

10. Your IT budget is small and you want to triple it.


9. You have large empty room and can not justify hiring more RPGLE programmers to fill it.


8. Your CEO likes pretty GUI interface. (Who cares if its productive? So what if it takes 20 pretty screens to fill one ugly green screen.)


7. You have lots of empty rack space for MS servers or want to build out additional rack space.  (One Power 7 rack is not impressive enough to show visitors how important you are.)


6. Your part time iSeries operator is bored and needs company. (He'll have at least three new friends that will spend days preparing Oracle fixes and nights applying them.)


5. You need to be seen as an humanitarian and hire a dozens under paid Indian H1B visa holders to fill your empty cubes.


4. You have the need to field a Cricket team. (You'll have one with all the Indian .net programmers you hired.)


3. Your bored with RPGLE because it works so well and feel the need to chase .NET bugs for the next 10 years. RPGLE Development is to fast.


2. You need to feel wanted by causing as much production downtime as possible. (You can always blame the third party software vendor.)


And the number one reason to switch from iSeries to Oracle……..


You need 50 programmers to worship you and IT budget bigger than some countries GDP. You are a God!





 ~Richard

iSeries SQL Function Proper Case.....

One of my clients asked me to update the tax processing currently working in VAI System 2000 on the iSeries. The requirement is set up the county tax file and update the customer master with the proper county code based on the city field in the customer master.

Challenges abound, the city field in the customer file is mixed with all capitals, all lower case, proper case and misspellings. And yes, I have to learn how to fill out Florida sales tax form, which is where we find a $5000.00 limit on county tax. Always a twist somewhere.

First I looked for availability of current taxes for the State of Florida with city, county and current tax. I found Tax-Rates.org and for 39.00 bucks with the information needed, I downloaded CSV, changed to Excel and uploaded to the iSeries using Data Transfer To IBM i. This enables me to create the file on the IBM i automatically during the upload.

Once I have the file on the IBM i I have to overcome the case problem with the RMCITY field. There should be a way to do this without any code. I can do it in RPGLE using embedded SQL no problem but know there must be a way with just an SQL UPDATE statement.

Well, sort of correct, SQL does not have a PROPER function but I found this code about Functions with downloadable code, cool. First brush up on Control Statements and Create Function article by Scott Klement. It did not take me to long to figure out. I was tripped up on the definition of the inbound field but soon realized @TCSTRING VARCHAR(100) is the data field being returned.

I opened System i Navigator and expanded Databases so I can see Functions.




Right click and select New - SQL and the following is displayed -

 

Change the following -

Function = PROPER,
Description = Change Case To Proper
Type = VARCHAR,
Length = 100
Check = Same result returned from …....(Deteministic)
Data access = Modifies SQL data
Specific name = PROPER



Select the Parameters tab and then Insert, the following is displayed -




Change the Parameter Name to @TCSTRING, change Type to VARCHAR, Length to 100  and check of Return Null on Null input.




I downloaded the code and opened in notepad. Copied from BEGIN to END. I left out the SQL parameters since we are selecting them in the wizard. There are easier ways to just create the Function from the copied code, but the wizard breaks it out and explains it a little better.





Click on OK and the Wizard closes and the following should be displayed.



Now we can generate the SQL, right click on PROPER and select Generate.

If I need to change a field to be consistent like the city field in customer master I just run the following SQL statement.

update transfer.VARCUST
set rmcity = qgpl.proper(rmcity)

Several things to figure out, why library where the function is located in has to be specified, I thought it would come from the library list. And I did not take to the time to understand all the checkboxes in the wizard. The function performed as I needed and enabled me to continue on with my project.

I finished identifying the missing city county combinations and update the list.

The following SQL updated the county code field in the customer master based on the cross reference FL_CTYTXa file. There is a little problem with the SQL and I am looking for how it should be done correctly but what I created worked and updated the field as desired.

update transfer.varcust set rmcnty =
(select rkcnty
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)
where exists
(select *
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)

Previously I update the VARCNTY county tax file with the Florida taxes.

Now on to identifying where to put the hook in tax processing to only charge county tax on the first $5000.00 and testing.   

Any suggestions or questions, please send them on.

~Richard

Talk doesn't cook rice.  ~Chinese Proverb

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

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

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 

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