Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: Last_day() function not working in db2 5.6 version

    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.

  2. #2
    Join Date
    Nov 2009
    Location
    Bangalore
    Posts
    25
    You can refer to the example from this blog

    LAST_DAY, FIRST_DAY in DB2 | db2ude

    Regards,
    Sawan Gupta

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 11-16-09 at 00:58.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by tonkuma View Post
    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

  7. #7
    Join Date
    Nov 2009
    Posts
    4
    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.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by sekhar.gajjala View Post
    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

  9. #9
    Join Date
    Nov 2009
    Posts
    4
    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.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sekhar.gajjala View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sekhar.gajjala View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •