Help Processing Integer Byte by Byte

**Serge Rielau** · Feb 20 '07, 02:35 PM

Re: Help Processing Integer Byte by Byte

ML wrote:

Integers are stored in tables using only 4 bytes. Is there a way in
SQL to retrieve the value as it is actually stored, not converted back
into the displayed number?
>
For example, if I have 2030906, it gets stored in 4 bytes. Can a
query be written to give me the 4 bytes back, not 2030906. On an
unload, this is done automatically, can it be done in SQL?

Use the HEX() function. Note that such code will NOT be portable because
the storage depends on the CPU architecture.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

ML · Feb 20 '07, 03:05 PM

Re: Help Processing Integer Byte by Byte

On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:

ML wrote:

Integers are stored in tables using only 4 bytes. Is there a way in
SQL to retrieve the value as it is actually stored, not converted back
into the displayed number?

>

For example, if I have 2030906, it gets stored in 4 bytes. Can a
query be written to give me the 4 bytes back, not 2030906. On an
unload, this is done automatically, can it be done in SQL?

>
Use the HEX() function. Note that such code will NOT be portable because
the storage depends on the CPU architecture.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.

Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

What next?

Thanks!
ML

**Brian Tkatch** · Feb 20 '07, 03:45 PM

Re: Help Processing Integer Byte by Byte

On 20 Feb 2007 06:50:15 -0800, "ML" <lipsey.mike@gm ail.comwrote:

>On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:

>ML wrote:

Integers are stored in tables using only 4 bytes. Is there a way in
SQL to retrieve the value as it is actually stored, not converted back
into the displayed number?

>>

For example, if I have 2030906, it gets stored in 4 bytes. Can a
query be written to give me the 4 bytes back, not 2030906. On an
unload, this is done automatically, can it be done in SQL?

>>
>Use the HEX() function. Note that such code will NOT be portable because
>the storage depends on the CPU architecture.
>>
>Cheers
>Serge
>>
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>
>
>Yep, got that far. I can retrieve the above number into 001EFD3A.
>What I want to do now is to restore that 001EFD3A into a char(4)
>field.

Huh?

B.

I need to do all this in SQL, though, not a program.
>Specifically , I am wanting to take a char(2) and an integer and store
>them into a char(6) field (don't ask why!). I need to do this in a
>trigger.
>
>What next?
>
>Thanks!
>ML

**Serge Rielau** · Feb 20 '07, 03:45 PM

Re: Help Processing Integer Byte by Byte

ML wrote:

On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:

>ML wrote:

>>Integers are stored in tables using only 4 bytes. Is there a way in
>>SQL to retrieve the value as it is actually stored, not converted back
>>into the displayed number?
>>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>>query be written to give me the 4 bytes back, not 2030906. On an
>>unload, this is done automatically, can it be done in SQL?

>Use the HEX() function. Note that such code will NOT be portable because
>the storage depends on the CPU architecture.
>>
>Cheers
>Serge
>>
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>
>
Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.
>
Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

Yuck.. You could follow it up with the CHR() function.
Can you use C? It would be a one liner in a C UDF :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

ML · Feb 20 '07, 04:35 PM

Re: Help Processing Integer Byte by Byte

On Feb 20, 9:39 am, Serge Rielau <srie...@ca.ibm .comwrote:

ML wrote:

On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:

ML wrote:
>Integers are stored in tables using only 4 bytes. Is there a way in
>SQL to retrieve the value as it is actually stored, not converted back
>into the displayed number?
>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>query be written to give me the 4 bytes back, not 2030906. On an
>unload, this is done automatically, can it be done in SQL?
Use the HEX() function. Note that such code will NOT be portable because
the storage depends on the CPU architecture.

>

Cheers
Serge

>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.

>

Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

>
Yuck.. You could follow it up with the CHR() function.
Can you use C? It would be a one liner in a C UDF :-)
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Yeah, we are shoehorning in a 10 byte field into a 6 byte field to
accommodate an old system that doesn't want to expand their storage.
Unfortunately, this is zOS DB2, not UDB.

So, we want to split a 10 byte field, that looks like XX######## (ex.
AB02030906) and store it into a char(6) field by concatenating the AB
with the actual 4 bytes that the integer converts into. Pretty easy
with a program, not so easy with a trigger.

"Yuck" does sum it up, though!

--
ML

ML · Feb 20 '07, 07:05 PM

Re: Help Processing Integer Byte by Byte

