object_attr

Description

Reports the table’s current logging mode, depending on the session, table and database-wide settings.

Syntax

object_attr(table_name, string)

Parameters

table_name: name of a table.

string: is the name of the table property that has been queried. The supported string values are:

dml_logging – returns the DML logging level for the requested object in effect, based on the explicitly set table or database’s DML logging level.

dml_logging for session – returns the DML logging level for the current session, taking into account the user running object_attr, the table’s schema, and rules regarding multistatement transactions, and so on. The return value from this argument can be different for different users, and different for statements or transactions for the same user.

compression – returns the compression type for the requested object.

help – prints a list of supported string arguments.

Examples

Example 1

To determine which properties he or she can query, the user runs:

select object_attr('sysobjects', 'help')

Usage: object_attr('tabname', 'attribute')

List of options in attributes table:
     0 : help
     1 : dml_logging
     2 : dml_logging for session
     3 : compression

dml_logging reports the statically-defined dml_logging level for the object, and dml_logging for session reports the runtime logging level chosen for the object, depending on the database-specific and session settings.

Example 2

The default logging mode of a table with durability set to full:

select object_attr("pubs2..authors",
         "dml_logging")

Returns: FULL

Example 3

If the session has logging disabled for all tables, the logging mode returned for tables owned by this user is minimal.

select object_attr("pubs2..authors",
         "dml_logging")

Returns: FULL

SET DML_LOGGING MINIMAL
go

select object_attr("pubs2..authors",
         "dml_logging for session")

Returns: MINIMAL

Example 4

If a table has been altered to explicitly select minimal logging, object_attr returns a value of minimal, even if the session and database-wide logging is FULL.

create database testdb WITH DML_LOGGING = FULL
go

create table non_logged_table (...)
WITH DML_LOGGING = MINIMAL
go

select object_attr("non_logged_table",
         "dml_logging")
Returns: MINIMAL

Example 5

Changes a table’s logging from full to minimal. If you explicitly create a table with full logging, you can reset the logging to minimal during a session if you are the table owner or a user with the sa_role:

Create the testdb database with minimal logging:

create database testdb 
with dml_logging = minimal

Create a table with dml_logging set to full:

create table logged_table(...)
with dml_logging = full

Reset the logging for the session to minimal:
```
set dml_logging minimal
```

The logging for the table is minimal:

select object_attr("logged_table",
         "dml_logging for session")
--------------------

minimal

Example 6

If you create a table without specifying the logging mode, changing the session’s logging mode also changes the table’s logging mode:

Create the table normal_table:
```
create table normal_table
```

Check the session’s logging:

select object_attr("normal_table", "dml_logging")
-------------------------
FULL

Set the session logging to minimal:
```
set dml_logging minimal
```

The table’s logging is set to minimal:

select object_attr("normal_table", 
         "dml_logging for session")
-----------------------------
minmimal

Example 7

The logging mode returned by object_attr depends on the table you run it against. In this example, user joe runs a script, but the logging mode Adaptive Server returns changes. The tables joe.own_table and mary.other_table use a full logging mode:

select object_attr("own_table","dml_logging")

--------------
          FULL

When joe runs object_attr against mary.other_table, this table is also set to full:

select object_attr("mary.other_table", "dml_logging")

--------------
          FULL

If joe changes the dml_logging to minimal, only the logging mode of the tables he owns are affected:

set dml_logging minimal

select object_attr("own_table", "dml_logging for session")

--------------
       MINIMAL

Tables owned by other users will continue to operate in their default logging mode:

Select object_attr("mary.other_table", "dml_logging for session")

--------------
          FULL

Example 8

Identify the run-time choices of logging a new show_exec_info, and use it in the SQL batch:

Enable set showplan:
```
set showplan on
```
Enable the set command:
```
set show_exec_info on
```

Set dml_logging to minimal and check the logging with object_attr:

set dml_logging minimal
select object_attr("logged_table", "dml_logging for session")

Delete rows from the table:
```
delete logged_table
```
Adaptive Server reports the table’s logging mode at run-time with show_exec_info parameter.

Usage

The return type is a varchar, which appropriately returns the value of the property (for example, on or off) depending on the property queried for.
The logging mode as reported by extensions to showplan output might be affected at run-time, if there are set statements in the same batch, preceding the execution of the DML, which changes the logging mode of the table
The return value is the value NULL (not the string “NULL”) for an unknown property.
A special-type of string parameter, help prints to the session’s output all the currently supported properties for object_attr. This allows you to quickly identify which properties are supported by object_attr.