Running a large DB2 federated query and hitting SQL0101N

Body

In a DB2 Federated environment running a large query is returning SQL0101N.

Example of a query could be  a federated query with a large WHERE clause with many ORs and ANDs.

The details of  SQL0101N is documented in DB2 Knowledge Center,

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00101n.html

As documented,  it can be experienced due to insufficient memory settings. If issues related to memory settings are experienced there will be related messages logged in the db2diag.log.  Also, it can happen if the query is too complex which might return some additional indications of the issue.

But, in  a Federated setup the most common reason of the SQL0101N is, when a statement exceeds a limit of either a federated server or a federated data source.

Messages similar to following could be logged in db2diag.log messages,

2017-05-08-15.57.36.667681-480 I97463E3031           LEVEL: Event
PID     : 46802                TID : 58877017494272  PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : MYDB
APPHDL  : 0-32234              APPID: <>

AUTHID  : DB2INST1             HOSTNAME: <>
EDUID   : 789                  EDUNAME: db2agent (MYDB) 0
FUNCTION: DB2 UDB, SW- statement gen, sqlnt_walk_remote, probe:10
DATA #1 : <preformatted>
Caught sqlcode -101.  Dumping stack trace.
CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
  [0] 0x00002B59EA34B154 pdLogVPrintf + 0x2D4
  [1] 0x00002B59EA34AE6C pdLogPrintf + 0x8C
  [2] 0x00002B59EB4CF4E6 pdInvokeCatchInterface + 0x1A6
  [3] 0x00002B59EB66CEA5 _Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x1F5
  [4] 0x00002B59EAA16F1D _Z10sqlnn_erdsiiiiiz + 0x19D
  [5] 0x00002B59EC774C7F _Z17sqlnt_walk_remoteP13sqlno_globalsP19sqlno_plan_operatori + 0x2CF
  [6] 0x00002B59EC7746F0 _Z10sqlnt_walkP13sqlno_globalsP19sqlno_plan_operatori + 0xB0
  [7] 0x00002B59EC7747A9 _Z10sqlnt_walkP13sqlno_globalsP19sqlno_plan_operatori + 0x169
  [8] 0x00002B59EC77456A _Z10sqlnt_mainP13sqlno_globalsP19sqlno_plan_operator + 0x5A

.....

First,  try to see if following helps,

1. Determine whether it is a federated data source that is returning the error, or the federated server that is returning the error.
2. Take one or more of the general actions already described. For example:
   • If the error is being returned by the federated server, increase the size of the statement heap using the stmtheap database configuration parameter, or set the stmtheap parameter to AUTOMATIC.
   • Reduce the complexity of the statement.

If  the above actions don’t help then it might be the length of the statement which needs an attention.

Try to increase the  "max_stmt_len" server option.

An  example,

alter server <servername> options(add db2_max_stmt_len '65535' )

Following could be used to check the current statement length set  inside the catalog,

db2 "select char(WRAPNAME, 10) WRAPNAME, char(SERVERNAME, 10) SERVERNAME, char(SERVERTYPE,10) SERVERTYPE, char(SERVERVERSION, 10) SERVERVERSION, char(OPTION,40) OPTION, char(SETTING,10) SETTING from syscat.serveroptions"

Then  check for the OPTION name DB2_MAX_STMT_LEN and it’s setting.

The above query could be used to check the statement length before and after the change and verified.

Based on the need the DB2_MAX_STMT_LEN could  be increased further and tried.

db2_max_stmt_len default value is set by different wrappers. This value can be changed through server options. The value needs to be adjusted based on the length of the SQL being run against the targeted federated server.

For DRDA wrapper, the db2_max_stmt_len is consistent with DB2 SQL length limit (2097152). However, for FS NET8 Oracle Wrapper, the value is set to 65535 instead.
The default db2_max_stmt_len for FS SQL against Oracle is 65535.

A common mistake made about altering server option is to miss to choose the right server to alter the option. Users alter option against a wrong source server and find the way is not helping.  It’s suggested to check very closely whether the right source server is chosen or not.

Apart from trying an alter statement length following might be tried out in certain situations,

1) set the pushdown option to 'N' which is not always desired, unless somebody want to read everything from the file into federation server.
2) run the db2fedsvrcfg utility to find out and tune the server options. This utility might find out the max stmt len and sets the option. But, this is for ODBC wrapper.