For those of you who have a customer that just stopped checking the calendar before the turn of the millennium or really likes the good old-fashioned two character device IDs you’ll sometimes need to update data in a very awkward format.
If your data is in a format that UPDDTA can’t read it then perhaps SQL will be your friend today?
Using STRSQL, or if you’re using ACS perhaps you’d like to try your hand at the SQL scripts interface? You’ll find that you can select & update records where difficult data means that UPDDTA won’t work. For those not familiar with data updates in SQL you can use the substring function to build a full text string for your flat file.
Your first challenge will be in ensuring you’re finding the right record on your file, best practise is always to do a select before you try and do your update. In my example I’m looking for a record where positions 1-2 are ‘01’ and positions 585-590 are ‘000046’:
Select * from testfile where substr(f00001,585,6) = ‘000046’ and substr(f00001,1,2) = ‘01’
One thing to bear in mind if you are doing this under system36 is that if your filename has a special character (such as a decimal point) then SQL might have trouble reading the file name. Make sure to OVRDBF or otherwise rename your file ahead of time.
Another thing to bear in mind, if your file has a key defined under system36 then F00001 isn’t your whole file length. In my example testfile has a 9 long key at the start of it so my actual command would be:
Select * from testfile where substr(f00001,576,6) = ‘000046’ and substr(K00001,1,2) = ‘01’
Since my first 9 positions are part of the key my, F00001 criteria has moved back accordingly.
If you prefer, and you know the record number you’re after, you can also use RRN(testfile) to get at it to make 100% sure you’ve got the right one.
Once you’re happy you’ve found the right record you can now build a new version of it, you’ll want to use substring again to keep the bits of the record you’re interested in and then just replace the section you want shot of.
Update testfile
Set f00001 = substr(f00001,1,575) || ‘000000’ || substr(f00001,582,434)
Where substr(f00001,575,6) = ‘000046’
And there you have it, the field remains as it was apart from the small substring you wanted taken out.
David Shears is an IBM I programmer/admin in King’s Lynn, UK based MNI Services Ltd