This is part of the larger task of customizing
your z/OS environment and is relevant
only to the broker component. The Configuration Manager and User Name Server do not require access to DB2.
WebSphere Message Broker for z/OS accesses DB2 tables using ODBC. To connect to DB2 using ODBC, the location name of the DB2 subsystem is used. See the DB2
UDB for OS/390 and
z/OS V7 Data Sharing: Planning and Administration manual for more details.
You
need to give certain user IDs access to
DB2 resources
and these are summarized below:
- DB2 systems administrator
- Create database, storage groups and table spaces (BIPCRDB).
- Drop database (BIPDLDB)
- Administrator for the broker database (DBA). This should be the WebSphere Message Broker administrator.
- Create tables and indexes (BIPCRBK)
- Create tables, drop tables and create indexes (BIPMGCMP)
- Broker started task user Id:
- SQL to select, insert, and delete rows from the broker database tables,
and select from DB2 system tables.
- WebSphere Message Broker administrator and other users
- SQL to select, insert, and delete rows from the broker database tables,
and select from DB2 system tables.
When your DB2 system starts
up there should be a message DSNL004I DDF START COMPLETE.
The location name is displayed just after this message. When you customize
a broker component on z/OS you create
a dsnaoini file called BIPDSNAO in the broker
PDSE. It contains necessary information to establish the ODBC connection.
See the DB2 UDB for OS/390 and z/OS V7 ODBC Guide and Reference manual
for more details.
You should avoid using a data source name that is
the same as the subsystem ID or data sharing ID. If the same name is used,
this might affect the granularity of directives on connection with the database.
If
you choose to use the same value for the data source name and subsystem ID,
you must edit BIPDSNAO in the broker PDSE so that the Datasource
and Subsystem keywords are in one section.
See the DB2 UDB for
OS/390 and z/OS V7 ODBC Guide and Reference manual for more information
on customizing this file.
During customization you can specify which
plan name to use, or use the default
DSNACLI. If you want
your broker to access
DB2 data-sharing
groups other than its own, the
DSNACLI plan must be bound
in a special way. Check the wildcard location is specified by using SPUFI
and issuing the following command:
select * from SYSIBM.SYSPACKLIST where planname ='DSNACLI';
You
should rebind if the location column is blank and not
'*'.
You
should also check that DSNACLI is in the SYSIBM.SYSPLAN table.
You
will get significant performance benefits from using the CACHE DYNAMIC
SQL facility of DB2, because
this eliminates the need to reprocess DB2 statements.
See CACHEDYN=YES in the DB2 UDB for OS/390 and z/OS
V7 Installation Guide.
If your user database is configured to
use a comma as a decimal separator using the
DSNHDECP module,
you will find there is a restriction. If there is a mismatch between
DB2 and
the locale settings of the user ID under which the broker runs (specifically
LC_NUMERIC),
your user database updates can be unpredictable.
LC_NUMERIC is
set through the
LC_ALL setting in the
BIPBPROF member,
and therefore the environment file. The following list details the four possibilities:
- If DB2 is configured to use a period
as a decimal point and LC_NUMERIC is set to a value that
indicates a period decimal point; user database updates should work correctly.
- If DB2 is configured to use a comma
as a decimal point and LC_NUMERIC is set to a value that
indicates a comma decimal point; user database updates should work correctly.
- If DB2 is configured to use a period
as a decimal point and LC_NUMERIC is set to a value that
indicates a comma decimal point; user database updates can lead to unpredictable
behavior.
- If DB2 is configured to use a comma
as a decimal point and LC_NUMERIC is set to a value that
indicates a period decimal point; user database updates can lead to unpredictable
behavior.
You can use the DB2 security
mechanism, or if on z/OS 1.5 and DB2 Version 8 use an external security manager,
for example, RACF.
DB2 security
mechanism
The most practical way of managing access to a broker's
DB2 resources is to define two RACF groups
and connect users to these groups. For example, RACF groups
MQP1ADM and
MQP1USR are
defined for broker
MQP1BRK as follows:
- For group MQP1ADM
- Grant this group DBADM authority for the broker database.
- Typically owned by the WebSphere Message Broker administrator;
user Ids must be added to this group who need to submit BIPCRBK to
create a broker, or BIPMGCMP to migrate a broker.
- For group MQP1USR
- Give this group access to manipulate rows in the broker tables and allow
select access to DB2 system tables.
For example:
GRANT DELETE, INSERT, SELECT, UPDATE
ON TABLE
DB2_TABLE_OWNER.BSUBSCRIPTIONS
,DB2_TABLE_OWNER.BPUBLISHERS
,DB2_TABLE_OWNER.BCLIENTUSER
,DB2_TABLE_OWNER.BTOPOLOGY
,DB2_TABLE_OWNER.BNBRCONNECTIONS
,DB2_TABLE_OWNER.BRETAINEDPUBS
,DB2_TABLE_OWNER.BACLENTRIES
,DB2_TABLE_OWNER.BMQPSTOPOLOGY
,DB2_TABLE_OWNER.BUSERNAME
,DB2_TABLE_OWNER.BGROUPNAME
,DB2_TABLE_OWNER.BUSERMEMBERSHIP
,DB2_TABLE_OWNER.BROKERAA
,DB2_TABLE_OWNER.BROKERAAEG
,DB2_TABLE_OWNER.BROKERRESOURCES
,DB2_TABLE_OWNER.BRMINFO
,DB2_TABLE_OWNER.BRMRTDINFO
,DB2_TABLE_OWNER.BRMRTDDEPINFO
,DB2_TABLE_OWNER.BRMWFDINFO
,DB2_TABLE_OWNER.BRMPHYSICALRES
,DB2_TABLE_OWNER.BAGGREGATE
,DB2_TABLE_OWNER.BMULTICASTTOPICS
TO MQP1USR;
GRANT SELECT
ON TABLE
SYSIBM.SYSTABLES
,SYSIBM.SYSSYNONYMS
,SYSIBM.SYSDATABASE
TO MQP1USR;
- Connect the broker started task user Id and the WebSphere Message Broker administrator
to this group, and connect any other users who might need access to the tables,
for example those submitting BIPRELG to run the mqsireadlog command.
Note the following:
- When accessing DB2 resources, you
specify a CURRENT SQLID as a DB2 command,
or through the BIPDSNAO member.
If this Id is a group,
then DB2 checks to see if your user
Id is connected to this group, and if it is, you inherit the access from the
group; if the user Id is not in the group, you get SQL code -551.
If
your Id is in multiple groups, then the highest authorities are used.
- For BIPCRDB and BIPDLDB the CURRENT
SQLID is specified as a command in the JCL. For all other JCL it
is specified in BIPDSNAO.
- If you do not use groups to define permissions, but use a specific user
Id to define the permissions to individual user Ids, then, if the granting
user Id is removed from DB2 any permissions
that it gave are also removed. This can prevent other users working.
- When the BIPDLDB job drops the broker DB2 database,
it also deletes any Image Copy references to itself that you currently have.
If you restore the broker in future you need to reinstate the Image Copy references.
If this Id is a group, then DB2 checks
to see if your user Id is connected to this group, and if it is, you inherit
the access from the group; if the user Id is not in the group, you get SQL
code -551. If your Id is in multiple groups, then the highest
authorities are used.
If you do not use groups to define permissions,
but use a specific user ID to define the permissions to individual user Ids,
then, if the granting user Id is removed from DB2 any
permissions that it gave are also removed. This can prevent other users working.
See z/OS JCL variables for further information on the WebSphere Message Broker for z/OS jobs that are supplied.