Basic scripting tricks for DB2(LUW)

There are a couple of scripting tricks that I always teach when I’m training a new dba. There are some areas where we have to iterate over something that db2 doesn’t provide us with the tools to iterate.

Writing statements with SQL

I imagine most dbas have used this one before. I’ve mostly used it on Windows where I can’t use my ksh. Basically, you can write the SQL to write statements for you. The example I’m going to use is dropping staging triggers – something that’s useful for those using WebSphere Commerce. Basically the idea is that I need to drop every trigger that starts with ‘STAG%’. DB2 obviously won’t let me do “drop trigger where name like ‘STAG%’, so I have to iterate over the list. Here’s the general idea:

Obviously, you can use this for much more detailed statements too, with more complicated SQL. For example, if you need to grant select permissions only on every table and view in the database (dataaccess is great for select, update, insert, delete, but there’s still no database level permission that I’m aware of that does ‘read only’ style access):

> db2 -x "select 'grant select on table ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' to group db2sel;' from syscat.tables where type in ('T','V') with ur" >tab_and_view_sel_grants.sql
> head tab_and_view_sel_grants.sql
grant select on table SYSIBM.SYSTABLES to group db2sel;
grant select on table SYSIBM.SYSCOLUMNS to group db2sel;
grant select on table SYSIBM.SYSINDEXES to group db2sel;
grant select on table SYSIBM.SYSVIEWS to group db2sel;
grant select on table SYSIBM.SYSVIEWDEP to group db2sel;
grant select on table SYSIBM.SYSPLAN to group db2sel;
grant select on table SYSIBM.SYSPLANDEP to group db2sel;
grant select on table SYSIBM.SYSSECTION to group db2sel;
grant select on table SYSIBM.SYSSTMT to group db2sel;
grant select on table SYSIBM.SYSDBAUTH to group db2sel;

The main gotchas on this are the typical scripting ones – make sure you’re putting spaces in the correct places within your single quotes, and use functions like rtrim to get rid of extra spaces.

Of course the resulting files can then be executed with db2 -tvf

Update on February 7, 2012:

Thanks to a reader who commented, I can share with you a way of directly executing the queries rather than writing them to a file and then executing the file. His blog entry on it is here: http://angocadb2.blogspot.com/2011/12/ejecutar-la-salida-de-un-query-en-clp.html The key is the +p command line option. I had some inconsistent results – it didn’t seem to return for larger result sets, but maybe I just didn’t wait long enough. It’s definitely something I’ll be playing with. Here’s a sample syntax that could go with it:

Looping in KSH (Linux/Unix only)

This is actually my favorite method, and something I use at least once a week for those one-off tasks that I don’t already have a script for. I do it all at the command line, but you can also pop it into a simple script. I say KSH because that’s my favorite shell – though I do this in BASH a fair amount, too.

The idea here is to pop the list you want into a file, then iterate through it one item at a time. I’m going to use exactly the same examples as I did above – first generating the statements to drop all triggers that start with ‘STAG’:

Your connect statement must be inside the while/do loop. You cannot connect before and then loop through the commands. I don’t know why, but this happens frequently with shell scripting.

You want to make sure to use the -v on the db2 command so that if you get a statement failure, you know what statement failed.

It took me about a year before I could get that first semi-colon in the right place without copying and pasting, so don’t feel bad if you’re copying and pasting to get things right for a while.

Update on February 7, 2012:

Thanks to a reader who commented, I have a better way of doing this – avoiding the connect in every loop. What happens is the pipe creates a new shell process which is not connected to the original shell process that has the connection. This makes complete sense to me, as I’ve been playing with some parallelism in my Perl scripting lately. Here’s some alternate syntax which avoids this issue and should be much faster. I tested it myself, before sharing it, of course.

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

$for TRIGNAME in `awk ‘{print $1}’ trig.list`; do db2 -v “drop trigger wscomusr.$TRIGNAME”; done |tee trig_drops.out
drop trigger wscomusr.STAG0001
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1024N A database connection does not exist. SQLSTATE=08003

It looks very interesting your proposition to execute a set of commands in batch. However, you could eventually eliminate the temporary file, and you just execute the DB2 output in another DB2 environment. Here, I give an example:

Great option, and one I haven’t seen before. The only thing I would do if I were running it is to first run the query to make sure it gave the results I expected – would hate to have a damaging syntax error get executed.

There is a limit when executing with -x, and it is the pipe limit. It cannot be changed. When you execute something with -x | +p -tv and it does not show the results immediatly, it means that the buffer limit was reached. You have to kill the command (ctrl + c) and try a query with a smaller output.

Does anyone have a working example of a db2 shell script that reads multiple columns from a table via a cursor. Whatever I’m trying to do here is definitely not working for me. My host variables are not being populated. Their values are getting lost between shell processes. Somewhere out there I believe there is an example – help me.

Hi
I’ve got a questin for you. Is it possible to run a scipt sql in DB2 in a similar way that Oracle does? I can’t fond a solution to my problem:
For example:
CREATE OR REPLACE VARIABLE myVar VARCHAR(50);
SET myVar = ‘perico’;
SELECT * FROM pippo WHERE nome = myVar;

db2 -svtf prueba.sql
SET myVar = ‘perico’
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N “MYVAR” is not valid in the context where it is used.
SQLSTATE=42703
Can you help me, please? Thanks a lot
Carlos