Scripting for DB2 LUW – Best Practices for Perl and Other Languages – Part 3

This is my own set of basic scripting standards and practices. I’d love to hear criticisms, suggestions and comments from readers on these. Some of these would be basic “duh” moments for a developer, but for a DBA from a different background, may be more enlightening. This ended up being so long that I’ve split it into three parts.

This is the final entry in the series, focusing on Perl specifics.

Perl Specific

I’m just going to touch on a couple of the high points here because there will be future posts on some of these.

Good Coding Practices

You should include as the first line of your script “#!/usr/bin/perl -w” or alternately include the line “use warnings;”. This will cause Perl to warn you about things like variables that are only used once or references to undefined file handles. These are nearly always mistakes. For more information:

You should also include “use strict;”. This makes it so you are not allowed to do some bad coding practices like using variables that are not fully qualified or scoped, and several other bad scripting practices. For more information:

Global vs. Local Variables

All global variables must be laid out ahead of time something like this:

use vars qw(
 @cfg_db_name   $output_dir      $retain_days
 $arch_dir      $cfg_arch_ind    $arch_retain_days
 $help_ind      $version_ind     $arch_ind
 @db_name       $snap_dir        $sleep_interval

Think carefully about what you make a global variable. Many things may only need to be local if you’re using subroutines right. Local variables are ones that are defined only within a subroutine or loop and are defined with “my” like this:

 my $vers_ind;
 my $help_ind;


Perl offers many modules that you can use, some included in the standard distribution, and some that you can add on, sometimes requiring a C compiler. If you have the ability to install Perl modules at will and think you always will, then by all means use them. I severely restrict my use of them because it is hard to install for some clients. This hurts the part of me that hates to reinvent the wheel. Some clients will have trouble approving even an installation that is limited to your user id’s use. On AIX some of them can only be installed with the same C compiler that was used to install Perl – that being the one you have to pay for a license to install. So be cautious here. The only ones I’ve used recently were for encrypting a password when I had to store it in a file for use with dbclean (a WebSphere Commerce tool for pruning data).

Perl DBI

The Perl DBI is a RDBMS agnostic way of interacting with databases. I’m sure that’s great if you’re likely to interact with more than just one RDBMS. But only one (data pruning) of my scripts out of dozens even has the slightest chance of ever being used on another RDBMS. Backups, reorgs, runstats, snapshots, diag scripts – all would have no meaning on Oracle or MySQL – those databases use not just different commands, but whole different methodologies. The Perl DBI is also limiting in that it doesn’t have facilities for commands like reorg, runstats, etc, precisely because they are not even universal enough across RDBMSes to make that make sense. For these reasons, I have never yet used the DBI. I’m sure there may be situations in which DBAs find it useful – I just haven’t yet.


So now you’ve read all 3 posts in this series – what am I missing? I learn so much from reader comments.

You may also like...

2 Responses

  1. Ian Bjorhovde says:

    Viewing DBI as “only” an RDBMS-agnostic layer is selling it short. It allows you access database data without having to parse text. Even though perl is awesome for text manipulation, once you use DBI and leave the drudgery of text parsing behind, your life will be forever improved (and you won’t have to deal with minor formatting changes that occur between database releases). With the SYSIBMADM views and MON_GET table functions, you can pretty much eliminate having parse GET SNAPSHOT output.

    Your comment about not being able to execute DB2 utilities like RUNSTATS, LOAD, etc. have pretty much all been handled by the ADMIN_CMD stored procedure.

    It’s certainly a valid point about being able to install DBI and DBD::DB2 in customer environments… There are ugly workarounds for this, but it’s definitely a problem for perl.

  2. Dick Rietveld says:

    Thank you Ian. You formulated my thoughts exactly.
    It can be cumbersome when you are on a server without direct internet access to download & install it.
    So I want to reverse the common advice: “Kids, DO try this at home”.

    On your day to day windows/linux machine install perl with DBI alongside your db2-express-c and see if you can read some rows from employee 🙂 You will start to like it. Perl becomes so much more powerfull.

Leave a Reply

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