Re: can sql update a non database ascii text file in IFS

Jim,

#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):

    CRTPF QTEMP/PF133 RCDLEN(133)

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

CPYFRMSTMF FROMSTMF('/tmp/stmf133.txt')
            TOMBR('/qsys.lib/qtemp.lib/pf133.file/pf133.mbr')
            MBROPT(*REPLACE)


#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.

TIA

Jim Franz