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)

~Richard

One man's crappy software is another man's full time job.  ~Jessica Gaston

Clean iSeries output queue by days to keep

It’s has been a fast six months, new job has kept me hopping. One day I’m redesigning the EDI guidelines and RPGLE programming interfaces to the next day chasing Purchase order suggestions. Tape backup failing without notification to GL year end posting to rewriting code replacing OPNQRYF with embedded SQL. Point of Sale hiccups to AP check failure to working with eCommerce. Every single modification that the previous software vendor implemented has a problem one way or the other.

Basically I have not been happier than any other point in my career!  

I ran across the need to automate purging old spool file records and could not find any code close enough to cut, paste, compile, run and free. In the past have used TAATOOL but is not currently an option.

My good buddy Rick Santiago has a purge out queue by date program that he wrote a while back. Short and sweet I quickly envision modifying for my needs.

Through iSeries navigator I created a table with four fields, out queue name, out queue library, number of days to keep spool file entries and user data. 



I also added the following key constraints, C_OUTQUE, C_OUTLIB and C_USRDTA as unique.

Follow your nose and instructions in the comments of the CL and you’ll have a quick little program you can add to your nightly process.

Hopefully this help some other poor one man band quickly solve a minor annoyance programmatically.

CLNOUTQ is the driver and CLNOUTQ2 exists due to only being able to use one RCVF in a CL in a read loop and detect end of file.

CLNOUTQ

CLNOUTQ2

~Richard

Try to discover

The road to success
And you'll seek but never find,
But blaze your own path
And the road to success
Will trail right behind.
~Robert Brault, www.robertbrault.com

                                                     

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

What a day - part one

What a day!

I started out attempting to focus on a nasty intermittent program bug relating to retail point of sale automation of inter-store transfer and allocation and finished with 3 hours of head banging with a level two AT&T uVerse tech. In between found 500 GB of iSeries journal receivers with no upkeep, compressed, saved and preparation of  unneeded data file libraries for transfer to the cloud and call from user who deleted Accounts Payable checks from printing by mistake. I really do love my job!

It’s late and I wanted to share my AT&T uVerse escapades because it seems there are no ready answers for the poor guy who tries to accomplish what I accomplished.

The initial problem is dropped calls with a Cisco IP SPA504G phone and service with Vocalocity service. My current setup is AT&T uVerse with 2Wire 3801 HGV modem/router with Static IP address, WiFi and WiFi set top device feeding wireless TV to the family room.

Ever since I installed uVerse about 18 months ago I have not been happy. I was reluctant to call AT&T since everything worked ok, but when I took a job working full time form home my issues became apparent and had to resolve them.

I am having intermittent dropped IP calls, mostly with the boss. I started working for the company a couple of weeks earlier and had not really been receiving many help desk calls so I did not put a lot off effort into the issue. The issue became unbearable when users started calling for help and I would drop their calls. It is bad enough that the user is frustrated with a VAI software issue, but dropping the calls while they are trying to explain the issue was just too much.   I called Vocalocity and they readily accessed my PC and made one adjustment to the 2Wire. Basically the tech turned off the Packet Flood setting the advanced Firewall settings and checking time of ping to Google. The tech stated that there is a swing in the ping testing that is pointing to an AT&T issue.

Two weeks ago -

 I finally broke down and called AT&T and to my surprise had a tech on the phone within 3 minutes. He agreed that there is a problem and would dispatch a technician to come to the house. Again surprised, I am told and technician is in the area and could be here between 12 and 4pm, this is at 11:30am. Wow, sure send him on.

The technician arrives a little after 1pm, now I am just flabbergasted. After a few minutes of checking out my wiring he informs me that he should rewire and run Cat 5 and Coax replacing the Coax currently in place. Cool and can you run the new wiring trough the attic and into my office, sure no problem. Wow!

Takes about an hour to get everything setup and then on to reconfiguring. That’s where the ride starts to get bumpy. I informed him that I have a static IP and get the deer in the headlights look.  After he hooks everything up we find that nothing wants to connect. He spends the next few hours trying to get the connection working. Of course I had to make things more difficult by having him install the wireless set top device to feed the TV in the family room. He finally gives in and goes out the truck and gets a new 3801HGV 2wire device, configures and calls me to check it out. Now my new recently assemble Extreme PC desktop will not connect. It was working fine for the last three days with no connectivity issues and then all of a sudden no connection. We mess around for an hour. The tech then has me plug my Netbook directly into the 2wire device, it works. I plug in my Laptop and it works.  It’s now 6:30pm and the Tech wants to get the hell out of dodge and quickly. He states he can only be responsible to the 2wire device anything else us on me. Frustrated that my new PC and main device to work from home does not connect, I had to agree and let him out the door.

I take a deep breath and set about diagnosing my new issue. Takes me about 30 minutes to realize that I needed a driver update for my new Intel mother board and bam I now connect to the Internet

The next day I notice my connection speed to the net is much better than before, instead of surges in speed during speed testing I now have a solid 8bm connection. Great!

Present day –

Still having dropped calls and need to get this resolved, I place another call to Vocalocity and the tech states that it is a known issue with dropped call and AT&T 2wire device. He recommends purchasing a WNR3500L wireless router and put it in the DMZ. Provides me a PDF document on how to set up the Netgear router with an AT&T 3801 and seems simple enough. I run out to Staples and pick one up.

I setup the WNR3500L and get to step number three and receive error other device has same IP address. I am now beyond frustrated, I have big programming issue I must debug and don’t have time for this.

I call Vocalocity and luck of the draw I get a read from the script tech who states that he cannot make changes to my 2wire device and there is not much he can do. I try to get the tech form earlier but he is on a call and cannot take my call. I worked with the current tech and realize he is basically clueless and kindly thank him and hang up the phone. I then scream out a few expletive words and take another deep breath. I have to call AT&T tech support. At this point I think it would be easier to just slit my wrists.

I call AT&T and my luck improves. I sing my story to the Level one tech and he quickly realizes that I know what I am talking about and he was not going to be able to help me. Seems that static IP addresses a foreign to most of AT&T tech’s.  Within 3 minutes I’m on with a level 2 tech. I sing my song again and he states I have a real head scratcher.

He can give me no answer to why the IP address that the world sees me as is not my static IP address. I know my Static IP works since it is configured into a couple of other client’s firewalls to give me direct access. During our conversation filled with lots of huh and sighs. The tech states that is not really suppose to help me but since I seem to know what I am talking about he agrees to work with me to resolve the problem of installing the Netgear router.

During conversation I realize the duplicate IP is the AT&T public IP but the conflict is with the set top Wi-Fi device.  In the LAN->IP Allocation->settings I change the WAN IP Mapping to Address assignment to Public ( Select WAN IP Mapping). I save the configuration and the Current Address changes to 0.0.0.0. I then quickly change  to match the Vocalocity documentation.

Success! The config accepted.

End of part one, I have to get some sleep….To be continued….

~Richard

The difference between perseverance and obstinacy is that one comes from a strong will, and the other from a strong won't.  ~Henry Ward Beecher

Out goes Google docs and in comes Microsoft...

I had converted myself from a M$ proponent to a droid foot soldier in hopes that Google would get a toe hold in business but with my current employment and now find myself pulled back into MS.

As a recent co-worker mentioned many times to me, (reminds me of the insurance commercial with the guy on motorcycle with dollar bills flying off except he has MS certifications flying off, not mentioning any names Ned), MS is deep in business and will be for many years to come. There is no hiding and I really cannot fight it.

I have been provided Office 2010 Pro and the company foundation is based on MS. I must stand behind MS and provide support and future integration to back end systems. Not really a bad thing but a 180^ turn from my focus over the last 14 months. I have spent a lot of effort over the years integrating MS with iSeries and it will now come in handy.

So out the window (no pun intended) goes Google Docs, Drive and Evernote. While I believe they are great products I must stand behind my current employers choice to be based on MS.

If this were any other job I would stick with Google but I realy like this company and people and plan to be here for many years to come.

In is MS Office, Skydrive and MS Onenote.

My Office download is finishing and I'm off to configure connectivity and switch from Google. Good thing I grabbed richard.bryant@outlook.com when it became available months ago.  

~Richard

Multi-tasking challenges....

The days are moving way too fast and it’s a huge challenge just to keep up.

All the parts for my new PC arrived Monday afternoon. While applying the finishing touches my old PC hard drive crashed and I cannot repair with Windows tools. After several hours attempting to resolve the matter I decided to put the repair on the back burner and focus on bringing the new PC online.

While I am working on the PC I am supporting and following up on my day job task of supporting VAI software questions, IBM hardware repair and finding the correct VPN solution to connect to all the stores I am trying to support.

I managed to get the new PC online and all programs needed to work installed which included IBM iSeries Access, WDSC 7.0, VAI GUI, SonicWall NetExtender and Quicken 2012. I was extremely fortunate that I had WDSC, Iseries Access and Quicken backed up on a USB key.

I cannot say enough about Google Mail and Google Drive. I had Email and all my documents available within the first hour of working on the new PC. The last time I lost a drive it took me a day to get Microsoft Office back to where I needed it to be.

Just as I am catching up to my help desk calls and deciding my direction I get a call about EDI 850 PO failing at the vendor. The vendor item number is missing from the EDI document. Without getting into the detail of the issue I had to identify why, which was the easy part, provide a quick data fix so PO could be sent to the vendor, not so easy.

Once order processed and vendor can accept the PO to ship product to a waiting customer I had to find modified program for a custom process not familiar with, find where in the code a possible fix could be made and write up the issue and proposed fix. Finished and off to the boss by 6pm.

Whew! Thank goodness today is Thursday, and tomorrow is Tequila Friday!

I have never been happier. I am back in an environment I thrive in. It’s just like my first two years at Pilot Pen without the fear that everyday is my last. The people I work for now are extremely business orientated and ego is not the focus.

Have your best day!

~Richard

Try to discover
The road to success
And you'll seek but never find,
But blaze your own path
And the road to success
Will trail right behind.
~Robert Brault, www.robertbrault.com