Another type of data listener is a SQL table logger that writes data directly to a relational database. Use a component of class atg.service.datacollection.JTSQLTableLogger. SQL table loggers are configured with properties that link them to the JDBC data source, database table name, and database column in which to log the data items.
Each data item a SQL table logger receives is written to the named table using an appropriate INSERT statement. For this to work, the table must exist, and the dataSource must have INSERT permission on the table. The SQL table logger attempts to reconnect to the database as needed. It also gives you control over the size of the database transactions it uses when flushing data.
In production systems, you should use DataListenerQueues to feed data to your SQL table logger. This allows unlogged data to queue up without being lost and without affecting the performance of data sources. See Data Collector Queues.
The following table describes the properties you use to configure a SQL table logger:
Property | Description |
|---|---|
| A JTA data source that the SQL table logger uses to connect to the database. See the ATG Installation and Configuration Guide. |
| The name of the SQL table that will hold the data we are logging. |
| A mapping of the property name of the data collection event to the column name in the database table specified by the
where
|
| Flush data after receiving this number of data items. See Data Flushing. |
| Scheduler component to use with a data flushing schedule. See Data Flushing. |
| Schedule to use in flushing data. See Data Flushing. |
| The transaction manager used by the SQL table logger. See the Transaction Management chapter. |
| The maximum number of rows to be inserted in each database transaction. See Configuring Transaction Size. |
| With truncation enabled (the default), the SQL table logger determines the SQL column size when the application starts up. String items that are longer than the available SQL column size will be truncated before being logged. If truncation is disabled, then an attempt to log a string that’s too large for the SQL column results in the insertion failing and the data being lost. Truncation of number and time entries is handled, if at all, by your JDBC driver. |
| The maximum number of entries to accumulate before flushing the data to the database. See Configuring Transaction Size. |
| Should the data source be blocked if the buffer is full? See Using Blocking with a Data Collector Queue. |
The following properties can be helpful in cases where the user does not own the table to which log entries are to be made:
| If the user does not own the table to which log entries are to be made, you can use this property to construct a qualified table name. This property is not used during the initial metadata query, but if present will be prepended to the table name when inserts or updates are made. |
| A String representing a schema name pattern. If the user does not own the table to which log entries are to be made, this property can be used once during initialization of the logger in a call to determine the column types. Make sure you set this property using the exact case (upper, lower, mixed) that your database uses to store object identifiers. For example, Oracle stores its identifiers in uppercase. So, you would use |
| A String representing a catalog name. If the user does not own the table to which log entries are to be made, this property can be used once during initialization of the logger in a call to determine the column types. See the Javadoc for |
For instance, in a case where a table named ztab is owned by admin and the user is dynamo, here’s how these properties can be used with Oracle, Sybase, and Microsoft SQL Server DBMS:
Property | Oracle | Sybase | SQL Server |
|---|---|---|---|
|
|
|
|
|
| (3) | (5) |
| (1) | (4) | (6) |
| (2) |
|
|
(1) Oracle seems to ignore this value; just leave the property blank.
(2) For Oracle, leave this blank if user has an Oracle synonym, otherwise use admin.
(3) On Sybase, if the user is sharing a database with the table owner, you may be able to leave this blank; otherwise use the name of the table’s owner (here, admin).
(4) Sybase interprets the “catalog name” as the database name; leave blank or use the name of the database in which the table is stored.
(5) On Microsoft SQL Server, if the owner of the table is not the owner of the database, use the name of the table’s owner (here, admin). If the owner of the table is the owner of the database, use dbo.
(6) On Microsoft SQL Server, if the user is sharing a database with the table owner, you may be able to leave this blank; otherwise use the name of the table’s owner (here, admin).
