Missing Host Variables information on SQL -911

Products

• Detector for DB2 for z/OS
• Database Management for DB2 for z/OS - Performance Suite
• Database Management for DB2 for z/OS - SQL Performance Suite

Issue/Introduction

Some SQL -911 errors are missing host variable fields.  Xmanager shows no abends or msgs.  

 

UPDATE  LOCATIONGROUP                                                  
    SET LAST_UPDATE_DT = ? , LAST_UPDATE_TX_ID = ? , LAST_UPDATE_USER =
        ? , CONT_ID = ? , EFFECT_END_MMDD = ? , EFFECT_END_TM = ? ,    
        EFFECT_START_MMDD = ? , EFFECT_START_TM = ? , END_DT = ? ,     
        LAST_USED_DT = ? , LAST_VERIFIED_DT = ? , LOC_GROUP_TP_CODE = ?
        , MEMBER_IND = ? , PREFERRED_IND = ? , SOLICIT_IND = ? ,       
        SOURCE_IDENT_TP_CD = ? , START_DT = ? , UNDEL_REASON_TP_CD = ? 
  WHERE LOCATION_GROUP_ID = ?                                          
    and LAST_UPDATE_DT = ?                                             

HOST VARIABLE DATA USED WITH THIS SQL REQUEST                            
PARAMETER MARKER     1         = '2021-04-06-00.14.36.157000000000-04:00'
PARAMETER MARKER     2         = 874061768231529985                      
PARAMETER MARKER     3         = 'ESB-ENTLFPID'                          
PARAMETER MARKER     4         = 733259035022786252                      
PARAMETER MARKER     5         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     6         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     7         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     8         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     9         = '2021-04-06-00.14.33.624000000000-04:00'
PARAMETER MARKER    10         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER    11         = '2021-04-05-23.17.23.923000000000-04:00'
PARAMETER MARKER    12         = 'C'                                     

 

 

Environment

Release : 20.0

Component : CA Detector for DB2 for z/OS

Resolution

Some SQL -911 errors are missing host variable fields.  Xmanager shows no abends or messages. After some testing and discussions with the Lab team the Detector product is behaving as expected. The following default error messages are displayed when host variables cannot be collected:

SQLDA ERROR: SQLDATA OFFSET ZERO

SQLDA ERROR:SQLDATAOFFSET BEYOND MAX

SQLDA ERROR:SQLLEN LENGTH NEGATIVE.

The SQL statement has an invalid underlying SQLDA host variable structure and the values could not be displayed properly. Here is a link to  a Broadcom article explaining the default error messages: 

KD107682

When the host variables are set to "null" in the Java application will produce the default error message: SQLDA ERROR:SQLDATA OFFSET ZERO. In a Java application, I have this prepared insert statement:

INSERT INTO BARSC09.TEST1 (ID, FIRST, DATE1) VALUES (?,?,?)

The host values are set to “null” for the parameter 2 and 3.

pstmt.setInt(1, 1);
pstmt.setString(2, null);
pstmt.setTimestamp(3, null);

When SQL is executed it will fail because of a duplicate primary key, SQLCODE -803.

Here is the display from the “DETECTOR SQL Error Call Text” panel in Detector:

INSERT   INTO BARSC09.TEST1 ( ID , FIRST , DATE1)  VALUES ( ? , ? , ? )            HOST VARIABLE DATA USED WITH THIS SQL REQUEST

PARAMETER MARKER     1         = 1                               

PARAMETER MARKER     2         = SQLDA ERROR:  SQLDATA OFFSET ZERO

PARAMETER MARKER     3         = SQLDA ERROR:  SQLDATA OFFSET ZERO.                                                                                                                                                                                                  

The distributed DB2 application will be passing “null” values for these parameter markers?

PARAMETER MARKER     1         = '2021-04-06-00.14.36.157000000000-04:00'
PARAMETER MARKER     2         = 874061768231529985                      
PARAMETER MARKER     3         = 'ESB-ENTLFPID'                          
PARAMETER MARKER     4         = 733259035022786252                      
PARAMETER MARKER     5         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     6         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     7         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     8         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER     9         = '2021-04-06-00.14.33.624000000000-04:00'
PARAMETER MARKER    10         = SQLDA ERROR:  SQLDATA OFFSET ZERO       
PARAMETER MARKER    11         = '2021-04-05-23.17.23.923000000000-04:00'
PARAMETER MARKER    12         = 'C'