Table-level logging

Setting the DML logging option at the table level overrides the database-level setting, depending on how the table was created or altered. The default mode is to use the same setting as the database.

If minimal logging is not enabled at the database level:

• Minimal DML logging is done for tables that have dml_logging explicitly set to minimal via create table or alter table.

• DML is fully logged for all other tables.

If minimal logging is enabled at the database level:

• Full DML logging is done for tables that have dml_logging explicitly set to full.

• Minimal DML logging is done for all other tables.

The default logging setting of a table allows it to inherit—when the DML is executed—the then-current database-level setting for logging. The database administrator may want to periodically turn logging off, then back on again, at the database level. Then, only those tables with specific needs for full or minimal DML logging need to be controlled through explicit table-level settings.

You can execute minimally logged DML commands on a table only if the database has the select into database option turned on; otherwise, all DML commands are fully logged.

For any DML statement on a table where the corresponding trigger is enabled (for example, if an insert trigger is enabled when you execute an insert statement) Adaptive Server performs full logging. This avoids situations where a trigger implements business rules and security mechanisms that need the log records while executing the trigger. To perform minimal logging, specific triggers must be disabled by the table owner before executing the DML statement.

If a DML statement is executed on an view that can be updated, and it eventually resolves to a DML statement on a base table that is eligible for minimally logged DML operations, DML statements executed on the view result in minimally logged DML on the base table. To control the logging mode on base tables that are updated through views, use alter table or set dml_logging to set the logging mode on the underlying table.

You can use alter table to change only the logging mode of user tables, and not of views or other objects.

To create a table with full or minimal DML logging, use:

create table tablename (
	<rest of the column list specifications>
)
lock lock_scheme
with { max_rows_per_page = num_rows
	, exp_row_size = num_bytes
	, reservepagegap = num_pages
	, identity_gap = value
	, dml_logging = {full | minimal}
	}
on segment_name

To create a table using select into so that the target table has DML logging enabled or disabled, use

select <column list>
into table_name 
	[ <external table specifications> ]
	on segment_name
	[ partition_clause ]
	lock lock_scheme
	with { max_rows_per_page = num_rows
		, exp_row_size = num_bytes
		, reservepagegap = num_pages
		, identity_gap = value
			, dml_logging = {full | minimal}
		}
[ from_clause ]
[ where_clause ]
...