DBA’s friend — UNIX’s crontab utility

There are many tools and utilities that simplify routine tasks and empower DBAs to automate tasks and get the job done. One of them is UNIX’s crontab utility. Colloquially, this is referred to as ‘Cron’ . In this  blog post, first I will introduce few basics about crontab. We will then look at few gotchas to watch out for when using crontab in UNIX environments.Discussion in this blog post is focused on basics of crontab’s functionality and troubleshooting. Advanced features (like running scripts as other user-id) are out of scope of this discussion. For further reading, cron’s wiki page could be found here.

What is crontab?

Crontab is a UNIX utility that facilitates scheduling jobs in the background. Jobs could be set to run at a particular time of the day. Cron allows UNIX shell scripts to be run for once or on a recurring basis. A UNIX wrapper around perl, python (and anything you could think of) etc. could also be scheduled.

Crontab’s format

There is a lot of documentation on crontab and it’s usage. For the sake of brevity, I am only listing below cron’s fields for quick reference.

# crontab entry format:
# .---------------- Minute (0 - 59)
# |  .------------- Hour (0 - 23)
# |  |  .---------- Day of month (1 - 31)
# |  |  |  .------- Month (1 - 12) OR Jan, Feb, March.. 
# |  |  |  |  .---- Day of week (0 - 6) (Sunday=0 or 7)
# |  |  |  |  |

00 0,4,8,12,16,20 *  *  * /path/to/script/database_up_down.ksh > /path/to/logs/database_up_down.ksh.log

The script 'database_up_down.ksh' shown in the above example would run at the top of the hour (minute = 0), every 4 hours (0,4,8,12,16,20),  every day of the month (* in 3rd position), every month (* in the 4th position) and every day of the week (* in the 5th position).

In many cases, '/path/to/script/database_up_down.ksh' has to be an absolute path unless it is UNIX OS command. However, if user id’s environment variable PATH is included in crontab file, you could get away with not using absolute path. For clarity, I prefer to have absolute paths in crontab.

Cron daemon

cron is a deamon (a background and a non-interactive program) and needs to be started only once. It is usually part of the start-up scripts and is started when the server starts. To find out if cron is running, use ‘ps’ command as shown:

$ps aux | grep -i cron
root      5951  0.0  0.0  29952  2648 ?        S     2014   0:00 /usr/sbin/cron

Invalid values in crontab

Suppose you enter invalid values in one of the cron’s fields.. What happens then?
Say for example, you have 24 as the hour (24 is an invalid hour value; hour extends from 0 to 23)

00 24 *  *  * /path/to/script/database_up_down.ksh > /path/to/logs/database_up_down.ksh.log

When you try to save an entry shown above, cron will ask you if you would like to fix the problem. It even points you at the line number (in this case line number: 34) and what it thinks is the problem (bad hour). See below.

"/tmp/crontab.XXXXWnWlvh":34: bad hour
errors in crontab file, can't install.
Do you want to retry the same edit?
Enter Y or N
Do you want to retry the same edit? y
crontab: installing new crontab

If you choose to enter ‘n’ for the above prompt, changes that were done are simply sent to a temporary file and are temporarily discarded from making into crontab.

Do you want to retry the same edit? n
crontab: edits left in /tmp/crontab.XXXXzR3eoI

Crontab Command

Each user can have their own crontab. In most UNIX flavors, crontabs files are located at /var. Direct interaction with crontab files at this location is discouraged. So, how do you add or modify entries in crontab? Use the ‘crontab’ command.

Here are the options in ‘crontab’ command. Here is notes directly from man page for crontab.

  • The -l (crontab -l) option causes the current crontab to be displayed on standard output.
  • The -e option (crontab -e) is used to edit the current crontab using the editor specified by the VISUAL or EDITOR environment variables. After you exit from the editor, the modified crontab will be installed automatically.
  • The -r (crontab -r) option causes the current crontab to be removed.

 Why crontab?

As a DBA, I prefer crontab over other job schedulers because I have better control over it.

For example, if I am asked to run a DML / DDL statement at a particular time of the day, I can choose to include SQL commands in a UNIX script and schedule the script to be run at the desired time. For example, the following crontab entry would run only once (at 6 AM on 15th December in the current year.

00 06 15 12 * /db2home/db2inst1/dba/scripts/code_entries.sh &> /db2home/db2inst1/dba/logs/code_entries.sh.out

 Additionally, I can modify a job’s  frequency and/or simply choose not to run it’s next iteration during maintenance activity.

For example, if there is a script that monitors for a DB2 instance’s availability, I can simply ‘comment’ this specific job (just add ‘#’ at the beginning of cron’s entry) during maintenance window. Use caution when you do this. You would not want to have complete control over monitoring production environments because it is easy to forget to uncomment a cron entry once maintenance is done.

You could optionally use the command ‘at’ to schedule commands to be run one-time. Discussion about ‘at’ is beyond the scope of this article. More information about ‘at’ could be found at it’s wiki site.

Why not crontab?

A wise man once preached ‘Do not stand on the branch (of a tree) you plan to cut down’. In a DBA’s life, this is analogous to ‘Do not monitor a database instance’s availability via crontab on the same database server’. (I know.. I know.. this analogy sucks). This is okay if you rely on other 3rd party monitoring / alerting tools besides cron but is a big no-no if crontab is the only utility/tool that is monitoring a database instance, especially in production environments.

The reason is.. if there is an outage on a UNIX server that hosts DB2 engine, crontab daemon is down as well. This means, database is down but no alerts will be sent because crontab is down as well. However, if the database server is up and running but the instance is down, a cron job should do it’s job to alert you of instance’s outage.

One limitation of cron is that there is no straight forward option to run a given job more frequently than a minute. Such a requirement could be rare. For such needs, crontab is probably not the right choice as scheduler. Advanced options within crontab could give such an ability. I have not yet explored such options.

Top 3 reasons why crontab would not work

In my experience with working on crontab, I found the following 3 to be primary reasons for unexpected behavior from crontab.

I list them in the order of how frequently (higher to lower) they caused me headaches.

1) Permission problems

2) Environment variables

