Playing With CLPPLUS

I’ve played with the clpplus at least once before, but have generally thought of it as a tool created to satisfy those coming from Oracle and looking for Oracle-like features. One of the features I actually liked about Oracle in the class and certification tests that I took for it was the ability to specify values for an SQL statement stored in a file on execution. When I lamented the fact that DB2 doesn’t have this feature on twitter, @idbjorh was quick to remind me that such functionality is indeed available using CLPPLUS.

Starting CLPPLUS

I actually found this part annoying. Almost as annoying as connecting to an Oracle database. First off, I had not enabled TCPIP for my toy SAMPLE database on the Ubuntu VM where I do a lot of my playing. No need to, as I just connect locally for anything I’ve been testing thus far. CLPPLUS uses JDBC type 4 drivers and thus TCP/IP is required. So I set up TCP/IP like this:

$ db2set DB2COMM=TCPIP
$ db2 update dbm cfg using SVCENAME db2c_db2inst1

And finally by adding this line to /etc/services:

db2c_db2inst1   50000/tcp

That’s all pretty standard stuff, and it’s likely that for any real server implementation you’ll already have set it up. Port numbers can vary and should vary for production implementations for security reasons.

I also find it annoying to have to specify the full jdbc url to connect, when I’m used to just issuing db2 connect to SAMPLE. Well, there’s a workaround for that too. You can find details on that in Ian’s guest blog post A Tale of Two Connections, or in his IDUG article on the same topic.

Creating a File With SQL I Can Pass Parameters To

I went through several iterations to get what I wanted just passing the parameters in. This is what I ended up with:

select 
    i.lastused
    , substr(indname,1,20) as indname
    , substr(colnames,1,50) as colnames
    , fullkeycard as fullkeycard
    , card as table_card
    , decimal(clusterfactor,10,5) as clusterfactor
    , indextype
from syscat.indexes i 
    join syscat.tables t 
        on i.tabname=t.tabname 
        and i.tabschema=t.tabschema
where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur;
EXIT

And I have to execute it like this (output shown too):

$ clpplus -nw db2inst1/db2inst1@localhost:50000/sample @desc_indexes.clpplus DB2INST1 SALES
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL10054 
SQL authorization ID = db2inst1 
Local database alias = SAMPLE 
Port = 50000 

 
Original statement:where t.tabschema=upper('&1') and t.tabname=upper('&2') with
ur
New statement with substitutions:where t.tabschema=upper('DB2INST1') and
 t.tabname=upper('SALES') with ur

LASTUSED   INDNAME             
---------- --------------------
COLNAMES                                                    FULLKEYCARD
-------------------------------------------------- --------------------
          TABLE_CARD CLUSTERFACTOR INDEXTYPE
-------------------- ------------- ---------
0001-01-01 IX_SALES01          
+REGION                                                               4
                  41      -1.00000 REG      

I again find this annoying because running the query at the regular command line, I get exactly what I want for output – a fairly wide output that shows me everything in a nice format. So I have to add a line to get reasonable output:

SET LINESIZE 160
select 
    i.lastused
    , substr(indname,1,20) as indname
    , substr(colnames,1,50) as colnames
    , fullkeycard as fullkeycard
    , card as table_card
    , decimal(clusterfactor,10,5) as clusterfactor
    , indextype
from syscat.indexes i 
    join syscat.tables t 
        on i.tabname=t.tabname 
        and i.tabschema=t.tabschema
where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur;
EXIT

Now my output looks like this:

$ clpplus -nw db2inst1/db2inst1@localhost:50000/sample @desc_indexes.clpplus DB2INST1 SALES
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL10054 
SQL authorization ID = db2inst1 
Local database alias = SAMPLE 
Port = 50000 

 
Original statement:where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur
New statement with substitutions:where t.tabschema=upper('DB2INST1') and t.tabname=upper('SALES') with ur

LASTUSED   INDNAME              COLNAMES                                                    FULLKEYCARD           TABLE_CARD CLUSTERFACTOR INDEXTYPE
---------- -------------------- -------------------------------------------------- -------------------- -------------------- ------------- ---------
0001-01-01 IX_SALES01           +REGION                                                               4                   41      -1.00000 REG   

That’s better. Now let’s have some fun with the formatting options:

SET LINESIZE 160;
set headsep '!';
ttitle center 'Indexes for &1 . &2';
column lastused             heading 'Last!Used'
column indname      format a20  heading 'Index Name'
column colnames     format a40  heading 'Column Names'
column fullkeycard          heading 'Index!Cardinality'
column card             heading 'Table!Cardinality'
column clusterfactor    format 99.9999  heading 'Cluster!Factor' 
column indextype            heading 'Index!Type'
select 
    i.lastused
    , indname
    , colnames
    , fullkeycard
    , card
    , clusterfactor
    , indextype
from syscat.indexes i 
    join syscat.tables t 
        on i.tabname=t.tabname 
        and i.tabschema=t.tabschema
where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur;
EXIT

Which gives me:

CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL10054 
SQL authorization ID = db2inst1 
Local database alias = SAMPLE 
Port = 50000 

 
Original statement:where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur
New statement with substitutions:where t.tabschema=upper('DB2INST1') and t.tabname=upper('SALES') with ur

                                                                   Indexes for DB2INST1 . SALES                                                                 

Last                                                                                    Index                Table                Cluster Index    
Used       Index Name           Column Names                                      Cardinality          Cardinality                 Factor Type     
---------- -------------------- ---------------------------------------- -------------------- -------------------- ---------------------- ---------
0001-01-01 IX_SALES01           +REGION                                                     4                   41                -1.0000 REG      

Notice I was able to get the substr functions out of my SQL, and do the formatting though pure CLPPLUS commands. It did make my Cluster Factor column rather wider, though. My column headings are more natural English, with breaks where I want them.

I’ve added another index in my SAMPLE database to show off the next feature. This may be one that actually makes me want to use clpplus.

SET LINESIZE 160;
set headsep '!';
set wrap on;
ttitle center 'Indexes for &1 . &2';
column lastused             heading 'Last!Used'
column indname      format a20  heading 'Index Name'
column colnames     format a20  heading 'Column Names'
column fullkeycard          heading 'Indexr!Cardinality'
column card             heading 'Table!Cardinality'
column clusterfactor    format 99.9999  heading 'Cluster!Factor' 
column indextype            heading 'Index!Type'
select 
    i.lastused
    , indname
    , colnames
    , fullkeycard
    , card
    , clusterfactor
    , indextype
from syscat.indexes i 
    join syscat.tables t 
        on i.tabname=t.tabname 
        and i.tabschema=t.tabschema
where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur;
EXIT

And the output now looks like:

$ clpplus -nw db2inst1/db2inst1@localhost:50000/sample @desc_indexes.clpplus DB2INST1 SALES
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL10054 
SQL authorization ID = db2inst1 
Local database alias = SAMPLE 
Port = 50000 

 
Original statement:where t.tabschema=upper('&1') and t.tabname=upper('&2') with ur
New statement with substitutions:where t.tabschema=upper('DB2INST1') and t.tabname=upper('SALES') with ur

                                                                   Indexes for DB2INST1 . SALES                                                                 

Last                                                               Indexr                Table                Cluster Index    
Used       Index Name           Column Names                  Cardinality          Cardinality                 Factor Type     
---------- -------------------- -------------------- -------------------- -------------------- ---------------------- ---------
0001-01-01 IX_SALES01           +REGION                                 4                   41                -1.0000 REG      
0001-01-01 IX_SALES02           +SALES_DATE+SALES_PE                   41                   41                -1.0000 REG      
                                RSON+REGION+SALES+SE                                                                           
                                C_LABLE                                                                                        

I very much like the wrap option. I just wish I could tell it a charachter to wrap on. If I could wrap on each + or -, that would be some nifty output. I also like the wrap idea for when I’m querying mon_get_pkg_cache and want to see data in a tabular format, while still getting an SQL statement I can read. I just wish it would wrap on spaces there – I tried and it does not.

Here’s another neat thing you can do – prompt the user for input. Here’s the file I use:

SET LINESIZE 160;
set headsep '!';
set wrap on;
column lastused             heading 'Last!Used'
column indname      format a20  heading 'Index Name'
column colnames     format a20  heading 'Column Names'
column fullkeycard          heading 'Indexr!Cardinality'
column card             heading 'Table!Cardinality'
column clusterfactor    format 99.9999  heading 'Cluster!Factor' 
column indextype            heading 'Index!Type'

accept TABLE_SCHEMA prompt 'Table Schema:'
accept TABLE_NAME   prompt 'Table Name:'

define TABLE_SCHEMA
define TABLE_NAME

ttitle center 'Indexes for &TABLE_SCHEMA . &TABLE_NAME';

select 
    i.lastused
    , indname
    , colnames
    , fullkeycard
    , card
    , clusterfactor
    , indextype
from syscat.indexes i 
    join syscat.tables t 
        on i.tabname=t.tabname 
        and i.tabschema=t.tabschema
where t.tabschema=upper('&TABLE_SCHEMA') and t.tabname=upper('&TABLE_NAME') with ur;
EXIT

And the output I get:

$ clpplus -nw db2inst1/db2inst1@localhost:50000/sample @desc_indexes.clpplus 
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.


Database Connection Information :
---------------------------------
Hostname = localhost 
Database server = DB2/LINUXX8664  SQL10054 
SQL authorization ID = db2inst1 
Local database alias = SAMPLE 
Port = 50000 

Table Schema: DB2INST1
Table Name: SALES
DEFINE TABLE_SCHEMA = DB2INST1
DEFINE TABLE_NAME = SALES
 
Original statement:where t.tabschema=upper('&TABLE_SCHEMA') and t.tabname=upper('&TABLE_NAME') with ur
New statement with substitutions:where t.tabschema=upper('DB2INST1') and t.tabname=upper('SALES') with ur

                                                                   Indexes for DB2INST1 . SALES                                                                 

Last                                                               Indexr                Table                Cluster Index    
Used       Index Name           Column Names                  Cardinality          Cardinality                 Factor Type     
---------- -------------------- -------------------- -------------------- -------------------- ---------------------- ---------
0001-01-01 IX_SALES01           +REGION                                 4                   41                -1.0000 REG      
0001-01-01 IX_SALES02           +SALES_DATE+SALES_PE                   41                   41                -1.0000 REG      
                                RSON+REGION+SALES+SE                                                                           
                                C_LABLE                                                                                        

The lines in red were where it actually prompted me for the input. And the neat thing is that I can STILL specify the table schema and the table name when I execute the file – it will only prompt me if I do not specify them

Summary

I started out annoyed and I ended up having fun. I could have done most of this faster in ksh or perl, but the wrapping, I don’t know how to do off the top of my head. Will I keep using CLPPLUS? I doubt it – a combination of momentum and some odd issues that Ian reports in his post. It is tempting, though. I may try to use it more to see how it goes.

I’d love to hear your thoughts on clpplus if you’ve tried it, below in the comments.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

3 Comments

  1. i connected to my database via clpplus but whenever i run a command it returns nothing. it just goes to the next line with a 2.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.