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:

> db2 -x "select 'drop trigger ' || trigname || ';' from syscat.triggers where trigname like 'STAG%' with ur" >trig_drops.sql
> head trig_drops.sql
drop trigger STAG0001;
drop trigger STAG0002;
drop trigger STAG0003;
drop trigger STAG0007;
drop trigger STAG0008;
drop trigger STAG0009;
drop trigger STAG0013;
drop trigger STAG0014;
drop trigger STAG0015;
drop trigger STAG0016;

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:
db2 -x "select 'select count(1) from ' || rtrim(tabschema) || '.' || rtrim(tabname) || ';' from syscat.tables where tabschema= 'DB2INST1' and tabname like '%ORD%'"| db2 +p -txv

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’:

> db2 -x "select trigname from syscat.triggers where trigname like 'STAG%' with ur" >trig.list
> head trig.list
STAG0001
STAG0002
STAG0003
STAG0007
STAG0008
STAG0009
STAG0013
STAG0014
STAG0015
STAG0016
> cat trig.list |while read t; do db2 connect to dbname; db2 -v "drop trigger schema.$t"; db2 connect reset; done >trig_drops.out
> head -40 trig_drops.out

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

drop trigger schema.STAG0001
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

drop trigger schema.STAG0002
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

drop trigger schema.STAG0003
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

drop trigger schema.STAG0007
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
>

And our second example – granting select on all tables to a group:

> db2 -x "select tabschema, tabname from syscat.tables where type in ('T','V') with ur" >tab.list
> head tab.list
SYSIBM                                                                                                                           SYSTABLES
SYSIBM                                                                                                                           SYSCOLUMNS
SYSIBM                                                                                                                           SYSINDEXES
SYSIBM                                                                                                                           SYSVIEWS
SYSIBM                                                                                                                           SYSVIEWDEP
SYSIBM                                                                                                                           SYSPLAN
SYSIBM                                                                                                                           SYSPLANDEP
SYSIBM                                                                                                                           SYSSECTION
SYSIBM                                                                                                                           SYSSTMT
SYSIBM                                                                                                                           SYSDBAUTH
> cat tab.list |while read s t; do db2 connect to dbname; db2 -v "grant select on $s.$t to group db2sel"; db2 connect reset; done >db2sel_grants.out
> head -40 db2sel_grants.out

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

grant select on SYSIBM.SYSTABLES to group db2sel
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

grant select on SYSIBM.SYSCOLUMNS to group db2sel
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

grant select on SYSIBM.SYSINDEXES to group db2sel
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

>

So the gotchas here are:

  1. 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.
  2. 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.
  3. 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.
for TABNAME in `awk '{print $1"."$2}' tab.list`; do db2 -v "grant select on table $TABNAME to group db2sel" ; done >db2sel_grants.out
I’ll have a completely different post or series of posts on my favorite scripting language – Perl. Any other nifty tricks anyone wants to share?

You may also like...

10 Responses

  1. Frederik Engelen says:

    The reason you must repeat the connect statement is because the pipe will create a new shell process which will not be connected to your original db2bp process. Try this, it’ll be a lot faster:

    for TABNAME in `awk ‘{print $1″.”$2}’ tab.list`; do db2 -v “grant select on table $TABNAME to group db2sel” ; done

    in this case, you would do the concatenation in SQL of course, but awk works here as well for the time being… Thx for the blog.

    Kind regards,

    Frederik Engelen

    • Ember Crooks says:

      Awesome, thanks!

    • Ember Crooks says:

      Well, that’s not working for me. Same issue, it doesn’t realize it has a connection:

      $db2 connect to WC005Q02

      Database Connection Information

      Database server = DB2/AIX64 8.2.9
      SQL authorization ID = DB2INST2
      Local database alias = WC005Q02

      $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

  2. Hi,

    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:

    db2 -x “select ‘select count(1) from ‘ || rtrim(tabschema) || ‘.’ || rtrim(tabname) || ‘;’ from syscat.tables where tabschema not like ‘SYS%’ and type = ‘T'” | db2 +p -tv

    (Taken from my blog: http://angocadb2.blogspot.com/2011/12/ejecutar-la-salida-de-un-query-en-clp.html in Spanish)

    • Ember Crooks says:

      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.

  3. Charles Brown says:

    Hello All,

    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.

    Thanks and best regards,
    Iefbr14

  4. Carlos says:

    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

Leave a Reply

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