NonStop JMS User's Manual (H06.03+, J06.03+, NonStop JMS 3.0+)

Managing the NSJMS Environment
NonStop Server for Java Message Service User’s Manual—522356-006
7-20
Using SQLCI to Manage an NSJMS Database
Using SQLCI to Manage an NSJMS Database
NSJMS stores messages and destination information in SQL tables. You can use
SQL/MP commands and statements to query and manage an NSJMS database by
submitting queries directly through the SQLCI. For additional information about
SQL/MP commands and statements, see the SQL/MP Reference Manual. For
additional information about using SQL/MP to query a database, see the SQL/MP
Installation and Management Guide.
SQLCI Examples
These examples show how SQLCI can be used to query and manage an NSJMS
database. In each example, $vol.subvol represents the volume and subvolume
where the NSJMS SQL tables were created.

To delete all messages which have expired, use these commands:

To retrieve the count of messages in a table names invoices, use these
commands:

To display a list of destination names and message counts, use these commands:

To display a list of destination names and message counts for those destinations
that have more than 1000 messages, use these commands:
SQLCI>> volume $vol.subvol;
SQLCI>> delete from message where msg_expiration < juliantimestamp(current);
SQLCI>> delete from overlow where msg_expiration < juliantimestamp(current);
SQLCI>> volume $vol.subvol;
SQLCI>> select count(*) from message where dest_id = (select dest_id from dest
SQLCI>> where dest_name = 'invoices' );
SQLCI>> volume $vol.subvol;
SQLCI>> select dest.dest_name, count(*) from message, dest where
SQLCI>> message.dest_id = dest.dest_id
SQLCI>> group by dest.dest_name;
SQLCI>> volume $vol.subvol;
SQLCI>> select dest.dest_name, count(*) from message, dest where
SQLCI>> message.dest_id = dest.dest_id
SQLCI>> group by dest.dest_name
SQLCI>> having count(*) > 1000;