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.
