The IN_DB_UTIL_SP_SESSION_SETUP stored procedure should be called when initializing a session within the IN_DB_UTIL framework for the specified identifier(s), which include IN_DB_UTIL, SYNC, UPGRADE, or ALL (SYNC and UPGRADE).
The IN_DB_UTIL identifier allows for multiple sessions to be established and execute at the same time as long as the calling procedures are different. For example, it allows the CLEANUP_AUDIT_DATA and CLEANUP_WF_ARCH_DATA stored procedures to overlap when executed from different sessions. It will not allow the same stored procedure to run at the same time (overlap) from two different sessions. The SYNC, UPGRADE, ALL identifiers are not allowed to overlap with each other or with the IN_DB_UTIL identifer. This is to ensure that any UPGRADE or SYNC related steps are not impacted by maintenance related stored procedures that might be running under the IN_DB_UTIL identifier.
The IN_DB_UTIL framework uses the SQL Server APPLOCK procedures to lock the various resources (stored procedures and framework identifiers) while they are being used. The IN_DB_UTIL_SP_SESSION_SETUP procedure uses the sp_getapplock procedure, during session initialization, to lock the resources. These application resource locks are held until the procedure has completed and called the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure to cleanup the session and release the resource locks using the sp_releaseapplock procedure. If a procedure is cancelled prior to completion then these application locks will persist until the session is disconnected or until the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure is executed within the same session.
The IN_DB_UTIL_SP_SESSION_SETUP procedure accepts the IN_DB_UTIL_TY_SESSION_ATTR user-defined table type parameter which is used to pass specific, commonly used parameter values between the procedures interacting with the framework.
The IN_DB_UTIL_SP_SESSION_SETUP procedure is responsible for the following framework initialization steps:
- Acquire the application resource locks for the stored procedure and identifiers using the sp_getapplocks procedure
- Populate any common framework attributes, that are not provided, with default values.
- Validate any parameters passed into the framework.
- Get the current Perceptive Content schema version.
- Get schema version base (without type), and type (au, a, u)
- Check dependencies (existence of all stored procedures, functions, types)
- Create the table used to store the session attributes.
- Create the table used to store the callstack.
- Sets the parameters for the specified identifier(s).
- Extends the framework call stack.
| Parameter | Description | |
|---|---|---|
| SESSION_PARAMETERS | Type: | INPUT |
| Datatype: | IN_DB_UTIL_TY_SESSION_ATTR | |
| Default Value: | NULL | |
| Description | User-defined table type variable used to pass the values of all the
supported attributes for the IN_DB_UTIL framework. See the IN_DB_UTIL_TY_SESSION_ATTR definition above for more details. |
|
| V_RETURN_VAL | Type: | RETURN |
| Datatype: | INTEGER | |
| Default Value: | 0 | |
| Description | Contains the return code to be parsed upon attempting to initialize a
session within the IN_DB_UTIL framework. 0 = SUCCESS – Successful initialization. 1 = ERROR_EXIT – An error occurred during initialization. 2 = IS_EXECUTING – Another session is already executing and using the framework for the specified identifier 3 = MISSING_DEPENDENCY – There is a missing dependency which indicates that a required component is missing. |
|
Example
The following example demonstrates the typical initialization of a session within the IN_DB_UTIL framework.
For each procedure or script that will utilize the IN_DB_UTIL framework, you will want to declare a table variable that uses the IN_DB_UTIL_TY_SESSION_ATTR type as the definition:
DECLARE @V_SESSION_PARAMETERS IN_DB_UTIL_TY_SESSION_ATTR;
For each procedure or script that will utilize the IN_DB_UTIL framework, you will want to declare some or all of the following parameters to hold the individual parameters that are passed back and forth between procedures using the @V_SESSION_PARAMETERS table variable.
DECLARE @V_PROC_NAME SYSNAME, @V_IDENTIFIER SYSNAME, @V_SCOPE SYSNAME, @V_STATUS SYSNAME, @V_CALLER SYSNAME, @V_CALLER_HIST SYSNAME, @V_OBJECT SYSNAME, @V_SUBOBJECT SYSNAME, @V_FILEGROUP_NAME SYSNAME, @V_MAX_MINUTES INTEGER, @V_MAX_DOP SMALLINT, @V_FILLFACTOR SMALLINT, @V_BATCH_SIZE INTEGER, @V_SCHEMA_TARGET NVARCHAR(40), @V_SCHEMA_VERSION NVARCHAR(40), @V_SCHEMA_BASE NVARCHAR(40), @V_SCHEMA_TYPE NVARCHAR(2), @V_DATATYPE NVARCHAR(8), @V_DATABASE_EDITION INTEGER, @V_IS_SYNC_UPGRADE NVARCHAR(3), @V_SPID SMALLINT, @V_DEBUG NVARCHAR(3);
Before loading the parameters into the @V_SESSION_PARAMETERS table variable you may want to pre-populate some of the parameters such as @V_PROC_NAME and possibly @V_CALLER if the procedure is being called by another procedure already established in the call stack. You may want to also populate any other parameters with the desired values unless you want to take the default values.
Once the parameters above are populated as needed, you can use them to insert into the @V_SESSION_PARAMETERS table variable to be passed to the IN_DB_UTIL_SP_SESSION_SETUP procedure to initialize the session within the IN_DB_UTIL framework.
-- Setup Standard Session Parameters INSERT INTO @V_SESSION_PARAMETERS ( IDENTIFIER ,CALLER ,CALLER_HIST ,OBJECT ,SUBOBJECT ,FILEGROUP ,MAX_MINUTES ,MAX_DOP ,FILL_FACTOR ,BATCH_SIZE ,SCHEMA_TARGET ,SCHEMA_VERSION ,SCHEMA_BASE ,SCHEMA_TYPE ,DATATYPE ,DATABASE_EDITION ,IS_SYNC_UPGRADE ,SPID ,DEBUG ) VALUES ( @V_IDENTIFIER ,@V_PROC_NAME ,@V_CALLER ,@V_OBJECT ,@V_SUBOBJECT ,@V_FILEGROUP_NAME ,@V_MAX_MINUTES ,@V_MAX_DOP ,@V_FILLFACTOR ,@V_BATCH_SIZE ,@V_SCHEMA_TARGET ,@V_SCHEMA_VERSION ,@V_SCHEMA_BASE ,@V_SCHEMA_TYPE ,@V_DATATYPE ,@V_DATABASE_EDITION ,@V_IS_SYNC_UPGRADE ,@V_SPID ,@V_DEBUG );
Once the parameter values have been inserted into the @V_SESSION_PARAMETERS table variable it can be passed into to the IN_DB_UTIL_SP_SESSION_SETUP procedure to initialize the session within the IN_DB_UTIL framework.
Note the various return codes that should be captured and parsed accordingly during session initialization.
-- Session Initialization EXEC @V_CALL_RETURN = IN_DB_UTIL_SP_SESSION_SETUP @V_SESSION_PARAMETERS; BEGIN IF @V_CALL_RETURN = 1 GOTO ERROR_EXIT; ELSE IF @V_CALL_RETURN = 2 GOTO IS_EXECUTING; ELSE IF @V_CALL_RETURN = 3 GOTO MISSING_DEPENDENCY; END;
Once the session has been initialized, the session parameters (attributes) will be inserted into a table called IN_DB_UTIL_SESSION_ATTRIBUTES which can be referenced and selected from to finish populating any remaining parameters that are available and needed.
The IN_DB_UTIL_SESSION_ATTRIBUTES table will contain a record for each of the three possible identifiers so you must predicate on the appropriate identifier for the duration of your session.
Note the functional difference between predicating with IDENTIFIER = @V_SCOPE versus IDENTIFIER = @V_IDENTIFIER in the example query below. @V_SCOPE is the specific identifier such as IN_DB_UTIL, SYNC, or UPGRADE. @V_IDENTIFIER could be any of those three or ALL to indicate that the session identifies with both the SYNC and UPGRADE identifiers. This convention is used within the IN_DB_UPGRADE package when a procedure, such as the DOWNTIME_STEPS procedure, is initialized and intends to consume both the SYNC and UPGRADE identifiers to prevent any synchronization events from running at the same time. In some cases, @V_SCOPE and @V_IDENTIFIER will be the same.
Note that the ALL identifier does not include the IN_DB_UTIL identifier.
The following example shows how to populate some of the session parameters using the values that are stored in the IN_DB_UTIL_SESSION_ATTRIBUTES table after the session has been initialized.
SELECT @V_FILEGROUP_NAME = FILEGROUP ,@V_MAX_MINUTES = MAX_MINUTES ,@V_MAX_DOP = MAX_DOP ,@V_FILLFACTOR = FILL_FACTOR ,@V_BATCH_SIZE = BATCH_SIZE ,@V_SCHEMA_VERSION = SCHEMA_VERSION ,@V_SCHEMA_BASE = SCHEMA_BASE ,@V_SCHEMA_TYPE = SCHEMA_TYPE ,@V_DATATYPE = DATATYPE ,@V_DATABASE_EDITION = DATABASE_EDITION ,@V_IS_SYNC_UPGRADE = IS_SYNC_UPGRADE ,@V_DEBUG = DEBUG FROM IN_SESSION_ATTRIBUTES WHERE IDENTIFIER = @V_SCOPE;