Hey, Let's Check the Catalog!

My long time friend and teammate Kent Milligan has some great insight and advice for an often overlooked feature of DB2 for i - the catalog.

If you are not familiar with Kent and his knack for illuminating the functions and benefits of all things DB2, open up a browser and instigate a search using "Kent Milligan IBM".

Might want to get a caffeinated beverage as it's going to be a long night of reading.

Enjoy!

-------- 

The DB2 Catalog is probably one of the most overlooked resources on your IBM i system by both programmers and database engineers.  The DB2 catalog is comprised of a set of catalog views and tables that contain information about all of the DB2 objects on your system. Metadata is another term used to describe data about your database.  As DB2 objects are created and deleted on the system – either with SQL or DDS via IBM i commands  – the catalog objects are automatically updated to reflect the latest state of your databases. The catalog objects sit behind the scenes, helping DB2 manage your databases while at the same time storing valuable metadata about your databases.

The information stored in the DB2 Catalog is a valuable resource that make it easy to analyze your database and to develop utilities to manage your database.  The following IBM i Navigator screen shows some of the catalog views that reside in the QSYS2 schema.

 

 

The SYSCOLUMNS view shown in this window is a great resource to leverage for understanding the lengths and types of data stored in your DB2 for i databases.

Let’s say you’re interested in finding all of the “long” columns in your database – that’s easy as running the following SQL SELECT statement against the SYSCOLUMNS view. 

 

SELECT      column_name,

                   table_name,

                   length,

                   data_type
FROM         qsys2.syscolumns

WHERE       length > 2500

ORDER BY length DESC;

In this post, I obviously don’t have room to explain all of the DB2 catalog objects and the ways that they can be exploited to simplify your job, so I’ve included some links to more detailed information. 

The first link is to an article that I published 2 years ago. The second link takes you to the Catalog section of the DB2 for i SQL Reference.

·         Understanding and Exploiting the DB2 Catalog

·         DB2 for i Catalog Views

Hopefully, this introduction to the DB2 Catalog will motivate you to find a way to leverage the DB2 catalog to make your job easier because I’m confident that is the case.

Merry Christmas and Happy Cataloging!

 

- Kent