IZ62932: TEPS INCORRECTLY QUOTES INTEGER VALUES IN WHERE CLAUSE

APAR status

• Closed as program error.

Error description

• When creating a query with a where clause and the datatype of
  the column values is integer, the TEPS fails to identify it as
    an integer and places it in single quotes causing the error:
    SQL0401N  The data types of the operands for the operation
    ">=" are not compatible.  SQLSTATE=42818
  
  Detailed Recreation Procedure:
   Go into the CPU ID workspace = aggregate, history set to 48
   hours. Only 24 hours returned.  remove aggregrate no history.
   history set to 48 hours. 48 hours of data returned
   add aggregate, no history
   CPU ID = aggregate, history set to 48 hours.  Only 24 hrs
   returned.
  

Local fix

• No workaround available
  

Problem summary

• Creating a new query or a query based on another query, and a
  filter value is selected, and the value is a number and not a
  string. The resulting query that is created will have single
  quotes around the filter value, which can cause the query to
  fail when going to the warehouse database. The error generated
  when the warehouse database is DB2:
  
  [IBM][CLI Driver][DB2/NT] SQL0401N  The data types of the
  operands for the operation "X" are not compatible.
  SQLSTATE=42818
  
  Where "X" is the operation selected in the filter (eg. =, <, >,
  etc...).
  
  After defect 86493, the filter predicate type was not set in
  query request properties for new queries. The KFWQUERY table
  QUERY column data which contains the attributes of the query
  including the filter values is stored without filter type
  information. When the portal server creates the SQL SELECT
  statement, it treats the filter value without type information
  as a string and encloses it in quotes.
  
  This problem can occur on any Operating System platform and may
  affect other warehouse database types.
  

Problem conclusion

• The portal client ExpressionHelper was modified to store the
  column data type property as the predicate filter type, so that
  the portal server will know how to treat it.
  
  The fix for this APAR is included in the following maintenance
  vehicle:
      | fix pack | 6.2.1-TIV-ITM-FP0002
      | fix pack | 6.2.2-TIV-ITM-FP0002
  

Temporary fix

• The query stored in the KFWQUERY table QUERY column can be
  modified to add the filter type information. For example, the
  value of the QUERY column might look as follows:
  
  tableCount=1@table1.name=WTMEMORY@table1.column1.name=WTMEMORY.O
  RIGINNODE@table1.column2.name=WTMEMORY.AVAILBTMEM@table1.column3
  .name=WTMEMORY.AVAILKB@table1.column4.name=WTMEMORY.CACHEBTS@tab
  le1.column5.name=WTMEMORY.CACHEKB@table1.column6.name=WTMEMORY.C
  ACHEBTSPK@table1.column7.name=WTMEMORY.CACHEKBPK@table1.column8.
  name=WTMEMORY.CACHEFLTSC@table1.column9.name=WTMEMORY.COMMLIMIT@
  table1.column10.name=WTMEMORY.COMMITLKB@table1.column11.name=WTM
  EMORY.COMMBYTE@table1.column12.name=WTMEMORY.COMMKBYTE@table1.co
  lumn13.name=WTMEMORY.FRSYSTBLEN@table1.column14.name=WTMEMORY.TI
  MESTAMP@table1.columnCount=14@at=$THRUNODE$@filter1.connector=AN
  D@filter1.column=WTMEMORY.ORIGINNODE@filter1.operator=EQ@filter1
  .value=$NODE$@filter2.connector=AND@filter2.column=WTMEMORY.AVAI
  LBTMEM@filter2.operator=GT@filter2.value=0@parma1.value='TIMEOUT
  ','600',3@filterCount=2@parmaCount=1@property1.name=-1021A@prope
  rty1.value=knt.Memory@property2.name=enableHistory@property2.val
  ue=F@property3.name=enableTimespan@property3.value=F@property4.n
  ame=queryName@property4.value=Memory@property5.name=timeoutSecon
  ds@property5.value=600@propertyCount=5@ccsid=55050241@
  
  In that string the filter that should be described as an integer
  is filter2:
  
  
  filter2.connector=AND@filter2.column=WTMEMORY.AVAILBTMEM@filter2
  .operator=GT@filter2.value=0@
  
  To correct this filter you would need to add:
  
    filter2.type=I@
  
  This should only be done under the direction of IBM support. A
  syntax error here could cause the query itself to be unusable.
  

Comments

APAR Information

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

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

Fix information

• Fixed component name

  TEPS

• Fixed component ID

  5724C04PS

Applicable component levels

• R621 PSY

     UP