Command Line Processor on DB2 for z/OS

One of my colleagues once asked me whether it is possible to run a DESCRIBE TABLE command in DB2 for z/OS similarly as he was used to in database management systems (DBMS) on platforms other than IBM mainframe. By describing a table he meant getting a list of columns of a table together with the data types. After he looked into the SQL reference and found out that DESCRIBE TABLE can only be used as an embedded statement in an application, he was lost as there was no obvious option in DB2I ISPF panels.

My first answer was suggesting the use of a vendor tool that would describe the table as he would expect. But, wait a second. In the other DBMS you would usually use a command line processor to describe a table and DB2 for z/OS has a command line processor too!

This blog post will introduce the command line processor (CLP) for DB2 for z/OS and will show how you can take advantage of it.

Prerequisites

The command line processor for DB2 for z/OS is an optional component of DB2. It is implemented in Java and runs under UNIX system services (USS). The previous sentence summarizes the prerequisites for running CLP. You must have Java available on your system, your DB2 driver for JDBC and SQLJ must be configured, and of course you must be able to log in to USS. If anything is missing, you must first ask your system administrator or start the implementation; depending on your role.

For more details about installing and configuring CLP, refer to the Installation guide.

Setting up CLP

OK, you know that all requirements for CLP are met and you want to run it. If your system administrator has prepared a script for running CLP, you can skip the rest of this section and continue with the next one. Otherwise, you can set up your starting script as follows.

You have to gather some information at the beginning. First, you need to know the DB2 USS installation path. Let’s say it is /usr/lpp/db2b10 and let’s label it as a variable DB2HOME, i.e. DB2HOME=/usr/lpp/db2b10. Then define another environmental variable CLPHOME=$DB2HOME/base pointing to the installation base directory.

The second thing you need to know is your Java installation path, let’s say it is JAVA_HOME=/usr/lpp/java. (btw. have you ever wondered what does “lpp” stands for? It is Licensed Program Product). Now, you can put those things together.

The CLP main class is stored in clp.jar file under the $CLPHOME/lib directory. What you need to do is basically to include clp.jar into your CLASSPATH, make sure that your CLASSPATH includes the JDBC driver, and execute com.ibm.db2.clp.db2 Java class. That’s it.

With all these information in hand, update the .profile file in your USS home directory (or .bashrc depending on your default shell, or you can even create a standalone script) with the following content:

export DB2HOME=/usr/lpp/db2b10

export CLPHOME=$DB2HOME/base

export CLASSPATH=$CLASSPATH:$CLPHOME/lib/clp.jar

export CLASSPATH=$CLASSPATH:$DB2HOME/jdbc/classes/db2jcc4.jar

#export CLPPROPERTIESFILE=$HOME/clp.properties

export JAVA_HOME=/usr/lpp/java/

export PATH=$PATH:$JAVA_HOME/bin

alias db2="java com.ibm.db2.clp.db2"

 

Here is a brief explanation of each line:

●     DB2HOME defines the path to the DB2 USS installation directory

●     CLPHOME defines the path to the base directory of the DB2 installation

●     the first CLASSPATH line appends the clp.jar

●     the second CLASSPATH line appends the JDBC driver db2jcc4.jar. You don’t need this second CLASSPATH statement if your CLASSPATH already includes the JDBC drivers, but it shouldn’t hurt. (btw. did you know that “jcc” stands for Java Common Connectivity?)

●     CLPPROPERTIESFILE defines an optional properties file. As you can see it is currently commented out, so it won’t be used, we will describe and create a clp.properties file in a later section.

●     JAVA_HOME points to the Java installation directory and the PATH environmental variable on the next line is updated to include the Java path. Again, this might already be configured in your system.

●     the last line defines an alias db2 that you will use to execute CLP

●     if you put the lines into your .profile, you are ready to go.

●     if you decided to create your own startup script instead of updating .profile, then you don’t need the alias, but you can replace the last line as:

java com.ibm.db2.clp.db2

You would need to set the execution bit of the script in order to continue. 

IBM documents how to startup CLP in a $CLPHOME/README_CLP document and in the Installation guide. Make sure to review the latest documents; this blog is based on DB2 11.

Running CLP

All is set, so you just need to re-logon to USS and type db2 to start CLP. If everything goes well, you should get the CLP prompt:

db2 =>

If you type ? and hit Enter, you get a list of supported commands.

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

If you want to learn more about each command, just type ? followed by the command name to get help, or see the IBM reference. The TERMINATE command terminates CLP and returns you to the USS shell.

You’ve probably noticed the DESCRIBE command. Now, we are ready to go back to the original question: how to describe a table? At first you need to CONNECT to the DB2 subsystem. Let’s start CLP again and type:

db2 => connect to server:port/location user username using password

You must provide the text in bold. username and password are obviously your credentials. server, port, and location are the properties you use to connect to your system via DDF. You can get these information using -DISPLAY DDF DB2 command.

For example assume the server is server.example.com, port is 1234, and location is DSNLOC1. Then enter the following CONNECT command:

db2 => connect to server.example.com:1234/DSNLOC1 user username using password

after hitting Enter you should get the following response from CLP:

 Database Connection Information
 Database server        =DB2 DSN11015
 SQL authorization ID   =user
 JDBC Driver            =IBM Data Server Driver for JDBC and SQLJ 4.15.105
DSNC101I : The "CONNECT" command completed successfully.

The last line indicates that the connection has been established.

(Please note that it is possible to create an alias for a connection, so that you don’t need to type all the information for the CONNECT statement all the time. Aliases will be discussed in the Configuring CLP section.)

Before describing a table I would recommend setting an output width to a reasonable value, otherwise your output could be too wide. You can set the column width using CHANGE MAXCOLUMNWIDTH:

db2 => CHANGE MAXCOLUMNWIDTH TO 14
DSNC101I : The "CHANGE" command completed successfully.

Now, let’s finally invoke the DESCRIBE command for an IBM sample table:

10-30-2014 11-07-53 AM.jpg

In the output you can see all the columns of the sample table EMP, you can see the type of each column, and an indicator whether the column is NULLable. This could be the happy end of our story, but wait, CLP can do more!

More CLP features

Let’s have a look at few other interesting CLP features.

One of the most useful features of CLP is that you can use it for entering SQL; just like in SPUFI or DSNTEP2. Let’s try a simple SELECT:

db2 => select * from sysibm.sysdummy1
IBMREQD
Y
  1 record(s) selected

That’s great, isn’t it?

What if you wanted to list all tables for a given schema? Sure, you can do it by a simple SELECT from SYSIBM.SYSTABLES, or you can use some other tool. But you can utilize CLP as well, have a look at LIST TABLES:

db2 => list tables for schema dsn81110

TABLE_SCHEM    NAME           TABLE_TYPE

DSN81110       ACT            TABLE

DSN81110       DEPT           TABLE

DSN81110       EMP            TABLE

DSN81110       EMPPROJACT     TABLE

DSN81110       VACT           VIEW

DSN81110       VDEPT          VIEW

DSN81110       VEMP           VIEW

DSN81110       VEMPPROJACT    VIEW

Here we got a list of tables and views for IBM samples. (the output has been shortened)

Do you know that you can even call a stored procedure in CLP? Let’s start with ADMIN_INFO_SSID procedure, that just displays the name of the attached system

db2 => call sysproc.admin_info_ssid(?, ?, ?)

Value of output parameters

--------------------------

Parameter Name : DB2_SSID

Parameter Value : DSN

Parameter Name : RETURN_CODE

Parameter Value : 0

Parameter Name : MSG

Parameter Value :

DSNC101I : The "CALL" command completed successfully.

Please note that you enter question marks (?) as the output parameters and they are printed out by CLP. The returned SSID is DSN in this example.

Now, let’s move to something more useful. You can even execute DB2 commands from CLP using ADMIN_COMMAND_DB2 stored procedure, for example let’s see DISPLAY LOG:

db2 => call sysproc.admin_command_db2('-dis log', 8, NULL, NULL, ?, ?, ?, ?, ?, ?, ?, ?)

Value of output parameters

--------------------------

Parameter Name : CMD_EXEC

Parameter Value : 1

Parameter Name : IFCA_RET

Parameter Value : 0

Parameter Name : IFCA_RES

Parameter Value : 0

Parameter Name : XS_BYTES

Parameter Value : 0

Parameter Name : IFCA_GRES

Parameter Value : 0

Parameter Name : GXS_BYTES

Parameter Value : 0

Parameter Name : RETURN_CODE

Parameter Value : 0

