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?
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: 548

12 Comments

  1. 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

    • 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)

    • 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.

    • Most of the times db2 +p -tv option doesn’t work, eventhough waiting for infinite time. Not sure of what exactly is happening but its been observerd in many times.

      As an ex below

      db2 -x “select ‘drop table ‘||rtrim(tabschema)||’.’||tabname||’;’ from syscat.tables where tabname like ‘ABC%’ with ur” | db2 +p -tv

      Any idea and solution for this?

  3. 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. 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

  5. Just a quick comment about the use of piping the results to ‘db2 +p -tv’. This ability is limited by the pipe buffer size, which can vary across systems and platforms. There is no easy cross platform command for checking the pipe buffer size, so this is best left for smaller results sets. Great article, thanks Ember!

    References
    https://webcache.googleusercontent.com/search?q=cache:0FTBMFBsL9YJ:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.8.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010412.html+&cd=2&hl=en&ct=clnk&gl=us

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.