HOW-TO: recreate a big set of interdependent UDFs...

[Gustavo Randich]

Hello,
(Using DB2/LINUX 8.2.0)

HOW-TO: recreate a big set of interdependent UDFs... without having to
do it manually because of the imposed drop order due to the RESTRICT
keyword of the DROP statement.

There are also triggers and views mixed in the spaghetti.

Background: I've a huge set of .sql files which acts as the master
repository of DB objects source code to be re-compiled (DROP & CREATE)
automatically every night in a development database.

Interesting note: Informix, Oracle and SQL Server all permit "drop and
mark dependent objects as invalid".

[Serge Rielau]

Gustavo Randich wrote:

Hello,
(Using DB2/LINUX 8.2.0)

HOW-TO: recreate a big set of interdependent UDFs... without having to
do it manually because of the imposed drop order due to the RESTRICT
keyword of the DROP statement.

There are also triggers and views mixed in the spaghetti.

Background: I've a huge set of .sql files which acts as the master
repository of DB objects source code to be re-compiled (DROP & CREATE)
automatically every night in a development database. Take a peek at the SYSPROC.DB2LK_DEP_OF() table function.
Interesting note: Informix, Oracle and SQL Server all permit "drop and
mark dependent objects as invalid".

Understood.

Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Gustavo Randich]

> Take a peek at the SYSPROC.DB2LK_DEP_OF() table function.

Thanks, Serge, but what's the meaning of DB2LK_DEP_OF's first
parameter?

[Serge Rielau]

Gustavo Randich wrote:

Take a peek at the SYSPROC.DB2LK_DEP_OF() table function.

Thanks, Serge, but what's the meaning of DB2LK_DEP_OF's first
parameter?

Schema and name are not unique for DB2 objects. You could have a table
with the same name.
Check out the BTYPE or DTYPE descriptions fro the various SYSCAT.*DEP*
views.
Here are some (from syscat.routinedep). You want to use 'F', to find all
objects that depend on a given table use 'T':
The beast is an SQL Function. So you can see how it's works by looking
at the SYSCAT.ROUTINES.TEXT column.

A = Alias
F = Routine instance
O = Privilege dependency on all subtables or subviews in a table or view
hierarchy
R = Structured type
S = Materialized query table
T = Table
U = Typed table
V = View
W = Typed view
X = Index extension

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Gustavo Randich]

Sorry, I can't make work db2lk_dep_of.
Below is an example; Am I doing something wrong?
--
select a.routineschema, b.routinename, c.routinename
from syscat.routinedep a, syscat.routines b, syscat.routines c
where a.btype in ('F', 'P')
and b.routinename = 'GEN_PREMDACNV'
and b.specificname = a.routinename
and c.specificname = a.bname

ARBALLON GEN_PREMDACNV GEN_DIV
ARBALLON GEN_PREMDACNV LOG_LEECODIND
ARBALLON GEN_PREMDACNV LOG_LEEIND
ARBALLON GEN_PREMDACNV SIS_LEECFG
--
select * from
table(sysproc.db2lk_dep_of('F','ARBALLON','GEN_PRE MDACNV')) as a

(Empty result set fetched)
--

[Serge Rielau]

Gustavo Randich wrote:

Sorry, I can't make work db2lk_dep_of.
Below is an example; Am I doing something wrong?
--
select a.routineschema, b.routinename, c.routinename
from syscat.routinedep a, syscat.routines b, syscat.routines c
where a.btype in ('F', 'P')
and b.routinename = 'GEN_PREMDACNV'
and b.specificname = a.routinename
and c.specificname = a.bname

ARBALLON GEN_PREMDACNV GEN_DIV
ARBALLON GEN_PREMDACNV LOG_LEECODIND
ARBALLON GEN_PREMDACNV LOG_LEEIND
ARBALLON GEN_PREMDACNV SIS_LEECFG
--
select * from
table(sysproc.db2lk_dep_of('F','ARBALLON','GEN_PRE MDACNV')) as a

(Empty result set fetched)
--

Arghh.. sorry I keep forgetting. Use the SPECIFICNAME as input for
routines (overloading of function names).
A routine is uniquely identifies by its schema and specific name.
So type: 'F', schemaname: 'ARBALLON', name: (SQL????)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab