Service Broker in AbaPerls

This page describes how you work with Service Broker in AbaPerls.

Contents:
   Introduction
   Contents of a Service-Broker File
   DBBUILD and DBUPDGEN
   Ongoing Conversations and Preludes
   Generic Message Types

Introduction

When you work with Service Broker, there is a number of object that you may need to define for your Service Broker application:

Typically, you need a couple of each type, so you can easily end up with some 20-30 objects. Most of these objects are very small in terms of lines of code, never as many as 10 lines. Therefore, the traditional model in AbaPerls with one object per file does not fit well. Instead AbaPerls defines a file type, .sb, in which you can define most of your Service Broker-related objects. In the AbaPerls SQL Directory Structure you put .sb files in the SERVICEBROKER directory.

In an .sb file you define of a group of Service Broker objects that are used together with each other. That is, services that are used in the same related dialogs, their queues and so on. If you use Service Broker for several different purposes, you should have one .sb file for each purpose. In a .sb file, AbaPerls tracks the following object types as first-class AbaPerls objects: message types, contracts, queues, services and broker priorities. You must specify subsystem when you load Service-Broker files.

You can only use .sb files if the ABAPERLS subsystem is installed in the database.

Contents of a Service-Broker File

To make the discussion less abstract, let's look at sbexample.sb. (From a Service Broker perspective the file is somewhat funny; there is only one object per type since I wanted to keep the example short.)

IF user_id('DemoSBUser') IS NULL
   CREATE USER DemoSBUser WITHOUT LOGIN

CREATE MESSAGE TYPE DemoMsgType VALIDATION =
$IFDEF &SBTEST
   VALID_XML WITH SCHEMA COLLECTION my_xml_schema
$ELSE
   WELL_FORMED_XML
$ENDIF

CREATE CONTRACT DemoContract (DemoMsgType SENT BY ANY)

CREATE QUEUE DemoQueue WITH STATUS=ON,
ACTIVATION (
   STATUS = ON,
   PROCEDURE_NAME = an_added_sp,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS 'DemoSBUser'
)

CREATE SERVICE DemoService ON QUEUE DemoQueue (DemoContract)

CREATE BROKER PRIORITY DemoPriority FOR CONVERSATION
SET (LOCAL_SERVICE_NAME = DemoService,
     PRIORITY_LEVEL = 1)

GRANT SEND ON SERVICE::DemoService TO DemoSBUser
GRANT RECEIVE ON DemoQueue TO DemoSBUser

General Overview

When you load a service-broker file the first time, AbaPerls stores information about the first-class objects – message types, contracts, queues, services and broker priorities – to abasysobjects. Next time you run the file, AbaPerls will use the information in abasysobjects to generate drop for all objects loaded from the file. For sbexample.sb above, this means that AbaPerls generates these DROP commands:

DROP BROKER PRIORITY DemoPriority
go
DROP SERVICE DemoService
go
DROP QUEUE DemoQueue
go
DROP CONTRACT DemoContract
go
DROP MESSAGE TYPE DemoMsgType
go

Since the DROP command is based on what is stored in abasysobjects, this means that if you have renamed or dropped an object from the file, it will also go away from the database without further need for action from your side. As for what this means for ongoing conversations, see the section below.

Note that if you have created any object manually from a query window, you will get an error that the object already exists. In difference to stored procedures and many other object types, AbaPerls never changes CREATE to ALTER for Service-Broker objects. (For one thing, there is no ALTER CONTRACT command.)

Below follows remarks specific for certain object types.

Users

The first object created in sbexample.sb is a user, used for the queue activation later. Users are not AbaPerls objects, which means that you to need to add your own code to avoid an error message when you run the file a second time.

Often you define users you need in an .sql file in the MESSAGE directory, and you can do this for users that are used with queue activation as well. But if the user is only used for queues defined in your .sb file, it appears to be a good idea to have all in the same place.

Message Types and XML Schema Collections

As you see the definition of the message type uses conditional compilation provided by Preppis. When you develop a Service-Broker application, it is a good idea to define your message types to be validated against an XML schema, so you can trap errors due to that sender and receiver has different ideas about how the XML document should look like. However, validation is fairly expensive, and you may not want that overhead once you are in production. In this case, you can use $IFDEF to have both alternatives available without having to change your .sb file.

As you can see, the definition of my_xml_schema, is not present in sbexample.sb. With AbaPerls, you define XML schema collections in .xmlsc files in the TYPE directory. If you change and reload an .xmlsc file, and there are one or more message types tied to the schema collection, AbaPerls moves the existing definition of the schema collection to a temporary schema, loads the new definition and then alters the message types to refer to the new definition. Finally, it drops the old definition and the temporary schema.

AbaPerls checks that any message type you specify with the SEND statement actually exists as part of object checking.

Queues

The definition of a queue may include an activation procedure, which typically receives messages from the queue. This leads to a kind of catch-22 situation. You cannot create the queue, unless the activation procedure already exists; SQL Server does not permit this. But you cannot create a procedure that refers to a non-existing queue. SQL Server permits this because of deferred name resolution, but AbaPerls extends its object checking to check that all queues you refer to exist.

The way to resolve this situation is to first create a dummy version of the procedure, load the service-broker file, and then load the real version of the procedure. You could also first create the queue without activation, load the procedure, and then change the .sb file to include queue activation. Thankfully, what was said here, only applies when you load files through ABASQL. With DBBUILD and the updates scripts generated by DBUPDGEN, AbaPerls takes care of this, as detailed below.

