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:
- Create the user-defined function with SQL command CREATE FUNCTION.
- Precompile the user-defined function program and bind the DBRM into a package.
- 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).
- 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