3) Incorrect usage of crontab’s fields

Permission problems:

A UNIX script’s permission problem is the #1 reason why a job scheduled in crontab does not run as expected. A script like the one shown below will not run in crontab due to a permission problem. No user id has permission to ‘execute’ (permissions highlighted in red — there is no ‘x’ that indicates execute permission) this script.

$ ls -ltr -d -1 $PWD/database*
-rw-r----- 1 db2inst1 db2sgrp 6820 2014-04-23 11:16 /path/to/your/script/database_backup.ksh

When scheduled in crontab (example shown below), an error is generated indicating permission problem.

30 18 * * *  /path/to/your/script/database_backup.ksh &> /path/to/logs/database_backup.log
$ more /path/to/logs/database_backup.log 
/bin/sh: /path/to/your/script/database_backup.ksh: Permission denied

By running chmod +x /path/to/your/script/database_backup.ksh, the script becomes executable and this should resolve this issue.

The challenge with troubleshooting a permission problem is that if sufficient care is not taken to re-direct errors (stderr) to the output file, it would be hard to know that there is a permission problem. Re-directing crontab’s output is discussed towards the end of this blog post.

Environment Variables

This one could get confusing while troubleshooting. I would like to illustrate the importance of environment variables using PATH variable as an example. PATH is an environment variable specific to a user. We will use a dummy script to illustrate how PATH is interpreted by crontab.

$cat $HOME/dba/maint/scripts/test_path.ksh
#!/bin/ksh
echo $PATH

The output of this script when run at the command prompt is:

$~/dba/maint/scripts> ./test_path.ksh
/db2home/db2inst1/bin:/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/gnome/bin:/opt/kde3/bin:/opt/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin:/usr/SmartAnalytics/ha_tools:/db2home/db2inst1/sqllib/bin:/db2home/db2inst1/sqllib/adm:/db2home/db2inst1/sqllib/misc:/db2home/db2inst1/dba/custom/scripts:/db2home/db2inst1/dba/maint/scripts:/opt/IBM/dwe/db2/V9.7/java/jdk64/bin:/sbin:/db2home/db2inst1/bin:/db2home/db2inst1/sqllib/java/jdk64/bin

Now, let us find out what cron thinks to be the PATH. We will schedule the above job to run every minute (just to make the cron entry easy). It would look like below. It is a good idea to comment the script or remove it after you test it.

* * * * * /db2home/db2inst1/dba/scripts/test_path.ksh &> /db2home/db2inst1/dba/logs/test_path.ksh.log

After letting cron run the above script at least once, the output file contents would be like shown below:

$cat /db2home/db2inst1/dba/maint/logs/test_path.ksh.log
/usr/bin:/bin

Apparently, cron sees PATH variable to be very sparse. According to cron, only /usr/bin:/bin is the PATH for the user ‘db2inst1’ while PATH from the command line’s shell has lot more directories added to it. This is because cron does not execute .profile.

So, what does this mean? This means that although you could get away at the command prompt by not having absolute path for some of your commands/scripts (when they are in PATH), you have to be careful when you plan to implement such set of commands (that worked just fine at the command prompt) into a script that you plan to implement as a crontab script.

If this is confusing, simply know that you have few options to avoid problems related to PATH when working with crontab scripts:

1) Have PATH within the script at the beginning. This option tends to be impractical and is cumbersome. You are hard-coding what you are after because you cannot read PATH from crontab.

2) Have PATH in the crontab file (This path will then apply to all crontab jobs below the PATH entry) – Make sure you add all the PATHs that are in the ‘shell’ or else your simple commands like ‘date’ will not work.

Example: (Below is how PATH is added to a crontab file; The script ‘pavan.ksh’ will run as long as it is found in the newly modified PATH)

PATH=PATH:/db2home/db2inst1/dba/maint/scripts:/usr/bin
* * * * * pavan.ksh &> /db2home/db2inst1/dba/maint/logs/pavan.log

3) Have absolute paths in your scripts that you intend to schedule in crontab. This could be an easy alternative to #2 and provides more clarity. I prefer this approach.

