Teaching Old Dogs New Tricks
Using CLPPlus as a Bridge between DBAs and Developers
Why We Started Using CLPPlus
Like many “traditional” DB2 users, the new command line facility (CLPPlus) has been on my list of things I have to learn for quite some time. Only recently I've started to use it. But the reasons for doing so aren't what you would expect. In this article I'm going to describe why I started using CLPPlus and then go through the basics of using the tool, assuming that many readers of this article will also be first time CLPPlus users.
The normal reasons for someone moving to CLPPlus are as follows -
- It is compatible with Oracle's SQL*Plus, and so people with an Oracle background find it easier to use
- It is much more powerful than the old Command Line Processor
These benefits had to be offset with a number of drawbacks –
- It is different from what DB2 people are used to
- It may be a challenge to get running, particularly on UNIX or Linux environments
Up until recently the drawbacks were outweighing the benefits in our organisation. That's until I had the task of trying to bridge the gap between two groups –
- The DBAs who manage database deployments into preproduction and production environments. They work on the database servers at the command line, with their editor of choice being vi
- The developers who produce stored procedures and work within Data Studio
The need for the bridge between these two groups came as we tightened up our deployment procedures. All changes to databases, whether they be DBA sourced (e.g. table changes) or developer sourced (e.g stored procedure changes) must now be managed through source control. Deployment into production must be automated and bundled into one deployment package.
Our starting point for this deployment package had to be a Data Development project from Data Studio. In recent versions of Data Studio (v4.x onwards) this isn't a problem since the source code for a stored procedure is now held in a file with a “.spsql” suffix (UDFs are similar, having a .udfsql suffix). In older versions of Data Studio stored procedure definitions were held in files with a “.spxmi” suffix and contained not only the source code but also a variety of related information inside an XML document.
The only minor problem we were left with was that the code inside the “.spsql” file doesn't have a terminator and CLP needs to have one (for CLP we'd always added a hash symbol at the end of stored procedure definitions and then ran the script using “db2 -td# -vf <filename>”). Making manual edits to files between them being checked into source control and being executed on the server during a deployment wasn't acceptable. This is the point where CLPPlus was able to help us …
Getting CLPPlus to Run
First investigations with CLPPlus took place on our Windows developer desktops, which had the full IBM Data Server Client installed. Starting CLPPlus was very easy in this case since there was a option on the DB2 menu to run it. However our deployments had to be run across a variety of UNIX (AIX and Solaris) and Linux platforms, both locally hosted and on cloud platforms such as Amazon AWS. Things are a little bit more difficult in these cases for two reasons –
- CLPPlus by default operates in a separate window and requires a working X environment to function. It is possible to avoid this by using the “-nw” (non-windowed) switch on startup. This does put some limitations on what can be done in CLPPlus, but nothing which stops our usage at present.
- CLPPlus, even in the non-windowed mode, relies upon a number of shared libraries which aren't installed by default (particularly on the base Linux installs available from Amazon AWS). These libraries relate to X, and are needed even when operating in non-windowed mode. The following packages were needed on a basic Amazon AWS Linux image before CLPPlus would operate correctly : libxrender1, libxft2, libxtst6 and libxi6
Some notes on diagnosing a failure in CLPPlus starting are probably in order. It should be noted that when CLPPlus encounters an error on startup it does not give any error messages. This is because the shell script which starts CLPPlus sets up the environment and then starts up Java, piping STDOUT and STDERR to /dev/null. To see what is causing the problem you will therefore need to edit the CLPPlus script temporarily to cause these outputs to be displayed. Note that copying the script to another directory and editing it will not work as there are internal assumptions about the relative position of the CLPPlus script to other resources needed for CLPPlus to operate (and that the script is actually called clpplus). So if you are doing this make a backup of clpplus in the same directory, so that you can put it back easily, and edit the clpplus script directly.
Basic Interactive CLPPlus Functionality
Once CLPPlus is started it is necessary to connect to a database before running SQL. Simply typing CONNECT and hitting <Enter> will provide a prompted interface with defaults for the required information.
db2inst1@mymachine:~$ clpplus -nwCLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
SQL> CONNECTEnter DATABASE NAME [SAMPLE]: MYDB
Enter HOSTNAME [localhost]:Enter PORT :
Enter ID: db2inst1Enter password: ********
Database Connection Information :
---------------------------------Hostname = localhost
Database server = DB2/LINUXX8664 SQL10054SQL authorization ID = db2inst1
Local database alias = MYDBPort = 50000
Once the “SQL>” prompt appears again it is possible to enter SQL for execution.
In the older CLP DB2 command line each SQL statement must be entered on one line unless you key a backslash (“\”) at the end of the line to allow continuation. In CLPPlus, SQL can be entered over as many lines as you wish without having to enter a continuation character at the end of each line. The SQL is only executed when a line ending in a semicolon (by default) as a statement terminator is entered.
SQL> SELECT2 TABNAME FROM
3 SYSCAT.TABLES4 FETCH FIRST 1 ROW ONLY;
If an error is made in an SQL statement, typing EDIT (or even just ED) will open the statement in a text editor for correction. It uses the default system editor (normally Notepad on Windows and vi on UNIX and Linux systems). Once the editing is complete save the file in the usual way. The edited command will be displayed at the SQL prompt. Type RUN to execute it. To view the current contents of the SQL buffer use the “LIST” command. To save the contents of the SQL buffer for future use, use the SAVE command specifying a filename e.g. “SAVE myfile.out”
If a simple error occurred on a command in the buffer, it is also possible to fix the command in the SQL buffer without invoking an editor using a CHANGE command, which uses a simplified form of regular expression substitution –
SQL> change /TABNAME/TABSCHEMA/1* select tabschema from syscat.tables;
However there seems to be a bug in this functionality on Linux, since it only works if the SQL is all on one line.
To pull a command into the buffer from a file use the GET command. To put it in and execute it in one command use START (or STA). This last command is important for our requirements going forward, for the simple reason that a single command in a file can be executed without having to specify an end terminator and this is the format that the .spsql files from Data Studio are in.
To obtain a list of available commands within CLPPlus type “HELP INDEX”. Then to obtain help on a specific command type “HELP <command>” (e.g. “HELP START”).
Finally to leave the CLPPlus session type QUIT.
Non-Interactive CLPPlus Execution
Our requirement is to execute a number of SQL scripts in turn from an automated build process, invoked as an operating system shell script. Here is a typical shell script which does exactly that –
#!/bin/sh# Run as instance owner db2inst1
# Input parameters# $1 : database name
# $2 : user ID# $3 : password
. $DB2_HOME/db2profileclpplus –nw $2/$3@localhost:50000/$1 @wrapper.clp
In this example we have parameterized the database name, execution ID and password. The various CLPPlus scripts to be executed are contained in the file wrapper.clp. Typical contents of this would be –
WHENEVER SQLERROR EXITSPOOL wrapper.clp.log
STA @SP001FIRST_SPROC.spsqlSTA @UDF_FIRST_UDF.udfsql
STA @test.sqlSPOOL OFF
A few things of note here –
- We use the “WHENEVER SQLERROR EXIT” command to stop if any errors are found
- The “SPOOL” command writes a log of the execution to a file. We stop logging using “SPOOL OFF”
- We always have to end the wrapper script with the QUIT command otherwise the execution will never terminate
- We use the STA(RT) command to invoke three scripts. In this example we have a stored procedure and UDF script, as produced by Data Studio, and a standard SQL script.
- Instead of STA @filename you can simply code @@filename, however we think that the first of these is more expressive.
There are a variety of options which you can use to control exactly how CLPPlus operates. In particular the WHENEVER command allows you to control exactly what happens in event of failures (both SQLERROR and OSERROR can be detected and acted upon).
Note that the clpplus command within the shell script can also simply be run from a command line. Often our DBAs will simply invoke the wrapper script manually, but leaving out the password from the entry so that it doesn’t appear in the full execution history they store from their shell tool –
db2inst1@mymachine:~$ clpplus –nw db2inst1@localhost:50000/MYDB @wrapper.clpCLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.Enter password: ********
Database Connection Information :---------------------------------
Hostname = localhostDatabase server = DB2/LINUXX8664 SQL10054
SQL authorization ID = db2inst1Local database alias = MYDB
Port = 50000
DB2500000I: The command completed successfully.
In this example the password is prompted for during the execution. Care should be taken when using this option not to attempt to pipe the output to a file, otherwise the command will appear to hang.
The Overall Environment
Now that we have found a way to execute scripts from Data Studio directly at a command shell without modification this enables both the developers and DBAs to work together in their own preferred environments and still share a common code repository.
Typically the developers create a (Data Development) project in Data Studio. This project is checked into source control, ensuring that the Data Studio project files are available to all users (Data Studio has three hidden files which contain the project information called .conInfo, .project and .projInfo). After this has been done the project can be checked out into other developer’s Data Studio sessions or into a directory on the server for updating, with everything being controllable via the source control system (we have used a variety of these including ClearCase, git and even Microsoft TFS).
CLPPlus provided the missing piece of the puzzle that brought together the two very different worlds. Better than that, it gave our DBAs a reason to learn more about CLPPlus, and they have now started to exploit some of its other features (which are beyond the scope of this article).
Ironically it has been the Data Studio side of the picture which has been more difficult to integrate. Data Studio does not recognize CLPPlus syntax. And each Data Studio project type has a predefined set of file extensions which it recognized. Anything with a different file extension which it doesn’t recognize isn’t shown within the project, and it is necessary to drop into the Eclipse Navigator (Window / Show View / General / Navigator) to view and edit these; this applies to our shell scripts and also to our CLP wrapper scripts which we gave a suffix of .clp (we could see these if we called them .sql, but they got flagged as in error because of the lack of CLPPlus syntax support).
Hopefully this short article has piqued your interest in CLPPlus, albeit by introducing it from a less than typical angle.