How to schedule DB2 scripts to run

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!

Jim
Jim YoumansSr Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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

etc.

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.


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
Hi!

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.

Regards,
     Tomas Helgi
0
Jim YoumansSr Database AdministratorAuthor Commented:
Thank you!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.