Database Configuration Guide

CHAPTER 2

Configuring DB2 Databases

This chapter describes how to configure your DB2 database for use with the Novell exteNd Application Server as a SilverMaster or deployment database. It contains the following sections:

For more information    For the latest information on supported database versions, see the Novell exteNd Application Server Release Notes.

 
Top of page

Connecting to DB2 databases

The Novell exteNd Application Server connects to DB2 using a Type 2 JDBC driver written by IBM and included with their Universal Database client. This driver interfaces with the UDB client to establish database connectivity to the back-end DB2 server. The application server officially supports TCP/IP connectivity, but customers have successfully connected using APPC, IBM's native protocol.

Follow the steps outlined below to make a DB2 database available to the application server:

Step 1: gather information about your DB2 environment

Step 2: prepare DB2 for connection from Universal Database Client

Step 3: install the database client

Step 4: configure the DB2 client connection

Step 5: modify the DB2CLI.INI file

Step 6: set environment variables

Step 7: use the database with the application server

Supported database usage   DB2 can be used as the SilverMaster database and to store the application server's system tables required for deployment databases, with this exception: DB2 cannot be used as a SilverMaster (or store the system tables) when DB2 is running on AS/400 or System 390.

SilverMaster and deployment databases   Note that a deployment database cannot exist in the same physical space as the SilverMaster database, because several of the required tables have identical names.

 
Top of section

Step 1: gather information about your DB2 environment

Gather the following information for your DB2 environment.

NOTE:   If your DB2 version is earlier than V4R2, your database may require extensive configuration to support Universal Database Client.

 
Top of section

Step 2: prepare DB2 for connection from Universal Database Client

IBM has extensive documentation available on configuring the database and the client software to support connectivity from the Universal Database Client. You can find documentation in the Information Center that is installed with the Universal Database Client, or on IBM's Web site.

If you have an extremely large database schema, check for additional database configurations that can help reduce the time it takes to scan the schema in the DB2CLI.INI file. In addition, see the following IBM documents:

 
Top of section

Step 3: install the database client

The database client must be installed on the machine where the application server will reside.

Using Universal Database Client Version 5.2 (Enterprise Edition)

Windows NT   Follow the instructions in the setup program. Do not confuse the Universal Database Client with the Client Application Enablers (CAE). Installing the CAE will not give you all the components you need. You must install the entire Enterprise Connect product.

Solaris   Before you can begin the installation of the DB2 database client on Solaris, you must first alter several kernel configuration parameters to accommodate the install. In the /etc/system directory:

  set msgsys:msginfo_msgmax=65535 
  set msgsys:msginfo_msgmnb=65536 
  set msgsys:msginfo_msgseg=8192 
  set msgsys:msginfo_msgssz=16

Install the database client from the CD. You must install and create a DB2 instance during this installation so you can reference the DB2 profile script for the application server to successfully use the DB2 JDBC driver.

Using DB2 Connect Version 6.1 (Enterprise Edition)

Windows NT   Follow these instructions for running the setup program:

  1. Choose the DB2 Enterprise - Extended Edition check box.

    You can leave the default choice for the information about the server instance.

  2. Select Custom.

  3. Select the following items from the list:

UNIX   Follow the same instructions as shown above for installing Version 5.2 in Solaris, except that you install DB2 Connect - Enterprise Edition Version 6.1.

Using DB2 Connect Version 7 (Enterprise Edition)

Windows NT   Follow the instructions in the setup program.

Solaris    See the IBM DB2 Connect Enterprise Edition for UNIX Quick Beginnings guide on the IBM Web site for information on installing DB2 Connect and for setting Solaris kernel parameters.

 
Top of section

Step 4: configure the DB2 client connection

Windows NT   The application server connects using DB2 client aliases. These aliases are configured using IBM's Client Configuration Assistant, which can be found in the DB2 for Windows NT program folder. In order to run the Client Configuration Assistant, your NT login must have administrator authority and the ID must be no longer than eight characters. You need to be the DB2 administrator or have administrator privileges to complete these steps.