On Feb 20, 10:28 am, "ML" <lipsey.m...@gm ail.comwrote:

On Feb 20, 9:39 am, Serge Rielau <srie...@ca.ibm .comwrote:
>
>
>

ML wrote:

On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:
>ML wrote:
>>Integers are stored in tables using only 4 bytes. Is there a way in
>>SQL to retrieve the value as it is actually stored, not converted back
>>into the displayed number?
>>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>>query be written to give me the 4 bytes back, not 2030906. On an
>>unload, this is done automatically, can it be done in SQL?
>Use the HEX() function. Note that such code will NOT be portable because
>the storage depends on the CPU architecture.

>

>Cheers
>Serge

>

>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.

>

Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

>

Yuck.. You could follow it up with the CHR() function.
Can you use C? It would be a one liner in a C UDF :-)

>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>
Yeah, we are shoehorning in a 10 byte field into a 6 byte field to
accommodate an old system that doesn't want to expand their storage.
Unfortunately, this is zOS DB2, not UDB.
>
So, we want to split a 10 byte field, that looks like XX######## (ex.
AB02030906) and store it into a char(6) field by concatenating the AB
with the actual 4 bytes that the integer converts into. Pretty easy
with a program, not so easy with a trigger.
>
"Yuck" does sum it up, though!
>
--
ML

Maybe if I give additional information, one of you geniuses can find a
solution.

I can query my.table, and get the hex value of the integer field:

SELECT CAST(INTEGER_FI ELD_A AS CHAR(4))
FROM MY.TABLE
WHERE SOME_IND = 'Y'
;

This returns 001DFD3A. Great, no problem. I can even, manually,
update the char(6) field, as follows:

UPDATE MY.TABLE
SET CHAR_6_FIELD = 'AA'||X'001EFD3 A'
WHERE SOME_IND = 'Y'
;

What I want to do is 1 update statement (which I will put in a
trigger) that does both steps at once.

Is this possible, and how?

Thanks!
ML

ML · Feb 20 '07, 07:55 PM

Re: Help Processing Integer Byte by Byte

On Feb 20, 12:50 pm, "ML" <lipsey.m...@gm ail.comwrote:

On Feb 20, 10:28 am, "ML" <lipsey.m...@gm ail.comwrote:
>
>
>

On Feb 20, 9:39 am, Serge Rielau <srie...@ca.ibm .comwrote:

>

ML wrote:
On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:
ML wrote:
>Integers are stored in tables using only 4 bytes. Is there a way in
>SQL to retrieve the value as it is actually stored, not converted back
>into the displayed number?
>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>query be written to give me the 4 bytes back, not 2030906. On an
>unload, this is done automatically, can it be done in SQL?
Use the HEX() function. Note that such code will NOT be portable because
the storage depends on the CPU architecture.

>

Cheers
Serge

>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.

>

Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

>

Yuck.. You could follow it up with the CHR() function.
Can you use C? It would be a one liner in a C UDF :-)

>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>

Yeah, we are shoehorning in a 10 byte field into a 6 byte field to
accommodate an old system that doesn't want to expand their storage.
Unfortunately, this is zOS DB2, not UDB.

>

So, we want to split a 10 byte field, that looks like XX######## (ex.
AB02030906) and store it into a char(6) field by concatenating the AB
with the actual 4 bytes that the integer converts into. Pretty easy
with a program, not so easy with a trigger.

>

"Yuck" does sum it up, though!

>

--
ML

>
Maybe if I give additional information, one of you geniuses can find a
solution.
>
I can query my.table, and get the hex value of the integer field:
>
SELECT CAST(INTEGER_FI ELD_A AS CHAR(4))
FROM MY.TABLE
WHERE SOME_IND = 'Y'
;
>
This returns 001DFD3A. Great, no problem. I can even, manually,
update the char(6) field, as follows:
>
UPDATE MY.TABLE
SET CHAR_6_FIELD = 'AA'||X'001EFD3 A'
WHERE SOME_IND = 'Y'
;
>
What I want to do is 1 update statement (which I will put in a
trigger) that does both steps at once.
>
Is this possible, and how?
>
Thanks!
ML

Not sure where my brain is today. The following:

SELECT CAST(INTEGER_FI ELD_A AS CHAR(4))
FROM MY.TABLE
WHERE SOME_IND = 'Y'

Should have read:

SELECT HEX(INTEGER_FIE LD_A)
FROM MY.TABLE
WHERE SOME_IND = 'Y'

Sorry.

