SQL Query Question

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.

re: SQL Query Question

Sparky wrote:[color=blue]
> 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.[/color]

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

re: SQL Query Question

"Sparky" <tyates@newsguy.com> wrote in message
news:5h1aq092sjjiderdhmvckn0iqdaikocdtp@4ax.com...[color=blue]
> 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.
>[/color]
Try using the CAST function.

re: SQL Query Question

Sparky wrote:
[color=blue]
> 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.[/color]

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

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

re: SQL Query Question

Sparky wrote:[color=blue]
> 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.[/color]
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.