Ever since the mid-eighties the traditional Db2 for z/OS database administrators and developers have relied on 3270 terminals and/or terminal emulators for their primary user interface. IBM, as well as other vendors, have made attempts over the years at workstation-based administration and development tools. These have mostly been met with tepid response. Some from IBM that come to mind are:
- Control Center
- DB2 Estimator
- Visual Explain
- Optimization Service Center
- Optim Query Tuner
- IBM Data Studio
- Data Server Manager
I get the impression that IBM has been tossing these things against the wall over the years to see what sticks. Unfortunately, in my opinion, the effort hasn’t been strong enough and the result has been weak acceptance by the user base. With the constant “coming and going” of these tools the user base has been reluctant to install the “next great tool” when they believe it will not be widely adopted and discarded in short notice by IBM. The most recent attempts have been IBM Data Studio and Data Server Manager. Both of these tools are free to download and install, but for Db2 for z/OS they require a Db2 Connect license to facilitate a connection. IBM Data Studio had a rough start, but over the years it had developed a solid following with such capabilities as entity relationship modeling and DDL generation, Db2 catalog navigation, SQL construction and testing, query tuning, database management, REST service and stored procedure development and testing. Data Server Manager, in my opinion, was a failure for Db2 for z/OS administrators and developers with a less than impressive interface and limited features, when you could get it to actually work.
At this point in time IBM Data Studio is in “maintenance mode”, meaning they are only fixing errors (sometimes) and keeping it working with Db2 12 for z/OS. I have recently found it to be unreliable for DDL generation and lacking information for more recent Db2 for z/OS features, such as temporal tables and universal table spaces, and so caution should be exercised when using it for such purposes. Data Server Manager is being replaced by IBM Db2 Data Management Console, which for now is only for Db2 for LUW. IBM Data Studio functionality is eventually supposed to be replaced by the IBM Db2 for z/OS Developer Extension for VSCode (also should work with Eclipse Theia and other Language Server Protocol clients), at least for the Db2 for z/OS developer. A GUI tool supplied free by IBM to facilitate the Db2 administrator functionality that came with IBM Data Studio has yet to be announced, but I am personally expecting something before the end of 2021.
VSCode
VScode is a freeware source-code editor made by Microsoft for use in Windows, Linux, and macOS operating systems. The features include support for code editing, debugging, syntax highlighting, and embedded Git, amongst other features. It is lightweight and easy to install, but most importantly it is extensible, meaning the user can install a variety of extensions to increase the functionality. VSCode has been ranked as the most popular developer environment tool in a 2019 Stack Overflow survey. As of March 2021 there are close to 25,000 extensions in the VSCode Marketplace that can be used to improve the development process in a variety of languages and platforms. I personally have installed the IBM Db2 for z/OS Developer Extension, the Db2 Connect extension, a Python extension, and a Windows PowerShell extension. For IBM zSeries developers and administrators there exists a variety of extensions that can be used to support z/OS administration and development. However, they require Zowe and/or z/OSMF to provide access to z/OS resources.
I’m going to pause here for a second and address the Db2 Connect extension for VSCode. There are over 3,000 installs recorded and I have installed it myself. However, I have just recently been able to get it to work. The Db2 Connect extension touts connectivity and functionality for all Db2 servers (LUW, z/OS, iSeries). Having worked with both I can say that the Db2 for z/OS Developer Extension at this time has more overall features, including syntax checking and highlighting to validate the SQL syntax, stored procedure deployment and debugging, run SQL from anywhere, different commit/rollback strategy, and code completion/signature helper, amongst others. The Db2 Connect offers the advantage of connectivity to all Db2 servers, as well as an explorer feature and a result set exporter that will export in CSV, XML, or JSON. The Db2 for z/OS Developer Extension only offers result set exporting in CSV and has no explorer feature. I personally am using both!
Information about the Db2 VSCode extensions can be found here:
https://marketplace.visualstudio.com/items?itemName=IBM.db2forzosdeveloperextension
https://marketplace.visualstudio.com/items?itemName=IBM.Db2Connect
IBM is investing in development of VSCode extensions because the modern developer is not interested in a TSO/ISPF interface (I have personally worked with developers that would threaten to quit versus logon to TSO), and creating an extension is a relatively inexpensive way to support a GUI based developer interface to Db2 functionality. The VSCode footprint is smaller and loads much faster than IBM Data Studio.
The IBM Db2 for z/OS Developer Extension
The IBM Db2 for z/OS Developer Extension was first released in mid-2020 and has had several releases since that time. As of the writing of this article the current release is version 1.1.2, and features SQL syntax check and highlighting, Db2 connectivity, code completion, running SQL with commit control, running SQL from any type of file and source code, saving query output, and the ability to write, deploy, run, and debug stored procedures. The extension is still in its infancy, but is progressing nicely and is becoming a useful tool for SQL development and execution. New versions are being released every few weeks and include bug fixes and new features. I have begun recommending installation of this extension to my clients who have developers that have previously used IBM Data Studio for executing queries.
To install the IBM Db2 for z/OS Developer Extension you first have to install VSCode. The VSCode download can be found here:
https://code.visualstudio.com/download
Once installation is complete, I recommend first running through a tutorial or two to get used to the interface and basic functionality of VSCode. There are plenty of video tutorials available on the internet. Following that, you can simply download the IBM Db2 for z/OS Developer Extension from the extension marketplace within VSCode. The download should be seamless and fast, but I have found that there can be some difficulties downloading extensions when going through a company proxy server and/or firewall. Sometimes opening the developer tools (ctrl + shift + I) can get around this problem (I don’t know why). After the IBM Db2 for z/OS Developer Extension is installed it still needs to be configured. There are pretty thorough instructions for the configuration of the extension in the “Details” section of the connection information panel. Most importantly you’re going to need a license to connect to Db2 for z/OS. Another issue is that the extension uses JDBC, so you are going to need a Java SDK. I recommend the OpenJDK, and a link on the extension’s panel provides a direct link to the OpenJDK download page. It takes a little intuition to manage the JDBC setup, but basically the download will be a zip file. Unzip it and place the files somewhere on a local drive that’s convenient. Once that is done you need to tell the extension where the directory is for the JDK install, which is done by the db2forzosdeveloperextension.java.home setting. It is important that this is the directory of the install and not specifically the JRE executable. For example, on one of my workstations this is set to:
C:\Users\Dan.Luksetich\OpenJDK8
where this directory is the parent to the bin, jre, and lib directories, among others. After getting the extension configured you need to define the Db2 connections, which is very similar to typical configurations for IBM Data Studio or the Db2 Client configuration, including any secure connection information. Once this is all finished then you’re ready to write and execute SQL statements, including stored procedures, using the file explorer feature of VSCode. Since the extension is still in the early stages of development there are some “wish I had” features that just aren’t there yet. So, I’ve written a few queries to help myself and my developers navigate the system catalog and get some basic information they need to do their job. Here are some of those queries:
Db2 for z/OS Developer Extension assistance queries, click to expand
— Get a list of tables for a given schema
SELECT CREATOR, NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = :SCHEMA
AND TYPE = ‘T’;
— Get a list of columns for a table
SELECT C.TBNAME, C.COLNO, C.NAME, C.COLTYPE,
C.LENGTH, C.SCALE, C.NULLS
FROM SYSIBM.SYSCOLUMNS C
WHERE C.TBCREATOR = :SCHEMA
AND C.TBNAME = :TABNAME
ORDER BY C.COLNO;
–get detailed index information for a given table
SELECT I.TBNAME CONCAT ‘.’ CONCAT I.NAME
, CASE WHEN G.COLNO IS NOT NULL
THEN G.DERIVED_FROM
ELSE K.COLNAME END AS “COLNAME/EXPRESSION”
, COALESCE(G.KEYSEQ,K.COLSEQ) AS KEYSEQ
, CLUSTERING
, COALESCE(G.TYPENAME, C.COLTYPE) AS COLTYPE
, COALESCE(G.LENGTH,C.LENGTH ) AS LENGTH
, COALESCE(G.SCALE,C.SCALE) AS SCALE
, COALESCE(G.NULLS,C.NULLS) AS NULLS
, COALESCE(G.ORDERING,K.ORDERING) AS ORDERING
, I.UNIQUERULE
FROM SYSIBM.SYSTABLES T
INNER JOIN
SYSIBM.SYSINDEXES I
ON I.TBNAME = T.NAME
AND I.TBCREATOR = T.CREATOR
INNER JOIN
SYSIBM.SYSKEYS K
ON K.IXNAME = I.NAME
AND K.IXCREATOR = I.CREATOR
INNER JOIN
SYSIBM.SYSCOLUMNS C
ON C.TBNAME = T.NAME
AND C.TBCREATOR = T.CREATOR
AND C.NAME = K.COLNAME
LEFT OUTER JOIN
SYSIBM.SYSKEYTARGETS G
ON G.IXNAME = I.NAME
AND G.IXSCHEMA = I.CREATOR
AND K.COLSEQ = G.KEYSEQ
WHERE T.NAME = :TABNAME
AND T.CREATOR = :SCHEMA
ORDER BY I.TBNAME, I.NAME, COALESCE(G.KEYSEQ,K.COLSEQ)
WITH UR;
— Generate a SELECT statement for a table
WITH SEL(TBCREATOR, TBNAME, SECTION, COLNO, STMT)
AS
(
SELECT ”, ”, 1, 0, ‘SELECT’
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT TBCREATOR, TBNAME, 2, COLNO, NAME
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = :SCHEMA
AND TBNAME = :TABNAME
)
SELECT *
FROM (
SELECT S.STMT ||
CASE WHEN S.COLNO BETWEEN 1 AND (SELECT MAX(COLNO) – 1 FROM SEL)
THEN ‘,’ ELSE ” END ||
CASE WHEN S.COLNO = (SELECT MAX(COLNO) FROM SEL)
THEN ‘ FROM ‘ || S.TBCREATOR || ‘.’ || S.TBNAME || ‘;’ ELSE ” END
FROM SEL S
ORDER BY S.COLNO);
— Display the source of a native stored procedure.
— If you save the result set and open it in
— notepad the procedure text should be properly formatted.
— The output is limited to 32,704 bytes.
SELECT CAST(R.TEXT AS VARCHAR(32704))
FROM SYSIBM.SYSROUTINES R
WHERE R.SCHEMA = :SCHEMA
AND R.NAME = :TABNAME
;
Including and Beyond Db2
I have been experimenting with Db2 programs written in Python and Windows PowerShell. There are multiple extensions available for these languages and the Db2 for z/OS Developer Extension interacts with these extensions to highlight the SQL within the programming language and let you test it. Now, Python requires a shell to execute, but VSCode contains an integrated terminal with PowerShell as the default terminal on a Windows machine. This makes for easy testing of PowerShell and Python programs accessing Db2 for z/OS.
Support
The Db2 for z/OS Developer Extension is being developed by IBM Db2 development and they are extremely responsive to bug reports and requests from users. Communication with the development team is facilitated via GitHub.
https://github.com/IBM/db2forzosdeveloperextension-about
Conclusion
The Db2 community has been challenged lately with outdated management tools for the workstation. I am optimistic that the Db2 for z/OS Developer Extension for VSCode will help to close this gap, and by the end of 2021 provide a valuable tool for developers and application-minded database administrators with developing SQLPL procedures, SQL statements, and embedded SQL in a variety of programming languages. Now is a great time to get your feet wet and try out this technology, and it’s also a great time to reach out to IBM via GitHub and let your voice be heard! I am also hopeful to have a free workstation-based administration tool for Db2 for z/OS in some form, hopefully before the end of 2021.