DB2 package, plan and routine catalog tables

Packages versus plans

A package is the bound form of a DBRM (or SQL PL routine) in a collection. A plan is the named executable a CICS RCT, TSO DSN, or IMS attach often specifies; today that plan usually lists packages rather than containing every SQL statement itself. The catalog mirrors that split: SYSPACKAGE / SYSPACKSTMT / SYSPACKDEP versus SYSPLAN / SYSSTMT. Directory spaces SPT01 and SCT02 hold the skeleton runtime copies; the catalog holds the descriptive rows you SELECT.

SYSIBM.SYSPACKAGE

One row per package copy. Identify a package with LOCATION (blank for local), COLLID (collection), NAME, CONTOKEN (consistency token from precompile), and VERSION when you use versioned packages. OWNER, QUALIFIER, BINDTIME, ISOLATION, RELEASE, EXPLAIN, and DEGREE record bind options.

IBM documents that an automatic rebind is triggered when an authorized user runs a package whose runtime structures cannot be used—often after ALTER on a dependent object. VALID = N is the usual invalid flag. If autobind fails, OPERATIVE becomes N and you must fix the SQL or the objects and BIND/REBIND explicitly. Recent function levels add statement-level invalidation (VALID = S) and rebind-advisory OPERATIVE = R for some phase-in cases—check your function level before you write a monitor that only understands Y/N.

1
2
3
4
5
SELECT COLLID, NAME, VERSION, VALID, OPERATIVE, BINDTIME, ISOLATION
  FROM SYSIBM.SYSPACKAGE
 WHERE COLLID = 'HRCOLL'
   AND VALID <> 'Y'
  WITH UR;

SYSIBM.SYSPACKSTMT

One or more rows per static SQL statement in a package. Join to SYSPACKAGE on LOCATION, COLLID, NAME, CONTOKEN, and VERSION. Useful columns:

• STMTNO / STMTNOI — statement number (integer form avoids smallint overflow)
• SECTNO / SECTNOI — section number; EXPLAIN tables use SECTNOI to rebuild a path
• QUERYNO — query number (from QUERYNO clause or derived)
• STMT / STATEMENT — SQL text (VARCHAR historically, CLOB STATEMENT in later catalogs)
• STMT_ID — persistent statement identifier used with EXPLAIN correlation (PER_STMT_ID)
• STATUS / ACCESSPATH — statement status including hint-related values such as those shops check after REOPT

1
2
3
4
5
6
SELECT S.STMTNOI, S.QUERYNO, S.SECTNOI,
       SUBSTR(S.STATEMENT, 1, 80) AS SQL_START
  FROM SYSIBM.SYSPACKSTMT S
 WHERE S.COLLID = 'HRCOLL'
   AND S.NAME = 'HREMP'
  WITH UR;

SYSIBM.SYSPLAN and SYSIBM.SYSSTMT

SYSPLAN describes plans: NAME, CREATOR, VALID, OPERATIVE, BINDTIME, ISOLATION, and related bind options. SYSSTMT holds SQL text for DBRMs bound into plans the old way (PLNAME, NAME as DBRM, STMTNO, TEXT). If your shop is package-only, SYSPLAN rows still exist for the attach plan, but SYSSTMT may be sparse. SYSPLANDEP is the plan-side dependency table (cousin of SYSPACKDEP).

1
2
3
4
SELECT NAME, CREATOR, VALID, OPERATIVE, BINDTIME
  FROM SYSIBM.SYSPLAN
 WHERE NAME = 'HRPLAN'
  WITH UR;

SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS

SYSROUTINES has one row per user-defined function or stored procedure. SCHEMA + NAME + SPECIFICNAME identify overloads. OWNER, LANGUAGE (SQL, COBOL, JAVA, C, …), ORIGIN (external versus native SQL), and PARM_COUNT describe implementation.

SYSPARMS has one row per parameter (and the function return value as a special row in IBM’s design). Join on SCHEMA, NAME, SPECIFICNAME (and ROUTINETYPE when present). ORDINAL, PARMNAME, TYPENAME, LENGTH, SCALE, and ROWTYPE-style columns tell you IN / OUT / INOUT. This is how you generate CALL stubs and audit Java signature drift.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT R.SCHEMA, R.NAME, R.SPECIFICNAME, R.ROUTINETYPE,
       R.LANGUAGE, R.ORIGIN, R.PARM_COUNT
  FROM SYSIBM.SYSROUTINES R
 WHERE R.SCHEMA = 'HR'
 ORDER BY R.ROUTINETYPE, R.NAME
  WITH UR;
 
