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

You can use tools such as SPUFI or DSNTEP2 to run the database scripts that are used to create the DB2® for z/OS® database objects for your configuration. This task assumes that a DB2 system administrator with SYSADM authority has created the physical databases and storage groups, and granted DBADM authority to a WebSphere® user that is identified as the owner of the databases.

Before you begin

Create the database scripts for the IBM® Business Process Manager components by using either the BPMConfig command utility or the Deployment Environment wizard.
Use FTP to transfer the database scripts to the z/OS system that contains the DB2 for z/OS installation. Transfer the createDatabase.sh script as an ASCII text file, and transfer the database schema files in binary mode. Also ensure that you retain the directory structure when you transfer the files.
Create the required buffer pools. For more information, see Sample DB2 for z/OS commands for allocating buffer pools.
Create the databases and assign the relevant permissions.

About this task

When you generated the database scripts, the scripts for configuring each of the databases were generated into separate subdirectories for ease of execution. The default locations of the database scripts are:

DMGR_PROFILE/dbscripts/cell_name.deployment_environment_name/DB2zOS/cluster_database_name: Contains the files that can be used to create each of the cluster-scoped databases.

You can create the database objects by using the tool of your choice. For example:

SPUFI: A utility that runs SQL files from z/OS. SPUFI uses EBCDIC input.
DSNTEP2: A sample dynamic SQL program provided with the DB2 for z/OS product.

Procedure

On the z/OS system that contains the DB2 for z/OS installation, go to the location to which you transferred the database scripts:
- Each cluster_database_name subdirectory contains one or more of these files, which you must run in the following order:
  1. createTablespace_Standard.sql
  2. createSchema_Standard.sql
  3. createSchema_Messaging.sql
  4. createProcedure_Standard.sql
These files are in ASCII format.
Assign the appropriate read permissions to the SQL files; for example:
```
chmod 644 createSchema_Standard.sql
```
If the tool that you want to use to view and run the SQL files requires the files 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 createSchema_Standard.sql > createSchema_Standard_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.

Tip: 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 createSchema_Standard_EBCDIC.sql > createSchema_Standard.sql
To create database objects outside of the z/OS UNIX environment by using SPUFI or DSNTEP2, copy the SQL files from z/OS UNIX to a partitioned data set.
Run the SQL files by using the tool of your choice.
Verify that the database tables are created successfully with no errors by inspecting the output.

What to do next

You must now run the bootstrap utility to load configuration data for the IBM Business Process Manager applications into the Process database. This data is required for the applications to run correctly.