By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Determinining which PRODCEDUREs need to be REBINDed after a TABLE DROP/CREATE

P: n/a
We're going to expand a COLUMN from a lookup TABLE, which is REFERENCEd
from three other TABLEs, from CHAR(2) to CHAR(5). It is the PK of the
lookup.

To do this, we plan to EXPORT the data, DROP the TABLE, (re-)CREATE the
TABLE with the COLUMN expanded, and LOAD the data. Also, re-add any
FORIEGN KEYs associated with the TABLE. A DB2Look, or a query on
SysCat.References can handle that.

How do we verify which stored PROCEDUREs will be affected by a DROP
(and re-CREATE) TABLE? And will they all need a REBIND?

B.

Oct 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Brian Tkatch wrote:
We're going to expand a COLUMN from a lookup TABLE, which is REFERENCEd
from three other TABLEs, from CHAR(2) to CHAR(5). It is the PK of the
lookup.

To do this, we plan to EXPORT the data, DROP the TABLE, (re-)CREATE the
TABLE with the COLUMN expanded, and LOAD the data. Also, re-add any
FORIEGN KEYs associated with the TABLE. A DB2Look, or a query on
SysCat.References can handle that.

How do we verify which stored PROCEDUREs will be affected by a DROP
(and re-CREATE) TABLE? And will they all need a REBIND?

B.
How's this?

-- Before.
SELECT
'REBIND ' ||
VARCHAR(RTRIM(PkgSchema), 18) ||
'.' ||
VARCHAR
(
(
SELECT
RoutineName
FROM
SysCat.Routines
WHERE
SUBSTR(Implementation, 1, 8) = PackageDep.PkgName
),
18
) ||
';'
FROM
SysCat.PackageDep PackageDep
WHERE
BType = 'T'
AND BSchema = 'EPL'
AND BName IN ('MAUTOTYPE', 'MLINE', 'MSTDHITS', 'MSTUDYPROCESS')
-- After.
SELECT
VARCHAR(Routines.SpecificName, 18),
VARCHAR('REBIND ' || Packages.PkgName || ';', 30)
FROM
Syscat.Routines Routines,
Syscat.RoutineDep RoutineDep,
Syscat.Packages Packages
WHERE
-- Get the routine we want.
Routines.RoutineSchema = 'EPL'
AND Routines.Origin = 'E'
-- Join RoutineDep to get the filename.
AND RoutineDep.RoutineSchema = Routines.RoutineSchema
AND RoutineDep.RoutineName = Routines.RoutineName
AND RoutineDep.BType = 'K'
-- Join Packages to check validity.
AND Packages.PkgSchema = RoutineDep.BSchema
AND Packages.PkgName = RoutineDep.BName
AND Packages.Valid = 'X'
B.

Oct 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.