To record data to a Microsoft SQL
Server database, create the database, and configure an ODBC definition
to it. Configure your integration node so
that it can connect to the database.
Before you begin
Read the following topics:
About this task
The following steps describe how to create the SQL Server
database, create an ODBC definition, and set a user ID and password
for the database. You can use Integrated Windows Authentication to specify
a Windows user account to be used for
authentication when using Microsoft SQL
Server to record and replay data. For more information, see Using Integrated Windows Authentication when recording data with a Microsoft SQL Server database.
Procedure
- Use the script that is provided with IBM Integration Bus to
create and configure an SQL Server database to store your recorded data. Note that the script
creates some tables that are not currently used, and are reserved for future use, such as
WMB_EVENT_FIELDS and WMB_EVENT_TYPES.
- Locate the script at install_dir\server\ddl\sqlServer\DataCaptureSchema.sql, where install_dir is the location of your IBM Integration Bus installation.
- Optional: Customize the provided DataCaptureSchema script.
If you modify the SQL to specify a particular schema, you must also set the same schema name in
the DataCaptureStore configurable service.
- To run the script, at a command line, navigate to the script location and enter the
following command:
sqlcmd -d databaseName -i DataCaptureSchema.sql
- Create an ODBC definition for the database.
If
you used the supplied script to create your database without modifications,
create an ODBC definition for the database that is called MBRECORD,
with MBRECORD as the data source name (DSN). For
more information, see Enabling ODBC connections to the databases.
- Use the mqsisetdbparms command
to set a user identifier and password for the integration node to use when it connects
to the database.
For example:
mqsisetdbparms integrationNodeName -n dataSourceName -u userID -p password
- integrationNodeName is the name of your integration node.
- dataSourceName identifies the database to which you want to record data.
- userID and password specify the user identifier and the password that the integration node uses to connect to the database.
If Integrated Windows Authentication is being used for SQL Server database access, then the service user ID under which the broker process runs is used by Windows to access the SQL Server database. It ignores any user ID and password credentials that are set using the mqsisetdbparms command.
- To ensure that the changes to the mqsisetdbparms command
take effect, restart the integration node.
- Test the connection to your database by using the mqsicvp command.
What to do next
Next:
Follow the steps for recording data. See Recording data.