However, when you migrate scripts from one server to another one with different file systems/path layouts, choosing this approach could end up in tedious work fixing the path(s) for the scripts to work on the new servers. You could still get away with smart ‘sed’ scripts that take care of fixing the paths for you.

Incorrect Usage of crontab’s fields

This is an interesting one. I will explain this with an example. A script that I had written was working just fine when run at the command prompt but wouldn’t work when scheduled as a crontab job.

00 08,16 * * * *  /db2inst1/maint/scripts/mon_snap.sh PERF > /db2inst1/maint/logs/mon_snap.perf.out

When I tested it on crontab, I was getting a mail (UNIX mail) that was like below:

Message  6:
From daemon Wed Dec 10 16:21:00 2014
Date: Wed, 10 Dec 2014 16:21:00 -0500
From: NUID - daemon  <daemon>
To: db2inst1
Subject: Output from cron job *  /db2inst1/maint/scripts/mon_snap.sh  PERF > /db2inst1/maint/logs/mon_snap.perf.out, db2inst1@hostmon01, exit status 127

Cron Environment:
 SHELL = /usr/bin/sh
 PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin
 CRONDIR=/var/spool/cron/crontabs
 ATDIR=/var/spool/cron/atjobs
 LOGNAME=db2inst1
 HOME=/home/db2inst1

Your "cron" job executed on hostmon01 on Wed Dec 10 16:21:00 EST 2014
*  /db2inst1/maint/scripts/mon_snap.sh  PERF > /db2inst1/maint/logs/mon_snap.perf.out


produced the following output:

sh: mbox:  not found

I took care of many gotchas like environment variables and others but they did not seem to be the cause of the problem.

Finally, I checked the syntax of my entry and there it was.. the root cause of the frustration.. I had an extra ‘*’ in the cron entry..

It should have been:

00 08,16 * * * /db2inst1/maint/scripts/mon_snap.sh PERF > /db2inst1/maint/logs/mon_snap.perf.out

Instead it was: (see extra * in red below)

00 08,16 * * * *  /db2inst1/maint/scripts/mon_snap.sh PERF > /db2inst1/maint/logs/mon_snap.perf.out

Lesson learned — Just watch out for that tricky crontab syntax. Remember that each entry in crontab has 6 fields. I usually refer to this link when I need a quick reference to crontab’s syntax.

Re-directing output in Crontab:

Consider a UNIX script ‘testk.sh’ as shown below.

#!/bin/ksh
echo "HI there.."
date
datek

I have a typo (datek) to demonstrate error handling in cron.

A crontab job scheduled as shown below would re-direct the output of the job to the output file.

00 08,16 * * * /db2inst1/maint/scripts/testk.sh > /db2inst1/maint/logs/testk.sh.log

No error output will be sent to the output file (shown below).

$ more testi.log
HI there..
Wed Jan  7 17:26:10 EST 2015

Error will be sent to UNIX email. What if we want the error to be written to a file?

00 08,16 * * * /db2inst1/maint/scripts/testk.sh > /db2inst1/maint/logs/testk.sh.log 2>&1

Here are a quick cheat sheet to understand how this works:

STDIN = 0 ; STDOUT = 1; STDERR = 2

When we add 2>&1 at the end of the crontab entry, we are asking STDERR (2) to be sent to STDOUT (1) which is testk.sh.log. So, errors should appear in the testksh.log file and they do (shown below)

$more testk.sh.log
HI there..
Wed Jan  7 17:17:02 EST 2015
/db2home/db2inst1/dba/maint/scripts/testi.ksh: line 7: datek: not found

One more method that seems to work in a ‘bash’ shell is by adding an extra ‘&’ before redirect operator ‘>’ . This is shown below:

00 08,16 * * * /db2inst1/maint/scripts/testk.sh &> /db2inst1/maint/logs/testk.sh.log

What if you wanted to send errors to a separate file?

Now that we understood the basics of standard in, out and error sources of our program, it is easy to do so. Just replace STDOUT (1) with a new error file name. Here is how you do it.

 00 08,16 * * * /db2inst1/maint/scripts/testk.sh > /db2inst1/maint/logs/testk.sh.log 2 > /db2home/db2inst1/dba/maint/logs/testk.error

 

Summary:

crontab is a simple yet powerful companion to DBA’s toolkit. Once you understand how cron works and error handling, many tasks could be automated/scheduled and thus cron could simplify a DBA’s life.

Below are some of the tasks that I automate using crontab:

1) Schedule a database backup, runstats, reorg etc.

2) Schedule a script that automatically checks for reorg-pending tables (these mostly happen after running alter statements for tables) every 30 minutes or so.

3) Monitor and alert for DB2 instance’s availability (Use 3rd party monitoring as well for reasons discussed above in ‘Why Not Crontab’ section).

4) Monitor and alert for database log utilization and other KPIs that I am interested in.

5) Send periodic database growth reports to management.

6) Generate and archive periodic DDL (for DR purposes – In case the database needs to be built from scratch).

7) Monitor and alert for a long running backup job.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s