The 'Maximum value for final result rows' setting governs the actual number of result rows returned from the database to the Strategy Intelligence Server. It is located at Project Configuration > Governing > Result sets. Refer to the following Strategy Knowledge Base document for details on this setting:
This setting is implemented using the SQLSetStmtOption ODBC function call with the SQL_MAX_ROWS parameter. The following is a snippet from the ODBC trace log depicting this implementation:
M8DBMPE1 148c-e6c ENTER SQLSetStmtOption
HSTMT 010A1AC0
UWORD 1 <SQL_MAX_ROWS>
SQLPOINTER 0x00007D01
M8DBMPE1 148c-e6c EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 010A1AC0
UWORD 1 <SQL_MAX_ROWS>
SQLPOINTER 0x00007D01
The SQL_MAX_ROWS is set to M+1 where 'M' is value for the 'Number of report result rows' setting. The default value for this setting is 32,000 and the ODBC trace example above is for the setting at its default value. The hexadecimal number 0x00007D01 in the above example represents 32,001 in decimal. The SQL_MAX_ROWS parameter is set just before the SQLExecDirect call for the report SQL (if there is only one-pass) or it is set just before the SQLExecDirect call for the last pass (if the report SQL is multi-pass). An example of a similar odbc trace from a Unix/Linux Intelligence Server shows that the parameter itself is not identified in the logs - but the value is set in a corresponding manner. Also the numeric value that the parameter is set to is identified in decimal to make it easier to read (here the project limit was set to 50234).
ppid=2822:pid=3 e3b:2 ENTER SQLSetStmtOption
HSTMT 0x0029a730
UWORD 1
UDWORD 50235
ppid=2822:pid=3 e3b:2 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0x0029a730
UWORD 1
UDWORD 50235
Thereafter, this implementation is converted into database specific syntax by the ODBC driver. The syntax for two example databases is as follows (assuming N = M+1):
For SQL Server:
SET ROWCOUNT N
For DB2 UDB for Windows & Unix:
FETCH FIRST N ROWS ONLY
OPTIMIZE FOR N ROWS
NOTE:
These statements will not appear on any diagnostics on the Strategy product side since they are not issued explicitly by the Strategy Engine. Nor will they appear in the ODBC trace log. They will only be recorded on database side logs.
Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
