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

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 

DB2 to XML....

Today I finished polishing my item master daily update and gave a copy to the Director of Application Development for code review. He commented this is the Apollo 11 version compared to his old style RPGII coding and smiled. I will take that as a compliment. ;)

I continued and started the Store master file daily update program. I completed the coding and the program compiled but I am getting pretty tired and decided I could finish up in the morning. This program needed some date manipulation changing six digit numeric field, YYMMDD, to character representation, CCYY-MM-DD.  Things sure have gotten easier with RPGLE and with Google to the rescue I had no problem catching on.

The fields are not true dates so my friend Sam Lennon's article on RPG IV legacy dates cheat sheet was not where I landed. I did find another article that I have looked at from time to time. The RPG free-format date conversion cheat sheet by Mitchel Laman is just what I needed.

I added a couple of fields to my D spec’s -

D @numA       S      6  0  inz(041205)                      yymmdd
D @dateA       S        d    inz(D’2004-12-04’) date formatted field
D Strod           A     10 ccyy-mm-dd alpha

I added a subroutine to my code to deal with the conversion.

begsr datesr;
/free
@numA = sodte; // Store open date field from input file
@dateA = %date(@numA:*ymd);      // yymmdd to D’ccyy-mm-dd’
endsr;
/end-free

At the time I was not sure if the dashes would carry through. I then added the following to my routine that creates the XML for the IFS write. There is more to the XML but you get were I’m heading.

$xml = %char(@dateA)

I am really liking this RPGLE Free, much easier than when I worked with RPGII years ago.

~ Richard  

iSeries DB2 to XML

Arrived at the office before 7 am and spent an hour looking at third party xml solutions then decided to move forward with what I have already tested. During the meeting last Friday it was decided to spend a couple of weeks looking into rpgle oar. Since time is short and we don't want to miss our due date we decided to spend the next two weeks moving forward with what I have already tested know works. I believe that I can have the interfaces ready for stress testing in that time frame.

I put in the ear buds, turned up the music and starting coding. I only got up three times, one coffee refill and two bathroom. Took a 15min chat break along the way. By 2:30pm I had a commented and clean looking rpgle program that creates or replaces four xml documents containing data from four iSeries master files in an IFS folder. I pretty satisfied.

One down, six to go.

As soon as the network guys can get the test server up and running I will be able to set up daily full cycle loads. While I'm waiting I am going to start on the product sync programs.

I'll post the code and more details in the near future. Any critqueing will be greatly appreciated.

~Richard

XML to DB2 Exciting and Mind Numbing....

The past two weeks have been an exciting and mind numbing. My current task is to create interface programs that create and decompose pipe delimited text and XML documents between an iSeries @ V6R1 and a MS Windows 2008 application server application.  I will be using  the iSeries IFS /QNTC directory which is a share on the Microsoft 2008 application server to move documents between systems.

The iSeries data is created and used by an old unsupported version of Island Pacific ERP (IP). The MS application is Mid-Retail (MR). MR is providing the API’s to access their data.

After two weeks I was able to come up with an idea of how long it will take to get MR in the users hands. I have created some programs that can read and write XML based on the requirements. I spent the past couple of days putting my spreadsheets and documentation together to create a PowerPoint presentation. Friday the Project Lead (PL) and I presented our plan to the Director of Application Programming. After two hours we received the go ahead and approved my contract to the end of March. :)

The director did catch me a little off guard. In going over the possibilities for XML transformation I mentioned DB2 to XML Extender and RPGLE OAR. He showed great interest in using RPGLE OAR. After discussing the option further it was decided that was the direction we want to take.

I believe the right way to accomplish this goal is to use RPGLE OAR and write the Handler program. Unfortunately I have never created a handler and my programming skills may not be enough to create one in the time frame needed. I have already created programs that create and read XML using RPGLE XML-SAX, IFS API, External Data Structures, Procedures/Sub Procedures and Copy source files and I am comfortable with the current time frame for the project.

I explained my concerns about my current skills and the deadline for the project. They had no problem and are willing to let me spend some time getting up to speed. I am impressed that they are willing to help me increase my skill set and are comfortable with me proceeding with the project.

After my last employer spent a year destroying my self confidence I am finely returning to normal. I have increased my skills with every step in my career and turned negative into positive. What has made me successful is, while I may not know the details of everything I know how to find and learn what I need to accomplish any task at hand.

I have never really wanted to be a full time programmer and I get a bigger sense of accomplishment being in operations helping users, managers, customers with day to day technology and data issues. But I may be changing my mind, figuring out the XML to DB2 is a big challenge and so far is fulfilling my appetite for accomplishment.

All that I needed is available on the Internet. Below are a few of the sites I gleaned information from:

The Ins and outs of XML and DB2 for i5/OS
RGPLE XML Parser example code
Working with the IFS RPG IV
IBM Rational Open Access: RPG Edtion V1.1
IBM Systems Magazine - Getting a Handle on RPG Open Access
iSeries read IFS file
DB2 XML Extender Hints and Tips

A special thanks to the following people, without them there would be no Milk Bones for the dogs.

Scott Klement

Jon Paris

Susan Ganter

~Richard