Part of the IBM Analytics family, focused on Analytic Platform product support. This blog is administered by Andrew McCarl (amccarl) and Kate Nichols (KateDawson), and follows the IBM Social Computing Guidelines.
Recently worked on an issue with DB2 Fault Monitor (FM) process failing to start on a Linux RHEL 6 platform. After following the steps in the technical note below:
the DB2 Fault Monitor process was failing with the following message :
# su - db2inst1
$ . ./sqllib/db2profile
$ db2fm -s -S
Gcf module 'fault monitor' is INSTALLED PROPERLY but NOT ALIVE
Gcf module '/opt/IBM/DB2/lib64/libdb2gcf.so' is INSTALLED PROPERLY but NOT ALIVE
Consider the following scenario, in a DB2 HADR configuration using ASYNC mode. Presently,the log files were extracted from TSM to a local file system on the standby server. Standby database was deactivated and re-activated causing the standby to go into LocalCatchUp state. However, its unable to read the local log file because its considered as a "stale" file and the standby re-enters a RemoteCatchUp state. This process was repeated two to three times and the standby fails to read the log file and switch HADR to RemoteCatchUp... [More]
We often deal with PMRs on "SQL0805N Package not found" error and get questions about DB2 Packages from customers.
We do have lot of technotes and white papers on DB2 packages. I just wanted to consolidate all the information and give you a quick overview of DB2 CLI packages and help you to solve SQL0805N errors.
Packages : Packages in DB2 are control-structure database objects that contain executable forms of SQL statements or placement holders for executable forms or access plans.
Section: A dynamic section... [More]
Here is a simple DB2 command which will display the DB2 products that are installed on the system:
db2ls - it displays the Install path, product level, fix pack details, Special Install Number, Installation date , Installer UID. The db2ls command is the only method to list all DB2 product at Version 9 or later. However you cannot use the db2ls command on Windows operating systems.
db2pd is a very powerful tool which can be used for troubleshooting many complex issue in DB2 like - high memory usage, locking issues, HADR, and collecting various statistics. It can also be used to find out a simple thing like how long the instance has been up.
db2pd with a '-' option displays the uptime for an instance. It displays the number of days and hours the instance has been running. No database connection is required.
$ db2pd -
Database Member 0 -- Active -- Up 4 days 00:13:14 -- Date... [More]
Many have been utilizing the light weight deployment solution i.e. IBM Data Server Driver Package that provides run-time support for applications without the need to install Run-time Client/Data Server Client. We already have DS Driver install demos for Windows out there, but not for Unix platforms, So, I would like to take this opportunity to share a demo of IBM Data Server Driver install in a Unix environment. The documentation is in our Knowledge Center:... [More]
In a DB2 HA environment using TSAMP, here is what goes on in the background when certain DB2 commands are run.
Running db2stop will lock the Instance resource group where the stop is issued against. Consequently the HADR Database will fall out of peer state and the DB2 engine will request that the HADR resource group gets locked, preventing the resources from being restarted automatically by TSA.
Running db2start will unlock the instance resrouce group, once the DB automatically reintegrates, the lock... [More]
Ever wondered what the TSAMP control flags stand for in a DB2 HA environment? Here are some of the most common ones:
" StartInhibited " - Start of dependent resource is not satisfactory (only displayed if OpState is Offline and DesiredState is Online)
" StopInhibited " - Stop of dependent resource is not satisfactory (only displayed if OpState is Online and DesiredState is Offline)
" SuspendedPropagated " - A lock request is propagated to this resource
Hello db2 DBAs
Have you encountered any db2 instance hang situations where in the db2fodc -hang command also hung? Fear Not! I have created a template that walks through the commands to execute at various levels i.e. db2 instance / database etc manually to collect the hang diagnostics and provide that to the support team. It would make the diagnosis of the hang much easier :)
Clean out sqllib/db2dump directory, move or remove any FODC_* directories from this directory to keep the db2support.zip... [More]
Here are the links with details on what to collect when you run into any issues with IBM Infosphere Federation Server. Gathering this information before calling IBM support will help you familiarize with the troubleshooting process and save you time.
Collecting data for installation failures for InfoSphere Federation Server versions 9.x
The pureScale client affinity feature allows client specification of a single primary server and failover
sequence to alternate servers and all rerouting is controlled by the client driver.
That means all you have to do is basically configuring the right DB2 JCC JDBC properties in your WAS(Web Application Server) side.
However the reality is many people has difficulty about how to have right setting about pureScale client affinity during configuration and failover test.
For having right configuration for them, some people take over... [More]
Inoperative DB2 Package:
Packages in DB2, can be dependent on certain types of database objects such as tables, views, aliases, indexes, triggers, referential constraints and table check constraints. If a package is dependent on a database object (such as a table, view, trigger, and so on), and that object is dropped, the package is placed into an invalid state. If the object that is dropped is an UDF, the package is placed into an inoperative state.
For an inoperative package, in the SYSCAT.PACKAGES system catalog view, the VALID... [More]
When installing DB2 Universal Database for Linux, Unix, and Windows version 10.5, the installation script no longer lists the "ESE" (Enterprise Server Edition) and "AESE" (Advanced Enterprise Server Edition) options as it did with earlier versions. They have both been replaced with the option of "SERVER". The other options have remained the same.
The reason for this is that whether you have "ESE" or "AESE" depends solely on the license you install (EG the actual libraries that are... [More]
When trying to convert your database to BLU. You may see that some tables fail. This can be seen like this.
In the progress of the convert utility. The below will be seen.
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"TANJINXU"."ACT" 18 0 COPY Failed
This is caused by the utility heap size. The convert utility utilizes the admin move table function. If the utility heap size is not... [More]
One of our clients has a JCC application going through a DB2 Connect gateway connecting to DB2/Z database, instead of direct connection to DB2/Z. They wanted to know how to setup the automatic client re-route(ACR) if the DB2 Connect goes down.
Here are the steps:
In the case of the DB2 Connect server, because there is no requirement for the synchronization of local databases, you only need to ensure that both the original and alternate DB2 Connect servers have the target IBM mainframe database cataloged in... [More]
From DB2 V9.7 Batched INSERT statements can return automatically generated keys.
If batch execution of a PreparedStatement object returns automatically generated keys, you can call the DB2PreparedStatement.getDBGeneratedKeys
method to retrieve an array of ResultSet objects that contains the automatically generated keys.
Recently I had a customer trying to use getDBGeneratedKeys method with Batch inserts in a JDBC application using JCC driver and was looking for an example.
Here is an example of how to use the... [More]
If you need regular database backup during night or weekends, use DB2 automatic maintenance feature.
Here is DB2 V9.7 Information Center URL:
1) How automatic maintenance job run?
The automatic maintenance feature has two phases. 1) Evaluation and 2) Execution.
1) Evaluation: Check whether we do task. Evaluation activity starts in every 2 hours since database is activated.... [More]
Using a simple test case. This can be easily observed. Document was exported using a utility that applies a byte order mark to text documents.
db2 "create table numbers (col1 char(10),col2 char(10),col3 char(10))"
DB20000I The SQL command completed successfully.
[db2v97@ts-carose2 ~]$ db2 import from numbers.csv of del insert into numbers
SQL3109N The utility is beginning to load data from file "numbers.csv".
SQL3110N The utility has... [More]
We welcome you to the new Workload Replay Community! Click here to join today !!!!
This community provides a central location for users and business partners to find and share information about using IBM® InfoSphere® Workload Replay. Please help us make the community more valuable by providing your input and contributing information.
My friends contact me to ask db2 issues. Most of them are known issues or not difficult (at least I think) enough to be resolved by themselves. The main reason is that they don't have enough information where the documents are.
I'm writing a document for db2 troubleshooting before you are prompted to open DB2 ticket for defect. DIY and save time with the following URL addressing Frequently Asked Questions and known issue on OS.
1) If you use Linux or Oracle Solaris, check whether your OS is supported.
- DB2... [More]
I would like to share with our readers the license issue that one of our customers ran into.
Its straightforward and I'm sure you have seen this license problem before and know to resolve it but I would like to show the symptoms, what options we had, which one did we choose to fix the license error .
The product installed was DB2 10.1 Enterprise Server Edition (ESE) as seen here below:
Product name: "DB2... [More]
Traditional BIND command is not part of the IBM Data Server Driver Package.
You can use the DB2BINDER utility to bind packages from the IBM Data Server Driver Package.
You can follow the below steps:
Open the DOS command window. Change the directory to your IBM Data Server Driver Package's bnd directory. e.g. C:\Program Files\IBM\IBM DATA SERVER DRIVER\bnd
Run the DB2Binder command from that directory.
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bnd>java... [More]
For most of us, we refer the word ' ARCHIVE ' in DB2 to DB2 Transactional Logs archiving.
There are other items that you can archive as well. Let's explore what all we can archive:
1. DB2 Transactional Logs - This refers to DB2 transactional log archiving. I assume you are familiar with this concept. Hence I won't discuss it in detail here. If you have any question, please feel free to ask us. You can add a comment to this article at the bottom.
2. DB2 Diagnostic Logs Archiving -... [More]
I am quite sure that most of you are using 'svmon' utility with various option to monitor the memory utilisation on AIX box. The most useful option I found is 'P'. It quickly shows you the approximate memory consumption at process level:
$ svmon -G
size inuse free pin ... [More]
When you download IBM Data server driver for JDBC and SQLJ, the tar or zip file (For Example, DB2 V10.1 FP3 driver download is v10.5fp3_jdbc_sqlj.tar.gz file) contains following files:
Wondering which file to use in your application? Let me explain what those files are and you can decide on which file to use for your application.
db2jcc4.jar and db2jcc.jar, both of them are DB2 JDBC driver jar files and are Type 4 JDBC drivers.
db2jcc.jar includes... [More]
Last week, I ran into a customer problem where the 32-bit ODBC User DSN test from 32-bit ODBC Data Source Administrator (C:\Windows\sysWow64\odbcad32.exe) for a remote database running on AIX was failing with below error with 32-bit DB2 Data Server Driver Package 9.7 FP0 on Windows 7 64-bit:
We tried to create a new User DSN from 32-bit ODBC Data Source Administrator, but we could not, since it failed with the same error.
On their Windows XP, machine they have the same setup, the DSN... [More]
I had opportunity to implement AES encryption from a Java program using a DB2 JDBC driver with ORACLE Java. Here are all the steps I took:
1> On client machine, installed ORACLE Java 7:
C:\Program Files\Java\jdk1.7.0_45\jre\bin>java -version
java version "1.7.0_45"
Java(TM) SE Runtime Environment (build 1.7.0_45-b18)
Java HotSpot(TM) Client VM (build 24.45-b08, mixed mode, sharing)
2> On server, I changed the... [More]
Continuous availibility is the concept that enables 24/7 access to IT-enabled business functions, processes and applications.
And minimizing planned downtime is what business and IT departments consider seriously nowadays.
Especially, minimizing and setting the appropriate downtime is a bit challenging decision in case of DB2 version upgrade or migration to another system, because we also need to consider the time for getting back to original system in preparation for any unexpected problem after the planned change... [More]
Here are some of the interesting DB2 on LUW resources:
The DB2Night Show:
SQL Tips for DB2 LUW: