1. If one issues GRANT EXECUTE on procedure schema.* (or even the exact procedure name), should one need to issue
GRANT EXECUTE on schema.package (package associated with the exact procedure name)?
2. In the past, at a client, they've done both steps. The way the associated package name was retrieved was taking the 1st 8 characters
of the IMPLEMENTATION column from syscat.procedures and placing that value in the GRANT statement for the package.
However, I just noticed that with V8.2 (we're at Stinger signature 18.104.22.168) there's a db2pvm!... value in the IMPLEMENTATION
column and not the usual Pnnnnnnn beginning. I even tried in the stored procedure specifying NO EXTERNAL ACTION (which isn't
the default) but the same db2pvm! showed up in syscat.procedures.implementation column.
Bottom line: Assuming that one needs to correlate a procedure with its package, how can one accomplish this?
P.S. Here's an UGLY possible solution:
select char(procname,30) procname, char(pkgname,30) pkgname, create_time from syscat.procedures a, syscat.packages b where procschema='schemaname' and procname='procname' and a.procschema=b.pkgschema and timestampdiff(4,char(timestamp(b.pkg_create_time) - timestamp(a.create_time))) < 1 and pkg_create_time in (select max(pkg_create_time) from syscat.packages);
Last edited by rubystep; 10-19-04 at 12:21.
I think that would be the right way to figure out which package a procedure belongs to:
select bschema as pkgschema, bname as pkgname
from syscat.routines r, sysibm.sysdependencies d
where r.routinename=? and r.routineschema=?
and r.specificname=d.dname and r.routineschema=d.dschema
and d.btype='K' and d.dtype='F';