Configuring Zowe Db2 Command Line Interface
{Core} The Open Mainframe Project’s Zowe command line interface (CLI) provides the ability for a developer to manipulate z/OS by issuing commands on their laptop. It does this by connecting to z/OS and issuing z/OSMF REST APIs. As well as providing base function, the CLI is also extensible allowing it to connect to other endpoints on z/OS including our favourite enterprise database Db2. This blog describes how to obtain, configure, troubleshoot, and use the Zowe Db2 CLI plugin. I’m fortunate enough to use a Mac as my go-to laptop so the article includes idiosyncrasies peculiar to MacOS, however the Zowe Db2 CLI works equally well on Windows, Linux, or any operating system that can run Node.js.
Pre-requisites
The Command Line Interface has some pre-requisites that need to be installed beforehand, see Software requirements for Zowe CLI plug-ins.
Before installing the DB2 CLI plugin on MacOS you will need have installed xcode, and you may also need to issue commands to update the xcode command line tools. These are covered more in the section XCode required on MacOS.
Before being able to successfully execute a DB2 CLI command you’ll need to have a DB2 connect license enabled. This can either be done on z/OS by a DB2 system administrator with the db2connectactivated command, or by each user of the CLI by copying the file db2consv_zs.lic to their laptop and placing it at a specific location within the Zowe CLI node_modules. These are covered more in the section SQL1598N: Db2 connect license
Installing the DB2 CLI plugin
Zowe CLI plugins are installed with the zowe plugins install <plugin_name> command. When I’m installing Zowe plugins I often forget the exact name, and a good way to retrieve them is to search npmjs.com for the keyword zowe db2 e.g. https://www.npmjs.com/search?q=zowe%20db2.
Now that we know the <plugin_name> is @zowe/db2-for-zowe-cli the command to issue is zowe plugins install @zowe/db2-for-zowe-cli . If everything goes well during the install you should see the message “This plugin was successfully validated” shown below.
In the screen shot above I ran zowe plugins install @zowe/db2-for-zowe-cli which picks up the latest version of the code. Today (January 2021) Zowe is within its version one long term support (LTS) release, however at some point this may change as Zowe moves to version 2 and beyond in which case the current driver may be at a different level. To ensure you get always get the LTS version it is safer/recommended instead to append @zowe-v1-lts to the plugin ID, for example zowe plugins install @zowe/db2-for-zowe-cli@zowe-v1-lts guarantees that the version one LTS plugin is installed.
If you have the message Enjoy the plugin then you’re all set and can skip to Creating a DB2 profile section below. The npm WARN messages can be ignored.
When you run the command zowe plugins install @zowe/db2-for-zowe-cli you are installing the latest code from npmjs.com which is the on-line repository for the Zowe plugins. You can also install the CLI from a local package if you can’t reach npmjs.com following the documentation instructions Installing from a local package.
XCode required on MacOS
When you install the DB2 CLI using npm you’re running node package manager. This is a recursive installation process where the top level packages reference their dependencies, which in turn contain further references, and so forth. Npm will resolve the full list of dependent packages and install the full suite of software required to run the DB2 CLI. If you get an error from npm failing to install a particular package it’s possible this isn’t a top level package failure but instead one required for a dependent package, an example of which can occur on MacOS if you get Failed at the ibm_db@2.7.1 install script. This occurs because Xcode is required by the underlying DB2 node packages, see https://docs.zowe.org/stable/user-guide/cli-db2plugin.html#installing.
Xcode is available on the Apple app store. (Disclaimer: check with an adult if you haven’t had your fourth birthday as indicated on the app store site).
Once you’ve installed Xcode, execute zowe plugins install @zowe/db2-for-zowe-cli. If you get a successful Enjoy the plugin message then all’s well, however it’s possible you may get a log that includes the message:
xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun
If you see the xcrun error then remove and re-install the command line tools by executing the two commands.
sudo rm -rf /Library/Developer/CommandLineTools
sudo xcode-select --installYou’ll be promoted for your MacOS desktop password when you run the sudo command, and when you run xcode-select --install you’ll be promoted to accept the license before the download occurs.
With xcode installed and, if required, the command line tools updated, you should be able to re-run zowe plugins install @zowe/db2-for-zowe-cli and get a successful install.
Creating a Db2 profile
Connectivity in the Zowe CLI is defined by profiles. A profile has a name (to help you identify it), the endpoint details (hostname, port and any other parameters to identify the service domain), as well as TSO credentials.
If you already know the port and location name of the Db2 database on z/OS you want to connect to then you’re doing great ! The Zowe docs describe how you can get this information from running — DISPLAY DDF in SPUFI, see Creating a Db2 user profile. My preferred approach for finding the information needed to create the CLI profile is to look at the MSTR job for the database and search for the message DSNL004I. For example, the Db2 instance I want to connect to is DI2E which has a JES job DI2EMSTR. Looking at the JES log I can see the line starting DSNL004I which contains the all important LOCATION, TCPPORT, and DOMAIN.
DSNL004I #DI2E DDF START COMPLETE 025
LOCATION DSNV102E
LU GBIBMIYA.IYCYZDBE
GENERICLU -NONE
DOMAIN winmvs2e.hursley.ibm.com
TCPPORT 40100
SECPORT 30100Using this information I can create a db2 profile called 2e.
zowe profiles create db2 2e --host winmvs2e.hursley.ibm.com --port 40100 --user MYTSOUSR --password MYTOUSERPASSWORD --database DSNV112ETesting the Db2 CLI
Every Db2 database has a table SYSIBM.SYSTABLES so a good “Hello World” is to just run a query to count up how many there are with the command zowe db2 execute sql --query "select count(*) from SYSIBM.SYSTABLES".
The Db2 CLI also lets you export tables and execute stored procedures. For a full list of all commands see the online documentation.
SQL1598N: Db2 connect license
If you get an error SQL1598N then this is because the Db2 connect license hasn’t been applied.
zowe db2 execute sql --q "SELECT count(*) FROM SYSIBM.SYSTABLES"
Command Error:
DB2 ODBC Driver Error: [node-ibm_db] SQL_ERROR
Error Details:
Error: [IBM][CLI Driver] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968
SQLCODE: -1598
SQLSTATE: 42968The CLI uses open database connectivity (ODBC) to connect to Db2. To make things all work correctly, a Db2 connect license file needs to be applied, either locally on the client (your laptop) or on z/OS itself.
z/OS Server db2connectactivate utility
To configure Db2 on z/OS to be able to accept ODBC client connections execute the db2connectactivateutility, see Server License Activation Utility. Once db2connectactivate is run against a database every client using the zowe db2 cli can connect to it without needing a client side license. Performing db2connectactivate is a task where you’ll need to contact your friendly neighbourhood z/OS Db2 system programmer and have them activate the license.
Client license activation
If the db2connectactivate utility has not been run on the z/OS DB2 database you’re connecting to, you can resolve this by copying the file db2consv_zs.lic (which is included within DB2 connect itself) into the zowe plugin directory structure at :
~/.zowe/plugins/installed/lib/node_modules/@zowe/db2-for-zowe-cli/node_modules/ibm_db/installer/clidriver/licenseIf you don’t have a copy of db2consv_zs.lic you’ll need to contact your friendly neighbourhood Db2 system programmer and they should be able to send you the one that came with their copy of DB2 Connect.
SQL0805N: Database BIND
To be able to run remote client SQL commands against a database it needs to have had a BIND command invoked against it. If the database hasn’t been bound then you’ll see the error SQL0805N .
Command Error:
DB2 ODBC Driver Error: [node-ibm_db] Error in ODBCConnection::QuerySync while executing query.Error Details:
Error: [IBM][CLI Driver][DB2] SQL0805N Package "DSNV112E.NULLID.SYSSH200.5359534C564C3031" was not found. SQLSTATE=51002
SQLCODE: -805
SQLSTATE: 51002The TSO user ID that does this needs to have BINDADD authority (strictly speaking this depends on the value of the subsystem parameter BINDND whose default value is BINDADD which could be different between sites). More information on the BIND command can be found in the DB2 knowledge centre documentation, as well as The Bind process. It’s likely that you’ll need to ask your friendly z/OS Db2 sysprog to do the BIND for you, and as they might be getting tired of you pinging them with requests be sure to promise you’ll buy them a cup of tea and pastry next time you’re together in their favorite neighborhood cafe.
Finding out more
If you enjoyed this blog checkout more Zowe blogs here. Or, ask a question and join the conversation on the Open Mainframe Project Slack Channel #Zowe-dev, #Zowe-user or #Zowe-onboarding. If this is your first time using the OMP slack channel register here.
Zowe is an open source project and all of the code for the DB2 CLI is available at https://github.com/zowe/zowe-cli-db2-plugin. The DB2 CLI documentation chapter is at https://docs.zowe.org/stable/user-guide/cli-db2plugin.html.
