Tools for the Db2 for i DBE

In my prior entry, I reviewed the roles and responsibilities of a Db2 for i Database Engineer (DBE). This time around I want to highlight the tools that Db2 for i DBE should have in their toolbox.

The first tool in the list, IBM i Access Client Solutions (ACS), should come as no surprise. When it comes to Db2 for i SQL performance analysis and management, the ACS SQL Performance Center is the clear choice. This tool enables you to easily drill into the details of the longest running queries on your system with Visual Explain as well as get a reading on the overall health of SQL performance with the Plan Cache properties. 

 

In addition to supporting the creation & management of Db2 for i SQL objects, the ACS Schemas tool also houses the system-wide index advisor and index evaluator.  Index evaluator is not a term you’ll find within ACS, but it’s the label that I use to describe the “Work with -> Indexes” task you can access when right-clicking on a table object to quickly understand which indexes on your system are being used by the optimizer to improve query performance and which indexes are candidates for removal. 

 

The Schemas tool also serves as the launch point for the Db2 Health Center. The Health Center enables you to track how your database objects and activity are doing from the perspective of system and Db2 limits such as the maximum number of rows in table or the maximum size of a table.  My IBM Rochester colleagues recently released a new video highlighting how to access and use the Health Center.

 

The last ACS component to highlight is Run SQL Scripts.  This tool can be used to run SQL database creation scripts and provides access to Visual Explain for detailed SQL performance analysis. One can also launch a graphical debugger to debug procedural SQL objects such as functions and procedures.

 

Run SQL Scripts also facilitates interaction with the second DBE Tool – Db2 for i Services.  There are many services available to help with the managements of Db2 for i object management and performance. For example, there are services that can be used to regularly capture a plan cache snapshot which can be useful when trying to determine the cause of changes in SQL performance.  In fact, the Run SQL Scripts Edit pull-down menu makes it easy to find examples of these services in action so that you don’t have to start from scratch.

For the database modeling and design activities that I highlighted for a Db2 for i DBE, I think there are two viable modeling tools. Back in 2015 before I left for my 5 year stint working in IBM Watson Health, there was much a longer list of industry data modeling tools that I suggested to IBM i clients. After spending the last couple of months reviewing the Db2 for i support in industry data modeling tools, however, I found many of these tools have not kept current with all of the great enhancements made to Db2 for i.  As a result, my view is that IBM InfoSphere Data Architect and XCase for i are the only modeling tools that I would recommend Db2 for i.  The XCase support for Db2 for i also include other interesting data-centric tools that support test data generation and the anonymization of sensitive data. 

 

On the database security front, IBM Security Guardium is a tool that can be helpful to DBEs.  This tool can help find sensitive data in your Db2 for i databases and capture details on how the data in your Db2 for i databases is being accessed.

 

Good tools make any job easier by allowing you to complete tasks more efficiently. If you’re not using some of these DBE tools yet, then now is a great time to start and our Lab Services team can help you get off to a good start.