Identify Binary data stored in DB2 CHAR field 2

[letmelive1]

There is a DB2 table say TABLE1 that has a field FIELD1 which is of type CHAR. COBOL program which updates this field 'FIELD1' has the host varaible declared as comp-3. Thus there are several rows in TABLE1 which has got binary value stored in CHAR field. I need to identify such rows. Is there a way to identify the binary values in CHAR field?

 

[papadba]

Depends on which invalid/undesired values are in the table.

I suspect the values are packed-decimal ratner than binary numbers.

Suggest you extract this column, sort the extracted data eliminating duplicates, and then show the de-duplicated values in hex. This will identify the unique values that are in that column in a way you can "see" them.

Show us what you find.

 

[MarcLodge]

Hi lml,
Like papadba, I believe the data will be packed-decimal rather than binary as you've spoken about Cobol and comp-3. If this is the case, then it might be possible to find the data via a SELECT.

The last bit of the last byte of a comp-3 field contains the sign, which if positive is C, negative is D and unsigned is F. For example the value +200 in a 2 byte signed packed decmimal field when you looked at it in hex would show 200C.

You might therefore be able to interogate this last byte and specifically the last bit using the following code:

SELECT FIELD1
FROM TABLE1
WHERE SUBSTR(HEX(FIELD1),LENGTH(FIELD1),1) IN ('C','D','F')

If the data is as I suspect it is, this should identify any CHAR fields that contain packed decimal data.

Hope this helps,

Marc

 

[letmelive1]

Thank you papadba and Marc.
I apologize for my mistake. The COBOL host variable is declared as COMP and not COMP-3 as I had mentioned earlier. The table in question is something that is used in logic like check point restart. And this field I was talking about is defined as CHAR in DB2 where as in COBOL programs it could be alphanumeric, numeric, binary, packed decimal or any possible data type. Problem is when we tried to migrate the table from DB2 to MS SQL server a few rows errored out as they got some incompatible data(possibly the binary data stored as text). We may not have those rows still in production now but my purpose is to identify such data in table. Kindly suggest me how to achieve this.

Regards,
LML

 

[fredericofonseca]

one of the aspects that needs to be considered is what is going to be done with that data once migrated to SQL Server.

Is it going to be proceceed by a COBOL Program accessing SQL Server? if so further consideration needs to be taken based on the source/target operating system type

If not does is that data of business importance for the business? if so it needs to be converted to "readable" format before migration.

If the data needs to be kept as is, then the fields on SQL Server need to be defined as binary - but even so if the source data is from a EBCDIC system, then it probably should be converted to the ASCII version of the same representation during migration.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[papadba]

I believe you need to specifically identify which values are problematic. . .

If a db2 char value is loaded into a Sql Server char column, what values could be "incompatible"? If there is a problem with some cobol code and something that cobol expects to be "numeric", this does not sound like a db2/Sql Server issue?

If you post some actual values and the problem cobol code someone may have a suggestion.