How to schedule DB2 scripts to run

Jim Youmans
Jim Youmans used Ask the Experts™
DB2 10.5 on Windows

How do most people schedule DB2 scripts to run?  I would like to be able to run certain maintenance scripts to run at 3:00 am nightly.  

I have created a batch file that calls another batch file that runs the DB2 cmd window.  The script in the second batch files creates another file of SQL commands that is then run.  The whole thing is run from the windows task scheduler but there are some issues.  

1.  I hate batch files and having to have 2 seems silly.

2.  The files run fine when I run them but fail more often than not when called from the windows task scheduler.  (various reasons not the focus of this post).  The problem is in running the 3rd script file.

3.  I have to put the username / password in the bat file in clear text.

So my question is how do most DB2 DBA's do this on windows?  SQL Server has a nice built-in job agent that makes it a breeze but DB2 does not seem to have that ability.

Is there an easier way to do this?  Am I missing something obvious that would make my life simpler?  Is there a third party application that will run as the SQL Server Agent with DB2?

Thank you!

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi JIm,

I do much the same as you're doing -- scheduling the scripts via the Windows Scheduler.

Some of the items in the scripts are self generating, using ECHO statements to write the commands to a .bat or .sql file that will be executed.  It seemed ugly and cumbersome at first, but turned out to be incredibly flexible.  And there is no question of "what exactly was run" when the parameter substitution isn't exactly clear as the generated file is written to a temp folder and retained past the end of the script.

The issue of visible usernames and passwords is a security audit nightmare.  I solved it by using script variables for them and setting the variables by calling another script that's kept in a private (non-shared) folder.  Only people with access to the server can access the visible passwords, and they are all sysadmins or dbas anyway.

-- scheduled Job

  if exist M:\support_files\set_user_details.bat (
    call M:\support_files\set_user_details.bat

-- set_user_details.bat
  set db2adminuser=db2admin
  set db2adminpw=admindb2pw

  set db2batchuser=db2batch
  set db2batchpw=batchdb2pw


The scheduled job calls the second script to set up the usernames and passwords.  If the script in the scheduled job is called stand-alone, it also attempts to call the second script to set up the usernames and passwords.  If the calling user does not have access to the setup script, the script will fail unless the user sets up his own batch environment with the necessary variables.  A user can't run the script with system privileges, but can if he can supply appropriate credentials.

It's not a panacea, but I find it works incredibly well.  I don't have to keep a dozen passwords in my head (or at my fingertips), and when a critical password changes, I can change the setup script instead of changing every batch script.

Tomas Helgi JohannssonDatabase Administrator / Software Engineer

At my work I have both used the Windows Scheduler, crontab on *nix systems as well as workload scheduling software like IBM Tivoli Workload Scheduler and Automics Dollar Universe for reorgs/runstats/backup jobs depending on the requirements for the overall system that each database services.

When I setup the scripts I usually use the instance owner (with most privileges to the databases) and in the script I only use the simplest way to connect "db2 connect to db" then I use the Windows Scheduler with the run as user and type the password of the user that should run it and therefore there is no username and password in clear text of the batch scripts.

     Tomas Helgi
Jim YoumansSr Database Administrator


Thank you!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial