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

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 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

Fast and successful week...

It was a really good week, it went by very fast and I have managed to get ahead of schedule. I hoped to finish off the week with a home run but best I could do was lay the foundation for a good start next week. ;)

I am unable to see the recently setup MidRetail test server from the test LPAR. So I spent the morning verifying the iSeries TCP and NetServer configuration and testing the QNTC file system. I took my findings to the IT Manager and he reached the same conclusion, there is a DNS problem. Come to find out, the MS DNS server for the test network recently died and new server had to be reconfigured manually. Unfortunately the iSeries entries were missed. I do not have access to the server so have to wait until Monday to have the network guys figure it out.

The iSeries QNTC file system is part of the iSeries Netserver and allows the iSeries programs output stream files, STMF, directly to a Microsoft Windows share. Very cool stuff, easy to setup and really handy when you need to feed your back end data to a PC application.

I have been using MidRetail on the the production server since the iSeries test LPAR is on a different Domain and unable to directly communicate. While waiting on the Microsoft Application test server to be setup I have been moving documents between systems manually to test my XML output. Once we get the connection issues resolved I will be able to install the MidRetail application on the test server and be able to start daily full cycle testing of the completed interface programs.

I finished polishing the Store interface program and moved on to the Sales History interface program. Each program grows in complexity as I work through the project. This interface requires that store sales be summed to Date, Store, Style, Color and Date, Store, Style, Color, Size daily and  Date, Store, Style, Color weekly. The input data is from a file that is populated nightly from sales of over 300 stores. This is looking like a good fit for my first embedded SQL, whoopee!

I may break the task into two programs, daily and weekly, that creates three XML documents. Some consideration will be needed on handling missed Stores and how to process them once data is retrieved.

I have been writing and learning RPGLE Free and now ready for trying my hand at some embedded SQL. I have seen working examples but have never actually coded myself. I do use SQL Explorer on a regular basis to write Select queries to verify data and find it extremely useful. I have a day or two to work out the techniques but if it looks like it will take me too long to figure out I will just fall back to RPGLE.

After a couple of hours with the IT Manager I have enough information to start analyzing data and putting together a plan. I ran a few SQL queries over the file and have a good feeling for what needs to be done.  

~Richard


Three things are certain:
Death, taxes, and lost data.
Guess which has occurred.
~David Dixon, 1998, winning entry of the Haiku Error Messages 21st Challenge by Charlie Varon and Jim Rosenau, sponsored by Salon.com

Its a small world...

Had a great interview yesterday at an Apparel importer / distributor and I think I got the gig. Should only take a few weeks but hopefully there will be other projects. This will be my first Jacksonville based client and just 20 minutes from home.

Its a small world, Elwin Kunzler is working there as a programmer. I passed up Elwin for another candidate when I hired the team over at Pilot Pen. No hard feelings, the choice was between him and another and I chose the other.

The job seems simple enough, basically setting up file transfer between iSeries application and Windows based  planning application. One requirement is to use QNTC file system. The QNTC is what I tried to show Pilot Pen but the VP did not understand so he continued with copy file process and batch programs, ugh.

This will be a great opportunity to use QNTC in a production environment.