--
ML

**Knut Stolze** · Feb 20 '07, 08:25 PM

Re: Help Processing Integer Byte by Byte

ML wrote:

On Feb 20, 12:50 pm, "ML" <lipsey.m...@gm ail.comwrote:

>On Feb 20, 10:28 am, "ML" <lipsey.m...@gm ail.comwrote:
>>
>>
>>

On Feb 20, 9:39 am, Serge Rielau <srie...@ca.ibm .comwrote:

>>

ML wrote:
On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:
ML wrote:
>Integers are stored in tables using only 4 bytes. Is there a way
>in SQL to retrieve the value as it is actually stored, not
>converted back into the displayed number?
>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>query be written to give me the 4 bytes back, not 2030906. On an
>unload, this is done automatically, can it be done in SQL?
Use the HEX() function. Note that such code will NOT be portable
because the storage depends on the CPU architecture.

>>

Cheers
Serge

>>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>>

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.

>>

Specifically, I am wanting to take a char(2) and an integer and
store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.

>>

Yuck.. You could follow it up with the CHR() function.
Can you use C? It would be a one liner in a C UDF :-)

>>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>>

Yeah, we are shoehorning in a 10 byte field into a 6 byte field to
accommodate an old system that doesn't want to expand their storage.
Unfortunately, this is zOS DB2, not UDB.

>>

So, we want to split a 10 byte field, that looks like XX######## (ex.
AB02030906) and store it into a char(6) field by concatenating the AB
with the actual 4 bytes that the integer converts into. Pretty easy
with a program, not so easy with a trigger.

>>

"Yuck" does sum it up, though!

>>

--
ML

>>
>Maybe if I give additional information, one of you geniuses can find a
>solution.
>>
>I can query my.table, and get the hex value of the integer field:
>>
>SELECT CAST(INTEGER_FI ELD_A AS CHAR(4))
> FROM MY.TABLE
> WHERE SOME_IND = 'Y'
>;
>>
>This returns 001DFD3A. Great, no problem. I can even, manually,
>update the char(6) field, as follows:
>>
>UPDATE MY.TABLE
> SET CHAR_6_FIELD = 'AA'||X'001EFD3 A'
> WHERE SOME_IND = 'Y'
>;
>>
>What I want to do is 1 update statement (which I will put in a
>trigger) that does both steps at once.
>>
>Is this possible, and how?
>>
>Thanks!
>ML

>
Not sure where my brain is today. The following:
>

>SELECT CAST(INTEGER_FI ELD_A AS CHAR(4))
> FROM MY.TABLE
> WHERE SOME_IND = 'Y'

>
Should have read:
>

>SELECT HEX(INTEGER_FIE LD_A)
> FROM MY.TABLE
> WHERE SOME_IND = 'Y'

UPDATE my.table
SET char_6_field = 'AA' || HEX(integer_fie ld_a)
WHERE ...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

**Knut Stolze** · Feb 20 '07, 08:25 PM

Re: Help Processing Integer Byte by Byte

ML wrote:

Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field.

That won't work: you have 8 characters there, which won't fit in a CHAR(4),
obviously.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

**Bob Stearns** · Feb 21 '07, 06:25 AM

Re: Help Processing Integer Byte by Byte

ML wrote:

On Feb 20, 8:24 am, Serge Rielau <srie...@ca.ibm .comwrote:
>

>>ML wrote:
>>

>>>Integers are stored in tables using only 4 bytes. Is there a way in
>>>SQL to retrieve the value as it is actually stored, not converted back
>>>into the displayed number?

>>

>>>For example, if I have 2030906, it gets stored in 4 bytes. Can a
>>>query be written to give me the 4 bytes back, not 2030906. On an
>>>unload, this is done automatically, can it be done in SQL?

>>
>>Use the HEX() function. Note that such code will NOT be portable because
>>the storage depends on the CPU architecture.
>>
>>Cheers
>>Serge
>>
>>--
>>Serge Rielau
>>DB2 Solutions Development
>>IBM Toronto Lab

