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.