How to connect and fetch result in DB2 through python?

Till now I have automated db2 tasks using shell script, now I'm using python as automation tool.

To use db2 in python, we must install ibm_db2 plugin in python.

apt install python-pip
pip install ibm_db

Then we need to import ibm_db function in our python script.

Now I'll show you a simple script to connect and fetch result using Python script.

Read more

Installation of DB2 V11.5 software using db2_install

Before preparing to install DB2,we should check the required library files on OS.

Note: I installed DB2 on Ubuntu so used apt-get to install libraries.

Installation of library files:

To verify whether the required packages are installed on OS use below command:

 

dpkg --get-selections | grep -i <package-name> 

dpkg --add-architecture i386 #Command will enable i386 Architecture

apt-get update

We must install below library files:

Read more

Steps to ask yourself while troubleshooting in DB2.

First important step is to describe the problem completely. You will not know where to start and what investigation you need to do without a problem description.

Basic questions you should ask yourself:

==> What are the symptoms?

==> Where is the problem happening?

==> When does the problem happen?

==> Under which condition problem happen?

==> Is the problem reproducible?

Read more

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