Using IBM Business Process Manager (BPM) with the DB2 for z/OS SQL line length checker

Download Description

It includes a sample script file called lineLength.sh for scanning a file of SQL code to identify lines that are too long.

Introduction

The configuration of IBM Business Process Manager requires a significant number of database objects to be created. When the implementation database is DB2 for z/OS, you must manually run the SQL code to create the database objects rather than allowing the database definitions to be run dynamically.

IBM Business Process Manager provides the following tools, which you can use to run the generated SQL: createDB.sh, createDB2.sh, or createDatabase.sh. These tools are UNIX System Services-based, but some organizations prefer to use z/OS-based tools such as the SQL processor using file input (SPUFI) or DSNTEP2.

The SQL is generated in an ASCII format, but the z/OS-based tools require the SQL to be in EBCDIC format, with lines of SQL code that do not extend beyond column 72. The IBM Business Process Manager tools that you can use to run the SQL on UNIX System Services are not subject to these limitations. This document provides a utility named lineLength.sh, which you can run against the generated product SQL to identify lines of code that are too long. This document also describes techniques that you can use to render the code compatible with the z/OS tools.

Ddl2Pds.sh
IBM Business Process Manager for z/OS provides a sample tool called Ddl2Pds.sh to copy SQL files from a UNIX System Services file system to a z/OS partitioned data set (PDS). Ddl2Pds.sh is capable of converting the SQL to the EBCDIC code page, but it will fail if it tries to process a file of SQL code with lines that are too long. You must, therefore, modify the SQL in the UNIX System Services file system before you run the Ddl2Pds.sh tool.

Adjusting long lines
Two techniques are available for splitting long lines of SQL:

• Terminate the line at column 72 and continue in column 1 of the next line.
• Add the closing quote at the end of part 1, followed by a space and two pipe (|) characters, and start part 2 with an opening quote on the next line.

The following example shows one of each technique:

INSERT INTO LSW_USR(
   "USER_ID",
   "USER_NAME",
   "PASSWD",
   "FULL_NAME",      
   "IS_DISABLED",
   "LAST_LOGIN_DATETIME",
   "OLD_PASSWORDS")
 VALUES(
   1,        
   'tw_admin',                                                            
   'SHA-256:1:1I1UciaiF9RnG6zg:CWS308n6EUK2EOdwKqewX68obxykoCxD7Jful497RZ
 M=',
   'Internal TW Admin user',
   'F',
   NULL,                              
   'SHA-256:1:' ||
   '1I1UciaiF9RnG6zg:CWS308n6EUK2EOdwKqewX68obxykoCxD7Jful497RZM=');

Installing the lineLength.sh utility
Complete the following steps to install the utility:

1. Download the lineLength.zip file, and use FTP to transfer the file in binary mode to a temporary location in the UNIX System Services file system.
2. In the UNIX System Services system, extract the contents of lineLength.zip file to obtain the lineLength.sh utility; for example, unzip lineLength.zip.
3. Issue the chmod 755 lineLength.sh command to set the execution permissions for the file.

Running the lineLength.sh utility
The utility requires one parameter, which is the location and name of the file to be examined. It reports the line number of each line with a length greater than 72 characters, and how long the line actually is.

./lineLength.sh createSchema_Advanced.sql
lineLength.sh
Line 3: Length      77
Line 8: Length      74
Line 11: Length      77
Line 17644: Length      91
Line 19159: Length     106
Line 19555: Length      75
Line 19556: Length      76
Line 19589: Length      80
Line 19592: Length      75
Line 19718: Length      77
...

Note: The carriage return and line feed characters are included in the count and can be included in lines that are reported as greater than 72 characters. Thus, the following lines can be reported as having a length of 73 or 74 depending on whether there are x'0d' or x'0a' special characters at the end of the line:

000000000111111111122222222223333333333444444444455555555556666666666777
123456789012345678901234567890123456789012345678901234567890123456789012