INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Identify Binary data stored in DB2 CHAR field

Identify Binary data stored in DB2 CHAR field

(OP)
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?

RE: Identify Binary data stored in DB2 CHAR field

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.

RE: Identify Binary data stored in DB2 CHAR field

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:

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

RE: Identify Binary data stored in DB2 CHAR field

(OP)
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

RE: Identify Binary data stored in DB2 CHAR field

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Identify Binary data stored in DB2 CHAR field

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close