PL23433: MSGDSNT408I FIELDPROC SELECT SQLCODE407 (-407)

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• MSGDSNT408I SQLCODE = -407, ERROR: AN UPDATE OR INSERT VALUE IS
  NULL, BUT THE OBJECT COLUMN .... CANNOT CONTAIN NULL VALUES
  CSECT DSNXRBND DETECTS THE ERROR WITH SQLERRD AS: 100 0 0 1 0 0
  .
  IS RECEIVED WITH THE FOLLOWING QUERY
  SELECT * FROM EXITTEST A
  WHERE ASSNUMBER = '7799991'
  AND ASSISSUE = 'P01  '
  AND PARTISSUE = (
  SELECT MAX(PARTISSUE) FROM EXITTEST B
     WHERE ASSNUMBER = '7799991'
     AND ASSISSUE = 'P01  ' ) :
  THE USER EXPECTS AN SQLCODE100 BECAUSE THE SUBQUERY SELECT MAX.
  RETURNS AN EMPTY TABLE.
  COLUMN PARTISSUE IS DEFINED AS
   PARTISSUE CHAR(5) NOT NULL FIELDPROC AIDAE01
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL.                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -407 DSNXRBND WHEN USER ISSUES  *
  *                      A QUERY THAT COMPARES A COLUMN WITH A   *
  *                      FIELDPROC TO A SUBQUERY.                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  INCORROUT (ERRONEOUS -407 SQLCODE) FROM DSNXRBND WHEN USER
  ISSUES A QUERY THAT COMPARES A COLUMN WITH A FIELDPROC TO A
  SUBQUERY.  FOR EXAMPLE:
                                                                .
  THE FOLLOWING STATEMENTS WILL CREATE THE PROBLEM:
                                                                .
  CREATE TABLE T1 (C1 CHAR(4) NOT NULL,
                   C2 CHAR(4) NOT NULL FIELDPROC FPCVD4):
                                                                .
  SELECT * FROM T1 WHERE C2 = (SELECT MAX(C2) FROM T1):
  

Problem conclusion

• DSNXOW2 HAS BEEN CHANGED TO SET THE NULLABILITY OF THE RESULT
  LITERAL NODE IN THE SUBQUERY CORRECTLY.
                                                                .
  FOR NULL TO NOT NULL COMPARISONS, THE PREDICATE IS SARGABLE AND
  THE SUBQUERY RESULT LITNODE MUST CARRY THE NULLABILITY OF THE
  LHS OF THE COMPARISON.
                                                                .
  FOR NOT NULL TO NULL COMPARISONS, THE PREDICATE IS NOT SARGABLE
  SO THE SUBQUERY RESULT LITNODE MUST CARRY THE NULLABILITY OF
  THE RHS OF THE COMPARISON.
  ****************************************************************
  * PLEASE NOTE:                                                 *
  ***************************************************************
  IN ORDER TO CORRECT THE PROBLEM, THE USER MUST APPLY THE FIX
  AND REBIND THE APPLICATION.
  

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:

  UL28704

Modules/Macros

•    DSNXOW2
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R302 PSY UL28704

     UP88/04/22 P F805

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.