Db2 by the back door (part 1)

Introduction

65 million years ago, or so it seems to many, COBOL-programming dinosaurs roamed the earth. They accessed their Db2 data with highly efficient static SQL calls. They built their databases and tinkered with their data with SPUFI. All was well with the world. Then meteors struck their comfortable habitat. Meteors called “Java,” “Python,” and “REST.” ISPF gave way to Visual Studio Code. SPUFI to Command Line Processors. Their world changed forever…

There are now many methods of getting at your database and its data. Some of them might be alien to your traditional Db2 DBA on Z, yet strangely familiar to DBAs on LUW. Others might be too avant-garde to your boomer COBOL programmers, yet more orthodox to your pumpkin-spiced-latte-drinking millennial.

What this post attempts to do is introduce some of the newer and shinier methods of accessing your database to your common-or-garden Db2 on Z DBA and developer.

The Db2 Command Line Processor

Your LUW DBA would be as familiar with the CLP as your DBA on Z would be with DB2I. (My Content Committee Colleague, Emil Kotrc certainly is, and here is his blog to prove it!) This is your default method of running SQL, issuing commands like STOP, START, DISPLAY, BIND, FREE, DCLGEN. It is available for Z, too. It’s a Java program that runs under Unix System Services. It can be run interactively in a USS shell, or in batch via a script. It requires the following dependencies:

·         At least Java 1.4.2 (or 1.5 if you intend to use DEC FLOAT
·         An IBM Data Server Driver for JDBC and SQLJ
·         You have run the Db2 install jobs DSNTIJRT and DSNTIJRV for the Db2 metadata stored procedures
·         A modified .profile file, and a clp.properties file

The .profile file

I’ll walk you through a copy of my .profile...

These are your installation defaults. Wherever your Java home is, this is where it should go. The STEPLIB should be the datasets of the Db2/Z subsystem you want to attach to. The CLPPROPERTIES file is where your clp.properties file is located, and what it is called (you can call it anything you like).

I have added an alias because I don’t want to type java com.ibm.db2.clp.db2 each time I want to invoke it, so I type db2 instead.

The clp.properties file

This is where your rubber hits the road.

#Specify the value as ON/OFF or leave them blank
DisplaySQLCA=ON
AutoCommit=ON
InputFilename=
OutputFilename=
DisplayOutput=ON
StopOnError=OFF
TerminationChar=ON
Echo=ON
StripHeaders=OFF
MaxLinesFromSelect=100
MaxColumnWidth=40
IsolationLevel=UR
#Create your own alias name for DB2 servers
#SERVER=<ip:port/location>,<username>,<password>
DSN1=node1.mvs.local:7020/DSN,TSOUSR1,p4ssw0rd
DSN2=node2.mvs.local:7020/DSN,TSOUSR1,p4ssw0rd
DB2A=node1.mvs.local:7152/DB2A,TSOUSR1,p4ssw0rd
DB2B=node2.mvs.local:7152/DB2B,TSOUSR1,p4ssw0rd

The first lot is pretty self-explanatory, but the main caveat for me is TerminationChar. If it’s ON, then every statement must be terminated with a semicolon. If it’s OFF, then ENTER issues the command immediately. If you are building native SQL stored procedures, then TerminationChar=# would ensure that any semicolons in your procedure don’t get processed, and your statement must be terminated by the # symbol. (Hash, gate, but NEVER pound. That’s a £!)

The second section defines the Db2 subsystems you may connect to. For example, DSN1 is the alias you will be using in your session. It connects to data sharing group DSN on NODE1, with the userid TSOUSR1 and password “p4ssw0rd”. Bear in mind that the password is in plain text, so make the file read-only to yourself alone with a “chmod 700 clp.properties” command.

And you’re ready to go!

If you decide to try it out using TSO OMVS, bear in mind that you must have sufficient region size to your TSO address space, or you’ll get the following message:

TSO OMVS
===> db2  
JVMJ9VM015W Initialization error for library j9gc29(2): Failed to instantiate compressed references metadata.  200M requested
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

SSH works straight away.

You can get a list of available commands by issuing a “?”

$ db2

db2 => ?

ADD XMLSCHEMA DOCUMENT

BIND

CHANGE

CALL

CONNECT

COMMIT

COMPLETE XMLSCHEMA

DECOMPOSE XML DOCUMENT

DESCRIBE

DISCONNECT

DISPLAY RESULT SETTINGS

ECHO

LIST COMMAND OPTIONS

LIST TABLES

REGISTER XMLSCHEMA

REMOVE XMLSCHEMA

ROLLBACK

UPDATE COMMAND OPTIONS

TERMINATE

...oh, and any SQL statement, too!

db2 => connect to dejm;
DSNC103I : The parameter for option "-u" specified after the "db2" command is missing or incorrect.

Another thing to remember is the case of the alias in your CLP properties file. They MUST match!

db2 => connect to DEJM;
     Database Connection Information
 Database server        =DB2 DSN12015
 SQL authorization ID   =RDAMJD8C
 JDBC Driver            =IBM DB2 JDBC Universal Driver Architecture 3.72.54
DSNC101I : The "CONNECT" command completed successfully.

Operating in Batch mode is easy, too. Here is my input file, test.db2:

connect to DEJM ;
call admin_info_sysparm(NULL,?,?) ;
list tables for schema RDAMJD;
describe table department;
select * from department;
select count(*) as before from dsn_statement_cache_table ;
truncate dsn_statement_cache_table ;
explain stmtcache all;
select count(*) as after from dsn_statement_cache_table ;
terminate ;

and running it is as easy as

$ db2 –f test.db2 +o –z test.out

Your results file is now test.out.

Zowe CLI

To quote its website, Open Mainframe Project Zowe “offers modern interfaces to interact with z/OS and allows you to work with z/OS in a way that is similar to what you experience on cloud platforms today. You can use these interfaces as delivered or through plug-ins and extensions that are created by clients or third-party vendors.” And the CLI (Command Line Interface) “provides a command-line interface that lets you interact with the mainframe remotely and use common tools such as Integrated Development Environments (IDEs), shell commands, bash scripts, and build tools for mainframe development.”

In a nutshell, if you use the mainframe, ZOWE IS REALLY COOL!

z/OS Stuff

Here are some examples of what you can do on the Zowe CLI

C:\Users\mdavage> zowe zos-console issue command "D T“
 IEE136I LOCAL: TIME=09.27.51 DATE=2023.275  UTC: TIME=14.27.51 DATE=2023.275

C:\Users\mdavage> zowe files vw ds 'MVSMJD.MARCUS.JCL(IEFBR14)'
//MVSMJDA JOB (9999),
//  'MARCUS DAVAGE',
//  CLASS=A,
//  MSGCLASS=X,
//  MSGLEVEL=(1,1),
//  NOTIFY=MVSMJD
/*ROUTE XEQ BMCPLX1
/*JOBPARM SYSAFF=DB2B
// EXEC PGM=IEFBR14

C:\Users\mdavage> zowe zos-jobs sub ds 'MVSMJD.MARCUS.JCL(IEFBR14)'
jobid:   J0690669
retcode: null
jobname: MVSMJDA
status:  INPUT

C:\Users\mdavage> zowe zos-jobs ls js
J0690669 CC 0000 MVSMJDA  OUTPUT

C:\Users\mdavage> zowe zos-jobs ls sfbj J0690669
2 JESMSGLG   JES2
3 JESJCL     JES2
4 JESYSMSG   JES2

C:\Users\mdavage> zowe zos-jobs vw sfbi J0690669 3
        1 //MVSMJDA JOB (9999),
          //  'MARCUS DAVAGE',
          //  CLASS=A,
          //  MSGCLASS=X,
          //  MSGLEVEL=(1,1),
          //  NOTIFY=MVSMJD
        2 /*ROUTE XEQ BMCPLX1
        3 /*JOBPARM SYSAFF=DB2B
        4 // EXEC PGM=IEFBR14

Db2 Stuff

“What has any of this got to do with Db2?” I hear you ask. Zowe has a neat plugin for Db2, installation instructions here https://docs.zowe.org/stable/user-guide/cli-db2plugin/#installing-from-an-online-registry.

I had some installation issues which were solved by installing the IBM Db2 Data Server Client, acquiring a license from my IT Support, installing it with a db2licm -a .\db2consv_ee.lic 
and Bob’s your uncle!

C:\Users\mdavage> zowe db2 execute sql -q "select current timestamp from sysibm.sysdummy1"
Result #1
-
  1: 2023-10-03 07:27:58.127937

Of course, it cuts out a lot of typing if you set up your default parameters (host, database, port, user, password) in your initial zowe config init, first.

C:\Users\mdavage> zowe db2 execute sql -q "select creator, name from sysibm.systables where creator ='RDAMJD' and name like '%TABLE' fetch first 5 rows only"
Result #1
-
  CREATOR: RDAMJD
  NAME:    DSN_COLDIST_TABLE
-
  CREATOR: RDAMJD
  NAME:    DSN_DETCOST_TABLE
-
  CREATOR: RDAMJD
  NAME:    DSN_FILTER_TABLE
-
  CREATOR: RDAMJD
  NAME:    DSN_FUNCTION_TABLE
-
  CREATOR: RDAMJD
  NAME:    DSN_KEYTGTDIST_TABLE

If JSON is your thing, just tag --response-format-json afterwards, and you’re away!

{ "success": true,
  "exitCode": 0,
  "message": "",
  "data": [
    [ {"CREATOR": "RDAMJD",
          "NAME": "DSN_DETCOST_TABLE“},
      {"CREATOR": "RDAMJD",
          "NAME": "DSN_FILTER_TABLE“},
      {"CREATOR": "RDAMJD",
          "NAME": "DSN_PREDICAT_TABLE"},
      {"CREATOR": "RDAMJD",
          "NAME": "DSN_QUERYINFO_TABLE"},
      {"CREATOR": "RDAMJD",
          "NAME": "DSN_STATEMENT_CACHE_TABLE"}
    ]
  ]
}

Invoking stored procedures are possible, too…

C:\Users\mdavage> zowe db2 call sp "admin_info_sysparm(NULL,?,?)" -p 0 0

- 0
- null
-
  -
    ROWNUM:           1
    MACRO:            DSN6SYSP
    PARAMETER:        AUDITST
    INSTALL_PANEL:    DSNTIPN
    INSTALL_FIELD:    AUDIT TRACE
    INSTALL_LOCATION: 1
    VALUE:            00000000000000000000000000000000
    ADDITIONAL_INFO:  ONLINE=N
  ---- Etc…

A little feature that’s also available it the ability to export table data using
zowe db2 export table MVSMJD.T1 --outfile mvsmjd.t1
However, this feature has a “feature” whereby you have to specify the table name in UPPERCASE on the command line if you want to get anything back at all. And woe betide anyone who has Db2 tables with lower or mixed case names!

These are a few options for the keyboard warrior to get at his or her Db2 data. For those more used to pointing and clicking, I’ll suggest some more soon…