AbaPerls includes a DDL trigger which disallows changes to objects outside AbaPerls. However, the DDL trigger does permit you enable/disable a queue or its activation, as it may be needed for mainteneance. Likewise you may need to change the user in the EXECUTE AS clause. Note that the ALTER QUEUE command should include what you actually want to change, for instance:

ALTER QUEUE MyQueue WITH STATUS = OFF

If you include the clause PROCEDURE_NAME, the DDL trigger will disallow the command, even if you supply the name of the existing activation procedure. As a DDL trigger is an AFTER trigger, it has no way to tell whether you actually changed the procedure name of not.

Services

AbaPerls extends object checking to services so you cannot refer to a non-existing service in the FROM clause of BEGIN DIALOG. The TO services is not checked, as this service could reside in a different database on a different server.

Permissions

As you see, sbexample.sb includes GRANT statements to give DemoSBUser permissions on the queue and the service. You can also cater for permissions on Service-Broker objects through grant.template, but as with the user, it may be easier to include the GRANT commands directly in the file. Yet an alternative is to use the $DBPERM directive in procedures that include SEND or RECEIVE commands and not tie the permissions to a user.

Routes and Remote Service Bindings

Routes and remote service bindings are not first-class objects in AbaPerls since they are typically installation-specific. You can still include the commands CREATE ROUTE and CREATE REMOTE SERVICE BINDING in your .sb file, but you will need to add code to drop the objects, for instance:

IF EXISTS (SELECT * FROM sys.routes WHERE name = 'myroute')
     DROP ROUTE myroute

CREATE ROUTE myroute ...

Note: some of the activation information for CREATE QUEUE could be site-specific, particularly MAX_QUEUE_READERS. Currently, AbaPerls has no support to retain the current configuration for the queue.

DBBUILD and DBUPDGEN

When you run DBBUILD, DBBUILD runs .sb files twice at different places in the build order. The first round is directly after loading the files in the TYPE directory.  In this run, AbaPerls changes the CREATE QUEUE command to read:

CREATE QUEUE QueueName WITH STATUS = OFF

That is, the information about activation and the activation procedure is stripped and the queue is always disabled. When DBBUILD has completed loading of all executable code, DBBUILD runs the .sb files a second time, now without changing any of the commands.

DBUPDGEN handles .sb files in a similar fashion DBBUILD. If you have changed a .sb file, it will appear in two places in the update script. First in the section SERVICEBROKER, which comes after the TYPE section before any table. As with DBBUILD, when the files are run in this section, CREATE QUEUE commands are mutilated to only include the queue name and STATUS=OFF.

The .sb files then reappears in the section SERVICEBROKER REPRISE which comes right before the POSTSQL section. In this section the files are run without alteration.

Ongoing Conversations and Preludes

AbaPerls checks for actvice conversations related to the services defined in the file. If there are, this is handled depending on this is a production database or not. If the database is labelled DEV or TEST, AbaPerls issues the command END CONVERSATION WITH CLEANUP for all active conversations where the originating service is tied to the .sb file you load to avoid that you get a left with orphand conversations. AbaPerls prints an informational message when this happens: There are one or more active conversations that will be forcibly closed.

In the database is labelled as a production database, AbaPerls considers active conversations to be an error and issues the error message In this PRODUCTION database, there are one or more active conversations related to this file. You need clear up these conversations before the file can be loaded. And as the message says, you need to decide on a course of action yourself.

The thinking about this behaviour is that in production, killing conversations could potentially lead to data loss, if a process has been lagging behind. But in test and development environment, it is more likely that this message would bug you.

If you want to override this behaviour, you can add your own checks in a prelude section. Here is how you would do if you never want active conversations to be killed, even in test or development:

$PRELUDE
IF  EXISTS (SELECT *
            FROM   sys.conversation_endpoints ce
            JOIN   sys.services s ON ce.service_id = s.service_id
            WHERE  s.name = 'DemoService'
              AND  ce.state = 'CO')
BEGIN
   RAISERROR('There is an active conversation on DemoService which must be terminated before file can be reloaded', 16, 1)
END
$ENDPRELUDE

If you always want to kill open conversations, even in a production database, you can put this code in the prelude:

$PRELUDE
DECLARE @sql nvarchar(MAX)
SELECT @sql = (SELECT 'BEGIN TRY
                       END CONVERSATION ''' + convert(char(36), ce.conversation_handle) +
                       ''' WITH CLEANUP END TRY BEGIN CATCH END CATCH;'
               FROM   sys.conversation_endpoints ce
               JOIN   sys.services s ON ce.service_id = s.service_id
               WHERE  s.name = 'ExtNot_accounttransaction_service'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
EXEC(@sql)
$ENDPRELUDE

If you would happen to leave out  $PRELUDE, AbaPerls would run this code with the rest of the file, that is after on its own checks and actions, why the prelude code would have no effect. The code between $PRELUDE and $ENDPRELUDE is executed before AbaPerls attempts to drop any objects. If an error occurs in this section, AbaPerls abandons any further work with the file, including the DROP commands.

Generic Message Types

You may have a generic message type which is used by many unrelated contracts in more than one .sb file. It is unpractial to define such a type in an .sb file of its own, since DROP MESSAGE TYPE will not work well. For this reason, AbaPerls also permits message types to be defined in files with the extension .mty. .mty files should be in the SERVICEBROKER directory. An .mty file should hold the definition of a single message type. .mty files follow the traditional AbaPerls pattern. That is, the name of the message type must match the file name, and if the message type already exists, AbaPerls changes CREATE MESSAGE TYPE to ALTER MESSAGE TYPE.

DBBUILD and update scripts generated by DBUPDGEN have no special handling of .mty files like they have for .sb file. That is, .mty files are only loaded once, just before the first load of the .sb files.