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

Multi format fields in DB2

Multi format fields in DB2

(OP)
I am new to DB2 (coming from an MS SQL background) and am wondering how I can display the data held in a multi format field?  I'm trying to access a table where one of the columns has char(30), but also holds some decimals.  When I do a SELECT * on the table I just get the char fields back and not the decimals.  How could I display the decimals?

Thanks :)

RE: Multi format fields in DB2

Hi,
I've not come across multi-format columns before in DB2 and would be interested to see the DDL that defined the table. Is it possible that you could post a copy of it here.

Regards,

Marc

RE: Multi format fields in DB2

(OP)
do you just mean the create statement?  if so...

CREATE TABLE Tbl1
    (
    CODE   CHAR (3),
    DATE   CHAR (6),
    DETAIL CHAR (30),
    TYPIND CHAR (1)
    );

It's the detail field that is multi format.

RE: Multi format fields in DB2

OK, so the detail column as described to DB2 is NOT multiformat - it's a straight character column. If you decide to store data in it that is in a decimal format, that's a different matter. I guess that you can do this easily using a progamming language to redefine the column, but I can't think of any reason why that data would not then subsequently appear when selected.
 

RE: Multi format fields in DB2

Quote:

might not appear if it contain null values in addition to the numbers.
There is no such thing as a "null value" in db2. NULL is the complete absence of a value.
In the DETAIL CHAR (30) column, there may be characters that cannot be displayed.

Quote:

I just get the char fields back and not the decimals
What is the hex value in the data and what is shown when the SELECT * is executed? What do you want instead?
Also, there is no "multi format" definition in SQL Server that i'm aware of.

RE: Multi format fields in DB2

(OP)
Nothing is shown from the SELECT * statement - only the first few values are which are char values.  Then there are a load of decimal values stored and they come back as nothing within the results table.

As for the hex value, I don't know how I'd find that out, sorry.

All I want is to be able to type in SELECT * and filter to show the decimal values only and actually see them!  Am getting the idea that this isn't possible - I've Googled it a lot and can't find anything either.

The multi format is part of the Cobol programming language (apparently - I know nothing about Cobol), so you just have a char field but can store anything within it and then the programming language sorts it out.

 

RE: Multi format fields in DB2

Hi,
I think we're getting closer but need to clarify things a bit more. Please allow me to summarise what we know so far:

You have got a column 'DETAIL' which is 30 characters in length and can contain either proper CHAR data or decimal data. You are running a 'SELECT *' which appears to be only showing the CHAR data. The data is manipulated by a Cobol program to add the info as CHAR or decimal.

Questions:
How are you running your SELECT? (what environment, machine, operating system etc.)
How are you displaying the data from the SELECT? (ties in with the question above)
What makes you think that you are not seeing the data? Do you get an SQLCODE 100 or is it just that you can't see the info.
Is there any other column on the table (an indicator or flag) that tells you what format the data is in?

Sorry to ask so many questions, but they should hopefully lead to what you require.

Marc

RE: Multi format fields in DB2

COBOL (and every other coding language that i've worked with) supports "redefinition". This means that one "larger" field may contain multiple smaller fields which may vary from record to record. These violate database theory (there should be no redefinition and SQL does not support redefinition).

To see your values in hex, run the SELECT and store the output from the query on dasd. View/Browse the data with HEX ON and you should see something like:

CODE

|   º_ ±Õò |   '  "            *    &ð    
44129608EC44444744744444444440254442584444
F071BDEFFD0F000D00F000000000081C00010C0000
------------------------------------------
011000401187226000{000{008I000{017A026{   
FFFFFFFFFFFFFFFFFFCFFFCFFFCFFFCFFFCFFFC444
011000401187226000000000089000001710260000

If you post your 30-byte data in hex we may be able to help.

RE: Multi format fields in DB2

(OP)

Quote:

there should be no redefinition and SQL does not support redefinition

Thanks papadba - that's kind of the answer I was needing.  I've been shown how to use DDS files to store the data in a new table, so that's how I dealt with it in the end.

Thanks everyone for the help.

RE: Multi format fields in DB2

There was a discusiion on deciphering COBOL data here:
http://www.tek-tips.com/viewthread.cfm?qid=1508458&page=1
It's not so simple.

The best would be if you could find elsewhere the piece of COBOL program or copybook with the data structure which was written to the table field.

Quote (khsaunderson):


I've been shown how to use DDS files to store the data in a new table
For me DDS = 'Data Description Specification' - a format used on AS/400.
Are you on IBM System i aka iSeries aka AS/400 ?   
 

RE: Multi format fields in DB2

(OP)
Yep, I@m using DB2 on As400/iSeries.

RE: Multi format fields in DB2



It would help to see the COBOL PIC data spec (including the redefines), an example of a decimal data value in HEX format and the actual decimal value you expect to 'see'

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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