DBAppender

Writes log events to a SAS table or to a table in a third-party DBMS.

Valid in:

XML Configuration

Table of Contents

Syntax

[<param name="Column" value="column-parameter-n"/>]

</appender>

Syntax Description

class="DBAppender" name="appender-name"

specifies the user-assigned name for this instance of DBAppender.

Default	None
Requirement	This parameter is required.

name="ConnectionString" value="connection-string"

specifies options for connecting to the data store to which events are to be written. Within the connection string, place a semicolon between each option. The options vary depending on the type of data store you are using. For details, see:

Requirement

This parameter is required.

name="Locale" value="locale"

specifies the locale that is used to write messages to the file.

Default	The locale setting that is in effect for the SAS session. For example, the LOCALE system option might be specified in the configuration file for a SAS server or in the configuration file for Base SAS.
Default	For logging processes that run outside a SAS session (for example, logging for the SAS Object Spawner), the default is the locale that is specified in the operating system settings.
Requirement	This parameter is not required.
See	SAS National Language Support (NLS): Reference Guide

name="MaxBufferedEvents" value="integer"

specifies the maximum number of log events to buffer before sending them to the data store. When MaxBufferedEvents is set to 0, messages are processed synchronously, one row at a time.

Default	1
Requirement	This parameter is not required.

name="SelectStatement" value="SQL-statement"

specifies an SQL statement that identifies the name of the table, and the names of specific columns within the table, where event information is to be written. The table must already exist. If you want to write information to all of the columns in the table, use TableName instead of SelectStatement.

Requirement	SelectStatement is required if TableName is not specified.
Interaction	The TableName and SelectStatement parameters are mutually exclusive. If both of these parameters are specified, the SelectStatement parameter takes precedence.
Example	<param name="SelectStatement" value="select date,level,message from dblog;"/>

name="TableName" value="table-name"

specifies the name of the table to which event information is to be written. The table must already exist. If you want to write to just a subset of the columns in the table, use SelectStatement instead of TableName.

Requirement	TableName is required if SelectStatement is not specified.
Interaction	The TableName and SelectStatement parameters are mutually exclusive. If both of these parameters are specified, the SelectStatement parameter takes precedence.

name="Column" value="column parameter"

specifies a conversion character indicating the type of information that is to be written to a particular column in the table. The following conversion characters are valid: c, d, F, L, m, p, r, S, sn, t, u, x, and X.

To write literal text to a column, specify @ (the at sign) followed by the literal text.

Specify the Column parameters in exactly the same order that the columns occur in the table.

Restrictions	Limit each column parameter to a single conversion character. Combining multiple conversion characters in a single column could cause alignment problems in the output.
	DBAppender does not support the following: the n (newline) conversion character format modifiers, which control text justification and field widths for data items in a log event
	DBAppender has limited support for the d (date/time) conversion character. To insert a date, specify the underlying column to be either a DATE or any character type of length 10. To insert a timestamp, specify the underlying column to be either a TIMESTAMP or any character type of length 24.
	If you use the m (message) conversion character, position it in the final column of the table. Because DBAppender does not use format modifiers to control column widths, a lengthy message in an interior column position could cause text overflow problems.
Requirement	At least one Column parameter is required.
See	Conversion character details in the “Pattern Layouts” chapter: c, d, F, L, m, p, r, S, sn, t, u, x, X
Example	<param name="column" value="d"/> <param name="Column" value="p"/> <param name="Column" value="u"/> <param name="Column" value="@A literal string value to insert"/> <param name="Column" value="m"/>

Details

Before using DBAppender, you must first create the table to which the log events are to be written. The Column parameters must be specified in exactly the same order that the corresponding columns occur in the table.

DBAppender processes only a subset of loggers and ignores events for all other loggers. For each ignored event, a new event is created that contains the original event and a message stating that the event was ignored. These new events, which are written to the Logging.Appender.DB logger at the DEBUG level, can be routed to another appender for capture.

DBAppender processes the following subset of loggers:

Admin and all of its child loggers
App.Initialize and all of its child loggers
App.Program and all of its child loggers
Audit.Data and all of its child loggers
Audit.Meta and all of its child loggers
Perf.ARM.application-name.APPL
Perf.ARM.application-name.DSIO
Perf.ARM.application-name.PROC
All child loggers of Perf.ARM.IOM (but not Perf.ARM.IOM itself)
Perf.ARM.OLAP_SERVER
Perf.ARM.User and all of its child loggers

For the Perf.ARM loggers, you can use any of the following methods to specify application-name:

specify the LOGAPPLNAME= system option on the command line. See LOGAPPLNAME= System Option.
specify the AppName parameter in the configuration for the ARMAppender. See ARMAppender.
specify the value when coding one of the Application Response Measurement (ARM) macros. See the SAS Interface to Application Response Measurement (ARM): Reference.

If you do not specify a value for application-name, the default value SAS is used.

Examples

Example 1: Writing Events to a DB2 Table

In this example, log events are written to a DB2 table called LOG. The table includes the following columns: sequence number, date, logger name, and message.

Step 1: Use the following SQL statement to create the table:

create table LOG ( seqno VARCHAR(10), date VARCHAR(24), logger VARCHAR(100), 
   msg VARCHAR(500) );