SELECT PARMNAME, ORDINAL, TYPENAME, LENGTH, ROWTYPE
  FROM SYSIBM.SYSPARMS
 WHERE SCHEMA = 'HR'
   AND SPECIFICNAME = 'EMP_RAISE_SP'
 ORDER BY ORDINAL
  WITH UR;

SYSIBM.SYSSEQUENCES

One row per sequence object and, in IBM’s catalog design, also for identity-column sequences behind IDENTITY columns. SCHEMA and NAME identify the sequence. START, MINVALUE, MAXVALUE, INCREMENT, CYCLE, CACHE, and MAXASSIGNEDVAL (as documented for your release) describe numbering. SEQTYPE distinguishes an independent CREATE SEQUENCE from an identity-backed sequence. Sequence privileges live in SYSSEQUENCEAUTH (see the authorization page’s family, even though that table is not the focus there).

1
2
3
4
SELECT SCHEMA, NAME, INCREMENT, CYCLE, CACHE
  FROM SYSIBM.SYSSEQUENCES
 WHERE SCHEMA = 'HR'
  WITH UR;

SYSIBM.SYSTRIGGERS

One row per trigger. SCHEMA and NAME identify the trigger; TBOWNER and TBNAME identify the subject table. Typical attributes:

• TRIGTIME — B before, A after, I instead of
• TRIGEVENT — I insert, U update, D delete
• GRANULARITY — R for each row, S for each statement

Statement text may be in SYSTRIGGERS or a companion SYSTRIGGERS_STMT table depending on release. Finding triggers on a table is a required impact step before ALTER or DROP:

1
2
3
4
5
SELECT SCHEMA, NAME, TRIGTIME, TRIGEVENT, GRANULARITY
  FROM SYSIBM.SYSTRIGGERS
 WHERE TBOWNER = 'HR'
   AND TBNAME = 'EMPLOYEE'
  WITH UR;

SYSIBM.SYSGLOBALVARIABLES

CREATE VARIABLE objects are cataloged here: schema, name, data type, default, and owner. Global variables are not host variables and not columns. Application teams discover them the same way they discover sequences—by querying the catalog—because they will not appear in SYSTABLES.

SYSIBM.SYSPACKAUTH and SYSIBM.SYSPLANAUTH

These are privilege tables for programs, not objects. SYSPACKAUTH records who (GRANTEE) received EXECUTE and related package privileges from GRANTOR, with the usual Y / G / blank encoding (held, held WITH GRANT OPTION, not held). Qualify by COLLID and NAME (and CONTOKEN/VERSION as required). SYSPLANAUTH is the same idea for plans (EXECUTE on the plan name). Collection-level CREATE IN / PACKADM style privileges are resource privileges in SYSRESAUTH, not these two tables.

1
2
3
4
5
SELECT GRANTEE, GRANTEETYPE, COLLID, NAME, EXECUTEAUTH
  FROM SYSIBM.SYSPACKAUTH
 WHERE COLLID = 'HRCOLL'
   AND NAME = 'HREMP'
  WITH UR;

SYSIBM.SYSPACKDEP

SYSPACKDEP lists objects a package depends on. That is how Db2 knows to set VALID = N after DROP INDEX or ALTER TABLE. DNAME/DCOLLID identify the package; BCREATOR, BNAME, and BTYPE identify the base object.

1
2
3
4
5
6
SELECT DCOLLID, DNAME, BCREATOR, BNAME, BTYPE
  FROM SYSIBM.SYSPACKDEP
 WHERE BCREATOR = 'HR'
   AND BNAME = 'EMPLOYEE'
   AND BTYPE = 'T'
  WITH UR;

Run that query before you DROP a table: every package row is a bind you will break. Combine with SYSVIEWDEP and SYSTRIGGERS for a full impact list.

Explain It Like I'm Five

A package is a lunchbox of already-decided instructions for SQL. SYSPACKAGE is the label on the lunchbox (whose class, which kid, is the sandwich still good?). SYSPACKSTMT is the list of snacks inside. SYSPLAN is the cafeteria tray that can hold several lunchboxes. SYSROUTINES is the recipe book for special kitchen machines (procedures and functions). SYSPACKDEP is the note “this lunchbox needs the apple basket named EMPLOYEE”—if someone throws away the basket, the label switches to “not valid.”

Exercises

1. Query SYSPACKAGE for one collection and count rows where VALID <> 'Y'.
2. Join SYSPACKSTMT to SYSPACKAGE and display QUERYNO and the first 60 characters of STATEMENT for one package.
3. List SYSROUTINES in a schema with ROUTINETYPE and LANGUAGE. Then list SYSPARMS for one SPECIFICNAME.
4. Find all packages that depend on a given table using SYSPACKDEP.
5. Explain the difference between SYSPACKAUTH EXECUTEAUTH = 'Y' and 'G'.