DB2 catalog routine and JAR tables

Where routines live in the catalog

A routine is a stored procedure or a user-defined function. The master inventory is always SYSIBM.SYSROUTINES. JAR tables are supporting objects: they exist so LANGUAGE JAVA routines can resolve classes without each developer shipping a private jar on the WLM address-space STEPLIB. Parameter details live in SYSIBM.SYSPARMS. EXECUTE privileges live in SYSIBM.SYSROUTINEAUTH. Native SQL procedure text lives on the routine row (TEXT / TEXT_ROWID) rather than in a JAR.

Think of the catalog as three layers for Java work:

• SYSJAROBJECTS — the installed file (BLOB) and its JAR_ID
• SYSJARCONTENTS — which classes that file contains
• SYSROUTINES — which procedure or function uses that JAR_ID, plus EXTERNAL_NAME, CLASS, JAVA_SIGNATURE, WLM_ENVIRONMENT

If you skip the join and only look at SYSROUTINES, you can see that a procedure is JAVA but you cannot prove the JAR is still installed. If you only look at SYSJAROBJECTS, you see archives that nobody might be calling. Always join when you are diagnosing SQLCODE -430 or class-not-found failures after a REPLACE_JAR.

SYSIBM.SYSJAROBJECTS

SYSJAROBJECTS contains one row for each JAR that has been installed into the subsystem with the SQLJ JAR procedures. The schema is SYSIBM. IBM describes the table as holding the binary large object that represents the installed JAR. Related auxiliary storage for the BLOB may appear as SYSIBM.SYSJARDATA in the catalog (the LOB auxiliary table behind JAR_DATA). You query SYSJAROBJECTS; you do not CREATE the auxiliary table yourself.

JARSCHEMA plus JAR_ID is the qualified JAR name you pass to SQLJ.INSTALL_JAR and later to CREATE PROCEDURE ... JAVA. JAR_ID is not a z/OS data set name. PATH is the Java resolution path Db2 associates with that JAR so dependent classes in other JARs can be found. CREATEDTS versus ALTEREDTS tells you whether someone replaced the archive after the original install—vital when production still runs an old class file that developers thought they refreshed.

1
2
3
4
SELECT JARSCHEMA, JAR_ID, OWNER, PATH,
       CREATEDTS, ALTEREDTS
  FROM SYSIBM.SYSJAROBJECTS
 ORDER BY JARSCHEMA, JAR_ID;

Selecting JAR_DATA in SPUFI is rarely useful: it is a BLOB up to 100 MB. Use the identity columns for inventory. To change contents, call SQLJ.REPLACE_JAR with the new HFS or z/OS UNIX file. INSTALL_JAR fails if the JAR_ID already exists; REPLACE_JAR updates JAR_DATA and refreshes SYSJARCONTENTS. REMOVE_JAR deletes the catalog rows after you drop or alter routines that still reference the JAR.

How a JAR gets into the catalog

Typical flow on z/OS:

1. FTP or copy the .jar into a z/OS UNIX file that the WLM-established stored-procedure address space can read.
2. CALL SQLJ.INSTALL_JAR with a URL-style file name and the SQL JAR identifier (schema.jar-id).
3. Optionally CALL SQLJ.ALTER_JAVA_PATH if the routine needs classes from other installed JARs (those relationships also appear in SYSIBM.SYSJAVAPATHS).
4. CREATE PROCEDURE ... LANGUAGE JAVA EXTERNAL NAME 'java:class.method' with JARSCHEMA and JAR_ID (or the equivalent JAR clause your release documents).

After step 2, SYSJAROBJECTS has a row even if no procedure exists yet. That is normal: shops often install a shared utility JAR once, then create many procedures against it.

SYSIBM.SYSJARCONTENTS

SYSJARCONTENTS contains information about the Java classes inside each installed JAR. IBM documents one row per class. Columns include JARSCHEMA, JAR_ID, CLASS (the fully qualified class name), CLASS_SOURCE (a CLOB of source when present), CLASS_SOURCE_ROWID, and IBMREQD.

CLASS is the column you search when a Java exception names a missing class. If com.shop.payroll.BonusCalc is not in SYSJARCONTENTS for the JAR_ID named on the procedure, REPLACE_JAR never contained that class—or you pointed the procedure at the wrong JAR_ID. CLASS_SOURCE is optional metadata; many production JARs are compiled-only and the CLOB is empty. Do not treat an empty CLASS_SOURCE as proof the class is missing; look at CLASS.

