Wednesday, October 10, 2012

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

Tuesday, September 25, 2012

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

Thursday, September 20, 2012

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

Saturday, September 15, 2012

I feel the need for speed...

http://www.bit-tech.net/hardware/2012/04/23/intel-core-i7-3770k-review/1

~Richard

Excess on occasion is exhilirating.  It prevents moderation from acquiring the deadening effect of a habit.  ~W. Somerset Maugham, The Summing Up, 1938

Sunday, September 9, 2012

Building my new PC....

It’s been 13 years since I bought a new PC. That PC was a Micron and lasted many years past it’s expected lifespan. Upgraded memory, video card, hard drive and CD burner at one point or another. I believe I got about five or six years out of it.

Around 2005 I acquired a HP AMD machine from a company where I was working as IT manager. I was never really happy with it, equipped with a lower end AMD chip it could barely keep up with Windows XP and all the software I threw at it. It was free, so beggars can’t be choosy. Basically I will never buy an AMD machine.

My current PC I acquired from a good friend who acquired it new from one of the branch offices. He would not put it on his network since it is a clone and does not meet his network topology standards. The clone is a Intel Core Duo E6550 2.33GHz with a 500GB hard drive, 1GB of ram and DVD-R RW drive. For $200.00 bucks it is an ok deal.

I had to replace the power supply after two weeks since the PC would reboot periodically on it’s own. I added an additional GB of RAM, max is 2GB, and a ATI Radeon™ HD 5450 graphics card which gives me the capability of having three displays, two Samsung SyncMaster 940n I acquired from my time at Pilot Pen and a Dell 15” LCD that I traded a mini monitor for.

The machine came preloaded with Windows XP, I purchased and installed Windows 7 Home Upgrade. Overall the PC has served me well over the last year.

When land a new job I always treat myself to as new electronic gadget of one sort or another. I have finally landed another good full time job after a year of searching and I have decided to upgrade my System.

The new job is full time work from home providing programming and IT support to a lighting retailer. I don’t want to be waiting on my PC. I need to be able to run a lot of different software at the same time allowing me to multitask and efficiently provide services as needed. I hate waiting!

I spent the last few days researching components and systems and decided that if I want my dream machine I would have to build it myself. Below is what I finally ordered today.

I started with the CPU - Intel® Core™ i7-3770K Processor

I balanced need, want, speed and price. 3rd Gen I7 processor with some oomph!

Next the Mother Board - Intel® Desktop Board DZ77GA-70K
Mother board made with the K series chips in mind. SATA ports at 6Gb/s will come in handy as you will soon see. Wi-Fi / Bluetooth and support for 32Gb  DDR3 1600+ RAM.

Now for a little RAM -  Crucial Ballistix Sport 16GB (2 x 8GB) 240-Pin DDR3 1600 SDRAM      Desktop Memory
This I purchased from J&R for $99.88. Maybe later I’ll add another 16gb. ;)
The hardest part was picking the video card. My future expansion includes 2 additional 23” Samsung HD monitors, this will give me a total of four monitors.

Video Graphics card - EVGA - GeForce GTX 670 FTW 2GB GDDR5 PCI Express 3.0 Graphics Card
The next card up is over $500.00, I had to be real. I am already overkill and just could not get the want to override the price. Bottom line I don’t have much time for gaming, but if I ever do I can with the best of them. :P
Anyway you look at, it’s a badass card!
 
Let’s see what should I acquire next. How about a some Hard Drive? I want to be able to store pictures and video safely and programs to run at light speed.

SSD drive for programs -  Intel 330 Series Solid-State Drive 180 GB SATA 6 Gb/s 2.5-Inch
This is what I had in mind looking at the 6Gb/s on the motherboard. Not the best but damn close. My programs will launch at light speed just thinking of clicking the mouse!

HDD for storage -  Seagate - Barracuda 2TB Internal Serial ATA Hard Drive for Desktops
7200 rpm, 64Mb Cache and 6Gb/s SATA what's not to like?

If you're following this you have to be asking yourself what's it going to take to power all these beastly components? Well I’ll tell ya!

Power Supply - Thermaltake - SMART Series 850-Watt ATX Power Supply - Black  
NEED MORE POWER! Don’t think so. Don’t panic I did not pay $999.00, Best Buy $119.99.

Now all I need is a mid-tower case. After much research I descided on a Cooler Master.

Case -  Cooler Master - Elite 430 Mid-Tower Chassis
Dust filtered, support for five fans, water cooler connection if overclocking silliness overcomes me and support for beastly graphics cards like the GTX 670. I think my wife would divorce me if I went with the GTX 690!

Additional fan -  Corsair - Air Series AF120 Performance Edition 120mm Airflow Fan
I wanted at least one additional fan to start with and had a hard time deciding on one so I threw a dart at the board and this what I came up with.

I ordered all the parts today and hopefully have everything by next weekend. most of the items I purchased from Best Buy, 18 month 0%, the SSD from Amazon and Ram from J&R. Total cost $1692.57.

My current side work open receivables will cover most of the cost but liked the idea of 18 month 0% and most of the items are within $20.00 +/- when sourced outside of Best Buy. Either way it’s a business expense.

I am planning on adding a CPU heatsink but want to get everything put together first.

I hope to take the time to provide Youtube unboxing and installation, so stay tuned!

~Richard

Computers are like air conditioners.  They work fine until you start opening windows.  ~Author Unknown

Friday, September 7, 2012

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     

Saturday, August 11, 2012

Galaxy Tab 10.1 ICS update...


It's been a long time coming and due to a rotten Apple in the barrel who knows when the US OEM version of Ice Cream Sandwich for the Galaxy Tab 10.1 will be available.

The guys over at XDA-Developers are gods! They released a rooted version back in January which I installed a month ago and loved it. Right away I noticed a performance boost, look and feel was like it was made for a tablet and overall, a very cool look. The only annoyance is the camera does not work. At first I thought it would not be a big deal but after a week I start missing the capability to snap shot of the dogs during funny moments. So I decided to patiently wait for the OEM version.

Last week Samsung released ICS to the UK market and I knew someone would hack it and post. On day two I found a post and prepared for the update but decided to wait a few days to see what else popped up on the forum. I don't know if Scaryshark's instructions and rom will work or not but his user name gave me second thoughts. I hate sharks, remind me of salesman! ;)

Thursday the post I was waiting for appeared. Simple process that follows exactly what I have been doing to flash the ROM using ClockworkMod Recovery . Thirty minutes later I have the OEM version of ICS with working camera, WhoooHooo!

I manually reloaded my apps. I think there is a way to restore them but I like the idea of starting out fresh. I always seem to have apps that I tried and forgot laying around.

Now to wait for JellyBean!!!! The non-OEM version is available but no working camera, I think ICS will keep me happy for a while.

If you have Galaxy Tab 10.1 Wi-Fi I highly recommend updating to ICS.

~Richard


 
  

Sunday, July 29, 2012

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

Sunday, July 22, 2012

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
 

Tuesday, June 26, 2012

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

Thursday, June 7, 2012

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



Thursday, April 26, 2012

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

Tuesday, April 24, 2012

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