The SQL-92 statement 'SET PRO_SERVER LOG' controls logging for all connections to all OpenEdge SQL Servers (_sqlsrv2). It is enabled from any ODBC or JDBC client application by executing the following syntax to enable or disable server-side statement logging:
OpenEdge 10.1A:
SET PRO_SERVER LOG [ ON | OFF ] [ WITH ( STATEMENT ) ];
OpenEdge 10.1B and later:
SET PRO_SERVER LOG [ ON | OFF ][ WITH ( { STATEMENT, QUERY_PLAN } ) ];
OpenEdge 11.7 and later:
SET PRO_SERVER LOG [ ON | OFF ] [ WITH ({ STATEMENT, INOUT, QUERY_PLAN })] [[AND] WITH LEVEL 1|LEVEL 2|LEVEL 3|LEVEL 4];
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.
To enable logging for the current SQL client/connection:
To control the logging for JUST the current SQL connection, use SET PRO_CONNECT LOG instead of SET PRO_SERVER LOG.
Steps to enable the SQL Statement logging:
1. Start the database.
2. Connect with any JDBC or ODBC tool.
3. Run the following statements to enable Server Side SQL statement logging:
SET [ PRO_SERVER | PRO_CONNECT ] LOG ON with (statement);
COMMIT;
4. Run the application.
5. Log files with names in the form
SQL_*.log will be generated, which will record all the statements passed to the server.
Each SQL Server (_sqlsrv2) writes to a separate log file:
SQL_server_<server-id>_<ddmmmyyyy>_<hhmmss>.log.
Server-id : corresponds to the server ID shown in :
- PROMON > R&D > 1. Status Displays > 3. Servers
- The database lg file: <dbname>.lg
SQLSRV2 2: (-----) "dbname" started on IPv4 port 3000 for address 0.0.0.0, pid 6028
SQLSRV2 5: (-----) "dbname" started on IPv4 port 3000 for address 0.0.0.0, pid 6132
Log files can be found:
- In the directory where the database was started, this is often but not necessarily the same directory as the Database's Control Area <dbname>.db.
- In the server’s work directory when the database is managed by the AdminServer (started with the DBMAN command, autostarted with the AdminServer or through the OpenEdge Explorer Browser UI).
- The work directory corresponds to the value of the $WRKDIR environment variable otherwise the applicable registry settings in Windows systems.
- under Linux the command lsof | grep "SQL_server_" can be used when the OpenEdge sql server side logging is active to find out where the sql server side logs are located.
Once these logfiles are located, they can be used to further identify problem areas and isolate root cause. For example recorded statements from a production environment can be extracted to review or re-run in a test environment. Refer for example to Article:
To disable the SQL Statement logging:The following statement turns off SQL Statement logging:
SET [ PRO_SERVER | PRO_CONNECT ] LOG OFF;
COMMIT;
SQL Statement Logging is automatically turned off when any one of the logging files reaches the maximum size of 500 MB, which is not configurable at the time of writing. At this stage, it can be re-enabled as required with perhaps less verbose options.
