Zinox Web Log

Here is what I wrote to remind me....

DB2 11.1 BLU with DPF Monitoring scripts

I just wrapped a very large PoC for BLU with DPF with very high ingestion rate (110K messages per second) and as well as very high volume point queries (1500 queries per second) and I used the following queries to measure the various parameters and optimum tuning. The...

read more

db2haicu disable / enable and scripting

It is recommended that you always update RSCT / TSA from db2 software media and not from IBM Fix Central. The reason - DB2 is tested and certified with the version that ships with the DB2 software. If you go to server_t/db2/linuxamd64/tsamp and run db2cktsa command...

read more

DB2 pureScale – No Product License found

In db2diag, you may see this message - No product license found. Sample output:  2017-05-19-13.00.58.511626-240 E2575E1007 LEVEL: Severe PID : 35363 TID : 70366589481392 PROC : db2start INSTANCE: purf01 NODE : 000 HOSTNAME: va33dlvudb001 FUNCTION: DB2 UDB, high avail...

read more

DB2 pureScale instance creation hangs after GPFS

Hang Issue due to SMAP While creating DB2 pureScale instance, it appears that the node becomes unresponsive under RHEL 7.2. If you reboot the node and look at the /var/log/messages, you may notice these several messages: kernel:BUG: soft lockup - CPU#1 stuck for 23s!...

read more

DB2 pureScale Health Check

More and more customers are now asking for some way to check the health of the DB2 pureScale system. Let's just focus on few early diagnostics before we jump to the deep exploration at the SQL statement level. This is just akin going to primary health care physician...

read more

How to detect log full condition

I have seen working with hundreds of customers a chronic LOG FULL condition and the shortcut people use is to increase the LOGPRIMARY and LOGSECOND. This solution works well for a single unit of work that can not fit into the total log space available. So, first let's...

read more

Kernel upgrade and impact on pureScale

In Linux environment, it is a common practice to upgrade the kernel for security patches, vulnerability fixes etc. And if you are running a DB2 pureScale environment, it is likely that you are going to break the GPFS GPL as soon as you upgrade the kernel and this will...

read more

GPFS Disk Cleanup

During pureScale instance creation, you might run into instance creation failed condition. Under some circumstances, it is possible that db2icrt does not perform a proper clean-up for GPFS and it may fail next time complaining that the disk is already a GPFS disk....

read more

SQL fishing using WLM

The SQLs while DB2 is up and running can be captured in different ways such as: Query column STMT_TEXT from table functions such as MON_GET_ACTIVITY or WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES, and MON_GET_ACTIVITY_DETAILS --> SQLs that have been submitted but not...

read more

Automatic Client Reroute – Templates

Here are two templates that one can use to make Automatic Client Reroute to work properly in a HADR environment. Please note that these instructions are only valid for HADR pair in which the second machine is ready to takeover. I will publish / update this article...

read more

Rebuild TSA Resources in HADR Environment

This is about how to rebuild TSA resources in HADR environment if you have used db2haicu in past but added resources such as tie-breaker disks that are not supported by the db2haicu tool. So, in case of a difficulty - how do you get back soon. You can always recover...

read more

DB2 11.1 Rebuild TSA Resources

When you get errors like this: "SQL1517N db2start failed because the cluster manager resource states are inconsistent." or something like "Cluster manager resource states for the DB2 instance are inconsistent. Refer to the db2diag.log for more information on...

read more

DB2 pureScale Install Problem Determination

RSCT License Issue $ db2start 128 SQL1677N  DB2START or DB2STOP processing failed due to a DB2 cluster services error DATA #9 : SQLHA Remote Command Output, PD_TYPE_SQLHA_COMMAND_RESPONSE, 3508 bytes commandResponse->callRC: 0x00000000 commandResponse->output:...

read more

DB2 purescale Install GPL Error

GPL Compile Problem When you try to install DB2 pureScale on Linux platform (RHEL or SLES), you may receive the error. ERROR: A missing Linux package is preventing the compilation of the IBM General Parallel File System (GPFS) Portability Layer (GPL) module. The...

