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

XML schema collections to define message types.

Message types.

Contracts.

Queues.

Services.

Broker priorities. (A feature added in SQL 2008.)

Users for queue activation.

Activation procedures.

Routes.

Remote service bindings.

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.

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.)

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:

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:

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.

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:

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.

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.