IBM Db2 Design Tool: Browse Db2 Schemas and Model the Database Visually

A Db2 database that has run for years holds schemas nobody has drawn since the first release, and the catalog answers one query at a time. DbSchema connects to Db2 over JDBC, reads the catalog of the schemas you tick, and draws them as an ER diagram that you can rearrange and edit. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition.

Which Db2 catalog DbSchema reads

Db2 is three products with three catalogs. Db2 for Linux, UNIX and Windows (LUW) describes its objects in the SYSCAT views, Db2 for z/OS in the SYSIBM catalog tables, and Db2 for IBM i in the QSYS2 views. DbSchema has an entry for each:

The DB2 and Standard methods use IBM's Data Server Driver for JDBC and SQLJ, and AS400 uses the IBM Toolbox for Java driver. On Db2 LUW, DbSchema takes tables, columns and keys from the JDBC driver, and reads the views, triggers, procedures, functions, sequences, check constraints and comments from the SYSCAT views.

What a Db2 schema contains

A Db2 schema is a named collection of objects and a qualifier for their names[1], so SALES.ORDERS and BILLING.ORDERS live side by side in one database. A schema is created with CREATE SCHEMA, or implicitly when a user with IMPLICIT_SCHEMA authority creates an object under a new name, and SYSIBM owns a schema created that way. A query that filters the catalog on your own user as owner therefore misses those schemas. An unqualified table name in dynamic SQL resolves through the CURRENT SCHEMA special register, which starts as the session user's authorization ID[2].

List the schemas and the foreign keys between them

Db2 LUW keeps one row per schema in SYSCAT.SCHEMATA[3]. DbSchema lists the schemas it offers for import with this query:

SELECT schemaname FROM SYSCAT.SCHEMATA ORDER BY schemaname

The foreign keys that cross from one schema into another are the ones a single-schema diagram hides. SYSCAT.REFERENCES has one row per foreign key, with the schema on both sides[4]:

SELECT constname, tabschema, tabname, reftabschema, reftabname
FROM syscat.references
WHERE tabschema <> reftabschema
ORDER BY tabschema, tabname;

In DbSchema you skip both queries. The import dialog lists every schema your user can read, and when you tick several, their tables land on one diagram with the cross-schema foreign keys drawn as lines.

Connect DbSchema to Db2

The steps follow Connect to Database:

1. Choose File → New Model and keep Connect to Database selected.
2. Click Db2, or Db2zOS for a mainframe, choose New Connection and press Connect to Db2.
3. Choose the Method DB2 for Db2 LUW, or AS400 for Db2 for IBM i.
4. Choose Remote computer or custom port, then type the Server Host, the Port, the Database User, the Password and the Database.
5. Press Test Connection, then Connect.
6. In Reverse Engineer the Database, tick the schemas and press Import.

DbSchema downloads the JDBC driver the first time you connect, and connecting and importing only read from the database. On a production database, tick Read Only Connection on the Access and Model tab, and DbSchema refuses to change the schema through that connection.

If the connection is refused, the instance may not listen on TCP/IP. The DB2COMM registry variable is empty by default, so no communication manager starts[5], and the SVCENAME parameter names the port. As the instance owner, run:

db2set DB2COMM=TCPIP
db2 update dbm cfg using SVCENAME 50000
db2stop
db2start

Read the diagram and edit the model offline

The import draws each table with its columns, Db2 data types, primary keys and foreign keys. This sample schema, SAMPLEDB, has fifteen tables:

The foreign keys carry names such as SQL250724193847890: Db2 generated them because the statements that created the keys gave none. A large schema reads better split into several diagrams, one per part of the application, and a table can sit on more than one.

DbSchema keeps the design in a model file, .dbs, separate from the database. While you are connected, OK in a table dialog changes the model and the database at once. Choose Offline (connect later) from the connection button, and your changes stay in the model until you synchronize. Saving the model with File → Save Model to File needs DbSchema Pro.

Compare the model with Db2 and review the DDL

When you reconnect, or choose Database → Refresh Model from Database after someone changed Db2 directly, DbSchema compares the two sides. The Synchronize Model and Database dialog lists one row per difference, and for each row you choose whether it goes into the model, into the database, or nowhere:

Commit in the database runs the statements one at a time and shows what Db2 answered. Generate SQL script writes them to an SQL Editor or a file instead, so a DBA can review the script before a change window. To compare a test database with production without connecting to both, reverse-engineer each into its own model file and choose Model → Compare Model with Other Model From File. Schema synchronization is a Pro feature.

Document the Db2 schema and keep the model in Git

DbSchema imports the Db2 comments from the REMARKS columns of SYSCAT.TABLES and SYSCAT.COLUMNS, and a comment you type in DbSchema goes back to Db2 as a COMMENT statement:

COMMENT ON TABLE sampledb.customers IS 'One row per customer account';
COMMENT ON COLUMN sampledb.customers.email IS 'Address for order confirmations';

Db2 LUW limits each comment to 254 bytes[6]. File → Export As... → HTML5/PDF Documentation writes the documentation as one HTML5 file, a PDF or Markdown. The HTML5 file opens in any browser with no Db2 client, shows the comments as tooltips on the diagram, and has a search box for tables and columns:

The model file goes into a Git repository beside the application code. File → Git Collaboration opens the Git dialog, where you commit, push, pull and branch, and nothing in that loop touches the database. Documentation export and the saved model file are Pro features.

Query, browse and fill Db2 tables

The SQL Editor, in every edition, completes table and column names while you type. In Pro, right-click a table on the diagram and choose Query Editor to open the Query Builder: it joins tables through their foreign keys and writes the SELECT as you tick columns.

Right-click a table and choose Data Explorer to see its rows. The arrow next to the table name follows a foreign key to the related rows, so clicking a customer lists that customer's orders, and edits there run in Db2 at once. Following foreign keys in the Data Explorer needs Pro.

For test rows, Database → Data Generator fills the tables of the open diagram. A foreign key column gets the load_values_from_pk generator, which picks values from the parent's primary key, so the parent tables must come first in the list. The Data Generator is a Pro feature.

Download DbSchema, connect it to your Db2 database and tick the schemas you want on the diagram. The Community Edition covers connecting, reverse-engineering, the diagrams and the SQL Editor at no cost. The saved model file, schema synchronization, the documentation export, the Query Builder, following foreign keys in the Data Explorer, and the Data Generator are in the Pro Edition, and a new installation starts with a 15-day trial of DbSchema Architect, which has every feature.

Sources

1. IBM Db2 12.1: Schemas
2. IBM Db2 12.1: CURRENT SCHEMA special register
3. IBM Db2 12.1: SYSCAT.SCHEMATA catalog view
4. IBM Db2 12.1: SYSCAT.REFERENCES catalog view
5. IBM Db2 12.1: Communications variables
6. IBM Db2 12.1: COMMENT statement