Parameter Name : MSG

Parameter Value : null

ROWNUM      TEXT

1           DSNJ370I  ]DSN DSNJC00A LOG DISPLAY

2           CURRENT COPY1 LOG = DSN.LOGCOPY1.DS01 IS 89% FULL

3           CURRENT COPY2 LOG = DSN.LOGCOPY2.DS01 IS 89% FULL

4                     H/W RBA = 000000000243F63A0535

5                     H/O RBA = 000000000243C28BDFFF

6                     FULL LOGS TO OFFLOAD = 0 OF 6

7                     OFFLOAD TASK IS (AVAILABLE)

8           DSNJ371I  ]DSN DB2 RESTARTED 14:44:09 SEP  3, 2014

9                     RESTART RBA 0000000002438840B000

10                    CHECKPOINT FREQUENCY 500000 LOGRECORDS

11                    LAST SYSTEM CHECKPOINT TAKEN 19:47:50 SEP  3, 2014

12          DSN9022I  ]DSN DSNJC001 '-DIS LOG' NORMAL COMPLETION

  12 record(s) selected

DSNC101I : The "CALL" command completed successfully.

 

You can still do more with CLP, particularly,  you can BIND your packages in USS or you can manipulate the XML schemas. For more details, refer to the documentation.

Configuring CLP

When we were setting up CLP in the second section, there was a mention about an environmental variable CLPPROPERTIESFILE pointing to a CLP properties file. The properties file is used to define the CLP options so that you don’t have to configure CLP after each invocation. You can find the description of all available options in the CLP properties reference; I would point out just few of them:

●     MaxColumnWidth - we have already used this one. Set it to a reasonable value, the default is 500.

●     AutoCommit - The default is ON and this might not be what you want under certain scenarios. ON means that CLP automatically commits SQL statements.

●     Alias - lets you defining aliases in the form of ALIAS=connection-url,username,password. The aliases are particularly very useful, because they define shortcuts for DB2 connections.

Let say you created your clp.properties file with the following content:

MYALIAS01=server1.example.com:1234/DSNLOC1,username,password

MYALIAS02=server2.example.com:5678/DSNLOC2,username,password

MaxColumnWidth=14

Make sure, you’ve setup CLPPROPERTIESFILE variable to point to that file (just uncomment the corresponding line in the above .profile file) and after starting CLP you should be able to connect to DB2 using the alias:

db2 => connect to MYALIAS01

 Database Connection Information

 Database server        =DB2 DSN11015

 SQL authorization ID   =username

 JDBC Driver            =IBM Data Server Driver for JDBC and SQLJ 4.15.105

DSNC101I : The "CONNECT" command completed successfully.

 

You can always check your current CLP settings using LIST COMMAND OPTIONS and update them dynamically using UPDATE COMMAND OPTIONS and CHANGE commands. Also note that IBM provides a sample clp.properties file in $CLPHOME/base/samples/clp.properties.

Command line processor in batch mode

So far we have used CLP in the interactive mode, but you can also use it in the batch mode. If you save your SQL and CLP commands to the test.sql file, you can submit this file from the USS prompt using db2 -f test.sql.

For example, have a test.sql containing the following lines:

connect to MYALIAS01

select * from sysibm.sysdummy1

Execute the commands using:

$ db2 -f test.sql

And you get the following output:

 Database Connection Information

 Database server        =DB2 DSN11015

 SQL authorization ID   =user

 JDBC Driver            =IBM Data Server Driver for JDBC and SQLJ 4.15.105

DSNC101I : The "CONNECT" command completed successfully.

IBMREQD

Y

  1 record(s) selected

Please note that the default line termination for the batch file is a new line character, but if you want to use semicolon, set it by -t option. See more details about running CLP in the IBM reference.

Conclusion

CLP can be a good alternative to SPUFI and DSNTEP2, but it can help with other tasks as well. Plus, if you are coming from the distributed world, you are probably already familiar with a command line processor - now you can work with CLP even in DB2 for z/OS. CLP can be just another gate to DB2 for z/OS.

 

References

●     Command line processor reference

●     Command line processor tutorial

●     Configuring command line processor

●     Command line processor properties file

1 Like
Recent Stories
Introduction to Db2 for z/OS System Profiles

10 questions to review and understand your active log data set configuration

DB2 for z/OS query support over FTP