PK58185: STORED PROC WITH DECIMAL FIELDS MAY FAIL WITH DSQ10496 SQL CODE (SQL STATE): -310 ( 22023 ) WHEN CALLED BY QMF.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Stored procedures created with decimal fields
  EXAMPLE:
  CREATE PROCEDURE SPMUCH(IN DECIMAL(6), IN DECIMAL(6))
  and when called from QMF
  EXAMPLE:
  CALL SPMUCH(3.,4.) will fail with SQLCODE -310:
  Message No: DSQ10496 SQL Code (SQL State): -310 (22023
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All QMF for TSO/CICS V910 stored             *
  *                 procedures users.                            *
  ****************************************************************
  * PROBLEM DESCRIPTION: If a stored procedures defined with     *
  *                      DECIMAL input parms is used in QMF,     *
  *                      any decimal values passed to that       *
  *                      stored procedure are rejected with      *
  *                      either message DSQ10496, "DECIMAL HOST  *
  *                      VARIABLE OR PARAMETER 1 CONTAINS        *
  *                      NON-DECIMAL DATA." and SQL Code -310,   *
  *                      or message DSQ23728, "A parameter       *
  *                      length is invalid."                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The QMF code that formats stored procedure input parms
  can format the values in the wrong data type format and
  does not take into account that users can enter DECIMAL
  numbers that have fewer numbers than the defined precision and
  scale (including no scale value at all).  Some decimal numbers
  are rejected even if their number of digits matches the
  defined parm.
  
  DECIMAL numbers defined with NO scale are formatted as INTEGERS,
  thus leading to the SQL Code -301, invalid data type.
  
  DECIMAL parms defined WITH scale values receive the invalid
  length message if the number of digits in the input number
  does not exactly match the number defined.  For example,
  if the parm was defined DECIMAL(5,2), inputting nnn.n is
  considered an invalid length. However, if a decimal parm was
  defined with even precision, such as DECIMAL(6,2), even
  entering the exact number of digits, as in nnnn.nn, leads to
  the invalid data type SQL Code -310 error.
  

Problem conclusion

• Code was added to process decimal numbers of any length that
  is equal to or less than what was defined for the stored
  procedure.  However, all decimal values must contain a
  decimal point, even if no scale was defined.
  Thus, for a stored procedure with the defintion DECIMAL(6),
  input values can be from n. to nnnnnn. and must include
  the decimal point, or you will receive the SQL Code -310
  error.
  

Temporary fix

Comments

APAR Information

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

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

  UK33072

Modules/Macros

• DSQDCPAR DSQDCVAL
  

Fix information

• Fixed component name

  QMF-QUERY MGMT

• Fixed component ID

  566872101

Applicable component levels

• R910 PSY UK33072

     UP08/01/22 P F801

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.