Stored Procedure dependencies: the missing links

There are easily accessible means of checking what your Stored Procedure needs in the way of dependent objects (SYSCAT.ROUTINEDEP, basically). So, what if you find a, or a number of, Stored Procs that are marked as needing a REBIND and then, when you do that rebind, you get an SQL0440 indicating that “something” is missing. How do you go about checking that situation out?

 

Overview

Let me just illustrate the issue. I’m going to use “sanitized” SQL from our own R&D environment, but this is an issue that we have on a number of customer sites.

First off, I am creating a Stored Proc comme ca

 

 

You can see it calls another Proc (LOW_FLIGHT_HOURS) and does a SELECT on a view called LOGBOOK_DETAILS. By interrogating the SYSCAT.ROUTINES, SYSCAT.ROUTINEDEP and SYSCAT.PACKAGES you can see what dependencies exist.

 

 

This is nice and clear, I think, and pretty comprehensive. It shows, going row by row:

1. a function that calls the LOW_FLIGHT_YEARS Proc (which is the parent or calling Proc)
2. a reference to a table called AIRCRAFT
3. another to a table called AIRFIELD
4. and a last one to a table called LOGBOOK
5. a view called LOGBOOK_DETAILS, which is based on those preceding 3 tables
6. it shows the call to the subsidiary Proc called LOW_FLIGHT_HOURS
7. it shows the package which is associated with this routine and, finally
8. a function called MIDNIGHT_SECONDS which is used in the view mentioned above

This is all very good, but the situation I am trying to deal with is where one of these dependencies no longer exists.

By way of illustration, I am going to DROP the subsidiary Proc; LOW_FLIGHT_HOURS, and then rerun the Dependency query.

 

 

As you can see; only 7 rows in the dependency result set now. LOW_FLIGHT_HOURS has gone. As you’d expect: DB2 has cleaned up its system catalogues after the DROP command. And the Procedure is now showing as needing a rebind.

But, if you attempt a REBIND, it will (of course) fail:

 

 

Problems of Scale

Dealing with a single Proc and its various dependencies; this isn’t too much of a problem. The issue I have is that my customer has literally thousands of Stored Procs and, on any given night, our housekeeping routines will find hundreds that need a REBIND.

In order to identify these, and to either ignore them or, better still, invoke a clean-up routine to remove them, we need something that can find a dependency for the SP that is no longer there.

What I have done is to concentrate on a single scenario: where an SP makes a call to a subsidiary Proc that no longer exists. The SQL that I have written examines the text of the routine (from SYSCAT.ROUTINES) and

1. finds the first non-comment example of the keyword CALL
2. determines what the Stored Procedure is within that call
3. loops back to SYSCAT.ROUTINES to see if that subsidiary Proc exists

The current result set from this code is

 

 

The null values in the 3^rd and 4^th columns indicating that nothing matching the Called Proc was found. If I reinstate that subsidiary Proc and REBIND the parent Proc

 

 

I can re-execute my ‘Missing Dependencies’ code and the Called Proc is now found:

 

 

This is not an entirely polished bit of code, although it should be smart enough to locate either a qualified or a non-qualified call and will stop if the SP does not include any subsidiary calls at all.

I’ve managed to put this code into a Stored Proc of its own, and I can use a simple Function to do a simple

SELECT * FROM TABLE ( procname )

against that stored procedure so that I only retrieve a list of Procs which do have subsidiary calls and/or don’t have any existing code on the end of that call.

 

Conclusion

This may be a problem you just don’t need to solve, or it may be that your missing dependencies are more widespread than the ones I’ve referred to. But if you want to make a start on cleaning-up some of those nuisance failed REBINDs and redundant Stored Procs, this might be a starting point.

Please add a comment below or email me if you’d like me to share any of the code referred to in this blog although, please bear in mind, it’s supplied on a purely “as-is”, unsupported basis and is not fully-tested or guaranteed bullet-proof.