1
2
3
4
5
SELECT C.JARSCHEMA, C.JAR_ID, C.CLASS
  FROM SYSIBM.SYSJARCONTENTS C
 WHERE C.JARSCHEMA = 'PAYROLL'
   AND C.JAR_ID = 'PAYUTIL'
 ORDER BY C.CLASS;

Join to routines when you need “which procedure uses this class name”:

1
2
3
4
5
6
7
8
9
SELECT R.SCHEMA, R.NAME, R.SPECIFICNAME,
       R.LANGUAGE, R.EXTERNAL_NAME,
       R.JARSCHEMA, R.JAR_ID, C.CLASS
  FROM SYSIBM.SYSROUTINES R
  INNER JOIN SYSIBM.SYSJARCONTENTS C
    ON C.JARSCHEMA = R.JARSCHEMA
   AND C.JAR_ID = R.JAR_ID
 WHERE R.LANGUAGE = 'JAVA'
   AND C.CLASS LIKE '%BonusCalc%';

EXTERNAL_NAME on SYSROUTINES is the Java method descriptor Db2 invokes. It must match a class that actually appears in SYSJARCONTENTS. Mismatches are a common cause of failures that look like WLM problems but are really catalog drift after a partial REPLACE_JAR.

SYSIBM.SYSROUTINES_OPTS (SYSROUTINEOPTS)

Topic lists often say SYSIBM.SYSROUTINEOPTS. The IBM SQL Reference name is SYSIBM.SYSROUTINES_OPTS. The table contains one row for each generated routine—historically routines built by the Db2 for z/OS Procedure Processor DSNTPSMP—and records the build options used for that routine. IBM marks the external SQL procedure model as deprecated. Prefer native SQL procedures created with CREATE PROCEDURE and LANGUAGE SQL.

Rows can be inserted, updated, and deleted (unlike most catalog tables) because the procedure processor treated them as a build worksheet. Columns you will see in documentation include:

• SCHEMA and ROUTINENAME — which routine the options belong to
• BUILDDATE, BUILDTIME, BUILDSTATUS — when this option set was built and whether it is the current version (BUILDSTATUS default 'C' means current)
• BUILDSCHEMA, BUILDNAME, BUILDOWNER — identifiers for the generated build objects
• PRECOMPILE_OPTS — SQL precompiler or coprocessor options
• COMPILE_OPTS — host compiler options
• PRELINK_OPTS and LINK_OPTS — binder / linkage-editor options
• BIND_OPTS — BIND PACKAGE / PLAN options used when the generated program was bound
• SOURCEDSN — source data set name for the generated SQL procedure
• DEBUG_MODE — whether the generated routine was built for debugging

How each option group affects runtime:

• Precompile options decide SQL string delimiters, decimal-point, and host language—the same class of settings you would put on a COBOL precompile step. Wrong delimiters produce bind-time SQL errors that look like “bad source.”
• Compile and link options decide whether the load module matches the Language Environment level of the WLM application environment. A mismatch can abend in the stored-procedure address space even though SYSROUTINES looks healthy.
• BIND_OPTS decide isolation, release, currentdata, and package owner for the generated package. Changing isolation later requires a new build or an explicit REBIND, not an UPDATE of SYSROUTINES_OPTS alone.

1
2
3
4
5
SELECT SCHEMA, ROUTINENAME, BUILDSTATUS,
       BUILDDATE, SOURCEDSN, DEBUG_MODE
  FROM SYSIBM.SYSROUTINES_OPTS
 WHERE SCHEMA = 'PAYROLL'
 ORDER BY ROUTINENAME, BUILDDATE;

If this query returns no rows, that is expected for native SQL procedures and for ordinary COBOL EXTERNAL procedures that were compiled outside DSNTPSMP. Those objects never needed SYSROUTINES_OPTS. Do not invent rows to “document” a native procedure.

Finding routines

“Finding routines” is everyday catalog work: who owns PAY_CALC, is it JAVA or SQL, which WLM environment does it need, and who may CALL it. Start with SYSROUTINES, then fan out.

Inventory by type

ROUTINETYPE on SYSROUTINES is the first filter. P means stored procedure. F means function. LANGUAGE tells you SQL, JAVA, COBOL, C, ASSEMBLE, and so on. ORIGIN distinguishes native SQL, external, sourced functions, and other implementation styles. WLM_ENVIRONMENT is blank for native SQL procedures that run in the Db2 engine; it is required for most external procedures because they run in a WLM-established address space.