Step 2: Use the DB2 command-line processor to create a DSN called LOGDSN.

Step 3: In the logging configuration file, use the following instance of DBAppender to connect to the database and write log events to the table:

<appender name="dblog" class="DBAppender">
   <param name="ConnectionString" value="DRIVER=DB2;UID=User1;PWD=********;
      CONOPTS=(DSN=LOGDSN);CATALOG=X;" /> 
   <param name="MaxBufferedEvents" value="300" /> 
   <param name="TableName" value="LOG" /> 
<!-- 
The column parameters must be specified in exactly the same order that the 
columns occur in the table.
 --> 
   <param name="Column" value="sn" /> 
   <param name="Column" value="d" /> 
   <param name="Column" value="c" /> 
   <param name="Column" value="m" /> 
 </appender>

Example 2: Writing Events to an ODBC-Compliant Database

In this example, log events are written to a table called LOG on a Microsoft SQL Server. The table includes the following columns: sequence number, date, logger name, and message.

Step 1: Use the following SQL statement to create the table:

create table LOG ( seqno VARCHAR(10), date VARCHAR(24), logger VARCHAR(100), 
   msg VARCHAR(500) );

Step 2: Use Windows Data Source Administrator to create an ODBC data source for Microsoft SQL Server. Specify a DSN called SQLSERVERDSN.

Step 3: In the logging configuration file, use the following instance of DBAppender to connect to the server and write log events to the table:

<appender name="dblog" class="DBAppender">
   <param name="ConnectionString" value="DRIVER=ODBC;UID=User1;PWD=********;
      CONOPTS=(DSN=SQLSERVERDSN);" /> 
   <param name="MaxBufferedEvents" value="300" /> 
   <param name="TableName" value="LOG" /> 
<!-- 
The column parameters must be specified in exactly the same order that the 
columns occur in the table.
 -->
   <param name="Column" value="sn" /> 
   <param name="Column" value="d" /> 
   <param name="Column" value="c" /> 
   <param name="Column" value="m" /> 
</appender>

Example 3: Writing Events to an Oracle Table

In this example, log events are written to an Oracle table called LOG. The table includes the following columns: sequence number, date, logger name, and message.

Step 1: To create the table, the SAS/ACCESS Engine for Oracle can be used as follows:

libname x oracle user=User1 password=******** path='mypath';
        data x.LOG;
            length seqno $10;
            length date $24;
            length logger $100;
            length msg $500;
        run;

Step 2: In the logging configuration file, use the following instance of DBAppender to connect to the Oracle database and write log events to the table:

<appender name="dblog" class="DBAppender">
   <param name="ConnectionString" value="DRIVER=oracle;UID=User1;
      PWD=********;PATH=mypath;CATALOG=oracle_log" /> 
   <param name="MaxBufferedEvents" value="300" /> 
   <param name="TableName" value="LOG" /> 
<!-- 
The column parameters must be specified in exactly the same order that the 
columns occur in the table.
 -->
   <param name="Column" value="sn" /> 
   <param name="Column" value="d" /> 
   <param name="Column" value="c" /> 
   <param name="Column" value="m" /> 
</appender>

Example 4: Writing Events to a SAS Table

In this example, log events are written to a SAS table called LOG. The table includes the following columns: sequence number, date, logger name, and message.

Step 1: Use the following SAS statements to create the table:

libname x 'c:\temp';
        data x.LOG;
            length seqno  $10;
            length date   $24;
            length logger  $100;
            length msg   $500;
        run;

Step 2: In the logging configuration file, use the following instance of DBAppender to connect to the table and write log events:

<appender name="dblog" class="DBAppender">
   <param name="ConnectionString" value="DRIVER=base;CATALOG=base;
      schema=(name=mywork;primarypath='C:\temp');" /> 
   <param name="MaxBufferedEvents" value="300" /> 
   <param name="TableName" value="LOG" /> 
<!-- 
The column parameters must be specified in exactly the same order that the 
columns occur in the table.
 -->
   <param name="column" value="sn" /> 
   <param name="column" value="d" /> 
   <param name="column" value="c" /> 
   <param name="column" value="m" /> 
</appender>

Example 5: Writing Events to a Teradata Table

In this example, log events are written to a Teradata table called LOG. The table includes the following columns: sequence number, date, logger name, and message.

Step 1: Use the following SQL statement to create the table:

create table LOG ( seqno VARCHAR(10), date VARCHAR(24), logger VARCHAR(100), 
   msg VARCHAR(500) );

Step 2: In the logging configuration file, use the following instance of DBAppender to connect to the database and write log events to the table:

<appender name="dblog" class="DBAppender">
   <param name="ConnectionString" value="DRIVER=TERADATA;UID=User1;
      PWD=********;server=myserver;database=mydatabase;CATALOG=X;" /> 
   <param name="MaxBufferedEvents" value="300" /> 
   <param name="TableName" value="LOG" /> 
<!-- 
The column parameters must be specified in exactly the same order that the 
columns occur in the table.
 -->
   <param name="Column" value="sn" /> 
   <param name="Column" value="d" /> 
   <param name="Column" value="c" /> 
   <param name="Column" value="m" /> 
</appender>