Moderne OnlyThis recipe is proprietary to Moderne and runs on the Moderne platform or CLI — it isn’t part of the open-source catalog. Available with a Moderne subscription.Contact Sales

Find the tables the DDL creates and who reaches them

Recipe IDio.moderne.mainframe.db2.FindDb2Catalog

Artifactio.moderne.recipe:recipes-mainframe

Report the DB2 schema the portfolio creates and mark every statement that creates part of it: each table with its columns, primary key, foreign keys and indexes, anchored at the member and line it is created on. DDL is written in two places and neither stands for the other — a member of a DDL library, and the SYSIN stream of the job that submits it, which is where an index added to a live table years later usually ends up — so both are read and the job that carries a stream is marked in place of statements that are not its own. The summary joins each table to the programs whose EXEC SQL names it and to the packages and plans those programs are bound into, which is what a change to the table has to be rebound into; an unqualified table name in a program is resolved against the qualifier its package was bound with, because that is written in the bind deck and nowhere else. A foreign key becomes a relationship between the two tables, so the model diagram is the same graph everything else is drawn from. AnalyzeDb2Catalog answers the same question and changes nothing.

Single recipeOpenRewriteModerne Proprietary License

Try in PlatformTry this recipe in the Moderne platform. Not a user yet? You’ll get a no-setup demo environment, with nothing to install or configure.

Usage

Run this recipe

This recipe has no required configuration options. Users of Moderne can run it via the Moderne CLI.

You will need to have configured the Moderne CLI on your machine before you can run the following command.

shell

mod run . --recipe FindDb2Catalog

If the recipe is not available locally, then you can install it using:

RELEASE resolves to the newest release and LATEST to the newest build of any kind, including snapshots. Either one lets mod config recipes upgrade pull in later versions without editing this command; a pinned version stays where you put it.

RELEASE

mod config recipes jar install io.moderne.recipe:recipes-mainframe:RELEASE

LATEST

mod config recipes jar install io.moderne.recipe:recipes-mainframe:LATEST

Pinned version

mod config recipes jar install io.moderne.recipe:recipes-mainframe:0.1.0

Data tables

DB2 tables

io.moderne.mainframe.analysis.table.Db2Tables

One row per table the DDL creates: where it is defined, what it is made of, and who reaches it. This is the summary a person opens a table on — the columns, the primary key, the foreign keys either way and the indexes, with the programs, packages and plans that use it — so the question "what is this table and what would a change to it break" is one row rather than a search.

Export after a run

mod study . --last-recipe-run \
  --data-table io.moderne.mainframe.analysis.table.Db2Tables

Column	Description
Table	The table, qualified as the DDL wrote it. This is the name the SQL in a program has to resolve to, and the one an unqualified name is resolved against the bind qualifier to reach.
Schema	The qualifier the table was created under, empty for a table created unqualified — which leaves it to whatever SET CURRENT SQLID was in force.
Name	The table's own name, without its schema.
Table space	The table space the table lives in, empty where the DDL leaves it to DB2. This is what a RUNSTATS or a REORG card names, so it is the join from a table to the utility jobs that maintain it.
Member	The DDL member the table is created in, or the job that creates it in its own stream.
Source path	The file the CREATE TABLE was read from, which is the file to open at the line below.
Line	The one-based line of that file the CREATE TABLE begins on, counting from the top of the job for DDL written in a stream.
Written	Whether the DDL is a member of a library or written in a job's stream. A table created in a stream exists nowhere else, so the job is its definition and not just the thing that ran it.
Columns	How many columns the table has.
Primary key	The primary key's columns in key order, empty for a table declaring none — which happens, and is then enforced by a unique index instead.
Foreign keys	How many foreign keys this table declares, which is how many parents it has.
Referenced by	How many foreign keys point at this table, which is how many children it has. A table with children cannot have a row deleted without deciding what happens to them.
Indexes	How many indexes are defined on the table.
Programs	How many programs name the table in an EXEC SQL statement, unqualified names resolved against the qualifier their package was bound with.
Packages	How many DB2 packages reach the table, which is the programs above joined to the bind decks that bind them.
Plans	How many plans reach the table through those packages, a PKLIST naming a whole collection expanded. Every plan counted here has to be rebound when the table changes.

DB2 columns

io.moderne.mainframe.analysis.table.Db2Columns

