db2cli.ini Mysteries Revealed!

Posted by Frank Fillmore on January 16, 2009 under DB2 for Linux Unix Windows, DB2 for VSE&VM, DB2 for z/OS. Tags: , , .

After a Winter Solstice break, I’m back to blogging.

In recent days, I have been working with customers who have distributed applications that access a host DB2 system: one is DB2 for z/OS and the other is DB2 for VSE.  In each case, the customer is using a tool – Visual Basic and a Hyperion three-tier (browser, mid-tier server, and host database) reporting tool respectively – that exploits the ODBC API.  While ODBC has been around for decades (I used it to access SQL/DS data on VM with MS Excel at The World Bank in 1991), some review is in order.

Open Database Connectivity (ODBC) is a “lingua franca” or common language used by, primarily, Windows applications to access data stored in a variety of databases, data stores, and file systems.  The implementation of the ODBC API meant that each desktop productivity tool or programming language didn’t need to have an individual interface for each of the possible back end data stores that customers might want to access.  For example, over the years I have used Word Perfect, Excel, Access, DataDirect Explorer, Hyperion, PowerBuilder, VisualBasic, FoxPro, Clipper (among many others) to interface with data stores as disparate as DB2, Oracle, SQL Server, Sybase, and Access.  ODBC sat in the middle between the application/tool and the data store transforming a request into a common ODBC call, then delivering to the database a request it understood – in most cases an SQL Data Manipulation Language (DML) statement such as SELECT or UPDATE.  One final note is that IBM’s Call Level Interface (CLI) is a superset of the ODBC API.

The reason for this lengthy introduction is to establish a foundation to talk about how you can manage and manipulate the way an ODBC tool or application interacts with DB2 as a back-end database.  In order to access DB2 on a host (z/OS, System i, z/VSE, z/VM) or distributed (Linux, Unix, Windows) platform there needs to be a DB2 client.  The client can be DB2 Connect to access a host database, a version of DB2 distributed database (e.g. Enterprise Server Edition), or the new consolidated Data Server client.  The client might be located on a mid-tier platform along with a web server.  End users would only need a browser to connect to the web server; the mid-tier application would issue calls to the database.  Or each end-user can have an appropriate client installed on their desktop.

Wherever the client is located in your topology, there will likely be a directory called SQLLIB and in it will be a file called db2cli.ini.  The db2cli.ini file contains a series of parameters to govern the ODBC interface to your database.  When configuring the client database connection(s), you need to specify that they will accept ODBC API statements.  This can be done when manually configuring the database connection via the GUI wizard.  Alternatively, you can issue the follow command from the DB2 Command Window:
db2 catalog system odbc data source <database alias>

So what does the db2cli.ini file look like and what are the important parameters?

Let’s say we have cataloged a connection to a remote DB2 database using the alias MY_DB.  We specified that this is an ODBC database by either of the two methods mentioned above.  Here is the db2cli.ini entry.  You will have one entry for each database connection that you have cataloged.

[MY_DB]
UID=userid
PWD=password
SysSchema=SYSIBM
SchemaList=”’SYSIBM’,’PAYROLL’,’FINANCE’”
DeferredPrepare=1
ConnectTimeout=10
ReceiveTimeout=120
LockTimeout=60
AppendForFetchOnly=1
AutoCommit=1
ConnectType=1
CursorHold=0
TxnIsolation=1

So what do we have here?  Overall I have configured an ODBC read-only reporting tool to access DB2.  Note: the explanations below are based on the values chosen for each parameter in the example.

You might or might not want to imbed the userid and password in an in-the-clear text file.  Some shops do this to minimize authentication and privilege maintenance by specifying a single userid and password to be used by everyone for database connections.

SysSchema gives the creator or owner name for the DB2 system catalog (metadata) tables on the target server.  Typically this is SYSIBM although on DB2 Server for VSE&VM it’s SYSTEM.  SchemaList is a list of creator/owners of the tables that will be accessed by this application.  By limiting the number of schemas, you reduce the time it takes to populate the table names in GUI point-and-shoot SQL generation tools like MS Query.  Note that each schema name is enclosed in single quotes(‘) and the entire string is enclosed in double quotes (“).

The DeferredPrepare reduces network traffic by sending the dynamic SQL prepare statement along with the execute request.  The three adjacent Timeout parameters will cause the request to fail if a connection to the server database is not made within 10 seconds, data is not returned from the server in 120 seconds, and lock wait extends for 60 seconds respectively.  Obviously these thresholds are only examples and will vary from shop to shop.  The benefit to setting them is that in most cases, the default timeouts are indefinite.  When users get tired of waiting, they cancel the application or reboot.  That means resources will continue to be consumed on the DB2 server even after the end-user has given up.  In addition, by setting and tuning these parameters, the database administrator can focus on the reasons user requests are lengthy (e.g. timeouts due to I/O waits vs. locking).

AppendForFetchOnly means that FOR FETCH ONLY will be added to the end of all SQL SELECT statements.  These unambiguous read-only selects – remember, this is a read-only reporting database – might help with blocking from the server to the application (more on this is a future post).  AutoCommit  makes each individual SQL statement sent to the server its own logical unit of work; no ROLLBACK WORK will be possible.  ConnectType is set for Remote Unit of Work meaning that only a single database will be accessed within a logical unit of work.  CursorHold is set to destroy cursors after each commit (in this case after each SQL request because AutoCommit is also specified).  TxnIsolation will use the uncommitted read (“dirty” read) isolation level to avoid locking.

The goal here is to optimize SQL SELECT statements and to minimize DB2 server resource consumption as much as possible.  You application profile might be very different and require different settings for these parameters.  For example, if your application has complex update logic and many SQL statements comprise a single logical unit of work, you won’t want to specify AutoCommit=1.

A full description of all of these parameters, and many more, is found at the DB2 Information Center
(http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp).

Changing the Hostname for a DB2 Server

Posted by Frank Fillmore on November 14, 2008 under DB2 for Linux Unix Windows. Tags: , , .

Sorry I haven’t posted in a little while.  October was a 250 hour month with a critical customer project and the IBM Information OnDemand Conference (IOD – you know all about IOD from Kim).  My VP of Biz Dev is all about trying to make her “number”, so it’s been busy.

The Fillmore Group is an IBM Authorized Training Partner (ATP) so we use a product called Acronis to create a single image of our student lab machines and then deploy that image to multiple PCs.  Since these are Windows XP PCs running DB2 9 for Linux, Unix, Windows Fixpack (FP) 2, the computer name must be unique within the domain.  However, once you change the computer name, DB2 stops working.  The common error is SQL1042C.  Here’s how to fix that problem.

First, there is a file in each DB2 instance subdirectory (e.g. “c:\Program Files\IBM\SQLLIB\DB2”) called db2nodes.cfg; in this post we’re only going to address single-node (i.e. not Data Partitioning Feature or DPF) databases.  Open this file for each DB2 instance on the server – I use WordPad – and change the imbedded hostname to the current computer name for that PC.  The same hostname will be repeated twice on a single line in the db2nodes.cfg file.  Change both.  This used to be all you needed to do to fix the problem.  Now, there are a few more steps.

If you installed using the extended operating system security option, you likely will now receive an SQL1652N file I/O error.  In DB2 9.1 FP 2, support was added for domain userids and groups.  Use the db2extsec command to correct the authorities for the installation-defined groups DB2ADMNS and DB2USERS.  For example, let’s say the PC image was created using a computer name of TFGXP01.  We’ve deployed this image to another PC and changed the Windows computer name to TFGXP02.  Enter the command:

db2extsec -a TFGXP02\DB2ADMNS -u TFGXP02\DB2USERS

The last step involves correcting the DB2 registry variables.  Use the db2set -all command to examine the current registry variables.  In our example, you likely will have seen three variables with TFGXP01 as part of the value.  Issue the following commands:

db2set db2accountname=TFGXP02\db2admin

db2set db2instowner=TFGXP02

db2set db2system=TFGXP02

At this point, you should be able to start the DB2 instances.

Using Data Studio Administration Console for Q Replication

Posted by Frank Fillmore on October 15, 2008 under Data Studio, Q-Replication. Tags: , , , .

I mentioned in earlier posts that the Replication Center, a Java fat-client GUI used to build and administer an SQL or Q Replication environment, is being deprecated in favor of the Data Studio suite of thin-client, browser-based interfaces.  There’s also a link on an earlier post to the Q Replication Tools.  One of these tools, the Data Studio Administration Console (DSAC), is taking the place of the original Java Q Replication Dashboard.   DSAC is free and not to be confused with its for-fee sibling Data Studio Administrator.

There are a few anomalies I’ve encountered while installing and using DSAC in a DB2 for z/OS unidirectional Q Replication environment.

  1. You need local administrator authority on the supported Windows operating system in order to install DSAC.  This is true of DB2 9 as well.  The difference is DB2 9 will accept a domain logon as a local administrator (e.g. acmedomain\myuserid); DSAC will *not* install when logged into Windows with a domain userid even if it is part of the local administrators group will full privileges.  The workaround is to use the domain logon to create a new userid (e.g. “install”) on the Windows server with adminsitrator privileges, logon locally to the server with that userid, and install DSAC.
  2. There are a number of DB2 for z/OS prerequisites in order to use the DSAC.  If you *only* want the Q Replication monitor feature, it won’t be necessary to install DB2 Accessories Suite v1.3.
  3. You need to install the security certificates when using DSAC for the first time.  You will be prompted by Windows.  If you don’t install the certificate, the next time you attempt to logon with the same userid, DSAC will remain at the logon page with no error or other information.  Intertestingly, you can logon onto DSAC using a domain userid (acmedomain\myuserid).

Now that you’ve gotten DSAC installed, the next post will detail how to configure and use DSAC as a Q Replication real-time monitoring tool.