JR44910: PROBLEM WITH THE DECIMAL=COMMA SETTING IN DB2 FOR Z/OS IMPACTS DATABASE FUNCTION IN BPM

APAR status

• Closed as fixed if next.

Error description

• There is a problem with the DECIMAL=COMMA setting in DB2 for
  z/OS which impacts the following files in a BPM configuration:
  
  BPM/Lombardi/process-server/configTemplates/db/DB2zOS/60Database
  .xml./dbscripts:
  
  scripts/BusinessSpace/DB2zOS/createTable_BusinessSpace.sql
  scripts/BusinessSpace/DB2zOS/migrateBusinessSpaceSchema700.sql
  scripts/BusinessSpace/DB2zOS/preMigrateBusinessSpace700.sql
  scripts/BusinessSpace/DB2zOS/upgradeSchema612_BusinessSpace.sql
  scripts/BusinessSpace/DB2zOS/upgradeSchema620_BusinessSpace.sql
  scripts/BusinessSpace/DB2zOS/upgradeSchema750_BusinessSpace.sql
  scripts/BusinessSpace/DB2zOS/upgradeSchema751_BusinessSpace.sql
  
  scripts/CommonDB/DB2zOS/createTable_customization.sql
  
  scripts/PerformanceDW/DB2zOS/createTable_PerformanceDW.sql
  scripts/PerformanceDW/DB2zOS/wle_upgradeSchema_PerformanceDW.sql
  
  scripts/ProcessChoreographer/DB2zOS/createSchema_BPC.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema620.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema620DB2zOSV7.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema700.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema7003.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema7003DB2zOSV7.sq
  l
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema700DB2zOSV7.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema7500.sql
  scripts/ProcessChoreographer/DB2zOS/upgradeSchema7500DB2zOSV7.sq
  l
  
  scripts/ProcessServer/DB2zOS/admin_Data_ProcessServer.sql
  scripts/ProcessServer/DB2zOS/admin_GroupMemberRefData_ProcessSer
  ver.sq
  l
  scripts/ProcessServer/DB2zOS/admin_RefData_ProcessServer.sql
  scripts/ProcessServer/DB2zOS/bpmAuthor_GroupMemberRefData_Proces
  sServe
  r.sql
  scripts/ProcessServer/DB2zOS/createProcedure_ProcessServer.sql
  scripts/ProcessServer/DB2zOS/createTable_ProcessServer.sql
  scripts/ProcessServer/DB2zOS/twadmin_GroupMemberRefData_ProcessS
  erver.
  sql
  scripts/ProcessServer/DB2zOS/twauthor_GroupMemberRefData_Process
  Server
  .sql
  scripts/ProcessServer/DB2zOS/twportaladmin_GroupMemberRefData_Pr
  ocessS
  erver.sql
  scripts/ProcessServer/DB2zOS/twuser_GroupMemberRefData_ProcessSe
  rver.s
  ql
  scripts/ProcessServer/DB2zOS/twwebservice_GroupMemberRefData_Pro
  cessSe
  rver.sql
  scripts/ProcessServer/DB2zOS/wle_upgradeSchema_ProcessServer.sql
  
  There are guidelines in the BPM Development Wiki on how to code
  to accommodate the DECIMAL=COMMA setting
  
  DB2 for z/OS has a setting to accommodate the practice common to
  many Eastern European countries of using a period "." to
  separate groups of integers in large numbers, while the comma
  "," is used to represent the decimal point. For example, the
  number 12,345,678.90 might appear as 12.345.678,90.
  
  DB2 subsystems with this setting active sometimes encounter
  problems with SQL when numeric parameters are concerned, because
  in SQL the comma is also used as a parameter separator. The
  string "1,2" in systems which do not use decimal=comma is
  interpreted as two separate parameters, 1 and 2. In a
  decimal=comma system, this is interpreted as 1 parameter,
  1,2 (or 1.2).
  
  The solution is to make sure that when the comma is being used
  as a parameter separator, it is always followed by a space. A
  comma which has no space following it will be interpreted as a
  decimal separator in decimal=comma system, and attempts to run
  the SQL could result in a variety of SQL errors.
  
  We know of these SQL clauses which have caused issues with
  customers:
  
      DECIMAL
      VALUES
      IN
      DEFAULT
  

Local fix

• As a workaround the impacted files can be corrected manually by
  changing the decimal separator for all occurrences.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  Business Process Manager(DB2 on z/OS) v8.0  *
  *                  users                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: BPM has problem with the DECIMAL=COMMA  *
  *                      setting in DB2 for z/OS.                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  BPM has a problem handling comma in SQL script, with the
  DECIMAL=COMMA setting in DB2 for
  z/OS, which impacts all DB2zOS db scripts files that has SQL
  clauses:
  DECIMAL
  VALUES
  IN
  DEFAULT
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  PM77928

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

• R800 PSY

     UP