Designing ERD Diagrams with IBM DB2 and Lucidchart

Recently Indellient has adopted Lucidchart as an alternative to Microsoft Visio. Being the Mac-user I am, I decided to give Lucidchart a try because it is a more flexible option and has an added benefit of not using a Windows VM. Anyone who is making ERD diagrams (or a similar type of documentation) and prefers (or is required) to use Lucidchart will find this post beneficial.

On a recent project, I had to create an ERD diagram out of an existing IBM DB2 database and layer on top relevant content – a very common task on data projects for documentation. I considered using IBM Data Studio to get the ERD diagram and isolate what I needed as a starting point.

Out of curiosity, I decided to explore Lucidchart’s features comparatively to Visio to determine the extent of its capabilities. When I opened up Lucidchart, I immediately went to the import feature on ERD Templates.

Using Lucid Chart Database Import for IBM DB2

The database import is a simple concept. It provides you with a query to run against your DBMS to produce the data Lucidchart needs to import the database information.

Simple enough…but where is the IBM DB2 option?

When taking a look at the query, it occurred to me that there must be a DB2 equivalent. With the assistance of a colleague, we took the Oracle query and converted it to something DB2 could generate, but with the field names and values that would be recognized by Lucid using Oracle structure. We came up with this:

SELECT
'oracle' dbms,
CURRENT_SERVER as ORA_DATABASE_NAME,
t.CREATOR AS OWNER,
t.NAME AS TABLE_NAME,
c.NAME AS COLUMN_NAME,
c.COLNO AS COLUMN_ID,
c.COLTYPE AS DATA_TYPE,
c.LENGTH AS DATA_LENGTH,
CASE WHEN n.TYPE = 'F' THEN 'R' ELSE n.TYPE END AS CONSTRAINT_TYPE,
r.TABSCHEMA AS OWNER,
r.TABNAME AS TABLE_NAME,
r.COLNAME AS COLUMN_NAME,
FROM
SYSIBM.SYSTABLES t LEFT JOIN SYSIBM.SYSCOLUMNS c
ON t.CREATOR=c.TBCREATOR AND t.NAME=c.TBNAME LEFT JOIN
SYSCAT.KEYCOLUSE nc
ON c.TBCREATOR=nc.TABSCHEMA AND c.TBNAME=nc.TABNAME AND c.NAME=nc.COLNAME
LEFT JOIN SYSCAT.TABCONST n
ON nc.CONSTNAME=n.CONSTNAME AND n.TYPE IN('P','U','F')
LEFT JOIN SYSCAT.KEYCOLUSE r
ON n.CONSTNAME=r.CONSTNAME AND nc.COLNAME=r.COLNAME
WHERE
c.NAME IS NOT NULL;

The output of the above query stored in CSV format can be used in Lucidchart by pretending it’s an Oracle DBMS. Doing so will result in:

This gives you an organized view of your tables with:

• Column Names
• Data Types
• Primary Key and Foreign Key Indication

---

Lucid Chart Limitations with DB2

Unfortunately, relationship links were not created if I brought in two table objects that were connected and there is no differentiation between a table vs. a materialized query table vs. a view.

The main accomplishment, however, is getting the database objects into Lucidchart to speed up the documentation. This enables you to use and work within Lucidchart for ERD diagrams based on DB2.

Feel free to share your own experience with Lucidchart and IBM DB2.

---

Visit Indellients Data, Analytics and AI Services to explore business intelligence, advanced analytics, and data science initiatives. You can always reach out to us directly with questions at info@indellient.com.