A very important aspect of troubleshooting SQL is testing the SQL through different layers: 3-tier mode, 2-tier mode, Strategy DB Query tool, database native layer tools (such as SQLPlus) etc. For this purpose, users need to know the exact SQL submitted by Strategy Engine to the database. This article outlines various logs, diagnostics, and tools that can be used to capture this SQL.
- Default available means for SQL Capture
- SQL generated for a report can be obtained by using the 'View SQL' mode in Strategy Developer or 'Report Details' in Strategy Web. Refer to the following Strategy Knowledge Base articles for details:
KB3964 : How to view the SQL generated by Strategy SQL Generation Engine for a report job or element browsing
- Particular SQL is automatically logged in DSSErrors.log, if there is an error executing that SQL statement.
- Database Classes diagnostic
The diagnostic Database Classes -> SQL Trace traces SQL submitted by Strategy Engine to all database systems (warehouse, metadata, statistics etc.). It is the most ubiquitous log for capturing SQL. This diagnostic is very useful for capturing SQL from tasks other than report job, such as:
- Element load within prompt
- SQL against Statistics tables
- Refer to the following Strategy Knowledge Base article for details about enabling this diagnostic:
KB13189: How to trace the SQL generated by Strategy Engine - Metadata Server diagnostic
The diagnostic Metadata Server -> SQL Trace captures SQL exclusively submitted by Metadata Server. It is a subset of the Database Classes diagnostic outlined above. Users can still use the Database Classes diagnostic to capture SQL submitted to the metadata.
For information on how to log the metadata SQL in Strategy, refer to the following Knowledge Base document:
KB30087: How to capture SQL submitted by the Metadata Server in Strategy
- SQL Capture Related to Statistics/Enterprise Manager
The diagnostic Database Classes -> SQL Trace is most useful in capturing SQL submitted against statistics tables for general operations such as logging and purging of statistics. The diagnostic Kernel -> Statistics Trace also captures SQL submitted against Statistics tables.
Following are some logs used for SQL capture in special situations for Statistics/Enterprise Manager:
- Strategy Enterprise Manager Data Loading
By default, MSTRMigration.log does not contain SQL executed during the Strategy Enterprise Manager data load process. Refer to the following Strategy Knowledge Base article for details on this:
KB30594: How to enable the SQL executed duing the Strategy Enterprise Manager data load (ETL) to be logged to the MSTRMigration.log
- Statistics and EM Warehouse upgrades
STUpgrade.log and WHUpgrade.log capture SQL submitted during Statistics upgrade and EM warehouse upgrade processes, respectively. Refer to the following Strategy Knowledge Base article for details on this:
KB11259: Log files for tracking Upgrade Statistics and Enterprise Manager Warehouse processes in Strategy Enterprise Manager - Failed Statistics SQL statements
StatisticsFailedSQLs.log is generated when Statistics Manager within Strategy Intelligence Server fails to write to the statistics database. A common reason for this failure is the 10 second timeout. This log comprises the failed SQL statements that can be executed stand-alone at a later time. Refer to the following Strategy Knowledge Base articles for details on this:
KB8145: What is the 'StatisticsFailedSQLs.log' file and how is it used in Strategy Intelligence Server?
- ODBC Trace Log
Under normal circumstances, an ODBC trace should not be enabled just for the purpose of capturing SQL. This is because the ODBC trace log is very detailed and can get very large in a short amount of time, which can negatively impact performance. This log should not be enabled unless absolutely necessary. If this log has been enabled for some other purpose, users can also use this log to determine SQL statements submitted to the database.
- Database Tools for SQL Capture
Database specific tools may be used to capture SQL submitted to the database. This allows analysis of SQL statements from the database perspective. One example is SQL Profiler from SQL Server.
Capturing SQL using database specific tools is helpful when implementation of certain ODBC parameters is achieved using database specific syntax. One such example is SQL_MAX_ROWS ODBC parameter which for example, gets implemented as the statement 'SET ROWCOUNT' on the SQL Server database side. Refer to the following Strategy Knowledge Base article for details on this:
KB9933 : How is the 'Number of Report Result Rows' setting in the Strategy Intelligence Server implemented at the ODBC level?
NOTE: Many of the Strategy diagnostics are set through Diagnostics Configuration editor (Start -> Programs -> Strategy -> Tools -> Diagnostics Configuration). Refer to the following Strategy Knowledge Base article for details on this:
Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this article is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
