This chapter describes how to configure your DB2 databases for use within the SilverStream Application Server environment as a SilverMaster or an application database.
For the latest information on supported database versions, see the SilverStream Application Server release notes. In Windows, you can access the release notes from the SilverStream program group or from SilverStream online help.
NOTE As of SilverStream 3.0, DB2 can be used as the SilverMaster database and to store the SilverStream system tables required for application databases, with this exception: DB2 cannot be used as a SilverMaster (or store SilverStream system tables) when DB2 is running on AS/400 or System 390.
This chapter contains the following sections:
The online version of this chapter contains links to IBM support documents, all of which are included with SilverStream. They are available in SilverStream\docs\help\xfiles\db2 or from the IBM Web site.
NOTE The application database cannot exist in the same physical space as the SilverMaster database because several of the required tables have identical names.
This section details the connection and pre-installation setup recommendations by SilverStream Technical Support. Support of DB includes the following components:
The database must be a version that supports TCP/IP. In general, the database must be a minimum of V4R2 to support TCP/IP. SilverStream has tested with V4R3, although version requirements may differ for DB2, depending on the platform. If your DB2 version is less than V4R2 it may require extensive configuration to support Universal Database Client. AS/400 and OS/390 environments tend to require more configuration than NT, OS/2, or UNIX environments.
If you are running software that does not meet the version requirements shown, you need to obtain the specified versions.
SilverStream 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. SilverStream officially supports TCP/IP connectivity but customers have successfully connected using APPC, IBM's native protocol.
NOTE The SilverStream Designer will list DB2 tables and views. DB2 alias objects cannot be seen from the Designer. SilverStream data source business objects can be written to access these objects.
Follow the steps outlined below to make a DB2 database available to SilverStream.
Step 1: Gather information about your DB2 environment
Step 2: Prepare DB2 for connection from Universal Database Client
Step 3: Install the Universal Database Client version 5.2 or version 6.1
Step 4: Configure the DB2 client connection
Step 5: Modify the DB2CLI.INI file
Step 6: Set environment variables
Step 7: Use the database in SilverStream
Gather the following information for your DB2 environment.
NOTE If your DB2 version is less than V4R2, your database may require extensive configuration to support Universal Database Client.
IBM has extensive documentation available on configuring the database and the client software to support connectivity from the Universal Database Client. We have included links to several of the IBM documents listed below in the online version of this chapter. Additional documentation can be found in the Information Center that is installed with the Universal Database Client, or on IBM's web site. Also included in the online version of this chapter are links to the IBM Connectivity Supplement and the Quick Beginnings for DB2 Connect Enterprise Edition.
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, refer to the following IBM documents at the IBM Web site or as a link from the online version of this chapter:
The database client must be installed on the machine where the SilverStream Server will reside.
Windows NT
Follow the instructions in the setup program. Do not confuse the Universal Database Client with the Client Application Enablers. Installing the Client Application Enablers (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 Enterprise Connect 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 DB2 Enterprise Connect 5.2 from CD. You must install and create a DB2 instance during this installation so you can reference the DB2 profile script for SilverStream to successfully use the DB2 JDBC driver.
Windows NT
Follow these instructions for running the setup program:
You can leave the default choice for the information about the server instance.
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.
HP-UX note: The following are recommended values for HP-UX kernel configuration parameters when installing DB2 6.1:
Windows NT
SilverStream 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.
To create a new DB2 client alias:
NOTE You may have to contact your DB2 Administration staff to obtain the information required above.
Finally, test the connection from inside the Client Configuration Assistant.
For information on installation see Most Common DB2 Connect Problems, available as a link from the online version of this chapter.
Solaris
In order to establish connect to your DB2 database, enter the DB2 command line processor and enter the following 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 tcpip 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 the same as the actual name, and tcpipnodealias is the name given in the first catalog statement.
connect to aliasname user userid using password
For information on installation see Most Common DB2 Connect Problems.
IBM provides a client side .ini file for the purpose of tailoring the client connection to the database. Without modifications, SilverStream will attempt to add all DB2 tables and views into the SilverStream 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 SilverStream to read the schema and to reduce the final list of tables and views that are seen from the SilverStream Designer.
One option is to specify the subset of tables to make available to SilverStream when adding the database to SilverStream. For more information, see the section on adding a database in the Main Designer chapter in the online Tools Guide.
Alternatively, you can follow these steps to make changes to ini file parameters from the Client Configuration Assistant:
The specific settings found to help with SilverStream installs are listed below. The full list can be reviewed in the Call Level Interface document from IBM, included as a link in the online version of this chapter.
Optionally, the DB2 client ini file can be found in the sqllib directory, and 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 that SilverStream shows in the Designer 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 SilverStream interrogates, you need to complete these steps:
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"
SilverStream will then scan only those objects. If used in conjunction with SCHEMALIST, SilverStream 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.
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 eliminated the need for table journaling. For a complete list of options see the TXNISOLATION section of the Call Level Interface document.
TXNISOLATION=32
Windows NT
Connecting SilverStream 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 into C: drive, set the AGCLASSPATH variable accordingly.
Solaris
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 from the SilverStream environment file .agprofile, which is stored in the SilverStream directory. As this is a file preceded with a dot in its name, you have to use ls -a to see the file. In the SilverStream directory change your .agprofile by adding the following line:
. /export/home/db2inst1/sqllib/db2profile
Now that you have configured the DB2 client and set the appropriate environment variables, you can connect the database to SilverStream. For example, you can install the SilverStream Server and point it to the appropriate database as the SilverMaster or add the target DB2 application database to an already running SilverStream Server. You will need to know the credentials for a DB2 user that has sufficient rights to the target database.
NOTE If storing SilverStream system tables in another database, you must also have that database created and set up.
For information about installing the SilverStream Server, see
Installing SilverStream on Windows or
Installing SilverStream on UNIX.
For information on adding a database to SilverStream, see the Main Designer chapter in the online Tools Guide.
This section describes common errors and workarounds if available.
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 might have to bind the DB2 utility programs to the database. You must apply the procedure to each database you are using with SilverStream. Here is the binding procedure:
db2inst1@mymachine> cd sqllib/bnd
db2inst1@mymachine> db2
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
db2 => bind @db2ubind.lst blocking all grant public
db2 => bind @db2cli.lst blocking all grant public