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

EDI done wrong...

The last few months have been interesting to say the least. I have been in discovery mode for a medium size Jacksonville based retailer evaluating their EDI processes. The contract called for extracting and supplying EDI statistics to EDI providers and learning how the business does EDI.

This was my second contract in the retail industry and the experience is extremely beneficial. Most of my experience comes from manufacturing/distribution operations where I was either hired to implement, fix or re-implement ERP software solutions and provide IT management based on the IBM midrange systems.

What I discovered are EDI processes set up by RPG programmers who did not fully understand EDI. Instead of using Inovis TrustedLink translator on the iSeries to provide validation of the EDI documents, based on the published guidelines, RPG programming was created.

At very basic level this setup allows invalid data to populate into user files and then program failure farther down the line when code is not created for every variation of potential data error. I actually saw this happen during an invoicing run where missing data was allowed through the translator and validation programs. This required programming effort to back out data that had partially processed through the back end systems and delayed payments to suppliers.

I have seen this scenario several times before and successfully changed EDI operations to run with minimal intervention and staff. In my humble opinion if an EDI operation requires anything more than a EDI coordinator to maintain processing it is not implemented correctly.

Another interesting find is all element qualifiers are translated into the user files. Not that it’s wrong, just useless. Unless, the user file fields are so generic that the previous field denotes what the current field represents. Oh wait but how are you going to have to create programming to validate field contents? :) NOT!

If the wheel exists why reinvent, EDI software is a tool already written why not use of it?

I wish the company would have hired me as a permanent employee. I would have enjoyed re-implementing EDI and being more involved in the business side of IT.

I feel bad that I did not give any notice but felt that I had not integrated into the daily activities so I would not be missed. I needed health insurance and security of a full time position without the fear of working my way out of job.

~Richard

Obstacles are those frightful things you see when you take your eyes off your goal.  ~Henry Ford     

Next challenge, EDI integration....

My intended daily blog has turned into a when I can blog. This is a good thing since it means I am getting paid.

My current contract analyzing and documenting EDI processes for a major retailer in preparation for replacement of software or full outsourcing. The software currently being used is Trusted Link on an iSeries with a flat file IFS interface to Retail merchandising System based on Oracle.

If kept in house the software mandate is to run on Microsoft platform and all processes discontinued on the iSeries. Interface architecture is to be replaced with an Enterprise Bus Service package that will be used across the organization. I am also being asked to help with implementation new WMS from Highjump and TMS from Mercurygate software which requires interoperability with EDI processes.

Two potential software candidates that can fill the EDI and Enterprise Bus Service requirements are Extol EBI and Microsoft BizTalk.

This is exciting stuff and I am being given the opportunity to use my iSeries, EDI, integration, warehousing, distribution and management skills to help move a fast paced environment from a single ERP based system to a best of breed software using the cloud where possible environment. I will be getting exposure to Oracle, Cloud processing and Microsoft products such as SSIS, BizTalk and .Net.

I do still want to keep my iSeries programming skills up and am fortunate to have active clients requiring programming development and IT assistance. I am currently working on updating a System 2000 tax processes.

Have a great week,

~Richard

A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila.  ~Mitch Ratcliffe