Basic scripting tricks for DB2(LUW)

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 *