How to map values from a Text File with no delimiters in IFS to an SQL DB2 table?

[julius_alvero14]

Please help me, I'm just a newbie with AS400 environment.

I have created a DB2 table with 47 fields with assorted data types (char, varchar, numeric, int and decimal).

My input file is to be taken from the IFS and that file is a text file (.txt) without column headings and without delimiter as well.

I have created a flat file to the QTEMP library using the statement below:

CPYFRMSTMF FROMSTMF(&ORIGIN) TOMBR(&DEST) +
MBROPT(*REPLACE) +
ENDLINFMT(*CRLF) TABEXPN(*NO)

The above statement executed perfectly, so the temporary file was created with all the records from the text file copied in it.

I'm trying to load the records from that temporary file using the statement below:

CPYF FROMFILE(QTEMP/&TEMPFILE) +
TOFILE(&LIBRARY/&FINALTABLE) MBROPT(*ADD) +
FMTOPT(*MAP *DROP)

The statement above went unsuccesful.

What would be the alternative or the solution to this case?

Many thanks.

[TedHolt]

Re: How to map values from a Text File with no delimiters in IFS to an SQL DB2 table?

Use the DSPJOBLOG command to look at the job log. There will be at least one diagnostic message that will tell you why the copy failed.

[GLS400]

Re: How to map values from a Text File with no delimiters in IFS to an SQL DB2 table?

Hi julius_alvero14:

Your input file is in fixed format while your output file is externally defined.
If the record length is the same (input and output).....you can try to use the cpyf command with fmtopt(*nochk).

If the record lengths are different you will need a high level program (rpg/cobol etc) to load.

using rpg you will need to define (using I specs)

FMYFILE    IF   F  269        DISK
FOUTFILE   O    E               DISK
IMYFMT    NS  01                                 
I                                  1   10 0FIELD1   
I                                 11   12 0FIELD2 
I                                 13   13  FIELD3 
 /FREE
         DOU %EOF(MYFILE) ;
              READ MYFILE ;
              IF %EOF(MYFILE) ;
                   ITER ;
              ENDIF ;
              WRITE OUTFILER ;
           ENDDO ;
         *INLR = *ON ;
    /END-FREE

Where field1/2/3 are defined the same in your output file

Best of Luck
GLS

[julius_alvero14]

Re: How to map values from a Text File with no delimiters in IFS to an SQL DB2 table?

Hi GLS400,

I made a clone of the final table by declaring all the fields into CHAR data type and the CPYF function with FMTOPT(*NOCHK) from the temporary file in QTEMP which I created with a RCDLEN(1500) worked perfectly.

I am planning to use that cloned table to map the values to the final table.

Would it be possible?

Thanks!

[tomliotta]

Re: How to map values from a Text File with no delimiters in IFS to an SQL DB2 table?

If the streamfile is more or less 'pure' text and the target database file consists of "(char, varchar, numeric, int and decimal)", it's almost guaranteed that CPYF can't be used. The source file won't have fields in VARCHAR, INT or DECIMAL formats. The database will reject the input.

In order to help, we'll need to see a few representative lines from the source streamfile.