LASTDAY user-defined function

This tip descibes using a DB2 LASTDAY user-defined function in an SQL statement.

This tip describes my user-defined function (UDF) that is provided with DB2. A user-defined function is very similar to a stored procedure or a host language subprogram or function. However, a user-defined function is often the better choice for an SQL application because you can invoke a user-defined function in an SQL statement. You can use a user-defined function in an SQL statement in the same way as built-in functions.

To prepare a UDF for execution, you must perform these steps:

  1. Create the user-defined function with SQL command CREATE FUNCTION.
  2. Precompile the user-defined function program and bind the DBRM into a package.
  3. Compile the user-defined function program with a compiler that supports Language Environment and link-edit the appropriate Language Environment components with the user-defined function. You must also link-edit the user-defined function with RRSAF. The language interface module for RRSAF, DSNRLI, is shipped with the linkage attributes AMODE(31) and RMODE(ANY).
  4. GRANT EXECUTE authority on user-defined function or package.

A few more details:

  • The schema is SYSADM.
  • The LASTDAY function returns the last day of the month in smallint (small integer) format.
  • The expression must be a date.

An example:

SELECT LASTDAY(CURRENT DATE)       AS LDAY1,   
       LASTDAY(DATE('1987-03-17')) AS LDAY2    
FROM SYSIBM.SYSDUMMY1

The result:

 LDAY1   LDAY2   
------  ------   
30	31

The current date for this example was '2000-11-27'.

Create statement:

o	 LASTDAY

CREATE FUNCTION SYSADM.LASTDAY
       ( DATE )
RETURNS  SMALLINT
SPECIFIC LASTDAY           
EXTERNAL NAME 'LASTDAY'		-- MVS load module
LANGUAGE PLI     
PARAMETER STYLE DB2SQL
DETERMINISTIC
READS SQL DATA
DBINFO
FENCED
COLLID LASTDAY           
WLM ENVIRONMENT DSNNWLM1	-- WLM application 
STAY RESIDENT YES
PROGRAM TYPE MAIN
NO EXTERNAL ACTION
RETURNS NULL ON NULL INPUT
NO SCRATCHPAD
NO FINAL CALL
DISALLOW PARALLEL
ASUTIME NO LIMIT
SECURITY DB2

Load module:

LASTDAY - PL/I source code
* PROCESS SYSTEM(MVS);
 LASTDAY: PROC(UDF_PARM1, UDF_RESULT,                                  
               UDF_IND1,  UDF_INDR,                                    
               UDF_SQLSTATE, UDF_NAME, UDF_SPEC_NAME,                  
               UDF_DIAG_MSG, UDF_SCRATCHPAD,                           
               UDF_CALL_TYPE, UDF_DBINFO)                              
          OPTIONS(MAIN NOEXECOPS REENTRANT);                           
 /********************************************************************/
 /*    UDF   : LASTDAY                                               */
 /*    INPUT : UDF_PARM1    CHAR(10)                                 */
 /*    OUTPUT: UDF_RESULT   SMALLINT                                 */
 /********************************************************************/
  DCL UDF_PARM1      CHAR(10);          /* INPUT PARAMETER           */
  DCL UDF_RESULT     BIN FIXED(15);     /* RESULT PARAMETER          */
  DCL UDF_IND1       BIN FIXED(15);     /* INDIKATOR FOR INPUT PARM  */
  DCL UDF_INDR       BIN FIXED(15);     /* INDIKATOR FOR RESULT      */
  DCL 1 UDF_SCRATCHPAD,                 /* SCRATCHPAD                */
       3 UDF_SPAD_LEN    BIN FIXED(31),                                
       3 UDF_SPAD_TEXT   CHAR(100);                                    
                                                                      
  DCL 1 UDF_DBINFO,                         /* DBINFO               */
      3 UDF_DBINFO_LLEN BIN FIXED(15),     /* LOCATION LENGTH       */
      3 UDF_DBINFO_LOC  CHAR(128),         /* LOCATION NAME         */
      3 UDF_DBINFO_ALEN BIN FIXED(15),     /* AUTH ID LENGTH        */
      3 UDF_DBINFO_AUTH CHAR(128),         /* AUTHORIZATION ID      */
      3 UDF_DBINFO_CCSID CHAR(48),         /* CCSIDS FOR DB2 OS/390 */
       5 UDF_DBINFO_ESBCS BIN FIXED(31),   /* EBCDIC SBCS CCSID     */
       5 UDF_DBINFO_EMIXED BIN FIXED(31),  /* EBCDIC MIXED CCSID    */
       5 UDF_DBINFO_EDBCS BIN FIXED(31),   /* EBCDIC DBCS CCSID     */
       5 UDF_DBINFO_ASBCS BIN FIXED(31),   /* ASCII SBCS CCSID      */
       5 UDF_DBINFO_AMIXED BIN FIXED(31),  /* ASCII MIXED CCSID     */
       5 UDF_DBINFO_ADBCS BIN FIXED(31),   /* ASCII DBCS CCSID      */
       5 UDF_DBINFO_RESERV1 CHAR(20),      /* RESERVED              */
      3 UDF_DBINFO_QLEN BIN FIXED(15),     /* QUALIFIER LENGTH      */
      3 UDF_DBINFO_QUALIF CHAR(128),       /* QUALIFIER NAME        */
      3 UDF_DBINFO_TLEN BIN FIXED(15),     /* TABLE LENGTH          */
      3 UDF_DBINFO_TABLE  CHAR(128),       /* TABLE NAME            */
      3 UDF_DBINFO_CLEN BIN FIXED(15),     /* COLUMN LENGTH         */
      3 UDF_DBINFO_COLUMN CHAR(128),       /* COLUMN NAME           */
      3 UDF_DBINFO_RELVER CHAR(8),         /* DB2 RELEASE LEVEL     */
      3 UDF_DBINFO_PLATFORM BIN FIXED(31), /* DATABASE PLATFORM     */
      3 UDF_DBINFO_NUMTFCOL BIN FIXED(15), /* # OF TF COLS USED     */
      3 UDF_DBINFO_RESERV1 CHAR(24),       /* RESERVED              */
      3 UDF_DBINFO_TFCOLUMN PTR,           /* -> TABLE FUN COL LIST */
      3 UDF_DBINFO_RESERV2 CHAR(24);       /* RESERVED              */
  DCL (ADDR,LENGTH,SUBSTR,NULL)   BUILTIN;                            
  EXEC SQL INCLUDE SQLCA;                                             
                                                                      
  /* ********************************************************** */    
  /* RETURNS THE LAST DAY OF THE MONTH IN FORMAT 'DD'           */    
  /* LASTDAY(DATE('2000-08-18'))   ->   31                      */    
  /* ********************************************************** */    
                                                                      
  EXEC SQL SELECT DAY(DATE(STRIP(CHAR(YEAR(:UDF_PARM1)))!!'-'!!       
         SUBSTR(DIGITS(MONTH(:UDF_PARM1)),9,2)!!'-01')                
         + 1 MONTH - 1 DAY)                                           
       INTO :UDF_RESULT                                               
  FROM SYSIBM.SYSDUMMY1 WITH UR;                                      
                                                                      
 END LASTDAY;

For More Information

  • What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
  • The best DB2 web links: tips, tutorials, and much more.
  • Have another DB2 tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Do you have any technical questions about DB2 administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature! Our DB2 gurus are waiting to answer your toughest DB2 questions.

This was first published in March 2001

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close