Copyright © Cloud Software Group, Inc. All Rights Reserved
Copyright © Cloud Software Group, 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:
Post-installation Procedures
The installation/upgrade process installs a shared library stored procedure, SW_SEQ_TRANS, that is used by the SSOLite stored procedures for getting sequence numbers outside of the current transaction. This is installed in the following folder:
~$DB2INSTANCE/sqllib/function
Since the external stored procedure, SW_SEQ_TRANS, is fenced, you must grant the "fenced" user (reserved for executing stored procedures) permissions on the sequences table. For example:

 
GRANT REFERENCES, SELECT, INSERT, DELETE, UPDATE ON swpro.sequences TO USER DB2FENC2

 
Handling Exceptions
SSOLite stored procedures signal a SQL state and set the error text accordingly if any procedure fails. Note that:
For example,

 
SIGNAL SQLSTATE '38200' SET MESSAGE_TEXT = 'SW_GRAFTCOUNT:(SWERROR) Failed to get node ID (ID:001024)';

 
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.
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)
Case (case_num) is dead (ID:001039)
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 © Cloud Software Group, Inc. All Rights Reserved
Copyright © Cloud Software Group, Inc. All Rights Reserved