SQL Return Code -136 count incremented in MUF

Products

• Datacom
• DATACOM - AD

Issue/Introduction

Executing the following query...
SELECT MUF_NAME, RUN_UNIT, LUW_BEG_REQ_NBR, JOB_NAME, TERM_ID, PLAN_AUTH, PLAN_NAME, STMT_ID, QRY_LVL, QRY_NBR, PROC_STEP, TBL_NBR, EXEC_CNT, ROWS_OUT, CMD_TYPE, STMT_TYPE, ACCESSOR_ID, TSK_NBR, T1.DBID, DB_TBL_NAME, PROC_TYPE, STAMP_LUW_BEG, STAMP_LAST_REQ, STAMP_CUR, TIME(STAMP_CUR) - TIME(STAMP_LAST_REQ) AS REQ_DUR, TIME(STAMP_CUR) - TIME(STAMP_LUW_BEG) AS LUW_DUR, TRIM(T2.SQL_AUTHID) CONCAT '.' CONCAT TRIM(T2.TABLE_SQLNAME) AS SQL_TBL_NAME

FROM SYSADM.SQL_STATUS_CURRENT T1 LEFT JOIN SYSADM.DIR_TABLE T2 ON T1.DBID = T2.DBID AND T1.DB_TBL_NAME = T2.TABLE_NAME

WHERE JOB_NAME LIKE '%' AND ACCESSOR_ID LIKE '%' AND TIME(STAMP_CUR) - TIME(STAMP_LAST_REQ) >= 0 AND TIME(STAMP_CUR) - TIME(STAMP_LUW_BEG) >= 0 ORDER BY MUF_NAME, JOB_NAME, RUN_UNIT, LUW_BEG_REQ_NBR, PLAN_AUTH, PLAN_NAME, STMT_ID, QRY_LVL, QRY_NBR, PROC_STEP QUERYNO 900006

When executed under DBSQLPR the query executes successfully and no SQL codes are generated within MUF. When executed via Datacom Server (PHP web page using 64-bit driver, or CA Visual Express using 32-bit driver) the query executes successfully (results are returned) but the SQL Return Code -136 counter is incremented each time it is executed. By turning on MUF dumps for SQL code -136 we can see the error message SQLDA TRUNCATED - NEEDED 27 ENTRIES

Environment

CA Datacom/DB SQL 15.1, CA Datacom/DBsrv 15.0

Resolution

Need an enhancement request. 

Currently, Datacom Server guesses that the number of SQLDAs needed will be 20.

If that number is exceeded SQL generates a -136 but the client code(pc) never sees the error as since one of the things returned the actual number of SQLDAs need and the query is re-executed.

The code is operating as expected in this case

Additional Information

-136 - SQLDA TRUNCATED - NEEDED aaaa ENTRIES

Reason:
An insufficient number of SQLDA entries was provided to contain returned data.
The SQLSTATE that equates to this SQL return code is 53S33.

Action:
Ensure that the number of SQLDA entries corresponds to the number of columns to be returned by the SELECT statement in question.