OLAP-Functions missing in DB2 Express-C?

[mecej4]

DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
the OLAP functions available in other editions, such as ROW_NUMBER,
RANK and DENSE_RANK, but does provide sum(..) over (partition by ...
range between ..). Is there a documented list of functions that are
available in this version of DB2? Thanks.

N. Shamsundar
University of Houston

[Serge Rielau]

me****@gmail.co m wrote:

DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
the OLAP functions available in other editions, such as ROW_NUMBER,
RANK and DENSE_RANK, but does provide sum(..) over (partition by ...
range between ..). Is there a documented list of functions that are
available in this version of DB2? Thanks.

Impossible. How do you get the idea?
Try:
SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY 1
Should return: 1

There are 0 limitations to SQL in DB2 Express C

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[mecej4]

Serge Rielau wrote:

me****@gmail.co m wrote:

DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
the OLAP functions available in other editions, such as ROW_NUMBER,
RANK and DENSE_RANK, but does provide sum(..) over (partition by ...
range between ..). Is there a documented list of functions that are
available in this version of DB2? Thanks.

Impossible. How do you get the idea?
Try:
SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY 1
Should return: 1

There are 0 limitations to SQL in DB2 Express C

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Thanks for your reply. The message I receive is

------------------------------------------------------------------------------------------------------------
s:\>db2 SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY 1
SQL0440N No authorized routine named "ROW_NUMBER " of type "FUNCTION"
having
compatible arguments was found. SQLSTATE=42884.

s:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08023"
with
level identifier "03040106".
Informational tokens are "DB2 v8.1.10.1155", "special_15462" ,
"WR21362_15462" ,
and FixPak "10".
Product is installed at "C:\PROGRA~1\IB M\SQLLIB".
------------------------------------------------------------------------------------------------------------

However, this was (sorry, I did not state this earlier, not realizing
that it was relevant) when Express-C was run on a DB (I tried two such,
the Sample DB and my own) that had been created with DB2 WSE, FP-11.

When the DB is recreated with Express-C and loaded with data, the
problem disappears.

[Serge Rielau]

me****@gmail.co m wrote:

Serge Rielau wrote:

me****@gmail.co m wrote:

DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
the OLAP functions available in other editions, such as ROW_NUMBER,
RANK and DENSE_RANK, but does provide sum(..) over (partition by ...
range between ..). Is there a documented list of functions that are
available in this version of DB2? Thanks.

Impossible. How do you get the idea?
Try:
SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY 1
Should return: 1

There are 0 limitations to SQL in DB2 Express C

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Thanks for your reply. The message I receive is

------------------------------------------------------------------------------------------------------------
s:\>db2 SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY 1
SQL0440N No authorized routine named "ROW_NUMBER " of type "FUNCTION"
having
compatible arguments was found. SQLSTATE=42884.

s:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08023"
with
level identifier "03040106".
Informational tokens are "DB2 v8.1.10.1155", "special_15462" ,
"WR21362_15462" ,
and FixPak "10".
Product is installed at "C:\PROGRA~1\IB M\SQLLIB".
------------------------------------------------------------------------------------------------------------

However, this was (sorry, I did not state this earlier, not realizing
that it was relevant) when Express-C was run on a DB (I tried two such,
the Sample DB and my own) that had been created with DB2 WSE, FP-11.

When the DB is recreated with Express-C and loaded with data, the
problem disappears.

I'm taking a wild guess here.. Could it be that the system timestamp was
played with.
I.e. if the system time is earlier than the creation of the database
function resolution gets all confused because it can't see function
created in the future.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

Serge Rielau wrote:

I'm taking a wild guess here.. Could it be that the system timestamp was
played with.
I.e. if the system time is earlier than the creation of the database
function resolution gets all confused because it can't see function
created in the future.

That's actually intended to avoid breakages in packages due to a different
function resolution upon re-bind, I think.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany