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.
[…] Playing With CLPPLUS […]
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.
I wonder if SPOOL is set somehow? That sends output to a file. TIMING may also cause you to get the time instead of the output. Check this out for some more detailed info: http://www.db2dean.com/Previous/clpPlus.html