Kbase P119161: SQL-92: How to turn SQL statement logging on and off?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/14/2011 |
|
Status: Verified
GOAL:
How to programmatically set server connection logging?
GOAL:
How to turn SQL statement logging on and off?
GOAL:
What does the SQL-92 statement 'SET PRO_SERVER LOG' do?
GOAL:
How to debug SQL-92 statements in OpenEdge 10.1x?
GOAL:
How to debug a SQL92 statement?
GOAL:
How to turn on SQL statement tracing?
GOAL:
How to produce a SQL trace file?
GOAL:
How to produce a SQL trc file?
GOAL:
How to enable SQL logging?
FACT(s) (Environment):
OpenEdge 10.1x
All Supported Operating Systems
FIX:
The SQL-92 statement 'SET PRO_SERVER LOG' controls logging for all connections to all OpenEdge SQL Servers. It is executed from any ODBC or JDBC client application to set the logging on and off using the following syntax:
OpenEdge 10.1A: SET PRO_SERVER LOG [ ON | OFF ] [ WITH ( STATEMENT ) ];
OpenEdge 10.1B or later: SET PRO_SERVER LOG [ ON | OFF ] [ WITH ( { STATEMENT, QUERY_PLAN } ) ];
Where ON indicates that logging is turned on, OFF indicates that logging is turned off, STATEMENT indicates that statement tracing information is written to each log file and QUERY_PLAN indicates that the query plan information is written to the log file.
Steps to enable the SQL statement logging:
1. Start the database.
2. Connect with any JDBC or ODBC tool.
3. Run the following statement to enable SQL statement logging:
SET PRO_SERVER LOG ON with (statement);
4. Run the application.
You see some log files starting with SQL_*.log, which will contain the statements going to the server.
5. Run the following statement to turn off SQL Statement logging:
SET PRO_SERVER LOG OFF;
When logging is set ON, each SQL Server begins logging to a file named as follows: SQL_server_<server-id>_<ddmmmyyyy>_<hhmmss>.log.
Server-id corresponds to the server ID shown in database_name.lg and the logging files are located in the server?s working directory as defined by the WRKDIR environment variable.
NOTE: If the _sqlsrv2 is very busy the log can grow very quickly. The options given to the "set pro_server" i.e. statement or query_plan can also contribute to the growth of the log file.