Cataloging AS/400 database on DB2 Connect

October 14, 2005

Command sequence to catalog AS/400 database in DB2 Connect:

  • db2 catalog tcpip node <node_name> remote <host_name> server <port_no>
  • db2 catalog dcs database <local_name> as <real_db_name>
  • db2 catalog database <local_name> as <alias> at node <node_name> authentication dcs

Command sequence to catalog the as/400 database that was already cataloged in DB2 Connect.

  • db2 catalog tcpip node <node_name> remote <host_name_or_address> server <port_no>*
  • db2 catalog database <local_name> as <alias> at node <node_name>

*Use the portnumber of DB2 connect not AS/400


SQL0031C File sqllib/bnd/db2clish.bnd could not be opened and SQL0805N

August 4, 2005

Received the above while accessing DB2 database from MS Access. db2clish.bnd was not found in the local machine.

Binding the files didn’t help

o “DB2 bind @db2ubind.lst blocking all grant public” for the
DB2 utilities.

o “DB2 bind @db2cli.lst blocking all grant public” for CLI.

Re-installing the DB2 client fixed the problem.

DB2 Stats!

July 26, 2005

Instance Uptime

db2 get snapshot for dbm | grep -i start

Last Backup Timestamp

db2 get snapshot for db on dbname | grep backup

Database Activation timestamp

db2 get snapshot for db on dbname | grep First

SPM0438 The Sync point manager recovery log is bad

May 5, 2005

You might also see the following message when starting DB2

SQL5043N Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.

with the following message and db2 will not start.

SQL5042N One of the communication protocol server support processes failed to start up.


  • Stop the instance
  • Delete/move all the spm logs located in sqllib/spmlog/SPMLOGSD
  • Also del/move SPMLOG.LCF located in sqllib/spmlog
  • start the instance. Should starup without any error messages.

High Availability Disaster Recovery (HADR)

April 8, 2005

High Availability Disaster Recovery (HADR)

1. How to Set Up and Initialize HADR

Consider two test instances:
Primary Site:
Host Name: ABC.COM
UDB V8.2 Instance: db2inst1
Database: HADRTEST
Standby Site:
Host Name XYZ.COM
UDB V8.2 Instance; db2inst1
Database: HADRTEST

Step by step:

1. Make sure that file systems, table spaces and the containers are identical in Primary and Standby databases.

2. Create Standby database on Standby server:
Create db hadrtest on path

3. Make backup on Primary database.
backup db hadrtest to path

4. Restore the backup from step3 into Standby database.
Restore db hadrtest from path replace history file

Do not issue ROLLFORWARD. (though it is an offline backup, it will be in rollforward pending state)

5. Issue update db cfg to set HADR configuration parameters in Primary databases:

o HADR_LOCAL_SVC: local_port_number
o HADR_REMOTE_SVC: remote_port_number
o HADR_REMOTE_INST: db2inst1

in Standby database:
o HADR_LOCAL_SVC: local_port_number
o HADR_REMOTE_SVC: remote_port_number
o HADR_REMOTE_INST: db2inst1

6. Set the DB2_LOAD_COPY_NO_OVERRIDE registry variable on the primary database.

7. Set ALTERNATE SERVER on both Primary and Standby instances:

On Primary instance
Update alternate server for database HADRTEST using hostname port remote_port_number

On Standby instance
Update alternate server for database HADRTEST using hostname port remote_port_number

2. How to Fail Over
Perform fail over when current primary database fails or is not available.
1. Stop DB2 on failed Primary site completely.
2. Issue the TAKEOVER HADR (with FORCE option, if necessary) command on the Standby database
Now, the Standby database becomes Primary database.
Client will automatically reroute to new primary database. Application server may need to restart to establish connection to new server.
After Primary database is fixed and udb instance is up, issue START HADR to make it a standby server
If you want to take back the primary role, issue TAKEOVER HADR command.
Switching Database Roles
This is when both Primary and Standby are active. After the switching, Primary DB becomes Standby, and Standby becomes Primary.

1. Issue the TAKEOVER HADR command without the BY FORCE option on the standby database:

Automatic client reroute
There are some limitations with use of the automatic client reroute feature:
Automatic client reroute is only supported when the communications protocol used for connecting to the DB2(R) Universal Database (DB2 UDB) server, or to the DB2 Connect(TM) server, is TCP/IP. This means that if the connection is using a different protocol other than TCP/IP, the automatic client reroute feature will not be enabled. Even if DB2 UDB is setup for a loopback, TCP/IP communications protocol must be used in order accommodate the automatic client reroute feature.
If the connection is reestablished to the alternate server location, any new connection to the same database alias will be connected to the alternate server location. If you want any new connection to be established, to the original location in case the problem on the original location is fixed, there are a couple of options from which to choose:

You need to take the alternate server offline and allow the connections to fail back over to the original server. (This assumes that the original server has been cataloged using the UPDATE ALTERNATE SERVER command such that it is set to be the alternate location for the alternate server.)
You could catalog a new database alias to be used by the new connections.
You could uncatalog the database entry and re-catalog it again

Hardware and Operating System Requirements

· Use identical host computers for the HADR primary and standby databases. That is, they should be from the same vendor and have the same architecture.
· OS version should be same, including patches. You can violate this rule for a short time during a rolling upgrade, but take extreme caution.

DB2 UDB Requirement

· Primary and Standby databases must be identical.
· During rolling upgrades, the database version of the standby database may be later than the primary database for a short time. The DB2 UDB version of the primary database can never be later than the standby database version.
· Table spaces and their containers must be identical on the primary and standby databases.
· Primary and standby databases must have the same amount of memory.


· On the standby database there is always a connection to the database (replay Application). We cannot force this connection at any time. Also because of this connection, the database on both primary and secondary is always online. If we need to do any operation for which no one should be connected, we need to deactivate the standby database.
· The stored procedure created in the primary is also copied on to the secondary and will be ready to execute after takeover without compiling/promoting again.
· Changes to database parameters on primary server are not reflected on the standby.
· During the failover, the client connections and session information is lost.

How to use load in HADR

· We have to do load with copy yes option
· If an NFS mount is possible between primary and standby server, choose that file system in the copy yes option and do the load
· If an NFS mount is not possible, the following steps have to be done in the same sequence
o Deactivate the standby database
o Stop HADR on standby database
o Perform load on the primary database with copy yes option. A file will be created with name of the database and a timestamp.
o FTP the file to the standby server to same location as mentioned in the previous load operation
o Start HADR on standby database as standby (In db2diag.log, it is immediately noted that the replay has happened)
o Take a snapshot of database and make sure that the HADR state is set to peer. Once it is set to peer, the file can be deleted.

How to use BLOB/CLOB in HADR

· Insert, update, delete operations performed on a table with CLOBS/BLOBS is not replicated on the other server, unless the BLOB/CLOB is logged. The table has to be defined such that the DML operations performed on these columns is always logged.


HADR is NOT supported on multiple database partitions.
Reads on the standby database are not supported. Clients cannot connect to the standby database.
Log archiving can only be performed by the current primary database.
Backup operations are not supported on the standby database.
Non-logged operations, such as changes to database configuration parameters and to the recovery history file, are not replicated to the standby database.
Load operations with the COPY NO option specified are not supported. (COPY NO is default on databases with archival logging). If we choose COPY NO or don’t choose anything then the tablespace on the primary will be in the backup pending state and tablespace on the standby database will be in restore pending and HADR doesn’t permit restoring a tablespace on either primary or standby databases.
Tablespace level restore is not supported on both primary and standby
Use of Data Links is not supported.



April 6, 2005

SQL8000N DB2START processing failed.

Cause: License Expired or License key have not yet been added.

If you get this after installing You might have to add the license key

As root

/usr/opt/db2_08_01/adm/db2licm -a /ese.sbcs/db2/license/db2ese.lic

License added successfully!!

SQL8017W The number of processors on this machine exceeds the defined
entitlement of…

db2licm -n “Product identifier” “Number of processors entitled to this product”

db2licm -n DB2ESE 2

Updates the number of processors to 2 for the product DB2ESE.

db2licm -l gives more information about the installed products with license info.

SQL0443N Routine “*RCH_1K16” (specific name “”) has returned an error SQLSTATE with diagnostic text “CTE0101 A search engine operation failed

March 9, 2005

Received this error while trying to do db2text (NetSearchExtender) operation on a database that was restored from a different machine.

Noticed the text index was not there in the restored databases. Recreating & Updating the Text index helped.

SQL10004C An I/O error occurred while accessing the database directory

February 18, 2005

If there were no changes made to the Hardware. Then its most likely that the database directory file sqldbdir is corrupted.

You will not be able to create new databases, connect to or drop existing databases.


  • Move all the files found in “sqllib/sqldbdir” to a different location.
  • Now catalog all the databases. For Local databases use: db2 catalog db “dbname”.
  • This should get ride of the error.


I received this error after *successful* installation of DB2 8.1 fixpak 7a on DB2 8.1 Fixpak 2 OS AIX 5.3.

Include logs in online backup (As of DB2 8.2)

February 15, 2005

db2 BACKUP DB “dbname” ONLINE TO “backupdir” INCLUDE LOGS

Above command includes the logs required to restore the database and bring back the database to a consistent point.

And to restore

db2 RESTORE DB “dbname” FROM “backupdir” LOGTARGET “logdir”


To restore just the logs from the backup image

db2 restore db “dbname” logs from “backupdir” logtarget ‘logdir”

Setting up Tomcat for DB2 and some tips

November 16, 2004


  • These were tested on win2k
  • “C:jakarta-slide-2.0-tomcat-5.0.19” is mentioned as $CATALINA_HOME


  • DB2.
  • Latest Java SDK


  • Set an Enviromental Variable “JAVA_HOME” to the Java SDK’s Home Directory.
  • Download & Install the latest binaries from Apache’s Site (Get *.exe for windows to install as a service)
  • Copy “C:Program” file to C:jakarta-slide-2.0-tomcat-5.0.19commonlib” and rename the file to db2java.jar.

Running Tomcat as Service (Windows)

  • If you have installed *.exe file you already have Tomcat running as a service.
  • An work around for this is to run the startup.bat as a schedule task on system startup. This doesn’t shows the Tomcat’s command window (This method doesn’t really install Tomcat as service)

Changing Tomcat’s 8080 Port:

By Default Tomcat listens in the 8080 port (localhost:8080). It can be changed by changing values in the


Change the value of Connector port to desired port.


Connector port=”8080″

maxThreads=”150″ minSpareThreads=”25″ maxSpareThreads=”75″

enableLookups=”false” redirectPort=”8443″ acceptCount=”100″

debug=”0″ connectionTimeout=”20000″


Restart Tomcat to see the changes.

Enabling Tomcat for SSI (Server Side Includes)

Rename $CATALINA_HOME/server/lib/servlets-ssi.renametojar to $CATALINA_HOME/server/lib/servlets-ssi.jar.

Remove the XML comments from around the SSI servlet and servlet-mapping configuration in $CATALINA_BASE/conf/web.xml.

DB2 Catalog Commands

Tomcat may have to be restarted if you re-catalog an existing database that is being used in your code.


Looking at the Tomcat log files will help you solve the error messages that you see on browser.

Location of log files $Catalina_homelogs


Powered by Wordpress and MySQL. Theme by Shlomi Noach,