This table contains the session parameters/attributes for each of the supported identifiers (IN_DB_UTIL, SYNC, UPGRADE) and is maintained by the various procedures and functions of the IN_DB_UTIL framework. You should never need to manually update the records in this table.
The IN_DB_UTIL_SP_CREATE_SESSION_ATTR_TABLE procedure is used to create this table.
You can use the following framework procedures to interface with this table:
- IN_DB_UTIL_SP_SESSION_SETUP
- IN_DB_UTIL_SP_SESSION_UPDATE_ATTRIBUTES
- IN_DB_UTIL_SP_SESSIONS_DISPLAY
- IN_DB_UTIL_SP_CLEAR_IDENTIFIER
This table contains the following structure, which aligns with the columns defined in the IN_DB_UTIL_TY_SESSION_ATTR user-defined table type plus the START_TIME and MAX_TIME columns.
CREATE TABLE IN_DB_UTIL_SESSION_ATTRIBUTES
(
IDENTIFIER SYSNAME DEFAULT 'IN_DB_UTIL',
STATUS SYSNAME DEFAULT 'ISNULL',
CALLER SYSNAME NULL,
CALLER_HIST SYSNAME NULL,
START_TIME DATETIME NULL,
MAX_TIME DATETIME NULL,
OBJECT SYSNAME NULL,
SUBOBJECT SYSNAME NULL,
FILEGROUP SYSNAME NULL,
MAX_MINUTES INTEGER NULL,
MAX_DOP SMALLINT NULL,
FILL_FACTOR SMALLINT NULL,
BATCH_SIZE INTEGER NULL,
SCHEMA_TARGET NVARCHAR(40) NULL,
SCHEMA_VERSION NVARCHAR(40) NULL,
SCHEMA_BASE NVARCHAR(40) NULL,
SCHEMA_TYPE NVARCHAR(2) NULL,
DATATYPE NVARCHAR(8) NULL,
DATABASE_EDITION INTEGER NULL,
IS_SYNC_UPGRADE NVARCHAR(3) NULL,
SPID SMALLINT DEFAULT '',
DEBUG NVARCHAR(3) NULL,
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_IDENTIFIER
CHECK (IDENTIFIER IN ('IN_DB_UTIL', 'SYNC', 'UPGRADE')),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_STATUS
CHECK (STATUS IN ('IDLE', 'ISNULL', 'EXECUTING')),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_MAX_MINUTES
CHECK (MAX_MINUTES >= 0),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_MAX_DOP
CHECK (MAX_DOP >= 0),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_FILL_FACTOR
CHECK (FILL_FACTOR >= 0),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_BATCH_SIZE
CHECK (BATCH_SIZE >= 0),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_SCHEMA_TYPE
CHECK (SCHEMA_TYPE IN ('', 'u', 'a', 'au')),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_DATATYPE
CHECK (DATATYPE IN ('VARCHAR', 'NVARCHAR')),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_IS_SYNC_UPGRADE
CHECK (IS_SYNC_UPGRADE IN ('YES', 'NO')),
CONSTRAINT IN_DB_UTIL_SESSION_ATTRIBUTES_CHECK_DEBUG
CHECK (DEBUG IN ('Y', 'YES', 'N', 'NO')),
CONSTRAINT PK_IN_DB_UTIL_SESSION_ATTRIBUTES
PRIMARY KEY (IDENTIFIER, SPID)
);
| Column Name | Description |
|---|---|
| IDENTIFIER | The name of the identifier to which the session is tied. Used for defining the
scope of the session. Possible options are IN_DB_UTIL, SYNC, and UPGRADE. The default is IN_DB_UTIL. |
| STATUS |
The status of the session associated with the specified identifier. Possible options are IDLE, ISNULL, and EXECUTING. The default is ISNULL. |
| CALLER | The name of the procedure that is executing under the specified identifier. |
| CALLER_HIST | The name of the procedure that called the procedure (CALLER) that is executing under the specified identifier. |
| START_TIME | The timestamp for when the session was initialized. The START_TIME is also updated to the CURRENT_TIMESTAMP anytime the IN_DB_UTIL_SP_SESSION_UPDATE_ATTRIBUTES procedure is executed for the identifier. |
| MAX_TIME | For procedures that include the MAX_MINUTES parameter; if a value greater than zero was supplied for the MAX_MINUTES parameter during session initialization then this will contain the calculated MAX_TIME timestamp. This defines an end point for when new operations can be picked up for execution. If MAX_TIME has elapsed then no new operations will be started and the procedure will terminate cleanly. |
| OBJECT | The primary or parent object name affected by the current actions executing under the specified identifier. This is usually a table name. |
| SUBOBJECT | The name of the object affected by the current actions executing under the specified identifier. This could be an index, or column, or constraint. |
| FILEGROUP | If applicable, the name of the filegroup that contains the OBJECT or SUBOBJECT affected by the current actions executing under the specified identifier. |
| MAX_MINUTES | The number of minutes used to define an end time that a procedure is allowed to
start new operations. If MAX_MINUTES has elapsed and the current time is greater than
the calculated end time, then no new operations will be started and the procedure will
gracefully end without completing any remaining tasks that might have otherwise been
executed had time not expired. The default is 0 which is equal to unlimited. |
| MAX_DOP | The degree of parallelism for the session. Can be used for operations that
leverage the DOP parameter such as index creation or for parallel execution of
qualifying SQL statements. The default is 0. |
| FILL_FACTOR | The percentage to fill data and index pages. Used during the creation of tables
and indexes. The default is 95 percent. |
| BATCH_SIZE | The batch size (number of rows) to populate work queues (cursors). Currently only
used by IN_DB_UTIL_SP_CLEANUP_AUDIT_DATA and IN_DB_UTIL_SP_CLEANUP_WF_ARCH_DATA during
IN_DB_UPGRADE SYNC operations. Could also be used for TOP N constructs for SELECT,
INSERT, UPDATE, DELETE, and MERGE operations to iterate over the data in smaller
batches to keep transaction sizes smaller. The default is 1,000 rows. |
| SCHEMA_TARGET | The target schema version. This parameter is primarily used by the IN_DB_UPGRADE package during Perceptive Content database upgrades. |
| SCHEMA_VERSION | The current or starting schema version. This parameter contains the full schema version number including the type designators such as ‘u’ for Unicode or ‘a’ for Advanced. |
| SCHEMA_BASE | The base version of the schema which only includes the version number without the schema type designators such as ‘u’ for Unicode or ‘a’ for advanced. This is used for a simplified schema check during conditional execution based on the schema version. |
| SCHEMA_TYPE | The schema type designation such as ‘u’ for Unicode or ‘a’ for advanced filegroup configuration. |
| DATATYPE | The character data type that should be used for the schema. This is based on the schema encoding type. For Unicode (schema_type with ‘u’) then the datatype will be NVARCHAR, otherwise it will be VARCHAR. This parameter can be used to dynamically specify the datatype for character-based columns when creating tables. |
| DATABASE_EDITION | The edition of the SQL Server instance. This parameter can be used when determining if a script can use Enterprise Edition features such as online index rebuilds or compression. Based on the results of SERVERPROPERTY('EngineEdition'). |
| IS_SYNC_UPGRADE | This parameter is used by the IN_DB_UPGRADE package to determine if the upgrade
path uses the synchronization framework (SYNC) for the upgrade. This is for upgrades
with a starting schema version less than 7.5. Possible options are YES or NO. The default is NO. |
| SPID | This is the SQL Server session identifier for the current user process that is using the framework for the specified identifier. This is based on @@SPID for the session that instantiated the framework using the IN_DB_UTIL_SP_SESSION_SETUP procedure. |
| DEBUG | Specifies whether to display additional logging for debugging purposes. Includes
state changes, call stack, SQL statements and other details. Possible options are YES or NO. The default is NO. |