1
2
3
4
5
6
SELECT SCHEMA, NAME, SPECIFICNAME, ROUTINETYPE,
       LANGUAGE, ORIGIN, WLM_ENVIRONMENT,
       JARSCHEMA, JAR_ID, CREATEDTS
  FROM SYSIBM.SYSROUTINES
 WHERE SCHEMA NOT LIKE 'SYS%'
 ORDER BY ROUTINETYPE, SCHEMA, NAME;

Parameters

SYSPARMS has one row per parameter (and result columns for some function types). ORDINAL is the position. ROWTYPE (or equivalent parameter-mode column on your release) distinguishes IN, OUT, and INOUT for procedures. Join on schema and specific name so overloaded names do not mix signatures.

1
2
3
4
5
6
SELECT P.ORDINAL, P.PARMNAME, P.TYPENAME,
       P.LENGTH, P.SCALE, P.ROWTYPE
  FROM SYSIBM.SYSPARMS P
 WHERE P.SCHEMA = 'PAYROLL'
   AND P.SPECIFICNAME = 'PAY_CALC_SP'
 ORDER BY P.ORDINAL;

Java routines that lost their JAR

After a REMOVE_JAR or a failed REPLACE, SYSROUTINES can still name a JAR_ID that is gone from SYSJAROBJECTS. That left-join pattern is a standard health check:

1
2
3
4
5
6
7
SELECT R.SCHEMA, R.NAME, R.JARSCHEMA, R.JAR_ID
  FROM SYSIBM.SYSROUTINES R
  LEFT OUTER JOIN SYSIBM.SYSJAROBJECTS J
    ON J.JARSCHEMA = R.JARSCHEMA
   AND J.JAR_ID = R.JAR_ID
 WHERE R.LANGUAGE = 'JAVA'
   AND J.JAR_ID IS NULL;

Rows returned here will fail at CALL time. Fix by reinstalling the JAR or altering the procedure to a JAR that exists. Also confirm the WLM application environment is started (-DISPLAY PROCEDURE and -DISPLAY WLM, plus SDSF for the stored-procedure address space). Catalog health does not start WLM for you.

Who can execute the routine

SYSROUTINEAUTH records EXECUTE. EXECUTEAUTH values follow the usual catalog privilege encoding (granted, with grant option, or not held). Installation SYSADM can always manage routines; application IDs need an explicit GRANT EXECUTE unless PUBLIC holds it. When CALL fails with authorization SQLCODEs, query SYSROUTINEAUTH before rewriting Java.

Related catalog objects you will meet next

SYSJAVAPATHS describes the complete JAR class-resolution path for a JAR_ID. SYSJARCLASS_SOURCE may hold class source associated with contents. SYSROUTINES_SRC held generated SQL source for older external SQL procedures (also deprecated). None of those replace SYSROUTINES as the inventory. When you document a subsystem, export SYSROUTINES plus SYSJAROBJECTS as a pair so disaster recovery can reinstall JARs in the same schema names the procedures expect.

IBMREQD on these tables marks rows that Db2 itself requires. Do not DELETE IBMREQD = Y rows. User JARs have IBMREQD = N and are removable with REMOVE_JAR after dependents are dropped.

Explain It Like I'm Five

Imagine a toy workshop. SYSROUTINES is the list of toy machines (procedures and functions) and which room they run in. A Java machine needs a box of LEGO instructions. SYSJAROBJECTS is the box (the whole JAR). SYSJARCONTENTS is the paper listing every instruction sheet inside the box (each class). SYSROUTINES_OPTS is an old recipe card from when Db2 used to build a machine for you with a special builder (DSNTPSMP). New machines are built with CREATE PROCEDURE and do not need that recipe card. If you throw away the LEGO box but leave the machine on the list, the machine cannot play.

Exercises

1. Query SYSJAROBJECTS on a sandbox subsystem and list every JAR_ID. Note CREATEDTS versus ALTEREDTS and explain what a later ALTEREDTS implies.
2. Pick one Java routine from SYSROUTINES and prove its CLASS appears in SYSJARCONTENTS for the same JARSCHEMA and JAR_ID.
3. Run the left-join “JAVA routine with missing JAR” query. If it returns rows, write the REMEDY (INSTALL_JAR versus DROP PROCEDURE) you would choose and why.
4. Query SYSROUTINES_OPTS. If it is empty, explain why that can still be healthy on a subsystem that only uses native SQL procedures.
5. Write a SELECT that lists SCHEMA, NAME, and EXECUTEAUTH from SYSROUTINEAUTH for one procedure your shop cares about.