Help Processing Integer Byte by Byte

		Home \| Contact Us \| FAQ \| Search & Site Map \| Link to Us
		Sign In \| Join \| Other 45 Sites in Network

Discussion Groups

Database Servers

DB2 Informix Ingres MS SQL Oracle Pervasive.SQL PostgreSQL Progress Sybase

Desktop Databases

FileMaker FoxPro MS Access Paradox

General

General DB Topics Database Theory

Related Topics

Java Development .NET Development VB Development More Topics ...

Database Forum / DB2 Topics / February 2007

Help Processing Integer Byte by Byte

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ML - 20 Feb 2007 13:42 GMT

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

Reply to this Message

Serge Rielau - 20 Feb 2007 14:24 GMT

> 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
[quoted text clipped - 3 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

ML - 20 Feb 2007 14:50 GMT

> > 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
[quoted text clipped - 14 lines]
> 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

Reply to this Message

Brian Tkatch - 20 Feb 2007 15:37 GMT

>> > 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
[quoted text clipped - 18 lines]
>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
[quoted text clipped - 5 lines]
>Thanks!
>ML

Reply to this Message

Serge Rielau - 20 Feb 2007 15:39 GMT

>>> 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
[quoted text clipped - 20 lines]
> 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 :-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

ML - 20 Feb 2007 16:28 GMT

> >>> 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
[quoted text clipped - 28 lines]
> 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

Reply to this Message

ML - 20 Feb 2007 18:50 GMT

> > >>> 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
[quoted text clipped - 42 lines]
> --
> 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

Reply to this Message

ML - 20 Feb 2007 19:41 GMT

> > > >>> 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
[quoted text clipped - 68 lines]
> 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

Reply to this Message

Knut Stolze - 20 Feb 2007 20:13 GMT

>> > > >>> 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
[quoted text clipped - 81 lines]
>> FROM MY.TABLE
>> WHERE SOME_IND = 'Y'

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

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Knut Stolze - 20 Feb 2007 20:14 GMT

> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Bob Stearns - 21 Feb 2007 06:10 GMT

>>>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
[quoted text clipped - 27 lines]
> 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.)

Reply to this Message

Tonkuma - 21 Feb 2007 10:18 GMT

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').

Reply to this Message

Knut Stolze - 21 Feb 2007 10:20 GMT

> 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)

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Tonkuma - 22 Feb 2007 03:01 GMT

> > 2) substr might need 3rd parameter as 1.
> > Like: substr(hex(shortint),1,1)
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

Knut Stolze - 22 Feb 2007 09:51 GMT

>> > 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.)

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Tonkuma - 22 Feb 2007 03:18 GMT

> There are at least following issues for your sample.
> 1) If DB2 is DB2 for LUW, expression can not be used for 2nd parameter
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

Tonkuma - 21 Feb 2007 09:02 GMT

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.

Reply to this Message

ML - 22 Feb 2007 17:23 GMT

> You should be careful for internal data format on your platform.
> Following example was tested on Windows on PC.
[quoted text clipped - 28 lines]
>
> 4 record(s) selected.

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

--
ML

Reply to this Message

Tonkuma - 23 Feb 2007 09:36 GMT

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

Reply to this Message

ML - 23 Feb 2007 13:55 GMT

> How about this for z/OS?
> SELECT
[quoted text clipped - 81 lines]
> 2147483647 " 7FFFFFFF 7FFFFFFF
> -123456 { FFFE1DC0 FFFE1DC0

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

--
ML

Reply to this Message

Tonkuma - 23 Feb 2007 14:06 GMT

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);

Reply to this Message

Sign In

Join

My Latest Posts

My Monitored Threads

My Photo Gallery

My Homepage

Start New Thread

Enable EMail Alerts

Rate this Thread

©2009 Advenet LLC Privacy Policy - Terms of Use

This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.