read more

db2start add member fails

If you try to add a member to a running cluster, you may get the following db2start error. ERROR: The "db2start add MEMBER 2 hostname node04.purescale.ibm.local netname node04.purescale.ibm.local port 0 -iupdt" command failed with the return code: "55", and following...

read more

Install Dropline Neu! Icons in CentOS 7.2

One of my most favorite icons on GNOME has been Dropline Neu! for many years and I still have a copy of it since 2006. It is now hard to find. Download ICON-DroplineNeu.tar.bz2 And, copy to /tmp directory # cd /usr/share/icons # tar xvfj /tmp/ICON-DroplineNeu.tar.bz2...

read more

Drop a member in DB2 pureScale by Force

If you need to drop a member in DB2 pureScale, the db2iupdt process requires that member to be available during the drop process. But, the actual world is more cruel than what db2iupdt thinks. For example: If you have a 3 member cluster and someone moved / migrated...

read more

Enable Fast I/O Fencing Scipt for Linux

If you install DB2 pureScale in Linux, the Fast I/O Fencing does not get enabled automatically. If you refer to Knowledge Center for DB2, you will find number of steps to enable fast I/O fencing. After DB2 instance is created and databases are created, I usually run...

read more

Find out Slot number of PCI Card

Use dmidecode -t slot to find out the slot numbers that are in USE and the Bus Address. Use that bus address in lspci -s <busaddress> to find out what is in the slot. For example: I have NIC in slot-7, Q-Logic in Slot - 2 and Mellanox in Slot - 3. I can then...

read more

WWNN and WWPN of FC Card

A FC card has a World Wide Name (WWN). Card may have two ports and each port is known as by its World Wide Port Name (WWPN). I seemed to have confused myself with WWN, WWNN and WWPN and didn't understand the difference as this is not my topic. In the example below :...

read more

DB2 pureScale rebuild TSA Resources

In order to be a successful DB2 pureScale DBA, one has to learn how to diagnose and fix TSA. Though, this is not necessary but it will help to fix cluster in case you run into situation when you get messages like "SQL1517N db2start failed because the cluster manager...

read more

TCPIP Tuning at VIOS and AIX

This is for for my own reference and does not constitute any recommendations. VIO Server Changes Verify flow_control is set to yes - This is the default setting lsattr –El ent0 | grep flow and lsattr –El ent1 | grep flow chdev -l ent0 –a flow_control=yes chdev -l ent1...

read more

Avoid Tie Breaker Disk

It is possible to avoid tie breaker disk in pureScale and HADR just by following a simple majority rule. You need a tie breaker disk in pureScale and HADR if number of hosts are even. So, in HADR you have only two nodes so your need a tie breaker disk. But, even in...

read more

RSCT Commands – Cheat Sheet

Reset RSCT Configuration (completely remove all cluster configuration) # /usr/sbin/rsct/install/bin/recfgct Change the tie-breaker # chrsrc -c IBM.PeerNode OpQuorumTieBreaker='Operator' Find out which tie-breaker is in use # lsrsrc -c IBM.PeerNode OpQuorumTieBreaker...

read more

Clear Reservations from Tie-Breaker

Read reservations: /usr/bin/sg_persist --in --no-inquiry --read-reservation --device=/dev/sdc /usr/bin/sg_persist --in --no-inquiry --read-key --device=/dev/sdc Prepare for cancel: /usr/bin/sg_persist --out --no-inquiry --register --param-sark=0x1 --device=/dev/sdc...

read more

Tie-Breaker Disk for HADR or pureScale

I had documented same for zLinux here http://www.zinox.com/archives/182 When you use a tie-breaker disk in pureScale or in HADR, it must be Write Exclusive Registrants Only. It is a good idea to test if the tie-breaker disk is working as expected or not. The...

read more

GPFS, RSCT and TSA Version Info.

