Packed Fields DB2 SQL

[gdkz]

I know I can use DB2 SQL to read packed fields from a table. When loading data from a file I currently use a cobol program to convert packed fields and load tables.
Is it possible to insert or update packed fields from DB2 SQL?
What are some good resources on packed fields?

Thanks,
Greg

 

[stevexff]

By packed fields do you mean columns defined as decimal? In which case the answer is 'yes'. The DCLGEN will give you the COBOL PIC value (or PL/1 definitiion) of your host variable.

 

[MarcLodge]

DB2 has no problem with packed fields. The numeric definitions you are interested in are:
SMALLINT - This is a binary field, S9(4) COMP in Cobol
INTEGER - This is a binary field, S9(8) COMP in Cobol
DECIMAL - This is a packed field, S9(?) COMP-3 in Cobol

If the table is defined in any of these formats, then inserting the a value in the SQL will automatically get converted to the correct format, and stored successfully.

Hope this helps.

Marc

 

[gdkz]

Thanks stevexff and MarcLodge
That’s exactly what I needed to here, growing up in the other world, packed fields were foreign to me. I could not seem to find definitive info on decimals and packed fields, at least to explain it to me in my Microsoft paradigm.
Still learning my way in the cobol and DB2 arena.


Thanks,
Greg

 

[gdkz]

In addendum to the packed fields question, if a table layout does not include packed fields, but uses a copybook to populate a field containing multiple fields including decimal(packed) field. For example:

Physical Tbl1:
KeyField1 X(44), Field2 X(25), Field3 X(200)

Copy Book references this table in this manner:
KeyField1 X(44), Field2 X(25), Field3 S9(04)V9(03), Field4 X(191)

How does DB2 SQL select the data from Field3 separately from Physical Tbl1.
If you
SELECT
Field1,
Field2,
Field3
FROM Tbl1

The packed data does not unpack. So how do you reference the packed data from within the Field3 in Tbl1?


Thanks,
Greg

 

[MarcLodge]

What you are describing is data stored as character on a DB2 table, which is not in fact character, but something else. This is not an unheard of situation, but is mildly unusual.

In your example, the definition of Field3 is not packed, but a plain numeric field - S9(04)V(03). This is read into the first 8 bytes of Field3 in working storage in this way is. Normally in these sort of circumstances, you would redefine the field, in order to give you the correct format.

Let us know what you are after, and how, it at all, we can be on any further help.

Marc

 

[stevexff]

Greg

OK, so the numeric data is held as the first few bytes of field3. If it's plain numeric (as Marc notes) then it will be x'F1F2F3F4F5F6F7', seven bytes. In which case try:

05 FIELD-3          PIC X(200).
   10 FIELD-3A      PIC S9(4)V9(3).
   10 FILLER        PIC X(193).

and use :FIELD-3 as your SELECT statement host variable. Then use FIELD-3A to manipulate the numeric afterwards.

If it's genuinely packed decimal, then it will be x'1234567C' (four bytes), and you will need something like

05 FIELD-3          PIC X(200).
   10 FIELD-3A      PIC S9(4)V9(3) COMP-3.
   10 FILLER        PIC X(196).

Steve

 

[gdkz]

Yes, I'm sorry I ommitted this, it is a comp3 field. I am trying to query the info in this field from a SQL Store Procedure. I have tried to substring on this field, but the data does not unpack. I have also tried to convert to decimal/char to retrieve the packed data.

select decimal(substring(Field3,1,4))
from Tbl1


My goal is to retrieve this data and update when necessary from the stored procedure.
Thanks for the help on my learning curve.

Thanks,
Greg

 

[stevexff]

The data won't unpack because DB2 thinks it is character data. The only possibilities I can suggest are:

05 FIELD-3       PIC X(200).
..
..
01 CHAR-3        PIC X(4).
01 PACKED-3 REDEFINES CHAR-3 PIC S9(4)V9(3) COMP-3.
..
select field_3 into FIELD-3
..
MOVE FIELD-3(1:4) TO CHAR-3.

You can then use PACKED-3 in your code, and the compiler will know that it's packed.

But I have to ask, what is the point of having a database that supports multiple column data types, if you are going to stick everything in one big character field and treat it as a blob? You might be better off redesigning your table than trying to mess around like this...

 

[gdkz]

Thanks stevexff, I agree with redesigning the table.
Our company has purchased a software package and we are trying to interface with it. This is the way it has been set up from the provider.
I will try what you suggest.
Thanks again.

Thanks,
Greg