Which is better db2expln or db2exfmt?

As a database administrator sometimes we definitely face query slowness issues. For this IBM provided tools to analyse the query and get few details about the plan how it is accessing the table and indices of the table. The main tools provided by IBM in DB2 are db2expln & db2exfmt. Both these tool gives the same output such as how the table and index scans are? how much time it is taking to get the results back. But the which one is better and what is difference between these two tools? why db2 provided two tools for single purpose.

Lets see how these two works.

DB2EXPLN:

Before using db2expln we need to check whether the tables related to this tool are exists or not. If these tables does not exist we need to create the tables as below:

Read more

How to resolve SQL20249N The statement was not processed error?

Hello All, recently we cloned the OS layer to build the DEV environment same as QA. As part of this, we prepared to restore the old DEV database backup into newly build DEV database. Then after the system support team will shut down the old DEV machine.

We restored the database to the new dev database and changed the hostname of the new server same as old one. So, the hostname change activity has to be done from DB side too. We did all the process and tried to connect from the Data Studio to test the connection establishment. We succeeded in connectivity, but while trying to get the list of tables Data Studio throws an error such as "Fetching the Schema failed"

Read more

Script to Reduce the tablespace size and reduce the free pages

#!/bin/bash

# Establishing database connection
db2 "connect to itgdb"

# Gathering the tablespaces other than temporary and placing them in a file
db2 list tablespaces show detail | grep -i -A2 name | tac | sed '/Temporary/I,+2 d' | tac | grep -i name | awk '{print $3}' > /tmp/TablespaceList.out

# Using for loop to get the tablespace name one by one
for i in `cat /tmp/TablespaceList.out`
do

# Gathering free pages count for each tablespace
db2 list tablespaces show detail | grep -i -A9 $i | grep -i 'free pages' | awk '{print $4}' > /tmp/FreePages.out

FreePages=`cat /tmp/FreePages.out`

# Comparing whether the free pages are more than 1000 or not
if [ $FreePages -gt 1000 ]
then

# If the free pages are more than 1000 then reducing them
db2 "alter tablespace $i reduce max"
fi
done

Read more

Monitoring database backup performance in db2

Monitoring backup performance:

Sometimes it is difficult to know why the backup process is slow down. So for that we need continuous monitoring on backup process. Here we present some scenarios and monitoring steps to monitor the backup process in db2.

Starting with DB2 V10.1 FixPack 2, every successful backup or restore operation logged into the db2diag.log file. This feature also exists in DB2 V9.7, but must be enabled by using the DB2_BAR_STATS registry variable.

The meanings of the various columns are as follows:

Read more

Basic concepts that are useful to design and implement a successful backup strategy in db2

The following concepts are critical to designing and implementing a successful backup strategy:

• --> Balanced table spaces are the primary factor
• --> Difference between system managed space (SMS) and database managed space (DMS)             tablespaces
• --> What pages are included in an incremental or delta backup

Read more

What happened in the background while taking backup in DB2?

Read more

Important Configuration Parameters in DB2 part-1

Read more