This topic describes how to create tables for a scheduler on a
DB2 for z/OS database using data definition language (DDL) or structured query
language (SQL) files.
Before you begin
This task requires that you configure a database and make it available.
See the "Creating
DB2 for z/OS databases for schedulers" topic for more information.
In
addition, you must have the following two machines:
- The z/OS machine that is hosting the database
- The WebSphere Application Server machine that is running the scheduler
About this task
To create tables for a scheduler on a DB2 for z/OS database, using
data definition language (DDL) or structured query language (SQL) files, use
these steps.
Procedure
- Work with the z/OS machine that hosts the database to:
- Log on to the native z/OS environment.
- Decide which subsystem you want to use, if multiple DB2 systems
are installed.
- Note of the Internet Protocol (IP) port to which the DB2 subsystem
is listening.
- Use the DB2 administration menu to create a new database named,
for example, SCHEDDB. Note the database name.
- Create a storage group and note the name.
- Decide which user ID is used to connect to the database from
the remote machine running the product. Normally, for security
reasons, this user ID is not the one you used to create the database.
- Grant the user ID the rights to access the database and storage
group. The user ID must also have permission to create new tables
for the database.
- Work with the Application Server machine to:
- Verify that you have DB2 Connect Gateway (Version 8.1 fix pack
3 or higher) installed. This component is part of the DB2 UDB ESE package;
however, you can also install it separately.
- Catalog the remote database using the following commands, either
in a script or in a DB2 command line window:
catalog tcpip node zosnode remote hostname server IP_port ostype mvs; catalog database subsystem as subsystem at node zosnode authentication dcs; catalog dcs database subsystem as subsystem parms ',,INTERRUPT_ENABLED'
An
important difference exists between DB2 UDB and DB2 for z/OS. DB2 UDB does
not have the concept of a subsystem, but DB2 for z/OS does have subsystems.
To avoid confusion between Database name and Subsystem name, remember that
because DB2 for z/OS runs in a subsystem, the catalog node and catalog
database commands must identify the appropriate subsystem. On DB2 UDB,
the subsystem name is not a known concept, and the database name to which
it connects is actually the name of the DB2 for z/OS subsystem.
- Verify that you can establish a connection to the remote subsystem
by entering the following command:
db2 connect to subsystem user userid using password
- Change to the scheduler subdirectory in the application server
installation root directory.
- Edit the createTablespaceDB2ZOS.ddl script. Replace @STG@ with
the storage group name. Replace @DBNAME@ with the database name (not
the subsystem name), and replace @SCHED_TABLESPACE@ with the name
of a valid table space. After you replace the database name, place it into
an existing JCL and run the job.
- Run your customized version of createTablespaceDB2ZOS.ddl,
as described in the header of the script. If this script does not work, or
if you want to remove the table space, edit and run the dropTablespaceDB2ZOS.ddl script.
- Edit the createSchemaDB2ZOS.ddl script. Replace @STG@ with
the storage group name. Replace @DBNAME@ with the database name (not
the subsystem name). Replace @TABLE_PREFIX@ with the Table Prefix
in the configured scheduler resource, and replace @SCHED_TABLESPACE@ with
a valid table space that was created by the createTablespaceDB2ZOS.ddl script.
Note: When setting the table prefix, capitalize all characters.
- Run your customized version of the createSchemaDB2ZOS.ddl script,
as described in the header of the script. If this script does not work, or
if you want to remove the tables and views, use dropSchemaDB2ZOS.ddl to
drop the schema.
To avoid deadlocks, verify that the
DB2_RR_TO_RS DB2 flag is set to YES. If necessary, restart the DB2
instance to activate the change. In addition, verify that the table space
was created with the LOCKSIZE ROW statement.
Results
The DB2 for z/OS tables and schema for the scheduler exist.