SQL Logging

The dfs_log_SQL_Logging.xml configuration file controls the behavior of SQL Logging for SAS Federation Server. This configuration file is located in the/etc directory of the server’s configuration path. Using dfs_log_SQL_Logging.xml, you can set the level of information that each logger captures by specifying a logging level of WARN or TRACE. You can also change logging levels dynamically without stopping the server.

SQL logging is disabled by default. You can enable either full SQL logging, or enable specific transaction loggers to capture information that is suitable for your environment. To enable full SQL logging, open dfs_log_SQL_Logging.xml and set the value for both of the top-level loggers to TRACE. (The default value for these logs is set to WARN.) To fully enable SQL logging, both of the top-level loggers must be activated. There is no need to change any of the configuration parameters. When you set the two top-level loggers, all transaction logging is enabled by default. In other words, enabling both of the top-level loggers is equivalent to setting all of the transaction loggers to TRACE. It should be noted that server performance might be impacted when SQL logging is fully activated.

You should never set one of the top-level loggers without the other. Here are the top-level loggers as they appear in dfs_log_SQL_Logging configuration:

<logger name="Perf.ARM.FederationServer" additivity="false">
       <level value="TRACE"/>
       <appender-ref ref="ARM"/>
    </logger>

    <logger name="Perf.ARM.SQLServices" additivity="false">
       <level value="TRACE"/>
       <appender-ref ref="ARM"/>
    </logger>

Following the top-level loggers, in the dfs_log_SQL_Logging configuration file, are additional transaction logs that you can use to control logging detail. The information captured by these loggers is enabled when you enable full SQL logging. Therefore, you must disable the two top-level loggers before configuring the transaction logs. Use the following task to enable individual transaction loggers:

To capture minimal information, activate the following logs:

Minimal logging captures overall session, connection, and SQL information. To increase the level of detail that is captured, enable Perf.ARM.SQLServices.Statement.Transaction.CURSOR, and then configure one or more of the following loggers, depending on the needs of your environment:

See the ARM Transactions table for a description of each of these transactions.

SAS Federation Server always starts with SQL Logging set at the default level in the configuration file, but can be enabled or disabled dynamically for a server session. This is done through the SQL Log tab of the Federation Server Properties dialog box. When SQL Logging is modified from the SQL Log tab, it is active for the server session only. When the server is restarted, the level of logging reverts to the value specified in the SQL logging configuration file.

By default the application name (client) name is defined in the federation server configuration file and passed to SQL Logging configuration as value="X{App.Name}". When a SAS Federation Server connection string has been configured with a particular client name, you can define the name in SQL Logging by modifying the following value in dfs_log_SQL_Logging.xml:

<param name="column" value="X{App.Name}" />

and replacing App.Name with Client.AppName:

<param name="column" value="X{Client.AppName}" />

See Federation Server (FEDSVR) Driver Reference for information about the APPLICATION NAME connection string option.

The EVENTS table resides in the SQL_LOG database that is created with the SQL_LOG data service as a result of enabling SQL Logging. The EVENTS table contains transactional data records written from SAS Federation Server.

Data captured and stored in the EVENTS table includes the number of bytes inserted from an SQL transaction. This does not include any literal data sent to SAS Federation Server. Only data sent through bound memory buffers, such as parameter data, is included. Also, the number of bytes inserted reflects the amount of data stored in the bound memory locations. It does not reflect the size of the data on disk.

The EVENTS table must be managed manually. With SQL Logging enabled, data records are continuously written to the EVENTS table. Therefore, the table increases in size when the server is active and processing requests. SAS Federation Server maintains logging data indefinitely until the table is managed or purged. You can use the SQL console window to issue commands to manage the EVENTS table. By connecting with the SQL_LOG DSN for which the FedSQL dialect is enabled, use any SELECT or DELETE statement that is supported by the FedSQL language to manage the table.

Because the size of the EVENTS table increases as activity is logged, you should check the size of the table periodically to determine whether records need to be archived or deleted. Use the following statement to calculate the number of rows in the EVENTS table:

SELECT COUNT(*) FROM EVENTS

As the EVENTS table grows in size, you can move data to another table for archive purposes. This is accomplished by creating a federated DSN to the SQL_LOG DSN and another data source to use for storing the archived data.

Use the DELETE statement to remove outdated records that are no longer needed or have been archived. Here is an example:

DELETE FROM SQL_LOG.SQL_LOG.EVENTS
WHERE TRAN_TIMESTAMP < TIMESTAMP ‘2011-04-11 12:00:00’

You can configure SQL Logging so that it has minimum impact on SAS Federation Server performance, especially during periods of heavy processing.

The maximum buffered events option tells the server how many events to buffer before writing them to the EVENTS database. You can set this option using the MaxBufferedEvents option in the SQL Logging configuration file. The default setting for MaxBufferedEvents is 100, meaning that 100 events are buffered before writing them to the EVENTS table. Setting MaxBufferedEvents to a higher number might consume server resources but will show improved performance during periods of heavy processing. A sustainable high value for MaxBufferedEvents is approximately 500. Here is the syntax used in the SQL Logging configuration file:

 <param name="maxBufferedEvents" value="100" />

When using the default configuration of the TRAN data store, writes to the SQL Logging database can sometimes affect overall performance, especially during periods of heavy processing. This is caused by the indexes that are present on the EVENTS table. During these high activity periods, you can drop indexes onto the EVENTS table by updating the SQL_LOG data service as shown here:

alter service SQL_LOG {options AUTOINDEX off}

To activate indexing on the EVENTS table:

alter service SQL_LOG {options AUTOINDEX on}

Because indexes are required to process SQL queries in SAS Federation Server Manager, it is not advisable to view SQL Logging information using SAS Federation Server Manager during periods when indexes are not active. You can re-enable indexes after processing activity decreases on SAS Federation Server. At that time, you should be able to view SQL Logging information in SAS Federation Server Manager.

An alternative to dropping indexes is to configure SQL Logging to use an external, or third party, database. For more information see Configuring a Third Party DBMS for SQL Logging.