In my previous 2 posts we have discussed some basics of Perl coding namely
Keeping these basics on mind and adding to them here we develop our first Perl script .
Although it's the DBA who deploy STORED PROCEDURE and FUNCTIONS , some times you want to quickly check the PROCEDURES deployed from last week or so and check the cost(timerons) against them to find the culprit.
I use this script more than seldom, but every time I use this , it fulfills my tasks
This script helps you check the ESTIMATED COSTS of the deployed STORED PROCEDURES from the last n no. of days on the go.
Listing 1 :
#!/usr/bin/perl
use strict();
use warnings();
use diagnostics();
$dbname = "SAMPLE";
`db2 "connect to $dbname"`;
#my @procs = grep (/\d+-\d+-/ , ` db2 "SELECT SUBSTR(SYSCAT.PROCEDURES.PROCNAME, 1, 40) AS PROCNAME,
SUBSTR(SYSCAT.ROUTINEDEP.BNAME, 1, 20) AS PACKNAME, SUBSTR(SYSCAT.PROCEDURES.PROCSCHEMA, 1, 20) AS SCHEMA,
SYSCAT.PROCEDURES.CREATE_TIME
FROM SYSCAT.PROCEDURES, SYSCAT.ROUTINEDEP
WHERE SYSCAT.PROCEDURES.SPECIFICNAME = SYSCAT.ROUTINEDEP.ROUTINENAME
AND SYSCAT.PROCEDURES.PROCNAME IN (
SELECT SYSCAT.PROCEDURES.PROCNAME
FROM SYSCAT.PROCEDURES
WHERE SYSCAT.PROCEDURES.CREATE_TIME > CURRENT TIMESTAMP - 1 DAYS
ORDER BY SYSCAT.PROCEDURES.CREATE_TIME)"`);
my @procs = grep (/\d+-\d+-/ , `db2 "SELECT SUBSTR(A.PROCNAME, 1, 40) AS PROCNAME, SUBSTR(B.BNAME, 1, 20) AS PACKNAME,
SUBSTR(A.PROCSCHEMA, 1, 20) AS SCHEMA, A.CREATE_TIME
FROM SYSCAT.PROCEDURES AS A, SYSCAT.ROUTINEDEP AS B
WHERE A.SPECIFICNAME = B.ROUTINENAME AND A.CREATE_TIME > CURRENT TIMESTAMP - 1 DAYS
ORDER BY A.CREATE_TIME"`);
#print "Procedures Deployed\n";
#print "------------------------------------------------------------------------------";
#print "@procs\n\n";
if (@procs){
print "List of Procedure(s) deployed\n\ ";
print "------------------------------------------------------------------------\n";
foreach my $procname (@procs){
chomp($procname);
$procname =~ s/^\s*//;
$procname =~ s/\s*$//;
my($name,$bname,$schema,$time) = split (/\s+/ ,$procname,4);
print "$name compiled under schema $schema at $time\n\n";
print "-------------------------------------------------------------------------\n";
print "Estimated Cost per section for $name\n";
print "-------------------------------------------------------------------------\n";
my @cost = `db2expln -d $dbname -c $schema -p $bname -s 0 -t | grep "Estimated Cost"|sort -k4 -r`;
print "@cost\n";
}
}
else {
print "No Procedure(s) Compiled\n";
print "--------------------------------------------------------------------------------\n";
}
`db2 "connect reset"`;
`db2 "terminate"`;
Copy the code and paste it in a file
Change the script to your requirement at respective line numbers as shown below
Change the database-name to the name you want to
Listing 2 :
$dbname = "SAMPLE";
Change the timestamp to your requirement .i.e.,
CURRENT TIMESTAMP -1 day ORDER BY CREATE_TIME- will return STORED PROCEDURES from the last dayCURRENT TIMESTAMP -2 day ORDER BY CREATE_TIME- will return STORED PROCEDURES from the last 2 dayCURRENT TIMESTAMP -10 day ORDER BY CREATE_TIME- will return STORED PROCEDURES from the last 10 day
Listing 3:
my @procs = grep (/\d+-\d+-/ , `db2 "SELECT SUBSTR(A.PROCNAME, 1, 40) AS PROCNAME, SUBSTR(B.BNAME, 1, 20) AS PACKNAME, SUBSTR(A.PROCSCHEMA, 1, 20) AS SCHEMA, A.CREATE_TIME FROM SYSCAT.PROCEDURES AS A, SYSCAT.ROUTINEDEP AS B WHERE A.SPECIFICNAME = B.ROUTINENAME AND A.CREATE_TIME > CURRENT TIMESTAMP - 1 DAYS ORDER BY A.CREATE_TIME"`);
Give the permissions
Listing 4:
chmod +x exfmt_proc_V2.pl
Run the script as show below and you will be given with the O/P mentioned
Listing 5:
dbadm@linux122:/home/DB_BKPS/scripts> perl exfmt_proc_V2.pl List of Procedure(s) deployed ------------------------------------------------------------------------ PROCEDURE1 compiled under schema DB2INST1 at 2014-01-24-15.45.38.645345 ------------------------------------------------------------------------- Estimated Cost per section for PROCEDURE1 ------------------------------------------------------------------------- Estimated Cost = 326.034515 Estimated Cost = 25.287561 Estimated Cost = 25.285561 Estimated Cost = 12.660875 Estimated Cost = 0.032631 Estimated Cost = 0.000035 Estimated Cost = 0.000035 Estimated Cost = 0.000000 Estimated Cost = 0.000000 Estimated Cost = 0.000000 PROCEDURE2 compiled under schema DB2INST1 at 2014-01-30-12.44.30.805063 ------------------------------------------------------------------------- Estimated Cost per section for PROCEDURE2 ------------------------------------------------------------------------- Estimated Cost = 461.201904 Estimated Cost = 0.000294 Estimated Cost = 0.000000 Estimated Cost = 0.000000 Estimated Cost = 0.000000 PROCEDURE3 compiled under schema DB2INST1 at 2014-01-31-17.01.36.225971 ------------------------------------------------------------------------- Estimated Cost per section for PROCEDURE3 ------------------------------------------------------------------------- Estimated Cost = 915.623962 Estimated Cost = 530.050110 Estimated Cost = 37.957714 Estimated Cost = 267.724121 Estimated Cost = 25.309597 Estimated Cost = 25.307592 Estimated Cost = 25.284758 Estimated Cost = 25.282570 Estimated Cost = 213.470810 Estimated Cost = 12.720264 Estimated Cost = 12.684752 Estimated Cost = 12.657022 Estimated Cost = 12.649393 Estimated Cost = 1226.605225 Estimated Cost = 0.000294 Estimated Cost = 0.000294 Estimated Cost = 0.000294 Estimated Cost = 0.000294 Estimated Cost = 0.000294 Estimated Cost = 0.000035 Estimated Cost = 0.000035 Estimated Cost = 0.000000 Estimated Cost = 0.000000 Estimated Cost = 0.000000
If you see the line 31 in Listing 1
my @cost = `db2expln -d $dbname -c $schema -p $bname -s 0 -t | grep "Estimated Cost"|sort -k4 -r`;
The above statement is a blend of Perl and Shell so as of now this script works only on a Linux box
The script is still in development process and full version of the script resides on Github .
Fork me on Github repository Explain plans Procs and Func ,
if you are interested and suggest with any changes . Suggestions are appreciated.How do I use this script ?, Some time ago I scheduled it using Crontab utility so that the procedures cost will drop in my inbox , I come and see every morning my mailbox and come to know the changes happened , this is the way I scheduled it
Listing 7:
20 04 * * * /home/DB_BKPS/scripts/exfmt_proc_V2.pl | mail -s "Deployments on `date +%d-%m-%Y`" youremail@address
As of now I removed this script from Cronjob and running manually like one below
/home/DB_BKPS/scripts/exfmt_proc_V2.pl | mail -s "Deployments on `date +%d-%m-%Y`" youremail@address
Refer the post regarding How do you CronJob a script to mail it to your INBOX
Have a Nice Day
No comments:
Post a Comment