By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

Help Processing Integer Byte by Byte

P: n/a
ML
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?

Thank!
ML

Feb 20 '07 #1
Share this Question
Share on Google+
20 Replies


P: n/a
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
Feb 20 '07 #2

P: n/a
ML
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

Feb 20 '07 #3

P: n/a
On 20 Feb 2007 06:50:15 -0800, "ML" <li*********@gmail.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
Feb 20 '07 #4

P: n/a
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
Feb 20 '07 #5

P: n/a
ML
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

Feb 20 '07 #6

P: n/a
ML
On Feb 20, 10:28 am, "ML" <lipsey.m...@gmail.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_FIELD_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'001EFD3A'
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

Feb 20 '07 #7

P: n/a
ML
On Feb 20, 12:50 pm, "ML" <lipsey.m...@gmail.comwrote:
On Feb 20, 10:28 am, "ML" <lipsey.m...@gmail.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_FIELD_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'001EFD3A'
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_FIELD_A AS CHAR(4))
FROM MY.TABLE
WHERE SOME_IND = 'Y'
Should have read:
SELECT HEX(INTEGER_FIELD_A)
FROM MY.TABLE
WHERE SOME_IND = 'Y'

Sorry.

--
ML
Feb 20 '07 #8

P: n/a
ML wrote:
On Feb 20, 12:50 pm, "ML" <lipsey.m...@gmail.comwrote:
>On Feb 20, 10:28 am, "ML" <lipsey.m...@gmail.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_FIELD_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'001EFD3A'
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_FIELD_A AS CHAR(4))
FROM MY.TABLE
WHERE SOME_IND = 'Y'

Should have read:
>SELECT HEX(INTEGER_FIELD_A)
FROM MY.TABLE
WHERE SOME_IND = 'Y'
UPDATE my.table
SET char_6_field = 'AA' || HEX(integer_field_a)
WHERE ...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 20 '07 #9

P: n/a
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
Feb 20 '07 #10

P: n/a
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('0123456789ABCDEF',substr(hex(shortint) ,1)) +
posstr('0123456789ABCDEF',substr(hex(shortint),2)) ) ||
chr(posstr('0123456789ABCDEF',substr(hex(shortint) ,3)) +
posstr('0123456789ABCDEF',substr(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.)
Feb 21 '07 #11

P: n/a
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(NULLIF(MOD(intdata,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/256,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/65536,256),0)),x'00')
||COALESCE(CHR(NULLIF(intdata/16777216,0)),x'00')
) AS char4_x
, COALESCE(CHR(NULLIF(MOD(intdata,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/256,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/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.
Feb 21 '07 #12

P: n/a
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(shortint),1,1)
3) I think you forgot *16 for line 1 and 3.
4) CHR(0) = x'20' (not x'00').

Feb 21 '07 #13

P: n/a
Tonkuma wrote:
2) substr might need 3rd parameter as 1.
Like: substr(hex(shortint),1,1)
SUBSTR(x, y, 1) is the same as SUBSTR(x, y)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 21 '07 #14

P: n/a
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(shortint),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(CharData);
--------------------------------------------------------------------

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

1 record(s) selected.

Feb 22 '07 #15

P: n/a
On Feb 21, 7:18 pm, "Tonkuma" <tonk...@jp.ibm.comwrote:
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(shortint),1,1)
3) I think you forgot *16 for line 1 and 3.
4) CHR(0) = x'20' (not x'00').
5) posstr('0123456789ABCDEF',substr(hex(shortint),1)) returns one
greater number than desired.
For example: posstr('0123456789ABCDEF','5') returns 6.
One idea is to remove '0' from HEX digits list.
Like this.
-------------------- Commands Entered ------------------------------
VALUES ('5', LOCATE('5','123456789ABCDEF') )
, ('0', LOCATE('0','123456789ABCDEF') )
, ('F', LOCATE('F','123456789ABCDEF') );
--------------------------------------------------------------------

