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 property of the table which is been queried for. 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.

  • help – prints a list of supported string arguments.

Examples

Example 1

To determine which properties they 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

dml_logging reports the statically-defined dml_logging level for the object, and dml_logging for session reports the run-time 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:

  1. Create the testdb database with minimal logging:

    create database testdb 
    with dml_logging = minimal
    
  2. Create a table with dml_logging set to full:

    create table logged_table(...)
    with dml_logging = full
    
  3. Reset the logging for the session to minimal:

    set dml_logging minimal
    
  4. 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:

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:

  1. Enable set showplan:

    set showplan on
    
  2. Enable the set command:

    set show_exec_info on
    
  3. 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")
    
  4. 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

Permissions

See also