Running a large DB2 federated query and hitting SQL0101N
Biswarup(Bis)Mukherjee 120000HKTY Visits (7852)
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,
AUTHID : DB2I
First, try to see if following helps,
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 sysc
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.
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.