IZ18944: STATEMENT TOO LONG SQLCODE:-101

APAR status

• Closed as fixed if next.

Error description

• Abstract:Statement too long SQLCODE:-101
  
  User gets "The statement is too long or too complex"
  errors (SQLCODE:-101) when single PA agent handles too many
  boxes.
  Depending on task type, number of boxes that causes breaching
  64KB of query differ from serveral dozen to appx. 300
  
  
  
  
  
  
  
  
  
  
  
  
  
  Detailed Recreation Procedure:
   Make ITPA agent produce query longer than 64KB
   by creating managed system list containing 1000
   boxes running on the same os type.
  

Local fix

• Create a number of small MSLs (MSL containing up to 50 nodes),
  clone the error generating tasks (#MSL -1) times and assign each
  small MSL to each cloned tasks.
  

Problem summary

• Occurs on any OS. It is caused by the DB2 query lenght
  limitation.
   If a number of nodes exceeds a safe number that does not cause
  the problem, the nodes are being divided into small sets.
  Instead of one query there is a number of queries issued, so
  that  none of the queries exceed 64 KB.
   This fix was introduced to ITPA 6.1.1 fixpack 1 GA available on
  eXtreme Leverage and Passport Advantage.
  
  ITPA agent, in its evaluation cycle, takes summarized data from
  TDW by issuing SQL queries. Those queries are build dynamically
  basing on e.g. monitored nodes. The more nodes of a specific
  type were monitored (like Windows, UNIX, DB2, etc.), the larger
  the query was. DB2 8.x has a limitation of 64KB in lenght of a
  query that can be processed. Every time a query larger than 64KB
  was issued, a DB2 server returned an error:
   com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -101,
  SQLSTATE: 54001, SQLERRMC: 1
    According to our estimations, no more than 300 nodes could be
  queried at a time to avoid this problem.
  

Problem conclusion

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:

Fix information

Applicable component levels

• R610 PSN

     UP