RSCT Version [root@node02 iscsi]# /usr/sbin/rsct/install/bin/ctversion -b rliss005a 3.1.5.5 14182.07:51:59 TSA Version [root@node02 iscsi]# /usr/sbin/rsct/bin/samversion rsa322s008a 3.2.2.8 Jul 18 2014 10:02:26 # lsrsrc -Ab -c IBM.CHARMControl # lssamctrl GPFS Version...

read more

Clean-up pureScale

If you want to clean the system having pureScale or want to drop pureScale instancefor installing a fresh instance of pureScale, follow on: Obviously, you want to follow the steps given in DB2 Knowledge Center first. If you have a working pureScale install where you...

read more

DB2 pureScale – Online Rolling update

These note are valid only on AIX. Please note the following: There are two types of updates - System update (applying eFixes or Service Packs on AIX). If you are applying the system packs or efixes on a pureScale host, please call the DBAs and work in coordination so...

read more

X Display Issues as sudo

When you do sudo to another user, you may have to do extra things to get X display working or any GUI working. Many organizations do not allow root login so X window cookie does not get generated and hence GUI does not work. I prayed to Google God for solutions and...

read more

DB2 10.5 pureScale TSA Recovery

A third party single sign-on application was installed on two nodes of db2 cluster having 2 CF and 4 members. The pureScale cluster started showing signs of trouble and naively, we did not think the impact of the software installed. This single sign-on software (as we...

read more

Client Connection Attributes and WLM

Client Connection Attributes When you establish connection to DB2 using Java programs, you need 5 minimum attributes DB2 server name or IP address Port Number (socket connection) Database name User ID Password This could be defined as a URL in the Java program such...

read more

Change mount point in pureScale Cluster

When we install DB2 instance as a pureScale, we can specify the name of the mount point by using option instance_shared_mount. If we do not use this option, the mount point name is generated by the db2icrt command as /dv2sd_<timestamp> and this may not be the...

read more

Monitor Crash Recovery Script

If DB2 was processing a very long running transaction (say updating millions of rows) and for some reason like power failure, server goes down. When you start the server and activate the database, it may take a long time as it needs to do the crash recovery. This is...

read more

How to find out LOG HOG

If you run into LOG FULL condition in DB2 with SQL error code: SQL0964C, you need to increase the size of either of the following: LOGPRIMARY, LOGSECOND or LOGFILSZ. So, you change that even after that you again run into LOG FULL condition. What is happening? You can...

read more

Change IP address in pureScale cluster

These instructions are for changing the IP address in a pureScale cluster. Please remember that the IP address can be changed easily for DB2 but it is RSCT and GPFS that requires special handling to change the IP address. You change the IP address one by one in a...

read more

Google God

I started using Google from very early on (Since Feb 1998) and since then it has remained my home page. Needless to say that I now consider Google as God since it knows everything. In my day-to-day conversation, I ask people "Did you ask Google God?" So the two words...

read more

Test Automatic Client Reroute

Wouldn't it be nice to do some Java testing without writing a line of code? For the following to work, it is necessary that db2jcc.jar and db2jcc_license_cu.jar are on the CLASSPATH. If you login as db2 instance user, these jar files should be in the CLASSPATH...

read more

DB2 pureScale GPFS Error

Say for example, you are creating a file system using db2clutser command in DB2 pureScale and it fails with this error: # db2cluster -cfs -create -filesystem db2data1 -disk /dev/dm-2 -mount /db2data/data1 There was an internal db2cluster error. Refer to the diagnostic...

read more

Validation of 10GbE in DB2 pureScale 10.5

Additional Packages After installation of basic Redhat 6.2 OS, the following additional components are required. # yum install perftest # yum install sysfsutils # yum install kernel-devel # yum install compat-libstdc++ # yum install tcl # yum install tk # yum install...

read more

Informix OAT Issues – SLES 11 SP3

I downloaded Informix 12.10.FC2 on my Linux SLES 11 SP3 using part number CIM0GEN. After unzipping of the Informix media in /tmp/informix, I see the following files. -rw-r--r-- 1 root bin 10201 Aug 30 00:53 bundle.properties drwxr-xr-x 3 root bin 4096 Aug 30 00:51...

read more

No Frills – DB2 10.5 WLM

In DB2 10.5 with default WLM configuration, you can achieve something very special that you might not have given a thought. WLM is one of my favorite topic on which I can talk non-stop for few hours. Based upon my experiences working with large customers, here is a...

read more

Post Login Script in DB2

One of less known but very useful feature of DB2 is the post login script after an application makes a connection. DB2 database has a configuration parameter known as CONNECT_PROC which is name of the database procedure which will be executed everytime someone...

read more

db2dsdriver validation failed

After specifying entries in db2dsdriver.cfg, the db2cli validate -dsn >dbname> does not show that the client actually read db2dsdriver.cfg file. For example: A sample db2dsdriver.cfg file: db2psc@node01:~/pot_purescale/04application> db2cli validate -dsn psdb...

read more

Automatic Backup when USB disk is plugged in

Management of my shoe-closet computer center requires that I should take some steps to protect the data by taking a backup. In last 10 years, I never took any backup and luckily I was never in the situation to have lost the data. The servers kept on running and...

read more

cups-1.6.3 RPMs for SLES 11 SP2

Usually enterprise distributions like SLES or Redhat are slow to adopt latest in consumer products such as CUPS used for printing. The version of cups on SLES 11 SP2 is 1.3 when the latest version at the time of writing this is 1.6.2. The older version 1.2 uses older...

read more

Security for the Paranoid

In today's world, due to some dishonest populace who are like parasites living on somebody's else blood, implemenation of security comes at a heavy price and it has more problems than solutions. It is also a great business model for the companies to exploit...

read more

DB2 Backup – DB2_BCKP_PAGE_VERIFICATION

When taking a DB2 backup, if you get the following error: $ db2 backup database <dbname> to /backup compress SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request...

read more

DB2 Database Backup Pending State

When database is converted from circular log to archive log, it will force the backup of the database which is a good thing. But, sometime, we do not want to do it. Say, when we are in test environment like testing upgrade from DB2 ESE to DB2 pureScale. If database is...

read more

DB2 pureScale – Role of netmon.cf

Best practice or things that appear to work well in a two nodes cluser or a single node (just with one DB2 and a CF). The cthats subsystem handles heartbeating between nodes. When in steady state, there is a ring formed where each member is sending heartbeats to their...

read more

DB2 pureScale – Switch Role of CF

In DB2 pureScale, when you run the command db2instance -list, it shows the DB2 pureScale topology and which member is up and running and which CF is in PRIMARY mode and which one is in PEER mode. If you want to change the role of CF from PRIMARY to PEER or from PEER...

read more

JBoss and DB2 pureScale

Work Load Balancing In order to utilize DB2 automatic Work Load Balancing for JBoss application, some changes need to be made in the JBoss JDBC driver manager configuration. Myth - Most people think that work load balancing is done by the DB2 pureScale and it is not...

read more

Excel Microsecond Timestamp Difference

DB2 timestamp can show micro seconds and I was trying to generate an excel report showing elapsed time between 2 DB2 timestamps and had to work a little bit to write this excel formulae. Timestamp (Cell B3) = 2013-03-04-13.38.26.677483 Timestamp (Cell C3) =...

read more

uDAPL Version

On AIX, how to find out the uDAPL version? # lslpp -la "*udapl*" Fileset Level State Description ---------------------------------------------------------------------------- Path: /usr/lib/objrepos udapl.rte 7.1.1.15 COMMITTED uDAPL Path: /etc/objrepos...

read more

WAITING_FOR_FAILBACK and Failed Offline

In DB2 pureScale if a node goes down, it is started on another host in a light mode just to do the crash recovery. When the home host becomes available, the host should fail back to its home host seamlessly. For example: This is a sample output from the db2instance...

read more

DB2 pureScale Tie Breaker for GPFS

After you install DB2 pureScale, it is a good idea to check the GPFS tie breaker and to see what DB2 install did to set the GPFS tie breaker. Run the following command. # db2cluster -cfs -list -tiebreaker The current quorum device is of type Majority Node Set. The...

read more

db2prereqcheck utility

While installing DB2 pureScale, if you receive the error: DBT3542E  An instance management task failed because the db2prereqcheck utility encountered an internal error. Reason code: "2", the fix is simple. Edit your /etc/hosts file and check if you have...

read more

Fun Things with DB2 10.1

As I am exploring new features of DB2 10.1, there are some fun things that I can do. Example-1: A table has rows in it. You want to make those rows invisible to all including you. Do this. $ db2 "select count(*) from ps_table" --> You see rows in this...

read more

Quiesce at Instance and Database Level

There are two types of DBAs Ones who are control freak - This article is not meant for you as you have already taken pains to grant privilges to an application user without granting DBADM. You have a very good mechanism to control how applications connect and what...

read more

DB2 pureScale, Fast I/O Fencing and GPFS

This must be read in conjunction with this post. The fast I/O fencing needs to be enabled in DB2 pureScale. This is a feature of GPFS in conjunction with your storage firmware or its driver. In a nutshell, the following procedure must be followed: The file...

read more

Connect Routers with a Wire and use same SSID

This is how I did a setup of two wireless routers with same SSID. Host Router: Linksys WRT54GS (I am replacing that with a Linksys E4200) connect WAN port to the modem. Client Router: Linksys E1000 connected through an ethernet cable with the main router. Do not...

read more

Connect Routers Wirelessly and use same SSID

These instructions worked for me to setup 2 wireless routers having same SSID with a single Internet connection. Note: Both routers connect to each other wirelessly so this setup is useful when it is not possible to connect both routers through a wire. My host...

read more

Run Slim and Effective

On my new Thinkpad W530 with 32 GB RAM and dual 500GB SSD, I am running multiple virtual machines using VMware 9 workstation. So far, I have tested 6 VMs running with a resonable performance. My aim is to run DB2 pureScale using minimum 4 VMs and 2 VMs for the...

read more

Priority Aging in DB2

The Work Load Management (WLM) feature of DB2 is mostly viewed as one of the best but least understood feature. The WLM has two main themes. 1. Use system defined resources such as CPU or I/O usage to do a task faster or slower 2. Use DB2 defined control such as...

read more

Optim Performance Manager – checkLicense Fix

After you install Optim Performance Manager 5.1.1.1, start the web console and try to add a database for monitoring. After establishing the connection, you may notice an opmConfigurationAdapter.checkLicense error and you may wonder that you specified the license...

read more

DB2 pureScale – Pending Online

RSCT is pretty smart to take care of automation but when unsual things happen and may be that logic is not yet there, you may have to intervene as a DBA and that is why you have a niche for yourself. Take this case: The output from lssam shows that two members of the...

read more

DB2 pureScale – Applications WLB, ACR and CA

DB2 pureScale has one of the best features known as WLB, ACR and CA and a brief introduction is here. WLB (Work Load Balancing) - at the unit of work level is the capability unmatched in the industry and built at the driver level where it can distribute the work to...

read more

DB2 pureScale – Failed Offline

The status of the db2 pureScale instance is best determined by the db2instance -list and lssam commands. For example: db2psc@node02:~> db2instance -list ID TYPE STATE HOME_HOST CURRENT_HOST ALERT -- ---- ----- --------- ------------ ----- 0 MEMBER STARTED node02...

read more

DB2 10.1 pureScale in VMware

============================================================== UPDATE for DB2 10.5 Everything holds good for DB2 10.5 except that you need to set the following db2 registry variables after DB2 pureScale instance in created. db2set DB2_SD_SOCKETS_RESTRICTIONS=false...

read more

DB2 10.1 – Disable TSA for Maintenance

If you have set up DB2 10.1 Active / Passive or HADR with TSA, the db2haicu command sets up the cluster manager DBM CFG to TSA and there is no way you can unset that by using db2 update dbm cfg command as it can only be done by the application and that is in our case...

read more

DB2 pureScale and Host Failure Detection Time

DB2 10.1 pureScale provides db2cluster command to do things that are related to the RSCT. For example: You can use db2cluster -cm -list -hostfailuredetectiontime to find out the host failure detection time. node02:~ # db2cluster -cm -list -hostfailuredetectiontime The...

read more

DB2 – Leader, Master and Manager

In DB2 pureScale, we use a RSCT peer domain and a GPFS domain. These domains have couple of machines networked together. In democracy, we choose someone to be a leader / master / manager to create an order. The same type of democracy exists in RSCT and GPFS where the...

read more

DB2 10.1 pureScale and RSCT logs

The GPFS logs are in /var/adm/ras and RSCT logs are in /var/ct/<db2domainname>log/mc directory. If you are lazy like me, create an alias in your profile to change the directory alias rlog='cd /var/ct/db2domain*/log/mc' The directory structure looks like:...

read more

DB2 10.1 GPFS and Fast I/O Fencing

Useful commands for day to day work to manage DB2 pureScale with RSCT and GPFS GPFS: Did you wonder why GPFS commands start with mm? The GPFS started as a IBM research project the the early 90s to build a multi-media (music and video) networked file system for some...

read more

DB2 10.1 pureScale and RSCT

Before Install DB2 10.1 pureScale feature uses RSCT, GPFS and Tivoli SA MP which is an application of RSCT. A DB2 DBA can take care of DB2 but there are additional things one must take care particularly on RSCT. In using DB2 pureScale, one is going from a stand alone...

read more

Poor Man SAN

The open source iscsitarget provides a great way to do SAN on a laptop using VMware. This is good for testing and learning purposes. We use iscsitarget to export devices so that we can use them for the shared database by multiple hosts in a DB2 pureScale environment....

read more

DB2 10.1 HADR

Scott Hayes, CEO of DBI gave me the opportunity to do the IBM DB2 10.1 HADR Presentation on his DB2 Night Show. Follow this link: http://www.dbisoftware.com/blog/db2nightshow.php?id=372

read more

DB2 pureScale and SCSI-3 PR

DB2 10.1 pureScale requires an optional but highly recommended SCSI-3 PR capable storage. DB2 10.1 pureScale requires a mandatory tie breaker disk which should be SCSI-3 PR capable. Optionally, you can configure an IP address to be a tie-breaker disk but this is not...

read more

Cloning VM when DB2 is on

In today's virtualized environment, it is easier to clone a virtual machine from an existing one. When DB2 is already installed with Tivoli SA MP for high availability, we need to also make sure that each host has a unique node id otherwise HA configuration will...

read more

BGInfo for Linux

If you are spoiled using BGInfo of Windows to show some useful information on your desktop, you may try this poorly written but working script that does the same thing. This was written for SuSe Linux but can be adapted for other distribution. If you happen to make...

read more

Generate and copy /etc/hosts file to all hosts

The scripts that I am sharing are used to do the DB2 pureScale install on 'n' number of hosts. One of the issue is to have consistent /etc/hosts file on all nodes. The input file is ip.txt similar to the previous posts.   Script to generate and copy hosts...

read more

Set up passwordless SSH between hosts

If you need to set up a passwordless SSH between multiple hosts, you can try the following script to do it automatically for you rather than manually copying the keys from one hosts to another. The script needs an input file ip.txt that has server information for all...

read more

SQL Monitoring from the Stored Procedure

You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature. Let us understand this through a simple exercise: Step-A:...

read more

Equivalent of Oracle KEEP function in DB2

Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort. It is best explained by an example: Case-1: Oracle query using KEEP function: CREATE TABLE emp(id INTEGER, name...

read more