Multi format fields in DB2

[khsaunderson]

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

 

[MarcLodge]

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

 

[khsaunderson]

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.

 

[MarcLodge]

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.

 

[fredericofonseca]

might not appear if it contain null values in addition to the numbers. not a good thing to do, but not unlikely either.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[papadba]

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.

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.

 

[khsaunderson]

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.

 

[MarcLodge]

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

 

[papadba]

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:

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

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

 

[khsaunderson]

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.

 

[mikrom]

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.

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 ?

 

[khsaunderson]

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

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]