Blogging to benefit Information Management Support customers worldwide. This blog is administered by Andrew McCarl (amccarl) and Kate Dawson Nichols (KateDawson), and follows the IBM Social Computing Guidelines.
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:
Here is a quick way to find the enhancements included in Fix Packs for DB2 For Linux, Unix and Windows for different versions:
DB2 Version 10.5 for Linux, UNIX, and Windows fix pack summary
DB2 Version 10.1 for Linux, UNIX, and Windows fix pack summary
DB2 Version 9.7 for Linux, UNIX, and... [More]
Hello DB2 Readers!
Looking for useful tips to solve some DB2 for Linux, Unix and Windows common problems. Here is a knowledge Collection of new DB2 Technotes published in Q1 2014 organized under different categories. Please review.
"The registry does not exist" error messages in db2diag.log for file global.reg
db2pd errors out with 'Failure: pdRememberInstanceOwnerID, rc: 0x870F00B4'
DB2 LUW has two basic edition classifications Clients and Servers. Clients include drivers and full DB2 clients. Servers include both DB2 and DB2 Connect servers. DB2 Clients do not require a license to use when connecting to DB2 LUW servers. DB2 servers include DB2 client code and if no permanent license is installed the server functionality will be unavailable once the trial period expires but the client code may still be used. To license purchased DB2 LUW server products two licenses will need to be installed.
One license referred to... [More]
Looking for a way to test Automatic Client Re-route using a simple JDBC program that uses IBM data server driver for JDBC & SQLJ (JCC) & JDBC DriverManager Connection?
Here are the steps to follow:
Step 1 : UPDATE ALTERNATE SERVER INFORMATION in BOTH PRIMARY & STANDBY SERVERS:
Lets assume that following are the Primary and Standby Server's hostnames and Port numbers.
Database: SAMPLE on both systems.
a) On DB2 Primary system:... [More]
Looking for step by step instructions for Installing DB2 on Windows?
Here are the education modules available with detailed description of each step in DB2 installation on Windows Operating System.
Installation of DB2 on Windows - Overview
DB2 installation on Windows
Fix pack upgrade installation on Windows
Refresh, modify, or repair an installation on Windows
Here is a collection of useful DB2 Performance Tips from our DB2 Advanced Problem Determination team, please visit this link for details which currently includes the following blogs:
Mystery behind SQL1229 error on DPF systems
What to do when CPU spikes up
Range Partitioned tables and long compile times
How to track and collect data for a long running query
Using Virtual IP (VIP) provides a more elegant solution for Automatic Client Reroute between HADR Primary and Standby servers. The client does not have to be aware of the IP address of the Primary or the Standby server and can connect to the database only via the VIP. In cases where there have to be changes made to the Primary and Standby IP address/hostname, the client is still able to connect to the Primary using the VIP. Here is a demo showing VIP in action
Working HADR database pair (... [More]
As we wind down 2013, we thought it would be fun to see our most popular posts of the year:
New to IBM InfoSphere DataStage Server ? First step... Download product!
Help shape future enhancements to IBM Support Portal, Fix Central, My notifications, Service Request, and more
IBM Netezza product names and models numbers
IBM Universal JDBC Driver behavior when calling ResultSetMetaData.getColumnName and getColumnLabel against a DB2 LUW server
discuss on developerWorks!
Looking for information about end of support dates for DB2 products on Linux, Unix and Windows to plan ahead for upgrade?
Here are the some links you will find it useful.
List of End of support dates for different versions of DB2 Products on Linux, Unix and Windows:
Common FAQ on End of Support products:
Check this out!
Here is a full list of Developer Works articles on DB2 for Linux, Unix and Windows Optimizer that our experts have published to this date which you may find useful.
1) Get the most out of DB2 optimizer: Leveraging statistical views to improve query execution performance
2) Recreate optimizer access plans using db2look
3) ... [More]