PowerWire.eu

Independent IBM i, AIX and Linux news and tech tips for Europe and beyond

  • Home
  • News
  • Technical Articles
    • IBM i
    • AIX
    • Linux
    • VIOS
  • Subscribe
  • About Us
  • Contact Us
  • Advertise with PowerWire.eu

The Joys of System36 – Update your flat files via SQL

January 9, 2019 by David Shears

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      

david.shears@mnis.co.uk

 

Filed Under: IBM i, Technical Articles Tagged With: IBM i, IBM Power Systems, Power Systems, system management

Free monthly newsletter signup

News

A screen a story – Not our problem anymore

Last week I ran into an issue, which did teach me something new about the command WRKPRB. After checking a … [Read More...]

Raspberry Pi and Machine Learning

In my last article, I demonstrated how I used Machine Learning on the Raspberry Pi to determine if a … [Read More...]

Shield Advanced Solutions – new Messaging and Monitoring solutions

September 2022 – Toronto As Shield Advanced Solutions celebrates the significant milestone, 25 years of … [Read More...]

More articles from this section

Quick Links

  • Advertise
  • Subscribe

Follow Us…

  • Email
  • LinkedIn
  • Twitter

Search This Website

Copyright © 2023 · Cue Communications · All Rights Reserved

Log in