Procedure To create a new DB2 client alias:

NOTE:   You may have to contact your DB2 Administration staff to obtain some of the information required in these steps.

  1. Open the DB2 Client Configuration Assistant from the DB2 for Windows NT program folder.

  2. Click the Add button at the top right of the Client Configuration Assistant screen. Select Next to continue.

  3. Choose to manually configure a connection to a DB2 database. Select Next to continue.

  4. Choose the appropriate protocol (default is TCP/IP). Select Next to continue.

  5. Choose the appropriate platform or LAN-based.

  6. Configure the DB2 server host name, TCP port number, and service name (optional) that your DB2 server is operating on. Select Next to continue.

  7. Enter the target database name. Select Next to continue.

  8. Enter the alias name (you will use this name later to add the database to the application server). Select Next to continue.

  9. (Optional) Register these settings as an ODBC data source. This step is not required but is recommended.

  10. Finally, test the connection from inside the Client Configuration Assistant.

For more information    For information on installation, see Most Common DB2 Connect Problems on the IBM Web site.

Solaris   In order to establish a connection to your DB2 database, enter the DB2 command-line processor and enter the following two commands.

  catalog tcpip node tcpipnodealias remote servername server port# 

where tcpipnodealias is the local alias for your DB2 server (usually the same as the servername), servername is the name of the DB2 server, and port# is the port on the database server that is set to receive TCP/IP calls.

  catalog database databasename as aliasname at node tcpipnodealias 

where databasename is the actual database name, aliasname is the local name of the database (which may be the same as the actual name), and tcpipnodealias is the name given in the first catalog statement.

To test the DB2 connection:

  connect to aliasname user userid using password

For more information    For information on installation, see Most Common DB2 Connect Problems on the IBM Web site.

 
Top of section

Step 5: modify the DB2CLI.INI file

IBM provides a client side .ini file for the purpose of tailoring the client connection to the database. Without modifications, the application server will attempt to add all DB2 tables and views into its environment. Most applications need only a subset of the entire list. Addition of a DB2 database without modifications to the .ini file can take an extraordinarily long time.

There are several things that can be done to reduce the time it takes for the application server to read the schema and two main approaches to follow:

Using exteNd tools

One option is to specify the subset of tables to make available to the application server when adding the database to the server. For more information, see the chapter on SilverCmd in the Facilities Guide or the section on adding a database (in the chapter on data source configuration) in the Administrator's Guide.

Using IBM tools

GUI approach   Alternatively, you can follow these steps to make changes to .ini file parameters from the Client Configuration Assistant:

  1. View the properties for an alias and then choose to modify the ODBC settings.

  2. Connect to the database, then choose advanced.

    The specific settings found to help with application server installs are listed below. The full list can be reviewed in the Call Level Interface document from IBM.

Text approach   Optionally, the DB2 client .ini file (which can be found in the sqllib directory) can be read and altered using a text editor. Your initial alias entry (with the name you entered via the Client Configuration Assistant) should look like this:

  [db2alias] 
  DBALIAS=db2alias 

To limit the number of tables, you can specify a particular schema. Schema here refers to the owner/user of the objects or library in the case of AS/400:

  SCHEMALIST="userid","userid2" 

For extremely large datasets, use of SCHEMALIST may not be enough to reduce scanning time. To greatly reduce the total number of tables that the application server interrogates, you need to complete these steps:

  1. Create views of all the system tables using an alternate owner (must be performed by DBA). Do not create these views under the same owner ID as the tables you wish to access for your application; create them under a unique owner.

  2. Reference that owner of those views in the .ini file.

      SYSSCHEMA="owner"
    

For example, you may create your views to see the objects of three different owners. This sample view syntax was used for DB2 on NT.

  CREATE VIEW MYSCHEMA.SYSTABLES AS  SELECT *  FROM SYSIBM.SYSTABLES  WHERE CREATOR IN ('USER1','USER2','USER3') 

After views were created for all of the required system tables under the same schema, the entry in the ini file would look like this:

  SYSSCHEMA="MYSCHEMA"

The application server will then scan only those objects. If used in conjunction with SCHEMALIST, the server will scan the objects of the three owners but only add those belonging to the owners referenced in SCHEMALIST.

IMPORTANT:   The preceding options are highly recommended to reduce installation and server start times.

Other configuration options

For MVS environments, you may have to set a variable for long data type compatibility. For a complete description of this setting, see the LONGDATACOMPAT section of the Call Level Interface document.

  LONGDATACOMPAT=1 

If you can't save records in DB2 after adding the database, you may have to set a transaction variable. The example listed below is for an AS/400 system and eliminates the need for table journaling. For a complete list of options, see the TXNISOLATION section of the Call Level Interface document.

  TXNISOLATION=32 

 
Top of section

Step 6: set environment variables

Windows NT   Connecting the application server to DB2 requires that you add the DB2 libraries to your AGCLASSPATH environment variable. To do this, open Control Panel / System and select the Environment tab. Create a System environment variable called AGCLASSPATH and set its value to C:\sqllib\java\db2java.zip (assuming that you installed your DB2 client onto the C: drive in the default location); if you did not install the DB2 client onto the C: drive, set the AGCLASSPATH variable accordingly.

By default, this sets up the JDBC 1.0 driver. In DB2 7.x, you can use the JDBC 2.0 driver by running this script: sqllib\java12\usejdbc2 (for details, click here: www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?&fn=1008960).

UNIX   DB2 requires that you add the DB2 JDBC driver's native libraries and Java classes to your Java classpath and system library path. To do this, you may reference the DB2 instance (created previously) directly in the application server's environment file .agprofile (which is stored in the application server's installation directory). Edit .agprofile by adding the following line:

  . /export/home/db2inst1/sqllib/db2profile

By default, this sets up the JDBC 1.0 driver. In DB2 7.x, you can use the JDBC 2.0 driver by adding the following lines to your .agprofile file (for details, click here: www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?&fn=1008960):

  . /home/db2inst1/sqllib/db2profile
  . /home/db2inst1/sqllib/java12/usejdbc2

For more information    For more information on editing AGCLASSPATH and .agprofile, see the chapter on data source configuration in the Administrator's Guide.

 
Top of section

Step 7: use the database with the application server

Now you're ready to access the DB2 database from the application server. This enables you to do any of the following, as appropriate:

For more information    For more information, see the chapter on data source configuration in the Administrator's Guide.

 
Top of page

Troubleshooting

SQL0551N "user does not have the privilege to perform operation GRANT"   If you receive this message from the DB2 client while connecting to DB2 6.1, you may have to bind the DB2 utility programs to the database. You must apply the procedure to each database you are using with the application server. Here is the binding procedure:

  1. Log in as your DB2 instance user (such as db2inst1), change to the bnd subdirectory of the instance, and start the DB2 utility.

      db2inst1@mymachine>  cd sqllib/bnd
      db2inst1@mymachine>  db2
    
  2. Connect to your database as the DB2 administrator:

      db2 => connect to mydatabase user db2admin using db2admin
    

    You will see the following info:

      Database Connection Information
      Database server        = DB2/NT 6.1.0
      SQL authorization ID   = DB2ADMIN
      Local database alias   = MYDATABASE
    
  3. Run the first bind command:

      db2 => bind @db2ubind.lst blocking all grant public
    
  4. Run the second bind command:

      db2 => bind @db2cli.lst blocking all grant public 
    


Copyright © 2004 Novell, Inc. All rights reserved. Copyright © 1997, 1998, 1999, 2000, 2001, 2002, 2003 SilverStream Software, LLC. All rights reserved.  more ...