IDB2 IQL request getting a syntax error in R20 but same request is not in R19

Article Id: 143389
Status: Published
Updated On: 24-01-2020 15:58
Products:
CA Bind Analyzer for DB2 for z/OS , CA SQL-Ease for DB2 for z/OS , CA Sysview Performance Management Option for DB2 for z/OS , CA Plan Analyzer for DB2 for z/OS , CA Subsystem Analyzer for DB2 for z/OS , CA Database Management for DB2 for z/OS - Performance Suite , CA Database Management for DB2 for z/OS - SQL Performance Suite , CA Detector for DB2 for z/OS , CA-Insight Performance Monitor for DB2 UDB for z/OS
Issue/Introduction:

The following error is revealed in the IDB2 r20 DC log at startup, but the problem does not occur when running the request in r19. 

DBG03038E DB2S 07:45:18 THIS FIELD CANNOT BE USED IN DECLARE OR WHERE CLAUSE

          CMD-TXT(,4,16) = '-DIS'              

         

 

 

Cause:

R20 introduced tighter controls over fields allowed to be used in a 'Where Clause' mostly relating to variable length fields, and in this case CMD-TXT.

Environment:

Release : 20.0

Component : CA Insight Database Performance Monitor for DB for z/OS

Resolution:

There has been a change in r20 where stricter controls have been put in place for 'where clause' fields mostly relating to variable length fields which CMD-TXT happens to be one of them. A sound circumvention is being offered to correct this failure by using IFCID 91 "COMMAND" to report the end of the DB2 command execution instead of IFCID 90 "COMMAND-ST" reporting the start of the DB2 command. Attached, please find the modified UPDBASPR request to use for r20.

Here are the changes that were made:

Changed COMMAND-ST to COMMAND
Changed CMD-TXT to CMD-TXT-VAR field in WHERE clause (CMD-TXT-VAR is derived from CMD-TXT)
Changed the substring offset in the WHERE clause in your request:
CMD-TXT(,4,16) -> CMD-TXT-VAR(,4,14)
CMD-TXT(,4,3) -> CMD-TXT-VAR(,4,1)