A Day in a DBA's Life: 2013

3 key Areas never to miss in InformationCenter .

People Wonder a new version of db2 is released , what are the new features , how will i know these features ?

Now say the documentation or Information center for the product have been released , So where to look first in that documentation to know the new features .

3 sections to look-in first :

Basic Shell command to make ourselves comfortable at all times - Part 2

Welcome to the Part 2 of this series , We have discussed about grep,ls,awk,find in my last post

Basic Shell command to make ourselves comfortable at all times

In this post we are gonna see some crontab and vi editor options.

Crontab :

Crontab is a utility provided by linux to schedule jobs or scripts , Like if you want to run a job every morning 8:00 am , you can do this with the crontab utility

dbadm@linux122:~> crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXX7mwgtU installed on Tue Nov 12 17:53:36 2013)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

####   BACKUPS

01 01 * * * /home/DB_BKPS/scripts/sample1_backup.sh
01 02 * * * /home/DB_BKPS/scripts/sample2_backup.sh

Basic Shell command to make ourselves comfortable at all times

People might wonder why i need to learn a scripting language , if i am a hardcore DBA , let me tell you , if you are in the administration side of your career you cannot avoid these scripting languages. Whether it might be a

Shell Scripting
Perl Scripting
Batch Scripting

These should become the primary skill in par Database Administration

If you work on linux server make sure you are good with at these least basics of Shell commands

If you work on windows machine you should be comfortable with Batch scripting

And Perl , this is a friend of all , The moment you make yourself comfortable with Perl , you break the environment barriers doesn't matter which machine you work on , Perl is supported almost in all the Platforms

Keeping apart Perl as this post is to discuss some basic and common commands to grip yourself while working on Linux environment

Shell Basic Commands :

grep
ls,ls -lrt
find
awk
sed
crontab
vi editor

DB2 Partitioned Tables - Life made easy - Part 3

So all we are going to see in this post is some left-outs from the last 2 posts from this series

Please refer to my previous posts
DB2 Partitioned Tables - Life made easy - Part 1 &
DB2 Partitioned Tables - Life made easy - Part 2 to catch up this series .

Nevertheless of our discussions in previous posts we have seen the partition's being attached and detached from the base partitioned table (i.e., DB2INST1.PARTBLE), You can also do this Roll-In of data from the other tables also .

ALTER TABLE DB2INST1.PARTBLE
   ATTACH PARTITION PART JAN15 
   STARTING '1/1/2015' 
   ENDING '3/31/2015' IN TBSP2
FROM TABLE DB2INST1.EMPLOYEE

DB2 Partitioned Tables - Life made easy - Part 2

Continuing from my first post of this series DB2 Partitioned Tables - Life made easy - Part 1

Go through that post to get an idea of the table structure created and partitions used in this exercise .

We now go through some workout examples of the following categories

Attaching a partition
Detaching a partition
Roll in a partition
Roll out a partition

Detaching / Roll out a partition :

DB2 Partitioned Tables - Life made easy - Part 1

Why partitioned tables ???

Well this cannot be said in a single statement

It makes the DBA's life lot more easier ?

Sure it does , If you are a DBA and you opted partitioned table , you need not worry of the purging activity . If the data is growing beyond your foresight , decided to keep only 3 or 6 or 9 months of active data in table and you have partitioned your table , simply detach the old partition and you are done .

Query will be processed faster than ever ?

Obviously , Say you have a table partitioned for every 3 months and same table without any partitions , Now both the tables have 30 million records , in the latter db2 has to scan all the table to get your result whereas in the former db2 has an advantage to take of every 3 months split , Once you update the statistics db2 goes directly to that respective 3 months slot and collects the data . So how large the table , its only 3 months data for db2 .

Script to check the Tablespace sizes

What does this script do ?

Check for the DMS(Auto Resize Enabled – No) Tablespaces ( As there is no point in checking the SMS and DMS with Auto Resize Enabled – Yes Tablespaces)
Query returns the following columns (Tablespace Name , Free Pages , Container path )
If less than 1000 pages throws warning message

Here’s the script

Password less SSH

In this post I would explain how to make the 2 servers( Remote and Local) password less .

Lets take a scenario

Resources : ServerA , ServerB
Requirements : To make ServerB password less (i.e., while you enter ‘sshusername@ServerB’ it should not prompt you for a password )
Explanation :To do this Generate a key pair on ServerA ( There are 2 types of keys rsa and dsa , you can use anyone of those)

ServerA@db2:~/.ssh> ssh-keygen -t rsa

Here is what you get

How do you CronJob a script to mail it to your INBOX

Many time we come across a situation where in we need to schedule a script and mail that report to our INBOX

If that is the case here is how we do it

DB2 Backup Script

After three months of my continuous hit’s i am successful in writing a backup shell script . Its really nice feeling you get when your first script is successfully executed and is ready to go to production server

So no wasting time here is what i have written

Here are the things what i have done in this script

Check for the space in the file system
Check if the backup is already taken
Take the backup
Copy the backup taken to another server
Remove the 5 days old backup (According to Retention policy)

And Here Goes the script

Trigger Sql relative to Server or CPU load

Tuesday, 15 January 2013 manohar viswanatha

Well to start with , We come across various daily needs where in which we find different paths to reach or bring the desired output , this post is one of them .

If you also came across kind of requirement in this post , this is for you ….Well Well , What is that requirement ?????…..Ya I’m coming to that !!

If you want to automate some of you db tasks depending on the server load , If your DB server load crosses 50 …..set this flag to ‘Y’ , if above 80 , set this flag to ‘N’ , if below 80 set the flag again to ‘Y’……………………………..

For doing that we need to primarily catch up the server load which we do with a familiar command top

We have 2 options

Collect Cost Of all the db2 Stored Proc's in a simple Possible Way

Before writing this post i just need to convey that the kind of approach i'm going to tell you might be done by many others by this time , I have been trying out to find the easy way to find out my need and on my way i came across this and thought of sharing with you guys .

So "What if you came across a need in your DBA life where in you need to collect all the Query Costs of all the Stored Proc's or Packages in your Database ? "
Here is one i have tried