Creating and configuring a DB2 database for recording data

To record data to a DB2® database, create the database and configure an ODBC definition to it. Configure your integration node so that it can connect to the database.

1. Use the script that is provided with IBM® Integration Bus to create and configure a DB2 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.
   1. Locate the script for your operating system:
      • Windows: install_dir\server\ddl\db2\DataCaptureSchema.sql
      • Linux® or UNIX: install_dir/server/ddl/db2/DataCaptureSchema.sql
      • z/OS® : the JCL script is in the data set and member SBIPPROC(BIPRRDB)

      install_dir is the location of your IBM Integration Bus installation.
   2. Optional: To specify your own database or schema, customize the provided DataCaptureSchema script, and save your changes.

      If you modify the SQL to specify a particular schema, you must also set the same schema name in the DataCaptureStore configurable service.

      You might also want to edit the script for the following reasons:

      • If you ran the script, and want to run it again, you must drop the database MBRECORD first. Insert the command drop database MBRECORD before the line that reads create database MBRECORD.
      • The maximum message body size that you can record (after encoding has taken place) is 5 MB. The default size is 5 MB, but you can increase this size by editing the script to make the value in the WMB_BINARY_DATA.DATA column larger.
   3. At a command line, navigate to the script location and run it.

      On Windows, use a DB2 Command Window to ensure that the command environment is set up correctly. Click Start > IBM DB2 > databaseInstance > Command Line Tools, and select Command Window, where databaseInstance is the DB2 installation name.

      On UNIX, a script called db2profile is provided for setting up the environment; for more information, see Running database setup scripts.

      When the command environment is set up, you can run the script.

      For example, on Windows or UNIX, enter the following command:

      db2 -tvf DataCaptureSchema.sql

2. 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 called MBRECORD, with MBRECORD as the data source name (DSN). For more information, see Enabling ODBC connections to the databases.

3. Use the mqsisetdbparms command to set a user identifier and password for the integration node to use when connecting 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.
4. To ensure that the changes to the mqsisetdbparms command take effect, restart the integration node.
   For more information, see Starting and stopping an integration node.
5. Test the connection to your database by using the mqsicvp command.
   For more information, see mqsicvp command.