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]]]]

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

sp_msgadmin 'show',
	showtype, provider[, options_clause]

Parameters

sp_msgadmin 'config'

allows you to specify various configurations for either the Java Virtual Machine (JVM), or the key repository file path for Adaptive Server for using MQ SSL. The configured values take effect after you re-enable RTDS. The options for sp_msgadmin 'config' are:

'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’ – is the default value for logging_level, and returns information-level log information
- ‘error’ – returns only error log information
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.
'ibmmq_keystore', keystore_name – configures the key repository file path for Adaptive Server to be able to send and receive messages to or from WebSphere MQ through SSL.

keystore_name is the location of the key database file in which keys and certificates are stored.

sp_msgadmin 'default'

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

'login' – when used with 'default' specifies a default login.
provider_name – is the messaging provider you are registering, which can be as many as 30 characters in length.
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.

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

sp_msgadmin 'help'[, 'list' | 'register' | 'default' | 'remove']: provides syntax information about sp_msgadmin or about its particular parameters.

sp_msgadmin 'list'

lists syntax information about message providers, logins, or subscriptions using the following options:

'login'[, provider_name,[login_name] – lists information about a particular messaging provider login mapping or about all messaging provider logins. provider_name is the provider name, and login_name is the login name.
'provider'[, provider_name] – specifies the message provider, and lists information about a particular messaging provider or about all message providers. provider_name is a provider name.
'subscription'[, subscription_name] – lists information about a particular subscription or about all subscriptions. subscription_name is a subscription name.

sp_msgadmin 'register'

provides stored procedure syntax to register a messaging provider, login, or subscription. The options are:

sp_msgadmin 'register' provider – registers the messaging provider, where:
- provider_name – is the name of the messaging provider
- provider_class – is the class of the messaging provider you are adding. Valid values are:
  - EAS_JMS
  - TIBCO_JMS
  - IBM_MQ
- messaging_provider_URL – is the URL of the messaging provider you are registering.
sp_msgadmin 'register' 'login' – registers a login mapping, where:
- provider_name – is the name of a previously registered provider, and be as many as 30 characters in length.
- local_login – is an Adaptive Server login that maps to the local login.
- provider_login – is the login name of the messaging provider that local_login maps to when connecting to the message provider.
- provider_password – is the messaging provider password of the provider_login.
- 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.
You cannot use sp_msgadmin 'register', 'login' if endpoint is an MQ queue manager.
sp_admin 'register' 'subscription' – registers a subscription, where:
- subscription_name – is a subscription name.
- endpoint – is the topic to which the subscription is addressed. See the description of endpoint in msgsend.
- selector – is a message filter that allows a client to select messages of interest. See the description of filters in msgrecv.
- 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.
- 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.
You cannot use sp_msgadmin 'register', 'subscription' if endpoint is an MQ queue manager.

sp_msgadmin 'remove'

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

'provider', provider_name – removes a messaging provider previously defined with:
```
sp_msgadmin 'register', 'provider', provider_name
```
provider_name is an alias referring to the messaging provider you are removing.
'login', provider_name, local_login [, role] – 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,... 
```
Where:
- local_login – is an Adaptive Server login that maps to the local login.
- role – is the role.
'subscription', subscription_name – removes a subscription previously created by:
```
sp_msgadmin 'register' 'subscription', subscription_name, ...
```

sp_msgadmin 'show'

requires Adaptive Server version 15.0.2 ESD #1 or higher, and displays the information about some MQ objects on a specified queue manager, where:

showtype – allows you to specify which WebSphere MQ process or object you want to display:
- qmgr – is the name of the queue manager.
- queues – is all of the queues and their types that belong to the queue manager.
- channels – is all the channels and their types that belong to the queue manager.
provider – specifies the messaging provider. Use the full path format described in endpoint.

option_string – is the list of options. Table 3-5 lists the valid option parameters.

**Table 3-5: Valid sp_msgadmin 'show' option option_string types and values**
Types	Values	Default	Description
timeout	timespec between 0 and (2³¹–1)	30000 (30 seconds)	Specifies the maximum time in milliseconds that the MQAI should wait for each reply message.
replyqueue	string	None	The command server returns the reply message t the queue. If you do not define the option, the command server returns the message to a dynamic queue, created by opening SYSTEM.DEFAULT.MODEL.QUEUE.

Examples

Example 1

MQ – configures the key repository for Adaptive Server to enable the use of SSL, where the key database file path is /var/mqm/clients/ssl/KeyringClient.kdb:

sp_msgadmin 'config', ibmmq_keystore, 
    'var/mqm/clients/ssl/KeyringClient'

Example 2

JMS – logs the level of JVM:

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

Example 3

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

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

Example 4

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

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

Example 5

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

sp_msgadmin 'config', 'jvmmaxthreads', 100

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 – lists the details for the user with a login of “loginsa”:

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

Example 13

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 14

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 15

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 16

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 17

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 18

JMS – removes the default login:

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

Example 19

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 20

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

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

Example 21

MQ – displays the queue manager name from machine “bigcrunch” with a listening port of 3150:

sp_msgadmin 'show', 'QMGR', 'ibm_mq:/tcp/bigcrunch(3150)'

Name
---------------------------------------------------
TEST

Example 22

MQ – displays the queue manager name. The queue manager is on machine “bigcrunch” with a listening port of 3150. The reply message is placed in the Q1 queue and the longest that Adaptive Server waits for a reply message is 20 milliseconds:

sp_msgadmin 'show', 'QMGR', 'ibm_mq:channel1/tcp/bigcrunch(3150)',
    'timeout=20, replyqueue=Q1'

Example 23

MQ – displays all of the queues on the queue manager. The reply message is placed in the Q1 queue and the longest that Adaptive Server waits for a reply message is 20 milliseconds:

sp_msgadmin 'show', 'queues', 'ibm_mq:/tcp/bigcrunch(3150)',
    'timeout=20, replyqueue=Q1'

Name                                                        Type
------------------------------------------------------------------
Q1                                                          LOCAL
SYSTEM.MQSC.REPLY.QUEUE                                     MODEL
RQ1                                                         REMOTE
AQ1                                                         ALIAS
...

Example 24

MQ – displays all of the channels on the queue manager:

sp_msgadmin 'show', 'channels', 'ibm_mq:/tcp/bigcrunch(3150)'

Name                                                        Type
--------------------------------------------------------------------
SNCH1                                                       SENDER
SECH2                                                       SERVER
RCCH3                                                       RECEIVER
CHL5                                                        SRVCONN
...

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'