SQLDA Cache space reached size threshold limit

[solpssi@bellsouth.net]

I have seen this message before. I believe you have a RPG program with Embedded SQL which is Defined as ACTIVATION GROUP *NEW. Also you CLOSE Cursor is probably *ENDMOD. You are repeatedly calling it over and over. Recompile as ACTIVATION GROUP *CALLER and CLOSE CURSOR *ENDACTGRP. Note: if the behavior of your SQL is in Error after this change the SQL operations in your program may not be in the correct order or missing some statements. Note: A SQL Statement only needs to be PREPARED once. It can be re-used many times.

[Guest.Visitor]

The program is being called over and over. The ACTIVATION GROUP was set to *CALLER and the CLOSE was set to *ENDACTGRP. The way the code is build it does not need to use OPEN, CLOSE, or the PREPARED statements. The issue identified above does appears to be due to the SQL statement that we had constructed for our testing. I say appears because we have no definitive answer at this time but we have made some assumptions based on further testing. The imbedded SQL code uses a complex select statement that is heavy on SQL Joins, uses a CASE statement, and uses a MAX statement (which is in a sub-select) on one of the selection variables. This code returns a single row of data from multiple tables. The code causes a temporary sort to occur when the statement is run. Since this temporary sort is on one of the select variables it appears that this is causing the ODP to NOT be reusable. After about 15,000 rows have been returned the batch job log starts logging the message "SQLDA Cache space reached a size threshold limit and was cleared" repeatedly. The imbedded SQL code was modified as follows: 1) Removed the CASE statement, 2) Removed the MAX statement (which removed the sub select) These changes appear to allow the reuse of the ODP as we did not get any SQLDA Cache messages in the log. The new code is still

[Guest.Visitor]

Our batch job log is receiving the message "SQLDA Cache space reached a size threshold limit and was cleared" multiple times. This is occuring in a new application that we are testing. Our operations person is contacting IBM support, per the message text. Can anyone tell me what this is and how to avoid getting this message? Is there a PTF that would correct this issue? Is there a system value that needs to be changed? Thanks.

---

Code

[solpssi@bellsouth.net]

"complex select statement that is heavy on SQL Joins, uses a CASE statement, and uses a MAX statement (which is in a sub-select) on one of the selection variables." I prefer not to use Joins. Joins usually rebuild on every execution. Joins have a purpose but it is not to retrieve a single row. Joins are for Doing large set processing (batch). You may be able to improve this SQL statement further by eliminating the Joins since the SQL statement's purpose is to return a single Row. Consider using User Defined Functions for Finding Values in Related tables. Using the UDF approach you can give each SQL Statement for Accessing the Other related tables hints to use a specific index similar to SETLL/READ. Your SQL statement would look something like this: Select fld1, fld2,func(fld3,fld4) AS fld_3, func2(fld5,fld1) as fld_4 from table where fld1 = :key and fld2 = :key2 Also you may observe that the UDF way is multi-threaded capable. I think you have already taken some of the right steps. Good luck.