IBM BPM, V8.0.1, All platforms > Install IBM BPM > IBM BPM Advanced for z/OS: Process Server > Configure IBM BPM Advanced for z/OS: Process Server > Create the databases > Create databases in the DB2 for z/OS subsystem

Create DB2 for z/OS database objects using SPUFI or DSNTEP2

You can use tools such as SPUFI or DSNTEP2 to run the database scripts used to create the DB2 for z/OS database objects for your configuration. If you want to work in the UNIX System Services environment, you can also run the database scripts by using the DB2 command line processor.

• Decide on the DB2 for z/OS database design and configuration, including the number of databases required for the IBM BPM components.

  For example, in a stand-alone server environment, you might decide to set up all the component databases in a single DB2 for z/OS database. Or, in an ND environment, you might define two DB2 for z/OS databases, where one database is used for the Common database component, which is cell-scoped, and another database is used for all the other components, which are cluster-scoped.

• Decide on a naming convention for the DB2 for z/OS objects, such as databases, storage groups, VSAM catalog names (VCATs), schema prefixes, buffer pools, and security IDs.

• Configure the stand-alone server or ND environment. When you configure the nodes, ensure that the dbDelayConfig parameter in the augmentation response file is set to true.

• Create the required buffer pools. See Sample DB2 commands and SQL.

• Run the database design tool (DbDesignGenerator.sh) to generate the database scripts used to create the database objects for the IBM BPM components. Set up your database configuration so that the database design tool creates a single output directory for each database to be created, and generates all the relevant database scripts, which are required to create the database, into that directory.

  Tip: The generated scripts that you use to create the database objects can either be in ASCII or EBCDIC format. Typically, the generated .sql files are in ASCII format, and the equivalent .ddl files are in EBCDIC format.

You can create the database objects across multiple databases by using the tool of your choice.

For example:

SPUFI

A utility that runs SQL scripts from z/OS. SPUFI uses EBCDIC input.

DSNTEP2

A sample dynamic SQL program provided with the DB2 for z/OS product.

Procedure

1. Assign the appropriate read permissions to the SQL scripts; for example:

   chmod 644 createTable_AppScheduler.sql

2. If the tool that you want to use to view, edit, and run the scripts requires the scripts to be in EBCDIC format, rather than ASCII format, use the iconv command to convert the files to EBCDIC.

   For example:

   iconv –t IBM-1047 –f ISO8859-1 createTable_AppScheduler.sql > createTable_AppScheduler_EBCDIC.sql

   Important: After converting from ASCII to EBCDIC, check that no SQL statements exceed 71 characters in length. Longer lines will lead to line truncation and invalid statements when copying to fixed-width MVS™ data sets.

   If you have converted the files from ASCII format to EBCDIC, but need to run the files in ASCII format, you can also use iconv to convert the files back to ASCII.

   For example:

   iconv -t ISO8859-1 -f IBM-1047 createTable_AppScheduler_EBCDIC.sql > createTable_AppScheduler.sql

3. Edit the values in the createDatabase.sql and dropDatabase.sql files according to your requirements. The following symbolic variables need to be updated:

   @STOGRP@    = DB2 Storage group name @DB_NAME@   = DB2 Database name @SCHEMA@    = DB2 SQLID (Schema Qualifier)
   @BPTABLE4K@ = Buffer Pool of 4k Size
   @BPINDEX@   = Buffer Pool Index
   @VOLUMES@   = DB2 comma separated list of volume-ids
   @VCAT@      = DB2 integrated catalog facility name @DB_USER@   = The user ID which will be used to create the database objects

4. If you want to create database objects outside of the UNIX System Services environment, for example, by using SPUFI or DSNTEP2, you can use the supplied Ddl2Pds.sh script to copy the customized database scripts from UNIX System Services to a partitioned data set.

   For example, to copy the scripts for the Common database component, you can enter the following command:

   cd /usr/lpp/zWebSphere/V8R0/zOS-config/samples ./Ddl2Pds.sh -Source /WebSphere/V8S05Z1/AppServer/profiles/default/dbscripts/CommonDB/DB2zOS/S5CELLDB -PDS HEALDR.DDL2PDS.TEST -Component WPS

5. Run the customized scripts by using your preferred tool.

   If you previously ran the createDB2.sh script and chose to bypass execution of the SQL scripts, you can also run the consolidated SQL files (z_schema.sql and z_schemaProc.sql), which were generated.

6. Verify that the database tables are created successfully with no errors by inspecting the output.

What to do next

For a stand-alone configuration, complete the remaining database configuration tasks, if relevant. Then run the bootstrap utility to load the database with system information.

For an ND configuration, complete the remaining database configuration tasks, if relevant. Then start the dmgr server.

Create databases in the DB2 for z/OS subsystem

Related concepts:
Decide on a naming convention for the databases
Decide on schema names and SQL IDs

Related tasks:
Augmenting stand-alone and ND profiles
Create a database design file and database scripts for a stand-alone profile or deployment environment by using the database design tool
Generate scripts for creating database objects in DB2 for z/OS
Create DB2 for z/OS database objects using the DB2 command line processor
Loading the database with system information (stand-alone environment)
Loading the database with system information (ND environment)
Starting the dmgr