1 2
- -----------
5 5
0 0
F 15

3 record(s) selected.

Feb 22 '07 #16

P: n/a
Tonkuma wrote:
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(shortint),1,1)

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

At least DB2 for LUW, SUBSTR(x, y, 1) is not same as SUBSTR(x, y).
You are right of course. I mixed up the length with the starting position.
(And then it must have been another programming language and not SQL.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 22 '07 #17

P: n/a
ML
On Feb 21, 3:02 am, "Tonkuma" <tonk...@jp.ibm.comwrote:
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(NULLIF(MOD(intdata,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/256,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/65536,256),0)),x'00')
||COALESCE(CHR(NULLIF(intdata/16777216,0)),x'00')
) AS char4_x
, COALESCE(CHR(NULLIF(MOD(intdata,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/256,256),0)),x'00')
||COALESCE(CHR(NULLIF(MOD(intdata/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.

Unfortunately, CHR is not an avaible function for DB2v7 on zOS.

--
ML

Feb 22 '07 #18

P: n/a
How about this for z/OS?
SELECT
INTDATA
, SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
1,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
2,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
3,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
4,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
5,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
6,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
7,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
8,1)),1)
AS CHAR4
, HEX(
SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
1,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
2,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
3,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
4,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
5,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
6,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
7,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
8,1)),1)
) AS CHARHEX
, HEX(INTDATA) AS INTHEX
FROM (SELECT 2030906 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2147483647 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT -123456 FROM SYSIBM.SYSDUMMY1
) AS TESTDATA(INTDATA)
, (SELECT CAST(ROWID(X'000102030405060708090A0B0C0D0E0F') AS
CHAR(16))||
CAST(ROWID(X'101112131415161718191A1B1C1D1E1F') AS
CHAR(16))||
CAST(ROWID(X'202122232425262728292A2B2C2D2E2F') AS
CHAR(16))||
CAST(ROWID(X'303132333435363738393A3B3C3D3E3F') AS
CHAR(16))||
CAST(ROWID(X'404142434445464748494A4B4C4D4E4F') AS
CHAR(16))||
CAST(ROWID(X'505152535455565758595A5B5C5D5E5F') AS
CHAR(16))||
CAST(ROWID(X'606162636465666768696A6B6C6D6E6F') AS
CHAR(16))||
CAST(ROWID(X'707172737475767778797A7B7C7D7E7F') AS
CHAR(16))||
CAST(ROWID(X'808182838485868788898A8B8C8D8E8F') AS
CHAR(16))||
CAST(ROWID(X'909192939495969798999A9B9C9D9E9F') AS
CHAR(16))||
CAST(ROWID(X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF') AS
CHAR(16))||
CAST(ROWID(X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF') AS
CHAR(16))||
CAST(ROWID(X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF') AS
CHAR(16))||
CAST(ROWID(X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF') AS
CHAR(16))||
CAST(ROWID(X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF') AS
CHAR(16))||
CAST(ROWID(X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF') AS
CHAR(16))
FROM SYSIBM.SYSDUMMY1) AS R(HEX_LIST);

The result is
INTDATA CHAR4 CHARHEX INTHEX
----------- ----- -------- --------
2030906 D Q 001EFD3A 001EFD3A
2147483647 " 7FFFFFFF 7FFFFFFF
-123456 { FFFE1DC0 FFFE1DC0

Feb 23 '07 #19

P: n/a
ML
On Feb 23, 3:36 am, "Tonkuma" <tonk...@jp.ibm.comwrote:
How about this for z/OS?
SELECT
INTDATA
, SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
1,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
2,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
3,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
4,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
5,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
6,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
7,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
8,1)),1)
AS CHAR4
, HEX(
SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
1,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
2,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
3,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
4,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
5,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
6,1)),1)
||SUBSTR(HEX_LIST, POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),
7,1))*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),
8,1)),1)
) AS CHARHEX
, HEX(INTDATA) AS INTHEX
FROM (SELECT 2030906 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2147483647 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT -123456 FROM SYSIBM.SYSDUMMY1
) AS TESTDATA(INTDATA)
, (SELECT CAST(ROWID(X'000102030405060708090A0B0C0D0E0F') AS
CHAR(16))||
CAST(ROWID(X'101112131415161718191A1B1C1D1E1F') AS
CHAR(16))||
CAST(ROWID(X'202122232425262728292A2B2C2D2E2F') AS
CHAR(16))||
CAST(ROWID(X'303132333435363738393A3B3C3D3E3F') AS
CHAR(16))||
CAST(ROWID(X'404142434445464748494A4B4C4D4E4F') AS
CHAR(16))||
CAST(ROWID(X'505152535455565758595A5B5C5D5E5F') AS
CHAR(16))||
CAST(ROWID(X'606162636465666768696A6B6C6D6E6F') AS
CHAR(16))||
CAST(ROWID(X'707172737475767778797A7B7C7D7E7F') AS
CHAR(16))||
CAST(ROWID(X'808182838485868788898A8B8C8D8E8F') AS
CHAR(16))||
CAST(ROWID(X'909192939495969798999A9B9C9D9E9F') AS
CHAR(16))||
CAST(ROWID(X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF') AS
CHAR(16))||
CAST(ROWID(X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF') AS
CHAR(16))||
CAST(ROWID(X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF') AS
CHAR(16))||
CAST(ROWID(X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF') AS
CHAR(16))||
CAST(ROWID(X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF') AS
CHAR(16))||
CAST(ROWID(X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF') AS
CHAR(16))
FROM SYSIBM.SYSDUMMY1) AS R(HEX_LIST);

The result is
INTDATA CHAR4 CHARHEX INTHEX
----------- ----- -------- --------
2030906 D Q 001EFD3A 001EFD3A
2147483647 " 7FFFFFFF 7FFFFFFF
-123456 { FFFE1DC0 FFFE1DC0

Had to get rid of the ROWID function, then it performed brilliantly!
Should win an award!

--
ML

Feb 23 '07 #20

P: n/a
I was foolish. It is not necessary to use ROWID to make HEX_LIST.
It is enough to concatenate hexadecimal constants.

Here is updated example.
SELECT
INTDATA
, SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),1,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),2,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),3,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),4,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),5,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),6,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),7,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),8,1 )),1)
AS CHAR4
, HEX(
SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),1,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),2,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),3,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),4,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),5,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),6,1 )),1)
||SUBSTR(HEX_LIST
,POSSTR('123456789ABCDEF',SUBSTR(HEX(INTDATA),7,1) )*16
+POSSTR('0123456789ABCDEF',SUBSTR(HEX(INTDATA),8,1 )),1)
) AS CHARHEX
, HEX(INTDATA) AS INTHEX
FROM (SELECT 2030906 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2147483647 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT -123456 FROM SYSIBM.SYSDUMMY1
) AS TESTDATA(INTDATA)
, (SELECT X'000102030405060708090A0B0C0D0E0F'||
X'101112131415161718191A1B1C1D1E1F'||
X'202122232425262728292A2B2C2D2E2F'||
X'303132333435363738393A3B3C3D3E3F'||
X'404142434445464748494A4B4C4D4E4F'||
X'505152535455565758595A5B5C5D5E5F'||
X'606162636465666768696A6B6C6D6E6F'||
X'707172737475767778797A7B7C7D7E7F'||
X'808182838485868788898A8B8C8D8E8F'||
X'909192939495969798999A9B9C9D9E9F'||
X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'||
X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'||
X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'||
X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'
FROM SYSIBM.SYSDUMMY1) AS REFERENCE(HEX_LIST);

Feb 23 '07 #21

This discussion thread is closed

Replies have been disabled for this discussion.