OLAP-Functions missing in DB2 Express-C?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mecej4@gmail.com - 17 Jun 2006 03:39 GMT

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

Reply to this Message

Serge Rielau - 17 Jun 2006 13:25 GMT

> 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.SYSDUMMY1
Should return: 1

There are 0 limitations to SQL in DB2 Express C

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

mecej4@gmail.com - 18 Jun 2006 00:08 GMT

> > DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
> > the OLAP functions available in other editions, such as ROW_NUMBER,
[quoted text clipped - 17 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Thanks for your reply. The message I receive is

------------------------------------------------------------------------------------------------------------
s:\>db2 SELECT ROW_NUMBER() OVER() FROM SYSIBM.SYSDUMMY1
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\IBM\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.

Reply to this Message

Serge Rielau - 18 Jun 2006 00:18 GMT

>>> DB2 Express C (Fixpack 10, WR21362_15462) seems to be lacking some of
>>> the OLAP functions available in other editions, such as ROW_NUMBER,
[quoted text clipped - 42 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

Knut Stolze - 19 Jun 2006 09:06 GMT

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message