>
>
>
Yep, got that far. I can retrieve the above number into 001EFD3A.
What I want to do now is to restore that 001EFD3A into a char(4)
field. I need to do all this in SQL, though, not a program.
>
Specifically, I am wanting to take a char(2) and an integer and store
them into a char(6) field (don't ask why!). I need to do this in a
trigger.
>
What next?
>
Thanks!
ML
>

The absolute kludge which will work is (for a shortint; obvious
extensions for other data types):

select 'AA' ||
chr(posstr('012 3456789ABCDEF', substr(hex(shor tint),1)) +
posstr('0123456 789ABCDEF',subs tr(hex(shortint ),2))) ||
chr(posstr('012 3456789ABCDEF', substr(hex(shor tint),3)) +
posstr('0123456 789ABCDEF',subs tr(hex(shortint ),4)))

but what it will print as depends on so many things I can't begin to
count them (code page, terminal (simulator), etc.)

**Tonkuma** · Feb 21 '07, 09:15 AM

Re: Help Processing Integer Byte by Byte

You should be careful for internal data format on your platform.
Following example was tested on Windows on PC.
So, I assumed little endian.
Another asumption is values are not negative.
The reason of using COALESCE and NULLIF is based on the fact CHR(0) =
x'20'.
------------------- Commands Entered ------------------------------
SELECT intdata
, HEX(intdata) AS indata_x
, HEX(
COALESCE(CHR(NU LLIF(MOD(intdat a,256),0)),x'00 ')
||COALESCE(CHR( NULLIF(MOD(intd ata/256,256),0)),x' 00')
||COALESCE(CHR( NULLIF(MOD(intd ata/65536,256),0)), x'00')
||COALESCE(CHR( NULLIF(intdata/16777216,0)),x' 00')
) AS char4_x
, COALESCE(CHR(NU LLIF(MOD(intdat a,256),0)),x'00 ')
||COALESCE(CHR( NULLIF(MOD(intd ata/256,256),0)),x' 00')
||COALESCE(CHR( NULLIF(MOD(intd ata/65536,256),0)), x'00')
||COALESCE(CHR( NULLIF(intdata/16777216,0)),x' 00') AS char4
FROM (SELECT INT(intdata) intdata
FROM (VALUES 2030906, 2147483647, 1234567, 0 ) Data(intdata)
) R;
-------------------------------------------------------------------

INTDATA INDATA_X CHAR4_X CHAR4
----------- -------- -------- -----
2030906 3AFD1E00 3AFD1E00 :
2147483647 FFFFFF7F FFFFFF7F
1234567 87D61200 87D61200 ?
0 00000000 00000000

4 record(s) selected.

**Tonkuma** · Feb 21 '07, 10:25 AM

Re: Help Processing Integer Byte by Byte

There are at least following issues for your sample.
1) If DB2 is DB2 for LUW, expression can not be used for 2nd parameter
of POSSTR. You may be necessary to use LOCATE instead of POSSTR.
2) substr might need 3rd parameter as 1.
Like: substr(hex(shor tint),1,1)
3) I think you forgot *16 for line 1 and 3.
4) CHR(0) = x'20' (not x'00').

**Knut Stolze** · Feb 21 '07, 10:35 AM

Re: Help Processing Integer Byte by Byte

Tonkuma wrote:

2) substr might need 3rd parameter as 1.
Like: substr(hex(shor tint),1,1)

SUBSTR(x, y, 1) is the same as SUBSTR(x, y)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

**Tonkuma** · Feb 22 '07, 03:15 AM

Re: Help Processing Integer Byte by Byte

On Feb 21, 7:20 pm, Knut Stolze <sto...@de.ibm. comwrote:

Tonkuma wrote:

2) substr might need 3rd parameter as 1.
Like: substr(hex(shor tint),1,1)

>
SUBSTR(x, y, 1) is the same as SUBSTR(x, y)
>
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

At least DB2 for LUW, SUBSTR(x, y, 1) is not same as SUBSTR(x, y).
-------------------- Commands Entered ------------------------------
SELECT CharData
, SUBSTR(CharData ,1)
, SUBSTR(CharData ,2)
, SUBSTR(CharData ,3)
, SUBSTR(CharData ,4)
, SUBSTR(CharData ,1,1)
, SUBSTR(CharData ,2,1)
, SUBSTR(CharData ,3,1)
, SUBSTR(CharData ,4,1)
FROM (VALUES 'ABCDEFGHIJ') TestData(CharDa ta);
--------------------------------------------------------------------

CHARDATA 2 3 4 5 6 7 8 9
---------- ---------- ---------- ---------- ---------- - - - -
ABCDEFGHIJ ABCDEFGHIJ BCDEFGHIJ CDEFGHIJ DEFGHIJ A B C D

1 record(s) selected.

Help Processing Integer Byte by Byte

Help Processing Integer Byte by Byte

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment