Making the DB2 Connection With ReportCaster

By Mona Baker

We have received many requests lately for running the ReportCaster Distribution Server on a UNIX or Windows platform, but using DB2 on the mainframe as the ReportCaster repository. We have tested this from the AIX and Windows 2000 platforms. It works great.

This article will seek to familiarize you with what you need to do to set up this configuration.

We support DB2 client 7.1 and 8.1 using the Type 2 driver. However, if the Report Library is installed, the db2 connect client must be version 8.1. Don’t worry, though, this can connect successfully to DB2 version 7.1 on z/OS.

In this article, I will review the UNIX-to-z/OS connection.

Preparing the DB2 Connect Client

Log on to UNIX using the DB2 user ID.

Navigate to the client’s bin directory. For example:

/rdbms/db2810/sqllib/bin

Execute the following connect statements. Run only these three commands when you first set up the connection:

	db2 catalog tcpip node node_name remote hostname server port
	db2 catalog db remote_location_name at node node_name authentication dcs
	db2 catalog dcs database remote_location_name as local_name

Where:

	node_name is a user-defined name for the host database on which the remote database resides
	hostname is the hostname of the z/OS machine running DB2; port is the port for DB2 on the z/OS machine (default is 446)
	remote_location_name is the location name for the DB2 database on the z/OS machine
	local_name is the db2 location name

For example:

	db2 catalog tcpip node dbn1 remote ibizos.ibi.com server 446
	db2 catalog db LOCDBLN at node dbn1 authentication dcs
	db2 catalog dcs db LOCDBLN as LOCDBLN

Execute the following to set the connection type. This only needs to be run when you set up the connection.

db2set DB2COMM=tcpip

Note: db2set should be in the search PATH.

After executing the connect statements, start the following:

/rdbms/db2810/sqllib/bin/db2jstrt

You should receive a message like this:

Licensed Materials -- Property of IBM
(c) Copyright International Business 
Machines Corporation, 1996, 2000.
All Rights Reserved.

This process must keep running, so if you reboot the machine, be sure to execute db2jstrt again. Also, be sure to execute it using the DB2 ID.

Testing JDBC Connectivity to DB2

Verify that the ID you are using for the test has the required environment variables set and that your CLASSPATH variable has the DB2 JDBC driver appended to it.

Navigate to the following directory:

/rdbms/db2810/sqllib/bin

Execute the following:

db2 connect to location_name user UserID using passwd

Where:

	location_name is the location name for the DB2 database on the z/OS machine (e.g., LOCDBLN)
	UserID is the DB2 User ID
	passwd Is the DB2 password

After executing this d2 connect command, you will see that you are connecting successfully to DB2 on the mainframe from your UNIX machine.

Creating the Tablespaces on z/OS

The following file is installed with the ReportCaster Distribution Server on the UNIX machine and contains the required tablespaces:

/install_directory/ibi/reportcaster52/bin/db2tabsp.txt

Edit this file as follows:

Edit the line at the beginning of the file to specify the SQLID that owns the database.

SET CURRENT SQLID=’ userid’;

Where userid is the owner ID for the database.

Change each CASTERDB to the database name you provided when you installed the Distribution Server. If you are unsure which name this is, it appears as the DBNAME parameter in

/install_directory/ibi/reportcaster52/log/dserver.log

Change each RCSTGRP to the desired storage group.

If you are not using Two-Way Email, remove the following tablespace definitions: BOTJOURN and BOTTELL.

If you are not using the Report Library, remove the following tablespace definitions: BOTLIB, BOTACCES, BOTCAT, BOTLIST, BOTLDATA, and the LOB tablespace for BOTDBLOB.

Adjust the size of the tablespaces to anticipate your site’s needs. The PRIQTY and SECQTY sizes in the sample DDL are only suitable for a small ReportCaster Repository. Modify them to anticipate the number of schedules and address books as well as how often ReportCaster logs will be purged.

Creating the Tables on z/OS

Since you have installed the Distribution Server on the UNIX platform, you will use the createDB2 utility within the reportcaster52/bin directory to create the SQL necessary to create the ReportCaster DB2 repository. The createDB2 utility will create a rbcreate.sql file that contains the SQL needed to create the actual tables. This rbcreate.sql file has to be moved to the mainframe platform so the tables can be built on the mainframe. All is well for all tables with the exception of the Report Library blob table. On z/OS, the blob table requires an additional field in the BOTLDATA table and an auxiliary index table.

You must manually change this Report Library table to the following so that the SQL will function properly with z/OS DB2. You will be adding one new field to the BOTLDATA table, an auxiliary table and two indexes:

CREATE TABLE BOTLDATA (
REPORTID VARCHAR(17) NOT NULL,
COUNTER VARCHAR(4) NOT NULL,
REPROWID ROWID NOT NULL GENERATED 
ALWAYS, REPORT
BLOB(256K),
DUMMY1 VARCHAR(128),
DUMMY2 VARCHAR(128),
PRIMARY KEY (REPORTID,COUNTER))
IN DBNAME.BOTLDATA;
CREATE UNIQUE INDEX IDXLDATA
ON BOTLDATA ( REPORTID, COUNTER );
CREATE AUX TABLE BOTDAUX
IN DBNAME.BOTDBLOB STORES BOTLDATA
COLUMN REPORT;
CREATE UNIQUE INDEX IDXDAUX
ON BOTDAUX COPY YES;
COMMIT;

Where DBNAME is the Database Name.

Save and exit the file. FTP both files (db2tabsp.txt and rbcreate.sql) to MVS in (ASCII) text mode so that it is correctly converted to EBCDIC.

Run each DDL through SPUFI or use a batch job.

One additional important note: ReportCaster will use the profile on USS of the owner ID of the DB2 database to read the environmental variables for DB2 on the mainframe. Be sure that this profile has been updated to include the steplib information, Java™ path information (in PATH, LIBPATH, JAVA_HOME, and CLASSPATH), db2sql jproperties, and subsystem information.

For more information on setting up db2 connect on a UNIX platform, please read TM4560 (DN 4500651.0704). For information on Windows platform, please see TM4562 (DN4500655.0804).