sp_msgadmin

Description

Configures and administers messaging-related information.

Syntax

sp_msgadmin 'config', ['jvmlogging', logging_level 
	| 'jvmpropertyfile', filepath
	| 'jvmlogfile', filepath
	| 'jvmmaxthreads', thread_number
	| 'jvmminthreads', thread_number
	| 'jvmthreadtimeout', thread_timeout
	| 'jvm' , jvm_parameter]

sp_msgadmin 'default', 'login', provider_name, provider_login,
		provider_password

sp_msgadmin 'help'[, 'list' | 'register' | 'default' | 'remove']

sp_msgadmin 'list', [| 'login'[, provider_name, [login_name] 
	| 'provider' [, provider_name]
	| 'subscription' [, subscription_name]]

sp_msgadmin 'register', 
	['provider', provider_name, provider_class,
		messaging_provider_URL 
	| 'login', provider_name, local_login, provider_login,
		provider_password [, role_name] 
	| 'subscription', subscription_name, endpoint[, selector 
		[, delivery_option [, durable_name, client_id]]]]
	| ['publisher', publisher_name, endpoint, topic
		[, broker_queue[, request_queue[, options]]]

sp_msgadmin 'remove', 
	['provider', provider_name 
	| 'login', provider_name, local_login [, role] 
	| 'subscription', subscription_name

Parameters

client_id

is the identification used by the messaging provider to identify the subscription as durable. client_id is a character string value. If you specify either client_id or durable_name, you must also specify the other, and the subscription is a durable subscription. Otherwise, it is a nondurable subscription.

The client_id and durable_name combination identifies durable subscriptions with the message provider, and must be unique. No two subscriptions can have the same client_id and durable_name.

client_id uniqueness extends across the messaging provider. JMS allows a particular client_id to be connected only once at any given time. For instance, if one application already has a durable subscription using a specified client_id, the client_id specified by another application cannot be the same if the applications are to be connected at the same time.

A durable subscription exists even when the client is not connected. The messaging provider saves messages that arrive even while the client is not connected.

A nondurable subscription exists only while the client is connected. The messaging provider discards messages that arrive while the client is not connected.

'config': allows you to specify various configurations for the Java Virtual Machine (JVM).

'default': specifies a default. In the case of sp_msgadmin 'list', lists the syntax to specify the default login for a specified message provider.

You cannot use sp_msgadmin 'default', 'login' if endpoint is an MQ queue manager.

delivery_option

species whether a SQL session can consume messages that it publishes. Valid values are:

local – the SQL session can consume messages that it publishes.
nonlocal – the SQL session cannot consume messages that it publishes.
null – assumes the value is local.

durable_name: is a character string value. See the description of client_id.

endpoint: is the topic to which the subscription is addressed. See the description of endpoint in msgsend.

help: provides syntax information about this stored procedure or about particular parameters.

‘jvmlogging’, logging_level

allows you to configure your messaging service to display only the trace information in your code that is higher than your configured level.

logging_level specifies the level using the Apache log4j logging sytem. The values for logging_level are:

‘all’ – returns all the trace information in the code
‘debug’ – returns JVM debug information
‘fatal’ – returns JVM fatal information
‘off’ – turns off logging
‘info’ – returns information-level log information
‘error’ – returns only error log information

The default value for logging_level is 'info'.

See the Apache log4j Web site for more information on the log4j logging system.

‘jvmpropertyfile’, filepath

specifies the property file that JVM uses for your configuration.

filepath defines the location of your filepath. This can be any valid path for your property file, including the use of environment variables. The default value is $SYBASE/$SYBASE_ASE/lib/rtms.properties.

‘jvmlogfile’, filepath

defines the path to the log file that JVM uses for your configuration.

The log information for JVM displays on the console and is written to a single log file. Every time your log file reaches its maximum size of 5MB, JVM automatically creates a new log file and appends a new number at the end of the file (such as XXX.2, XXX.3, and so on).

The default value for filepath is $SYBASE/$SYBASE_ASE/rtms.log.

‘jvmmaxthreads’, thread_number

specifies the maximum number of Java threads you want to run at the same time in the JVM server’s thread pool.

thread_number is the number of threads. When using jvmmaxthreads, the value of of thread_number must be higher than the value of jvmminthreads. The default value is 10.

‘jvmminthreads’, thread_number: specifies the maximum number of Java threads you want to run at the same time in the JVM server’s thread pool. The value of thread_number can be 0 or higher, but must be lower than the value of jvmmaxthreads. The default value is 0.

‘jvmthreadtimeout’, thread_timeout

allows a thread to be automatically destroyed after a specified period of inactivity.

thread_timeout is the number of seconds before a thread is destroyed. The default value is 600 (10 minutes).

‘jvm’, jvm_parameter

defines the parameters you pass to Java when you start JVM.

jvm_parameter is the name of any valid Java parameter string. The default value is “-Xmx500m”, which is a generic Java flag that specifies that Java start with 500Mb of allocated RAM. For mroe information on the Java -Xmx flag, see the Java Web site.

list: lists syntax information about message providers, logins, or subscriptions.

'login'

lists information about a particular messaging provider login mapping or about all messaging provider logins. When used with:

register – registers a login mapping.

You cannot use sp_msgadmin 'register', 'login' if endpoint is an MQ queue manager.
default – specifies a default login.
remove – removes the mapping previously created between an Adaptive Server login and a service provider login, defined by this call:
```
sp_msgadmin 'register', 'login', local_login,... 
```

login_name: is a login name.

local_login: is an Adaptive Server login that maps to the local login.

messaging_provider_URL: is the URL of the messaging provider you are registering.

provider

specifies the message provider. When used with:

register – registers a message provider.
list – lists information about a particular messaging provider or about all message providers.
remove – removes a messaging provider previously defined by this call:
```
sp_msgadmin 'register', 'provider', provider_name
```

provider_class

is the class of the messaging provider you are adding. Valid values are:

EAS_JMS
TIBCO_JMS
IBM_MQ

provider_name: is an alias referring to the messaging provider you are adding, which can be as many as 30 characters in length. In the case of sp_msgadmin 'register', 'provider', provider_name is an alias for messaging_provider. In the case of sp_msgadmin 'register', 'login', provider_name is the name of a previously registered provider.

provider_login: is the login name of the messaging provider that local_login maps to when connecting to the message provider. It is also the login the provider uses as the default login when sending or receiving messages from the messaging provider specified by provider_name when using sp_msgadmin 'default'.

provider_password: is the messaging provider password of the provider_login.

register: provides stored procedure syntax to register a message provider, login, or subscription.

You cannot use sp_msgadmin 'register', 'login' or sp_msgadmin 'register', 'subscription' if endpoint is an MQ queue manager.

remove: lists the stored procedure syntax to remove a message provider, login, or subscription.

role_name: is a SQL role name. If you specify a role_name, the local_login is ignored, and the provider_login and provider_password apply to the role_name.

selector: is a message filter that allows a client to select messages of interest. See the description of filters in msgrecv.

subscription

lists information about a particular subscription or about all subscriptions. Specifies the message provider. When used with:

register – registers a subscription.

You cannot use sp_msgadmin 'register' 'subscription' if the endpoint is an MQ queue manager.
list – lists information about a particular subscription or about all subscriptions.

remove – removes a subscription previously created by:

sp_msgadmin 'register' 'subscription', subscription_name, ...

subscription_name: is a subscription name.

Examples

Example 1

JMS – logs the level of JVM:

sp_msgadmin 'config', 'jvmlogging', 'info'

Example 2

JMS – specifies /usr/1.prop as the properties file:

sp_msgadmin 'config', 'jvmpropertyfile', '/usr/1.prop'

Example 3

JMS – defines the log file path as $SYBASE/$SYBASE_ASE/rtms.log:

sp_msgadmin 'config', 'jvmlogfile', '$SYBASE/$SYBASE_ASE/rtms.log'

Example 4

JMS – specifies the maximum number of threads in the JVM server’s thread pool as 100:

sp_msgadmin 'config', 'jvmmaxthreads', 100

Example 5

JMS – specifies the minimum number of threads in the JVM server’s thread pool as 0:

sp_msgadmin 'config', 'jvmminthreads', 0

Example 6

JMS – specifies 10 minutes as the amount of time that a thread is idle before it is automatically destroyed:

sp_msgadmin 'config', 'jvmthreadtimeout', 600

Example 7

JMS – starts the JVM with 500Mb of RAM by using the -Xmx500m flag:

sp_msgadmin 'config', 'jvm', '-Xmx500m'

Example 8

MQ – registers the “mq_provider_1” messaging provider, which has a class of IBM_MQ and a URL of chanl1/TCP/host1(5678):

sp_msgadmin 'register', 'provider', 'mq_provider_1', 'ibm_mq', 
    'chanl1/TCP/host1(5678)'

Example 9

JMS – registers the “eas_1” message provider, which has a class of EAS_JMS and a url of iiop://localhost:7222:

sp_msgadmin 'register', 'provider', 
    'eas_1','eas_jms','iiop://localhost:7222'

Example 10

JMS – specifies the default login that applies to all unmapped Adaptive Server logins, when using a specified messaging provider for either sending or receiving:

sp_msgadmin 'default', 'login', 'my_eas','eas_user','eas_password'

You must first register the provider_name by calling sp_msgadmin 'register', 'provider'.

Example 11

JMS – specifies the default login:

sp_msgadmin 'default', 'login', 'one_jms_provider', 'loginsa', 
    'abcdef123456'

Example 12

JMS – describes the syntax for sp_msgadmin 'list':

sp_msgadmin 'help', 'list'

Example 13

JMS – checks the default login:

sp_msgadmin 'list', 'login', 'my_jms_provider'

Example 14

JMS – lists the details for the user with a login of “loginsa”:

sp_msgadmin 'list', 'login', 'my_jms_provider', 'loginsa'

Example 15

JMS – lists the details for the “my_jms_provider” message provider:

sp_msgadmin 'list', 'provider', 'my_jms_provider'

Example 16

JMS – lists the details for subscription “subscription_1”:

sp_msgadmin 'list', 'subscription', 'subscription_1'

Example 17

JMS – registers the login “ase_login1” using messaging provider login “jms_user1” and messaging provider name “my_jms_provider”:

sp_msgadmin 'register', 'login', 'my_jms_provider', 'ase_login1', 
    'jms_user1', 'jms_user1_password'

Example 18

JMS – registers a login using the messaging provider login “jms_user1” and a specified password used for all unmapped Adaptive Server logins:

sp_msgadmin 'register', 'login', 'my_jms_provider', null, 'jms_user1',
     'jms_user1_password'

Example 19

JMS – registers a login with the messaging provider login “jms_user1” and a specified password used for all Adaptive Server logins that have sa_role permissions:

sp_msgadmin 'register', 'login', 'my_jms_provider', null, 'jms_user1', 
    'jms_user1_password', 'sa_role'

Example 20

JMS – registers the “my_jms_provider” messaging provider, which has a class of TIBCO_JMS and an IP of 10.23.233.32:4823 as its address:

sp_msgadmin 'register', 'provider', 'my_jms_provider', 'TIBCO_JMS',
     'tcp://10.23.233.32:4823'

Example 21

JMS – registers a durable subscription named “durable_sub1”, then sp_msgadmin 'list' displays information about the new subscription.

sp_msgadmin 'register', 'subscription', 'durable_sub1', 
    'my_jms_provider?topic=topic.sample', null, null, 'durable1', 'client1'
sp_msgadmin 'list', 'subscription', 'durable_sub1'

Example 22

JMS – registers “subscription_1”, a nondurable subscription.

sp_msgadmin 'register', 'subscription', 'subscription_1',
     'my_jms_provider?topic=topic.sample'

You must first use sp_msgadmin register, provider to register “my_jms_provider”.

Example 23

JMS – removes the default login:

sp_msgadmin 'remove', 'login', 'my_jms_provider'

Example 24

JMS – removes the messaging provider “my_jms_provider”:

sp_msgadmin 'remove', 'provider', 'my_jms_provider'

Example 25

JMS – removes the Adaptive Server login “ase_login1” associated with the messaging provider “my_jms_provider”:

sp_msgadmin 'remove', 'login', 'my_jms_provider', 'ase_login1'

Example 26

JMS – removes the default login, indicated by a null login parameter:

sp_msgadmin 'remove', 'login', 'my_jms_provider', null

Example 27

JMS – removes all logins for role sa_role on “my_jms_provider”:

sp_msgadmin 'remove', 'login', 'my_jms_provider', null, 'sa_role'

Example 28

JMS – removes “subscription_1”:

sp_msgadmin 'remove', 'subscription', 'subscription_1'

Usage

You cannot use sp_msgadmin inside a transaction.

sp_msgadmin 'register'

When a login name is used to connect to the message provider, login names are resolved in the following order:
1. Explict login names and passwords, specified in the endpoint, if provided.
2. Explicit login mapping for the current Adaptive Server login.
3. The default login name and password for the message provider, and the role corresponding to the Adaptive Server login.
4. The default login name and password for the message provider, with no specific role association.
5. Null login name and password if none of the above apply.
You can modify the login mapping between the Adaptive Server login and the messaging provider login only by removing and reregistering it with a different set of mappings.
MQ only – if you enter an endpoint using a registered provider, using msgsubscribe, msgunsubscribe, msgpublish, and msgconsume return errors.
See sp_msgadmin for usage common to the variants of sp_msgadmin.

sp_msgadmin 'remove'

Removing a messaging provider does not affect messages that are in transit (that is, messages that are in the process of being sent or received) to this message provider.
sp_msgadmin 'remove' does not affect any current connections to the message provider. This means that if a message provider, login, or default is removed while there is a current connection to the specified message provider, the connection is not affected. However, Sybase does not recommend this practice.
You must specify local_login as null if you specify role_name.

sp_msgadmin 'config'

sp_msgadmin 'config' is only available for JMS.
All the values you specify when you call sp_msgadmin 'config' are stored in the sysattributes table. To retrieve the values, execute:
```
1> select * from sysattributes where class = 21
```
See Adaptive Server Enterprise: Tables for more information on sysattributes.
All the parameters available for sp_msgadmin 'config' are dynamically configured except for 'jvm'.

Permissions

You must have messaging_role to run the msgsend and msgrecv functions.

You must have messaging_role and sso_role permissions to issue:

sp_msgadmin 'default'
sp_msgadmim 'register'
sp_msgadmin 'remove'

Any user can issue:

sp_msgadmim 'help'
sp_msgadmin 'list'