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