A Day in a DBA's Life

Browsers and Me

If there's anything inevitable in our daily life's, that is Browser.

Choosing a right browser for the kind of work we do is a real important deal.

What the options we have with us

Log Manager and the Archive Array

Let me give a warm up before jumping into how this was understood.

A very generic introduction to the environment, Long story short -

2 databases in Environment
1 for live application, 1 for reporting
live application database gets restored to the reporting database everyday before the business starts every day.
Soon after the restore happened we started seeing log archival failures all of a sudden from no where
2016-02-11-06.22.26.070851-360 I100204A452        LEVEL: Warning
PID     : 11469034             TID : 3678        PROC : db2sysc 0
INSTANCE: db2             NODE : 000
EDUID   : 3678                 EDUNAME: db2logmgr (SAMPLE) 0
FUNCTION: DB2 UDB, data protection services,
sqlpgRetryFailedArchive, probe:4760
MESSAGE : Unable to find log file 9543 for LOGARCHMETH1 using
method 4 and<
          target . Assume log was archived and continue.

Handy Perl script to check the cost of STORED PROCEDURES

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.

Pro's and Con's of default variable $_ in Perl

In this post we will see some good practice while you write your Perl scripts . If you see my previous post Getting started with Awesome Perl I have discussed usage of default variable $_ while what we see in this post how good for a programmer is to use the default variable

It's for sure that the default variable ($_) does the work but when you have a large chunk of code , readability of the code will become a problem . Lets take a piece of code and analyze it .

Let's pass some sql data into an array like one shown below

my @data = `db2 "list tables for schema DB2INST1"`

Now the array @data is populated with some data , lets go and do some manipulations

Accidents happen - Recovering a dropped table

A must quality of a DBA is to know the recovery strategy first before entering the battlefield .That is the reason I have started the Accidents happen series so that we can discuss further recovery strategies in this series of posts.

I have not found many search results where somebody who could show in detail how to recover an accidentally dropped table . So I thought of giving an example scenario as it will help at least some people who are dealing with it for the first time.

The first precaution you can take is to add WITH RESTRICT ON DROP clause to the CREATE TABLE

Say you created a table as shown below

CREATE TABLE "DB2INST1"."EMPLOYEE" ( "EMPNO" CHAR(6 OCTETS) NOT NULL , "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , "MIDINIT" CHAR(1 OCTETS) , "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , "WORKDEPT" CHAR(3 OCTETS) , "PHONENO" CHAR(4 OCTETS) , "HIREDATE" DATE , "JOB" CHAR(8 OCTETS) , "EDLEVEL" SMALLINT NOT NULL , "SEX" CHAR(1 OCTETS) , "BIRTHDATE" DATE , "SALARY" DECIMAL(9,2) , "BONUS" DECIMAL(9,2) , "COMM" DECIMAL(9,2) )  
IN "USERSPACE1"     
WITH RESTRICT ON DROP       
ORGANIZE BY ROW  ;

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

public - beware of this pseudo group

It was almost 3 years back when my career started , I had a chance of creating users and granting them privileges where i created a user and was shocked the moment i created a user in OS he could connect to the database even before granting privileges to him.

This eventually led me to the documentation where i came to know the pseudo group rather PUBLIC group is the culprit.Culprit ? No No ... Not actually , DB2 may have launched this to make things a bit easier for DBA , granting some predefined set of privileges to the user created .

But if you don't believe in accidents and don't want to leave any loopholes , then you may not be very comfortable with this group as it leaves some control over to others

Now lets get into work , db2 has provided us with various tables under SYSCAT catalog schema for administrating authorizations and privileges , here is one way you can look them

Storage groups - A comprehensive solution of handling your data

Monday, 20 January 2014 Storage groups manohar viswanatha

Presumptions :

In this post we assume 3 mount points namely C:\ , D:\ , E:\ .

C:\ & D:\ are considered to be SSD's and

E:\ is considered to be a RAID device or normal HDD

I have gone through Ember's recent post A Few Differences When Moving From 9.7 to 10.1 where I came across storage groups - a new way of handling physical data .I just thought of giving it a try and went through the db2 v10.1 documentation where the feature was introduced , let me tell you db2 is getting rock-steady version by version

Don't be an ORTHODOX DBA

Most of the times we DBA's take on the developers like underdogs , even I used to but things have changed as my years of experience in the industry is growing by , Every person is equally important right from the application developers, DB developers, Network Administrator.... to DBA's . Anyone one role in the Software life Cycle is neglected , the whole application gets affected

I myself have neglected DB Development like creating procedures . Recently , very recently I happened to create some custom stored procedures which will help the DBA's , they save a lot of time

Ill discuss one case I have come across , I usually use the google drive to store my documents where in I store all my custom administrative queries I use daily

I use to copy paste the respective query depending on my need , because all the time you don't have sufficient time to write the query every time , say a tablespace DMS container size is full for some reason and you need to EXTEND it immediately , You cannot frame the query at that peak time which will be time consuming as every second counts

One day there came a issue and I don't have my laptop on hand , so connected through my other colleagues system where in I cannot access my google drive as I'm already connected to VPN , by the time I framed the query and issued the command some transactions in the application got affected

I then started thinking after resolving this issue as to how can I save this time I have wasted in framing my query at that peak time which made to go through basics of STORED PROCEDURES and yes they really help me a lot

Friendship between REORG and Temp Tablespace's - Part2

In my previous post we have discussed about REORG utility and some monitoring utilities for REORG

Friendship between REORG and Temp Tablespace's

Normally when you issue a REORG on a table like one below

REORG TABLE DB2INST1.EMPLOYEE

db2 does the reorganization of the table in the same tablespace it is residing on ,which is not a good thing always , especially when your data is growing there is no sense in doing reorg in the same tablespace and disturbing the other physical objects like tables and MQT's performance

That is where system temporary tablespaces save us from the performance degradations while REORG

So how do we achieve this ? , we create separate bufferpools for all different page sizes i.e., 4K, 8K, 16K, 32K

Let us look into what db2 does when a traditional classic offline REORG on a table is issued

When you issue the REORG TABLE DB2INST1.EMPLOYEE the table EMPLOYEE will go through the following phases shown in the image

Let's go through what actually is happening in these phases

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