Friday, October 29, 2021

Update on No-Charge IBM Tools for Db2 for z/OS

Recently there has been important news regarding no-charge IBM tools for application developers and for database administrators who work with Db2 for z/OS. This news has to do with strategic replacements for the development and administration features of IBM Data Studio and IBM Data Server Manager in a Db2 for z/OS environment. I want to use this blog entry to highlight two newer offerings in the no-charge IBM Db2 for z/OS tools space.

First, some background.

The original (and still there) no-charge IBM tooling for Db2 for z/OS

I've been working with Db2 for z/OS since about 1987 (Version 1 Release 2), and as far back as I can remember there has been no-charge IBM-provided tooling for users of the DBMS. I say "tooling" instead of "tools" because I'm referring here not to separate products but to the tooling that was - and still is - provided with Db2 for z/OS itself. Probably the best known of this "comes with Db2" tooling is the set of capabilities provided by DB2I (short for "Db2 interactive"), the panel-driven feature set that runs in a TSO/ISPF environment and is accessed via a 3270 terminal emulator (or, back in the day, by way of an actual 3270 terminal). DB2I functions that have long been useful to Db2 administrators and to application developers working with Db2 include SPUFI (SQL processor using file input), an interactive means of issuing dynamic SQL statements and reviewing associated output; DCLGEN (declarations generator), for generating structure definitions (for C and PL/I programs) and record descriptions (for COBOL programs) associated with Db2 tables and views that will be referenced in program code; binding and rebinding of plans and packages; a Db2 utility invocation mechanism; and an interface for interactive issuance of Db2 commands (though not commands of the DSN variety). 

To this day I use DB2I regularly, but it was apparent some time ago that a newer generation of developers and administrators wanted new ways of working with Db2 for z/OS, and that some no-charge tooling with basic capabilities was still required in addition to the advanced-function for-fee tools available from IBM and other vendors. An initial response to this "new way, no-charge" need was IBM Data Studio, launched a little over 10 years ago.

No-charge GUI tools: IBM Data Studio, and then IBM Data Server Manager

IBM Data Studio is a download-able, workstation-based tool built on the Eclipse platform. It provided a GUI for Db2 for z/OS that delivered a number of features useful to developers and to administrators. Of particular value to many developers was Data Studio's debug functionality for routines written in SQL Procedure Language, aka SQL PL (examples of such routines are Db2 native SQL procedures, compiled SQL scalar functions and advanced triggers). I personally found Data Studio to be very helpful for testing stored procedure calls, and for retrieving non-traditional Db2 data values such as CLOBs and XML documents (I really appreciated Data Studio's nice formatting of XML documents returned from Db2). Data Studio also evolved to be the GUI front-end for many of IBM's fee-based Db2 for z/OS tools, and for the Db2 Analytics Accelerator, and the resulting look-and-feel commonality was a productivity booster.

As helpful as Data Studio was, the tool came to be more popular with developers than Db2 administrators. In particular, the Eclipse-based interface, familiar to many application programmers, didn't win a lot of fans among DBAs. Over time, it became more clear that another no-charge GUI tool for Db2 for z/OS, more purpose-built for administrators, was needed. That administrator-first tool was IBM Data Server Manager, or DSM. In addition to the admin-friendly interface, DSM had the advantage of browser-based user interaction, simplifying product installation and management.

Even with no-charge tools aimed at the developer and administrator constituencies, it still seemed that we weren't quite where we wanted to be in the Db2 for z/OS space. That changed with the advent of IBM Db2 for z/OS Developer Extension for Visual Studio Code, and IBM Db2 Administration Foundation for z/OS.

A newer no-charge tool for developers: IBM Db2 Developer Extension for VS Code

