Search code examples
db2ibm-midrangeebcdicpacked-decimal

Loading packed decimals into DB2 for IBM i

I am having trouble loading packed data exported from IBM i DB2 into a second IBM i DB2 database. The file comes from a vendor and other kinds of methods like direct connections are not available.

Here are the contents of small test file. Note that I've added some spaces before the last two bytes that represent the packed data (404F):


dspf /home/person/test_file.ebcdic

************Beginning of data************** 

11111111111111111   |                        

 ************End of Data********************

Viewing the hex shows:

F1F1F1F1 F1F1F1F1 F1F1F1F1 F1F1F1F1 F10000 404F  11111111111111111

I create the table:

DROP TABLE IF EXISTS TESTSCHEMA.TESTTABLE ;
CREATE TABLE TESTSCHEMA.TESTTABLE (             
                CHARFLD1 CHAR(3),
                CHARFLD2 CHAR(12),
                CHARFLD3 CHAR(1),
                CHARFLD4 CHAR(1),
                BINFIELD1 BINARY(1),
                BINFIELD2 BINARY(1),
                PACKFIELD DECIMAL(3, 0)
) ORGANIZE BY ROW;

I create a field definition file:

*COL 1    3 0
*COL 4   15 0
*COL 16   16 0
*COL 17   17 0
*COL 18   18 0
*COL 19   19 0
*COL 20   21 0
*END

Try to load the data:

CL: CHGATR OBJ('/home/person/test_file.ebcdic') ATR(*CCSID) VALUE(37);

CL: CPYFRMIMPF FROMSTMF('/home/person/test_file.ebcdic')  
 TOFILE(TESTSCHEMA/TESTTABLE)                          
 FROMCCSID(37) TOCCSID(37)                             
 DTAFMT(*FIXED)                                       
 STMFLEN(21)                                          
 MBROPT(*REPLACE)                                     
 FROMRCD(*FIRST 1)                                    
 FLDDFNFILE(TESTLIB/TESTFILE TMEMBER)                 
;

But I just get an error:

Member TESTTABLE file TESTTABLE in TESTCHEMA cleared.
The copy did not complete for reason code 7.        
0 records copied to member TESTTABLE.               
Copy command ended because of error.                

7 - The FROMFILE numeric field PACKFIELD contains blank characters, or   
 other characters that are not valid for a numeric field.

It seems like the system is ignoring the actual packed bytes and trying to load the characters, which in fact display as blank. If I change the CPYFRMIMPF slightly to ignore the packed decmial by using STMFLEN(19), the character and binary fields load without issue and as expected. To load packed decimals into a different DB2 system on linux, I need to use a packeddecimal option in the db2 load command. But CPYFRMIMPF doesn't seem to provide such an option. Is there another way to go about this?

Solution

  • "Import Files" aren't design to contain packed data; they are supposed to be simple text files to allow data to be transferred between IBM i and non-IBM i systems.

    CPYTOIMPF will unpack the data in a table. CPYFRMIMPF will pack the data as it's loaded to a table.

    If it were me, I'd kick the file back to the vendor and tell them to give you something useable. I'd also seriously reconsider working with the vendor in the first place.

    But if you're stuck. Try using FTP in binary mode to transfer the data directly into the table.

    The other option, is to use the older Copy From Stream File (CPYFRMSTMF) to move the data into a temporary PF created with CRTPF RCDLEN(120). Then use Copy File (CPYF) command with FMTOPT(*NOCHK) to move the data into your actual table.

    If none of that works, you'd need to write an RPG/COBOL program that uses the IFS APIs to read the stream file and move the data to your table.