SQL Query Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sparky - 24 Nov 2004 22:10 GMT

Why do the two hex columns display differently between AIX and Linux
but yet both are DB2 and relatively close in fixpacks?

On Linux, DB2 Level -- DB21085I Instance "mdkint01" uses "32" bits
and DB2 code release "SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.72", "special_12467",
"MI00086_12467", and FixPak "7". Product is installed at
"/opt/IBM/db2/V8.1".

select tbspaceid,char(tbspace,20),hex(tbspaceid) from
syscat.tablespaces
order by 1

TBSPACEID 2 3
----------- -------------------- --------
0 SYSCATSPACE 00000000
1 TEMPSPACE1 01000000
2 USERSPACE1 02000000
3 TDYTS001 03000000
4 TDYTS002 04000000
7 TDYTS005 07000000
8 TDYTS006 08000000

7 record(s) selected.

On AIX, DB2 Level -- DB21085I Instance "dasinp02" uses "32" bits and
DB2 code release "SQL08015" with level identifier "02060106".
Informational tokens are "DB2 v8.1.1.48", "s040212", "U496793", and
FixPak "5". Product is installed at "/usr/opt/db2_08_01".

select tbspaceid,char(tbspace,20),hex(tbspaceid) from
syscat.tablespaces
order by 1

TBSPACEID 2 3
----------- -------------------- --------
0 SYSCATSPACE 00000000
1 TEMPSPACE1 00000001
2 MDRSGIDX 00000002
3 MDRTSSYSTEMTEMP 00000003
4 MDRTSAGS 00000004
5 MDRTSAGUD 00000005
6 MDRTSAPI 00000006

7 record(s) selected.

Reply to this Message

Jan M. Nelken - 24 Nov 2004 23:41 GMT

> Why do the two hex columns display differently between AIX and Linux
> but yet both are DB2 and relatively close in fixpacks?
[quoted text clipped - 41 lines]
>
> 7 record(s) selected.

When you are asking for hex value of any column - you are asking for internal
representation of that column - which is platform dependent.
Your question makes as much sense as asking why this query produces different
results on AIX and z/OS:

db2 "select hex('TEST') as HEXCOLUMN from sysibm.sysdummy1"

On AIX:

HEXCOLUMN
---------
54455354

On z/OS:

HEXCOLUMN
---------
E3C5E2E3

For your original "problem" - integer representation on AIX and Intel - differs.
Lookup "little endian" and "big endian" in google - for clarification.

Jan M. Nelken

Reply to this Message

Mark A - 25 Nov 2004 01:14 GMT

> Why do the two hex columns display differently between AIX and Linux
> but yet both are DB2 and relatively close in fixpacks?
[quoted text clipped - 41 lines]
>
> 7 record(s) selected.

Try using the CAST function.

Reply to this Message

Knut Stolze - 25 Nov 2004 10:06 GMT

> Why do the two hex columns display differently between AIX and Linux
> but yet both are DB2 and relatively close in fixpacks?
[quoted text clipped - 41 lines]
>
> 7 record(s) selected.

In this particular case, you see the effects of different endianess on AIX
(big endian) and Linux on Intel (little endian).

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Bob Stearns - 29 Nov 2004 19:10 GMT

> Why do the two hex columns display differently between AIX and Linux
> but yet both are DB2 and relatively close in fixpacks?
[quoted text clipped - 41 lines]
>
> 7 record(s) selected.

Little endian vs big endian. Both are performing the same logic: convert
1 byte of the value (from lowest address in memory to highest) at a time
to ASCII and concatenate the results.

Reply to this Message