Technical Blog Post
Running a large DB2 federated query and hitting SQL0101N
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,
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-18.104.22.1687681-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)
 0x00002B59EA34B154 pdLogVPrintf + 0x2D4
 0x00002B59EA34AE6C pdLogPrintf + 0x8C
 0x00002B59EB4CF4E6 pdInvokeCatchInterface + 0x1A6
 0x00002B59EB66CEA5 _Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x1F5
 0x00002B59EAA16F1D _Z10sqlnn_erdsiiiiiz + 0x19D
 0x00002B59EC774C7F _Z17sqlnt_walk_remoteP13sqlno_globalsP19sqlno_plan_operatori + 0x2CF
 0x00002B59EC7746F0 _Z10sqlnt_walkP13sqlno_globalsP19sqlno_plan_operatori + 0xB0
 0x00002B59EC7747A9 _Z10sqlnt_walkP13sqlno_globalsP19sqlno_plan_operatori + 0x169
 0x00002B59EC77456A _Z10sqlnt_mainP13sqlno_globalsP19sqlno_plan_operator + 0x5A
First, try to see if following helps,
- Determine whether it is a federated data source that is returning the error, or the federated server that is returning the error.
- 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.
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.