Every column of every table the DDL creates, in the order a SELECT * and a host structure both take: what it holds, whether it may be absent, where it sits in the primary key, the table it points at through a foreign key, and the indexes it is a key of. The last is what says whether a predicate written on the column has an access path or a table space scan behind it.

Export after a run

mod study . --last-recipe-run \
  --data-table io.moderne.mainframe.analysis.table.Db2Columns

Column	Description
Table	The table the column belongs to, qualified as the DDL wrote it.
Ordinal	Where the column sits in the table, counting from one. This is the order a SELECT * and a host structure both take, so it is what aligns a column with the COBOL field receiving it.
Column	The column name.
Type	The type as the DDL writes it, length and scale included: CHAR(12), DECIMAL(9, 2), DATE.
Nullable	Whether the column may hold no value. A primary key column may not, whether or not the DDL says NOT NULL — DB2 requires it and supplies it when it is left out. A nullable column is the one a program has to fetch with an indicator variable.
Key sequence	Where the column sits in the primary key, counting from one, or 0 for a column that is not part of it.
Foreign key to	The table this column points at through a foreign key, empty where it points at none. A column carrying two foreign keys names both.
Indexes	The indexes the column is a key of, each with its position in that key and the direction it is held in — CLM.XPOLICY1(2 ASC). A column with none is a column no predicate can be selective on.
Member	The DDL member the column is declared in, or the job that declares it in its own stream.
Source path	The file the column was read from, which is the file to open at the line below.
Line	The one-based line of that file the column is declared on.

DB2 primary keys

io.moderne.mainframe.analysis.table.Db2Keys

The primary key of each table, one row per key column in key order, with the unique index that enforces it. DB2 will not accept a primary key without one, so a key whose index is missing here is DDL that would not run — and the index is also the access path every foreign key check and every keyed read goes through.

Export after a run

mod study . --last-recipe-run \
  --data-table io.moderne.mainframe.analysis.table.Db2Keys

Column	Description
Table	The table the key belongs to, qualified as the DDL wrote it.
Constraint	The name the DDL gives the constraint, empty where it names none — which is usual for a primary key, and leaves DB2 to name it after the index.
Column	The key column.
Key sequence	Where the column sits in the key, counting from one. A composite key is only usable left to right, so the order is the whole of what it is good for.
Enforced by	The unique index whose key is exactly these columns, empty where the DDL read here declares none. DB2 requires one and creates it implicitly when a shop lets it, so an empty value means the index was not supplied rather than that there is none.
Member	The DDL member the key is declared in, or the job that declares it in its own stream.
Source path	The file the key was read from, which is the file to open at the line below.
Line	The one-based line of that file the PRIMARY KEY is written on.

DB2 indexes

io.moderne.mainframe.analysis.table.Db2Indexes

Every index the DDL creates, with the table it is on and the key it holds. An index is how a batch job reaches a table cheaply, so which columns it is keyed on is which predicates the schema was designed to answer — and an index no program writes a predicate for is maintenance nothing collects on.

Export after a run

mod study . --last-recipe-run \
  --data-table io.moderne.mainframe.analysis.table.Db2Indexes

Column	Description
Index	The index, qualified as the DDL wrote it.
Table	The table the index is on, qualified as the DDL wrote it.
Unique	Whether the index enforces uniqueness, which is what a primary key needs and what a shop with no primary key uses instead of one.
Key columns	The key in order, each column with the direction it is held in — STATUS_CODE ASC, POLICY_NO ASC. A composite key is only usable left to right.
Enforces the primary key	Whether this is the unique index whose key is exactly the table's primary key, which is the index DB2 requires a primary key to have.
Member	The DDL member the index is created in, or the job that creates it in its own stream. An index added to a live table years after its table is routinely in a job of its own, and that job is the only place it exists.
Source path	The file the CREATE INDEX was read from, which is the file to open at the line below.
Line	The one-based line of that file the CREATE INDEX begins on, counting from the top of the job for DDL written in a stream.

DB2 foreign keys

io.moderne.mainframe.analysis.table.Db2ForeignKeys

One table pointing at another, which is the referential arrow of the model diagram and the one relationship between two tables that can be read without reading a program. A key is written either in the CREATE TABLE or in an ALTER TABLE afterwards, and both are read here.

Export after a run

mod study . --last-recipe-run \
  --data-table io.moderne.mainframe.analysis.table.Db2ForeignKeys

