Getting started with Awesome Perl

Perl : Practical Extraction and Report Language is one of the widely used scripting language . I will be discussing this post in the DBA perspective . Most of our Databases reside on either Linux or Windows servers , so we choose either Shell or Batch scripting .

Lets take a case you been very comfortable writing shell script's as your databases resides on Linux box's ,but for some reason your client decided to move or migrate them to windows machines where there's a definite necessity for you to port all your shell scripts to batch script , you know you need to start all the way from bottom .

So who helps us from this kind of situations , Perl , yes one big advantage of Perl is it portability . Make sure you have the Perl compiler installed either in your Linux or Windows machine and you are free from platform concerns .

Let's see some basics of Perl and some sample scripts

Shell

Perl

SheBang

#!/bin/sh

#!/usr/bin/perl

Execute db2 commands

db2 connect to sample

Its straight , just type the command as it is .

`db2 connect to sample`

You need to place your command in between backticks ``

Handling Timestamp

# For current timestamp
date +%Y%m%d
date +%Y-%m-%d
# For 5 days ago timestamp
date +%Y%m%d -d "5 days ago"

# For current timestamp
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon += 1;
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);
# Calling the variable in the format you want your timestamp
$mday-$mon-$year
$year$mon$day
#For 5 days old timestamp change the localtime line as show below
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time-6*24*60*60);

Assing/Calling variables

#Assigning
date=`date +%Y%m%d`
#Calling
$date

Assign variables straight and call them with $variable

#Assigning
$year += 1900;
$mon += 1;
#Calling/Assigning
my $date = "$mon-$year";

Assigning and calling goes with $variable

It's not that if you are very good at shell scripting you can do good in Perl , Perl is entirely different from Shell . To be frank its pretty difficult for the Shell scripters to adopt Perl than guys who are aware of Javascripting,PHP...

There's an interesting variable in perl $_ , its called default variable i.e., it takes the default value in the current iteration

Lets go through some example , we take a query as show below

db2 "select RTRIM(substr(A.TBSP_NAME,1,30)) as TBSPNME,A.TBSP_FREE_PAGES as FREE,B.CONTAINER_NAME as CON_PATH from SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATSP_ID=B.TBSP_ID with UR"

TBSPNME                              FREE                 CON_PATH

------------------------------ -------------------- ------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
SYSCATSPACE                                      56 C:\DB2\NODE0000\SAMPLE\T0000000\C0000000.CAT

TEMPSPACE1                                        0 C:\DB2\NODE0000\SAMPLE\T0000001\C0000000.TMP

USERSPACE1                                     2048 C:\DB2\NODE0000\SAMPLE\T0000002\C0000000.LRG

SYSTOOLSPACE                                   8012 C:\DB2\NODE0000\SAMPLE\T0000003\C0000000.LRG


  4 record(s) selected.

If you want to manipulate your script with FREEPAGES this is how you do it

foreach (@tbsp){
print $_;
chomp;
s/^\s*//;
s/\s*$//;
my ($name,$fpages,$path)=split /\s+/,$_,3;
        if ($fpages<= 4000){
                my $pages=(10000-$pages);
                print MYFILE "alter tablespace $col1 extend (file \'$path\' $pages);\n";
        }
}

Lets undertand this piece of code

foreach (@tbsp){ ...code...}

foreach : Its pretty much like the for loop you know with a little difference , it traverse through each element of the set of value mentioned in the array @tbsp , which gives you lot of advantages for capturing desired values.

chomp : chomp removes trailing string that corresponds to current value i.e., $_

s/^\s*//;

You should be comfortable with regular expressions if you worked with the sed utility in shell , So we have just trimmed the leading white spaces in the current value $_

s/^\s*//;

trimmed the trailing white spaces in the current value $_

my ($name,$fpages,$path)=split /\s+/,$_,3;

split : see in the first iteration $_ has the values SYSCATSPACE 56 C:\DB2\NODE0000\SAMPLE\T0000000\C0000000.CAT. so you are telling to split that 1st row into 3 variable namely $name,$fpages,$path on the basis of one or more than one space (\s+).Doing this will assign following value to the variable as show below

• $name : SYSCATSPACE
• $fpages : 56
• $path : C:\DB2\NODE0000\SAMPLE\T0000000\C0000000.CAT

That's it once you got the values into variable , its all your game now .

In my future post's we will see some db2 scripts written in perl . To start with we will port the shell script in my one of the post Script to check the Tablespace sizes to perl .

Have a Nice Day