Capture current date in db2 using iseries driver

building semantic layer
1

Hi Everybody,

We are trying to create formula in the universe .
Requirement is to capture current date in the select statement .

The following query works fine when we try the query from WinSQL:
(select CURRENT DATE from SYSIBM.SYSDUMMY1)

Actually the requirement is much more than but the to start with we are trying to capture the current date in db2. So we tried creating a derived table with the following option to get the current date.

select DW_PLAYER_ID, (select CURRENT DATE from SYSIBM.SYSDUMMY1) sysdt from D_PLAYER

But we are getting the following error:
Exception: DBD, [IBM] [iSeries Access ODBC Driver]Communication link Failure.comm
rc=10054 - CWBC01047 - The iSeries server application disconnected the connectionState:08501

However the derived table with sysdate option works fine in Oracle.(The
same logic works fine with Oracle with sysdate and dual table)

So we will have to figure out a way to capture the current date in db.

The build-in function ‘CURDATE()’ for db2 doesn’t work in the Select clause of the object.

We are using BO XIR2 and db2 database with iSeries4 driver.

Regards,

anshubit@gmail.com :india: (BOB member since 2007-07-03)

2

Have you tried NOW(), it is listed under DB2 UDB for iSeries V5.
MAy work for v4 too

.

haider :es: (BOB member since 2005-07-18)

3

Hi Haider,
Thanks for you reply. Sorry about coming back late on this one.

Correct me if I am wrong normally we will use Cur date () & now() function directly in the select clause of the object.
In case you differ then please provide us with the correct syntax since the same is not available in the designer.pdf document or the help for designer.

anshubit@gmail.com :india: (BOB member since 2007-07-03)

4

You can always put it in the select box of object, associate a dimension table to it and try displaying from properties tab of object. I did that and it works

.

haider :es: (BOB member since 2005-07-18)