Visual Studio Code (aka VS Code) is a no-charge and very widely used IDE (integrated development environment), and it's the base for the IBM Db2 for z/OS Developer Extension (actually, one base - the Db2 Developer Extension is also available for Eclipse Theia, and other popular IDEs will likely follow). There are two ways to get the Db2 Developer Extension for VS Code:

  • From the Visual Studio Marketplace on the Web.
  • From within VS Code itself: in a VS Code session, go to the Extension view and type in “ibm z” - the Db2 Developer Extension should be at the top of the resulting list (see the screen shot below).

The Db2 Developer Extension has a lot of features that can be helpful for people writing SQL statements and routines that will target Db2 for z/OS-managed data, including:

  • SQL statement formatting
  • SQL syntax checking (including XML syntax checking)
  • SQL coding accelerators, like code completion and code snippets
  • SQL PL routine debugging (I highlighted this one because it's really important to a lot of people - this functionality was previously only available with Data Studio)
And there's more, and more to come. I encourage you to download and use the Db2 Developer. Extension, and I also recommend checking out the Db2 Developer Extension repository on GitHub - there, you can get lots more information about the Developer Extension, submit requests for enhancements and check out enhancement requests submitted by others (many of which have already been acted upon by the IBM Db2 Developer Extension team).

A newer no-charge tool for administrators: IBM Db2 Administration Foundation for z/OS

The Admin Foundation came out just this past summer. It's a licensed IBM product (the product ID is 5698-ADF), but there is no charge associated with the license (there's an optional service and support charge). Admin Foundation runs entirely in z/OS - it has a browser-based interface that leverages the Zowe virtual desktop.

Key features of IBM Db2 Administration Foundation for z/OS include the following:

  • Object dependency information
  • Generate DDL for an object from information in the Db2 catalog
  • SQL editor
  • Visual explain
  • Single-query tuning services (including statistics advisor)
  • Issue Db2 commands - including DSN commands (I highlighted this one because plenty of GUI tools let you issue SQL statements but not Db2 for z/OS commands, and Admin Foundation does DB2I one better by enabling issuance of Db2 commands of the DSN variety)
The screen shot below shows the object hierarchy information that can be displayed via Db2 Administration Foundation: 

Here's the bottom line: Developer Extension for VS Code and Administration Foundation are great additions to the no-charge IBM Db2 for z/OS tools scene. Check them out, and ride the new wave.


  1. I think you might have passed over the most underrated no charge tool - Visual Explain. The original was a simple standalone executable that would run on your workstation, and utilize the small set of explain tables in the Db2 system you were connected to. It provided a great way to review performance which to some DBAs is important. IBM went on to embed this wonderful little tool into a number of bloated and memory consuming applications - from Optim, to Data Studio, to DSM. None were as simple to run or roll out to development teams. Now we have to get ZOWE installed on our box, then install a new tool (VS), plus the necessary extensions. Not to mention that some enterprises have implemented strict controls over what can be installed on a workstation and by who. I appreciate the post, but as someone who has fought with IBM's scatter-brained approach to these tools I feel like they were not developed with simplicity in mind.

    1. Hello, Robert.

      It was not my intention to pass over Visual Explain. Visual Explain functionality is included in both the Db2 for z/OS Developer Extension for VS Code and Db2 Administration Foundation for z/OS. I understand that you would like for Visual Explain to again be a standalone tool. I do not know if that will happen at some future time - you can always make that request at the IBM Db2 for z/OS request for enhancements site (

      As for the Db2 Developer Extension for VS Code requiring VS Code itself, that is a very widely installed IDE. True, an organization might not permit VS Code to be installed on an organization-owned workstation. As noted in the blog entry, Eclipse Theia is another option as the base for the Db2 Developer Extension, and other popular IDEs are under consideration to also be a base for the Db2 Developer Extension. The aim is to go with the Db2 Developer Extension to the environments in which large numbers of developers already operate.

      As for Administration Foundation for Db2 for z/OS, the objective was a z/OS-only installation that would then make the functionality available within an enterprise by way of a Web browser interface; so, no requirement to install anything on a workstation (assuming that a browser's already there or can be installed).