Copyright © TIBCO Software Inc. All rights reserved.
Copyright © TIBCO Software Inc. All rights reserved.


Appendix B SSOLite Stored Procedures : Using SSOLite Stored Procedures

Using SSOLite Stored Procedures
The following sections discuss some general issues that you need to be aware of when designing an application to use SSOLite stored procedures:
Processing Asynchronous Message
SSOLite stored procedures work by sending a message to a database queue, which is processed by one or more background (BG) processes. This means that:
Transactional Processing
The BG process will not process any instructions issued by SSOLite stored procedures until the SSOLite transaction has been committed. You can therefore scope transactions according to the requirements of your particular application:
Handling Exceptions
SSOLite stored procedures raise a SQL level 16 error message if any procedure fails. Note that:
It is the application’s responsibility to handle any such database exceptions, and issue a rollback if appropriate.
The following table describes the different errors (and their unique IDs) that may be returned by the SSOLite stored procedures.
Invalid sequence type (seq_type) (ID:001000)
Error is a description of the error returned by the SQL Server sp_OAMethod system stored procedure.
Source is a description of the source of the error.
Source is a description of the source of the error.
Procedure version not found in database for procedure name=proc_name, Case Num=case_num (ID:001011)
Procedure version not found in database for procedure name=proc_name major_version minor_version (ID:001013)
Suspend type (suspend_type) is invalid, expected 2 (suspend) or 0 (activate) (ID:001014)
Case (case_num) is already active (ID:001036)
Case (case_num) is dead (ID:001037)
Case (case_num) is already suspended (ID:001038)
Sub-Proc casenum not found in database for Procedure proc_name, Case Number case_num, Step Name step_name, Sub-proc sub_proc_name (ID:001018)
Case (case_num) is dead (ID:001039)
SQL Distributed Management Objects (SQL-DMO)
SSOLite stored procedures access the sequences table to obtain work item and case number sequence numbers. This locks the table, preventing other iProcess processes from accessing it, for the duration of the transaction.This could cause a problem if, for example, you were batch starting a large number of cases as part of a single transaction.
To prevent this, SSOLite stored procedures use SQL-DMO to connect back to the iProcess database as a separate transaction when obtaining sequence numbers.
The use of SQL-DMO means that, when using SSOLite stored procedures, the SQL Server login used to connect to the iProcess database must:
1.
use Windows Authentication to validate the connection to the iProcess database.
2.
have the Server Administrators SQL Server Role assigned.
Processing Queues
SSOLite stored procedures write messages to the BG processes using the default background message queues, using a round-robin allocation on a per-session basis. This means that every time a new database session is started the first defined queue (BGMBOX1) is used first. As a result, BGMBOX1 can become overloaded if database sessions are not persisted.
You can override this default behavior for specific transactions by using the SW_SET_QUEUE and SW_UNSET_QUEUE control procedures.
Alternatively, you can dedicate specific message queues to handling requests from your SSOLite stored procedure calls. To do this:
1.
Create a new Mbox set named SSOLITE. (The Mbox set can use either existing message queues or new ones.)
2.
Set the MBSET_WRITE_BG process attribute for your application to assign the SSOLITE1 queue to it. All messages posted to a BG process by the SSOLite stored procedures will now use the SSOLITE Mbox set.
The following example shows a series of commands that you could use to do this.

 
# Add a new SSOLITEQ1 message queue. (Remember to create the
# sw_db_ssolite physical queue first.)
#
swadm add_queue SSOLITEQ1 Local 0003:swpro.sw_db_ssolite

# Add a new SSOLITE Mbox set.
#
swadm add_mboxset SSOLITE Local

# Add the SSOLITEQ1 message queue to the SSOLITE Mbox set (6 is the
# Mboxset ID of the SSOLITE Mboxset).
#
swadm add_queue_to_mboxset 6 SSOLITE1

# Set MBSET_WRITE_BG so that calls from the application’s SSOLITE
# stored procedures use the SSOLITE Mbox set to write messages to the
# BG processes.
#
swadm set_attribute 1 SSOLITE 0 MBSET_WRITE_BG 6
#
#Set background processes to read from the queue
#
swadm add_process 1 BG Y
swadm set_attribute 1 BG 5 MBSET_READ_BG 6

 
Because the SSOLite stored procedures cache queue information, you must shut down and restart all database connections if you change your message queue configuration in this way.
For more information about message queue configuration, see:
"Administering Message Queues and Mbox Sets" in TIBCO iProcess Engine Administrator's Guide.
Prioritizing Messages
You can now set priorities ranging from 1 to 999 (where 1 is the highest priority) for internal message queues when passing messages between iProcess processes such as from the background and the WISes, or from SSOLite to the BG processes. The default message queue priority is 50.
Use the SW_SET_PRIORITY control procedure to set the internal message queue priorities and the SW_UNSET_PRIORITY control procedure to restore the default message queue priorities.
The messages with higher internal message queue priorities are processed earlier than those with lower priorities, and the message with the highest priority will automatically be the next message processed, even if there is a backlog in the queue.
If the internal message queue priorities are not set, the messages will be processed in the order of SW_CP_VALUE or SW_IP_VALUE when using iProcess Workspace (Windows) to process work items.
If the value of the SW_CP_VALUE field is set, the message will be processed in the order of SW_CP_VALUE regardless of the message queue priority that is set by using the SW_SET_PRIORITY control procedure.
If the SW_CP_VALUE field is not set, the message will be processed in the order of the message queue priority that is set using the SW_SET_PRIORITY control procedure.
If both the SW_CP_VALUE field and the SW_SET_PRIORITY control procedure are not set for the message priority, the message priority will be set to the default value of the SW_CP_VALUE field, 50.
See TIBCO iProcess Modeler Advanced Design for more information about the SW_CP_VALUE field.
 
 

Copyright © TIBCO Software Inc. All rights reserved.
Copyright © TIBCO Software Inc. All rights reserved.