V7 for z/OS and UDF, Part2

Ruediger Kurtz

V7 for z/OS and UDF, Part2

Hi all,

I need your help; this is our first trip into UDFs and I guess I'm missing the obvious here, but what are we doing wrong:

Step 1:

CREATE FUNCTION T7.INVTSTF
(TIMESTAMP)
RETURNS CHAR(10)
SPECIFIC INVTSTF
EXTERNAL NAME 'INVTSTF'
LANGUAGE PLI
PARAMETER STYLE DB2SQL
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
WLM ENVIRONMENT DBTSWLM2 ;
GRANT EXECUTE ON FUNCTION T7.INVTSTF TO PUBLIC ;

(Ends with SQLCODE=0)

Step 2:

select *
from sysibm.sysroutines
where schema = 'T7'
---------+---------+---------+---------+---------+---------+---------+-------
SCHEMA OWNER NAME ROUTINETYPE CREATEDBY SPECIFICNAME
---------+---------+---------+---------+---------+---------+---------+-------
T7 T7 INVTSTF F T7 INVTSTF

Step 3:

select *
from sysibm.sysroutineauth
where schema = 'T7'
---------+---------+---------+---------+--
GRANTOR GRANTEE SCHEMA SPECIFICNAME
---------+---------+---------+---------+--
T7 PUBLIC T7 INVTSTF

Step 4:

SELECT CURRENT_PATH FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---

---------+---------+---------+---------+---
"SYSIBM","SYSFUN","SYSPROC","T7"

Step 5:

SELECT *
FROM T7.TAGEVO
WHERE GEVO_INV = INVTSTF('2006-01-05-08.17.55.293557')
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME INVTSTF HAVING
COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

What's going on here ?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: V7 for z/OS and UDF, Part2
(in response to Ruediger Kurtz)
Hi Ruediger,

Use T7.NVTSTF in the SELECT.
You use external UDF that costs much more then SQL UDF at run time ( I measured it many times as 1:10 ratio).
Try the SQL UDF I've posted before (tailor it to your columns)


Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Kurtz, R?diger
Sent: Friday, January 13, 2006 12:55 PM
To: [login to unmask email]
Subject: [DB2-L] V7 for z/OS and UDF, Part2


Hi all,

I need your help; this is our first trip into UDFs and I guess I'm missing the obvious here, but what are we doing wrong:

Step 1:

CREATE FUNCTION T7.INVTSTF
(TIMESTAMP)
RETURNS CHAR(10)
SPECIFIC INVTSTF
EXTERNAL NAME 'INVTSTF'
LANGUAGE PLI
PARAMETER STYLE DB2SQL
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
WLM ENVIRONMENT DBTSWLM2 ;
GRANT EXECUTE ON FUNCTION T7.INVTSTF TO PUBLIC ;

(Ends with SQLCODE=0)

Step 2:

select *
from sysibm.sysroutines
where schema = 'T7'
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
SCHEMA OWNER NAME ROUTINETYPE CREATEDBY SPECIFICNAME
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
T7 T7 INVTSTF F T7 INVTSTF

Step 3:

select *
from sysibm.sysroutineauth
where schema = 'T7'
---------+---------+---------+---------+--
GRANTOR GRANTEE SCHEMA SPECIFICNAME
---------+---------+---------+---------+--
T7 PUBLIC T7 INVTSTF

Step 4:

SELECT CURRENT_PATH FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---

---------+---------+---------+---------+---
"SYSIBM","SYSFUN","SYSPROC","T7"

Step 5:

SELECT *
FROM T7.TAGEVO
WHERE GEVO_INV = INVTSTF('2006-01-05-08.17.55.293557')
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME INVTSTF HAVING
COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

What's going on here ?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: V7 for z/OS and UDF, Part2
(in response to Isaac Yassin)
Hi Rüdiger

Did you try:

SELECT *
FROM T7.TAGEVO
WHERE GEVO_INV = INVTSTF(timestamp(('2006-01-05-
08.17.55.293557'))

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm