Import TXT to DB2 with binary data fields

[jborgh]

Hi people, have a mess here with some supposed binary data in a TXT file on IFS to get into a DB2.
I had tried CPYFRMPCD, CPYFRMIMPF but none of them helped me. Tried also by FTP, neither.

Here i attach de original TXT file to import and an excel (rename .DOC file to .XLS) file with de description of that TXT data structure, for us to play.
On the excel file on DETALLE flap, where have the complete file fields descriptions, the most important one for me is the one coloured in blue, that is an amount but in binary format.
For the beggining my DB2 DDS is plain of legtht char 600. For me is the same to have this DDS with all fields defined or plain with only one.
For example if you try to open the TXT with NotePad, will be opened perfect, but try with UltraEdit and it truncated data, same as happens on the iSeries.
My real problem is that when getting it on DB2, some records got truncated because of a special character that does this job.
I attach too, some green screens of the issue.

Any help would be very appreciated!!

[Terry Wincheste]

Re: Import TXT to DB2 with binary data fields

Took a quick look at the PTLFB.TXT and it seems to have a structured layout with the exception of the first and last entries which appear to be header/trailer records.

I couldn't open your DOC file...but would assume that if you created a valid DDS physical file thenyou should be able to copy the data from the TXT file directly into your physical file using CPTFRMIMPF.

Terry

[jborgh]

Re: Import TXT to DB2 with binary data fields

Yes, the TXT file has a header and trailer, the rest of the file are the detail records that had fixed format structure, that you could found it on the .DOC document (that you must rename to .XLS because the site dont let me upload as excel format).
I have done valid DDS and used plain DDS instead (one long field), but couldnt get it working, it keeps truncating data, always at same records in same positions.
I have tried CPYFRMIMPF but with no success.

[DeadManWalks]

Re: Import TXT to DB2 with binary data fields

What record and what position?

[Terry Wincheste]

Re: Import TXT to DB2 with binary data fields

The Excel file shows a standard mainframe style COBOL record layout with multiple "redefines". Typically, your signed fields like dollar amounts will be in packed (binary) format. I tried using a Field Definition File and copying just a single record but it fails with CPF2845 for reason code 7. I'm not sure which field is failing but I suspect that its one of the "redefined" fields. I have
included my FDF layout.

Is this being provided to your by another facility? If so, you may want to have them recreate the file with numeric editing on the "binary" fields so that you can successfully import it into a physical file. However, this is going to require more work on their part to ensure that they maintain the same group level sizes...

Also note: You may want to question the definition for the field named "FIID-LEN" because its declared as an unsigned numeric: 9(03) but the data length in column H is 2. Its either 2 or 3 digits long...not both ;-)

Terry

[jborgh]

Re: Import TXT to DB2 with binary data fields

What record and what position?

Examples:
Record 20, the binary field in positions 499 to 506, here truncates (first attached image).
Record 105, the binary field in positions 73 to 80, here truncates (second attached image).

As I can expect, truncation is made on the positions that are defined as binary data and just did not contain that garbage, instead, contains a numeric legit value.
On first case, there is a 0 on the truncation position and in case 2, there is an 8.

[jborgh]

Re: Import TXT to DB2 with binary data fields

The Excel file shows a standard mainframe style COBOL record layout with multiple "redefines". Typically, your signed fields like dollar amounts will be in packed (binary) format. I tried using a Field Definition File and copying just a single record but it fails with CPF2845 for reason code 7. I'm not sure which field is failing but I suspect that its one of the "redefined" fields. I have
included my FDF layout.

Is this being provided to your by another facility? If so, you may want to have them recreate the file with numeric editing on the "binary" fields so that you can successfully import it into a physical file. However, this is going to require more work on their part to ensure that they maintain the same group level sizes...

Also note: You may want to question the definition for the field named "FIID-LEN" because its declared as an unsigned numeric: 9(03) but the data length in column H is 2. Its either 2 or 3 digits long...not both ;-)

Terry

About the CPF2845 for reason code 7 error: As we dont know the field, by the error description, i think..... could be because of the first 3 binary fields (positions 73-80, 81-88 and 89-96), the 2nd one is always blanks? (and should be at least zeros).

Yes, we receive this file from another company and we have to impact those amounts on our savings accounts. But it is impossible to ask them to send us that binary data as numeric, not because of time, instead because the file is a standard format they use with other companies.

[DeadManWalks]

Re: Import TXT to DB2 with binary data fields

an elegant solution follows;
I would write a VBA piece in Excel or just a windows script. In there I would read the file into a record set and out put a new file that is either CSV or fixed flat, then use FTP to load the file to the server and then the QUOTE command to call the program to put this data away.

[jborgh]

Re: Import TXT to DB2 with binary data fields

Ok guys, after all, the company that owns the file agreed to send it with non packed data amount, after some discusions.
Anyway, thanks too all of you for your interest and predisposition to help!!

[jborgh]

Re: Import TXT to DB2 with binary data fields

Closed