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

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

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 EXTFILE and EXTMBR keywords

Great day, hit it out of the park. I started the morning wondering how I was going to combine 12 members of a multi-member file into one XML document and ended the day with one RPGLE Free, one RPGSQLLE and one CL program. Processed 25 million records, creating a five gigabyte file in the IFS in 20 minutes.

The requirement is to extract last 12 months sales history from the iSeries DB2 database, convert to XML document per API guide, transfer to PC server and load to SQL database. This process is an initial load and only be used a few times.

I called my good friend and excellent RPG programmer Rick and asked how would he combine all the records in a multi-member file. He suggested OVRDBF in a CL DO LOOP or QCMDEXC and construct the OVRBDF in a RPG program. Sounds doable, I have seen working code through debugging, but never written my own.

I could smack myself for not remembering the member parameter of the OVRDBF.  I never liked working or found use with that type of file, just pain. I am sure there must be good use somewhere.

With Rick’s suggestion and few searches with Google I discovered you no longer need QCMDEXC in your RPGLE to over ride database file and member. Two additional file specifications keywords EXTFILE( ) and EXTMBR( ) have been added.

I quickly hammered out the RPGLE Free program to extract the sales records to a temporary file created in library QTEMP.

Cloned one of my RPGLE programs, that creates daily sales XML output. Deleted all but what is needed for the yearly and massaged as needed.

Created a CL with three lines of code, one to create the empty file in QTEMP and two to call the RPGLE programs.

First time through I hard coded only one member to be extracted. This way I can test with a smaller set of data. Went over the output, the took out the hard coding, recompiled and ran again. Done in 20 minutes with output that looks pretty good. I need a little fine tuning and will have it completed in the morning. I will post some code if I get a chance.

Sleepy-nite-nite...

~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