PDA

View Full Version : AS400 Flatfile Updating



John_Wagner
10-01-2007, 04:25 PM
I have been utilizing Kettle to convert our legacy AS400 flatfile data into DB2 very successfully. A new requirement came out last week where I need to build a routine that will take certain columns from DB2 and update AS400 flatfiles with the column data. I have tried Table Output, Insert/Update, Update, all to no avail - "Entry to update with following key could not be found: [POLICY_NO=BP00562]" or "SQL7008 - SCMSTR in JWAGNER not valid for operation..... Could not get row from result set"

The AS400 flatfiles have been 'externalized' and understand SQL statements issued against them and Table Input works with the "Select" statement against the flatfiles.

One other dilemna in this 'new' routine is that some of the columns being brought from DB2 are to be packed decimals on the AS400 update...... I haven't gotten this under control either.....

Any ideas would be greatly appreciated.

Yours truly,

John

jternent
10-05-2007, 11:10 PM
John,

Does "externalizing the files" imply creating a journal for them? This SQL7008 error seems to be related to not having journaling turned on for files that were not created using the SQL tools. This article refers to the tools necessary for creating a journal physical file: http://www.itjungle.com/tfh/tfh072803-story04.html.

It's been a few years since I've tried accessing AS/400 data files, but I recall having to create some new logical files to resolve some data conversion issues in the physical files. I can't remember whether the JDBC driver handled the packed decimal conversion or whether we unpacked it using a logical file, but I'll e-mail the company's AS/400 admin and see if he remembers.

My advice would be to break the problem down into individual JDBC test programs prior to trying to bind the whole thing together with Pentaho Data Integration so you can isolate any issues as being in the AS/400 structure, the Open Client layer, the JDBC driver, or the PDI script.

Hope this helps somewhat,

John

jleeper
10-12-2007, 08:33 PM
Do your 'externalized' flat files have unique keys? While I don't have specific expericence with Kettle doing this, but from using other tools that use JDBC to connect to 400 db2... I wonder if your files have a unique key? Often JDBC can't do updates without a unique key, even though a select statement will work. That would be where I would start.

Jerry Leeper