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

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