DB2 Basics: Executing a Script of SQL

You may also like...

7 Responses

  1. Chris Aldrich says:

    I have started to redirect standard error before piping to tee. This is especially important when debugging the script as sometimes small syntax errors get written to the screen through standard error but do not show up in the log piped out by tee. So I like to do

    ksh myDBscript.sh 2>&1 | tee myDBScript.log

    Just a little extra protection in case I or someone else messed up the script. Gives a change to find the problem.

    I also want to mention that if you issue multiple statements and/or connect to multiple databases to save the TERMINATE command for the very end of the script. I have had where a TERMINATE was in between commands in a script and it killed the session with DB2 (even though I was running as db2 -tvf) and the rest of the commands then errored (including trying to reconnect).

  2. Manohar says:

    Hi Chris , when you are trying to write an SQL script which has multiple db connections in between , we better go for CONNECT RESET than TERMINATE , as the TERMINATE command even terminate the back-end process. , You can refer this http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001973.html?cp=SSEPGG_9.7.0%2F3-6-2-4-122 .
    Give a TERMINATE at EOF to make sure you come out clean

  3. Raf Mathijs says:

    I usually check my logs by using grep:

    cat logfile | grep -i -e “SQL….N” -e “SQL….W”

  4. Peter says:

    Just my personal preference is to check for errors. I went with logfile parsing in the past but this is too cumbersome for simple check whether my script was successful or not. I am looking at the error level that gets returned by db2. Everything other than 0 might be suspicious. However in most cases 1 is ok and sometimes I am fine with 2 as well.

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010411.html

    Another pattern that I started to like is to have shell and sql scripts in conjunction. The shell scripts make sure the db connection is set up and the sql script does the actual work. This way I can use parameters for connecting to different databases and the actual sql work still stays the same regardless of the DB name. Nothing beats the beauty of having only one call to cater the needs of dev and production environments alike.

    There is one more perk of always having a shell script as a driver. The last line should always be “banner success” (make sure that all error cases exit with an error code beforehand already).

  5. Srini says:

    Hi Ember,

    Need to be able to setup a db2 luw database on the fly with creating the database, creating as many table as one wants in it with indexes, primary keys creation etc on the fly as well as loading of generated data thru SQL with commit points within as it inserts records and thus be able to create however big a database size as one wants…Do you know of any such shell script or something that has all this capabilities or a set of SQL someplace that can do this?

    I know Vikram Khatri, one of amongst known names in DB2 World, used to have such scripts in his db2ude.com. which unfortunately got taken down….

    Incase you are aware of one such, please let know..Thanks..

    • Ember Crooks says:

      I don’t have or know of any such scripts. Obviously, db2move can be useful if trying to copy an existing database, when used with db2look. It’s not hard to write a script to cycle through a list of export files in a subdirectory and import them into tables of the same name using ksh or perl. But I don’t have any to share. I’d suggest asking on the forums and at conferences.

  1. September 4, 2014

    […] lot of work is done with DB2 using SQL scripts of commands. See my blog entry on DB2 Basics: Executing a Script of SQL if you’re not familiar with how to execute a script of […]

Leave a Reply

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