#1. create a temporary physical file in QTEMP with a RCDLEN long enough to hold the longest line, for example, (assuming 133 is long enough):


#2. use CPYFRMSTMF to copy the data to the temporary file.

CPYFRMSTMF FROMSTMF('/tmp/stmf133.txt')

#3.  use SQL to update the relevant rows in this temporary file (table), for example:

    update qtemp/pf133 set pf133 = substr(pf133,1,26) || 'DB2' ||
        substr(pf133,30) where substr(pf133,27,3) = 'SQL'

In this example above, I want to change the value "SQL" to "DB2" whenever it was found in position 27 in any row or record.   Note the second use of SUBSTR with starting position only to indicate to copy all remaining data up to the end of the record or row.

#4. Then use CPYTOSTMF to copy the results back to a stream file.

    CPYTOSTMF FROMMBR('/qsys.lib/qtemp.lib/pf133.file/pf133.mbr')
                TOSTMF('/tmp/stmf133.txt') STMFOPT(*REPLACE)

Repeat the above steps for each of your 20 or so stream files ...

Hope that helps,

Mark S. Waterbury

On Monday, January 13, 2020, 11:21:59 AM EST, Jim Franz <franz9000@xxxxxxxxx> wrote:

I need to update text files received for import (not a database file) in
the IFS with only sql (the text files were generated weeks ago, cannot be
regenerated, and a two byte column needs to be updated before processing if
value = 99). I don't have an option to do an emergency release of program
code to fix it (at least not easily), but I can execute sql script with a
simple approval.
Is such a thing possible?  It is many files (20 or so) and approx 100,000
rows per file.

psuedo code idea

  update '/input/file1.txt'
        set (substring,101,2) =
            (select ordertype from OrdMaster om
            where (substring,11,9) = om.OrderNbr)
        where (substring,101,2) = '99'

and the order number in substring,11,9 is a character string matching
OrderNbr which is character.


Jim Franz

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.