Last_day() function not working in db2 5.6 version

sekhar.gajjala · #1 (**permalink**) 11-15-09, 21:55

HI,

IN a select query when i used last_day() function it is showing an error that it is not an user defined function, but when i used the same function in mainframe udb it is working fine.

Please let me know whether ibm has not provided this function in other db2 forms as AIX etc...

Thanks
Somasekhar Gajjala.

sawangupta · #2 (**permalink**) 11-15-09, 23:18

You can refer to the example from this blog

LAST_DAY, FIRST_DAY in DB2 | db2ude

Regards,
Sawan Gupta

tonkuma · #3 (**permalink**) 11-15-09, 23:26

LAST_DAY supported on DB2 9.7 for LUW, DB2 for iSeries Version 5 Release 4 or later and DB2 for z/OS Version 7 or later.

But, DB2 5.6 for AIX is very old version.

Marcus_A · #4 (**permalink**) 11-15-09, 23:41

You could easily write your own function.

This is not a function, but will give you a hint on now to calculate it:

select date(substr(current_date, 1, 8)||'01') + 1 month - 1 day from sysibm.sysdummy1

Obviously, current_date in the above example would be replaced with the date for which you want to calculate the last day of the month.

tonkuma · #5 (**permalink**) 11-15-09, 23:54

Quote:

You can refer to the example from this blog

LAST_DAY, FIRST_DAY in DB2 | db2ude

FIRST_DAY in the blog:
FIRST_DAY(SYSDATE) ==> values (current date - day(current date + 1 month) days) + 1 DAY
looks wrong.

For example, replace current date with DATE('2009-01-31') result:

Code:

------------------------------ Commands Entered ------------------------------
values (DATE('2009-01-31') - day(DATE('2009-01-31') + 1 month) days) + 1 DAY;
------------------------------------------------------------------------------

1         
----------
2009-01-04

  1 record(s) selected.

Please try this:

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_
     , date_ - (DAY(date_) - 1) DAYS AS first_day
  FROM (VALUES current_date, DATE('2009-01-31') ) q(date_);
------------------------------------------------------------------------------

DATE_      FIRST_DAY 
---------- ----------
2009-11-16 2009-11-01
2009-01-31 2009-01-01

  2 record(s) selected.

sekhar.gajjala · #6 (**permalink**) 11-17-09, 03:57

Quote:

Originally Posted by tonkuma

LAST_DAY supported on DB2 9.7 for LUW, DB2 for iSeries Version 5 Release 4 or later and DB2 for z/OS Version 7 or later.

But, DB2 5.6 for AIX is very old version.

Sorry i mentioned version as 5.6 by mistake, actually i am using the db2 client 9.5.4

sekhar.gajjala · #7 (**permalink**) 11-17-09, 03:59

Thanks for your replies,
we can write our own user defined functions for this, but i wanted to know that is this function is not provided by IBM or is there any problem with my client

Thanks
Somasekhar Gajjala.

Lenny77 · #8 (**permalink**) 11-17-09, 10:10

Quote:

Originally Posted by sekhar.gajjala

Thanks for your replies,
we can write our own user defined functions for this, but i wanted to know that is this function is not provided by IBM or is there any problem with my client

Thanks
Somasekhar Gajjala.

What is yours query ?

Lenny

sekhar.gajjala · #9 (**permalink**) 11-17-09, 11:09

The following is the error which i am getting

AIX-DEV ==>db2 "select last_day(hist_eff_dt) from dsnt.dws_geo_regions fetch first row only"
SQL0440N No authorized routine named "LAST_DAY" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Thanks,
Somasekhar Gajjala.

Marcus_A · #10 (**permalink**) 11-17-09, 11:13

Quote:

Originally Posted by sekhar.gajjala

Sorry i mentioned version as 5.6 by mistake, actually i am using the db2 client 9.5.4

What version is your database server? The function does not exist in the client.

Marcus_A · #11 (**permalink**) 11-17-09, 11:16

Quote:

Originally Posted by sekhar.gajjala

The following is the error which i am getting

AIX-DEV ==>db2 "select last_day(hist_eff_dt) from dsnt.dws_geo_regions fetch first row only"
SQL0440N No authorized routine named "LAST_DAY" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Thanks,
Somasekhar Gajjala.

Make sure that your DBA updated the database after installing the last fixpack (the db update is done after the instance is updated). The database update typically adds SP and functions that are newly available in the latest releases. Please refer to fixpack install instructions for more info.

Internet Services

Web Development

Digital Marketing

Consumer Tech