Column	Description
Constraint	The name the DDL gives the relationship, empty where it names none. This is the name a -904 or a load utility message reports, so it is worth carrying even though nothing joins on it.
Table	The child table, the one holding the key, as the CREATE TABLE that creates it names it — or as the key names it where nothing here creates one.
Columns	The child's columns, in key order.
Referenced table	The parent table the key points at.
Referenced columns	The parent's columns, in key order. The DDL usually leaves these out and means the parent's primary key, and that key is filled in here — a row with a parent and no columns would say less than the DDL does.
Delete rule	What DB2 does to the child's rows when a parent row goes away: RESTRICT, CASCADE, SET NULL or NO ACTION. Empty where the DDL leaves it to the default of NO ACTION.
Member	The DDL member the key is declared in, or the job that declares it in its own stream.
Source path	The file the key was read from, which is the file to open at the line below.
Line	The one-based line of that file the FOREIGN KEY is written on.

COBOL relationships

org.openrewrite.mainframe.cobol.table.CobolRelationships

Relationships between different COBOL resources.

Export after a run

mod study . --last-recipe-run \
  --data-table org.openrewrite.mainframe.cobol.table.CobolRelationships

Column	Description
Dependent	The resource that defines the relationship.
Dependent type	The resource type of the resource that defines the relationship.
Action	The action that links the dependent and dependency.
Dependency	The resource that is exercised or linked to.
Dependency type	The resource type of the resource that is exercised or linked to.
Dependency missing	Indicates whether the dependency is a known resource.
Action metadata	Additional data about the action.
Dependent path	The source file the dependent's statement was written in, or null when the dependent is a name that has no source here.
Dependent line	The one-based line the dependent's statement was written on, or null when it has no source here.
Dependency path	The source file the dependency was written in, or null when the dependency is a name that has no source here, such as a vendor copybook.
Dependency line	The one-based line the dependency's statement was written on, or null when the dependency is a whole file rather than a statement within one.

Source files that had results

org.openrewrite.table.SourcesFileResults

Source files that were modified by the recipe run.

Export after a run

mod study . --last-recipe-run \
  --data-table org.openrewrite.table.SourcesFileResults

Column	Description
Source path before the run	The source path of the file before the run. null when a source file was created during the run.
Source path after the run	A recipe may modify the source path. This is the path after the run. null when a source file was deleted during the run.
Parent of the recipe that made changes	In a hierarchical recipe, the parent of the recipe that made a change. Empty if this is the root of a hierarchy or if the recipe is not hierarchical at all.
Recipe that made changes	The specific recipe that made a change.
Estimated time saving	An estimated effort that a developer to fix manually instead of using this recipe, in unit of seconds.
Cycle	The recipe cycle in which the change was made.

Source files that had search results

org.openrewrite.table.SearchResults

Search results that were found during the recipe run.

Export after a run

mod study . --last-recipe-run \
  --data-table org.openrewrite.table.SearchResults

Column	Description
Source path of search result before the run	The source path of the file with the search result markers present.
Source path of search result after run the run	A recipe may modify the source path. This is the path after the run. null when a source file was deleted during the run.
Result	The trimmed printed tree of the LST element that the marker is attached to.
Description	The content of the description of the marker.
Recipe that added the search marker	The specific recipe that added the Search marker.

Source files that errored on a recipe

org.openrewrite.table.SourcesFileErrors

The details of all errors produced by a recipe run.

Export after a run

mod study . --last-recipe-run \
  --data-table org.openrewrite.table.SourcesFileErrors

Column	Description
Source path	The file that failed to parse.
Recipe that made changes	The specific recipe that made a change.
Stack trace	The stack trace of the failure.

Recipe performance

org.openrewrite.table.RecipeRunStats

Statistics used in analyzing the performance of recipes.

Export after a run

mod study . --last-recipe-run \
  --data-table org.openrewrite.table.RecipeRunStats

Column	Description
The recipe	The recipe whose stats are being measured both individually and cumulatively.
Source file count	The number of source files the recipe ran over.
Source file changed count	The number of source files which were changed in the recipe run. Includes files created, deleted, and edited.
Cumulative scanning time (ns)	The total time spent across the scanning phase of this recipe.
Max scanning time (ns)	The max time scanning any one source file.
Cumulative edit time (ns)	The total time spent across the editing phase of this recipe.
Max edit time (ns)	The max time editing any one source file.