Setting up the database

The message flow used in this sample requires an external database, which is used in one of the graphical data maps that enriches the message data as it runs the transformation. The database must be set up in advance so that it can be accessed in the "StoreSales to SoldStock " Mapping node.

A JDBCProviders configurable service is required to resolve the data source name RETAILDB, which is used when creating the "StoreSales to SoldStock" graphical data map to the database instance that is available to the Broker when running the sample.

To access the database, the broker must also be configured with a valid JDBC service identifier and a password.

The data that the map accesses consists of three tables:

Setting up a DB2 database:

  1. Create a database called RETAILDB:
     

    Check whether you are able to connect to RETAILDB successfully.

  2. Create the tables using the SQL createTables.sql script provided in the sample under the GraphicalDataMappingRetailSampleDatabase project, which are listed under the "Other Resources" category in the Application. From the DB2 command prompt, change to the directory that contains the createTables.sql script, and run the following command:
    db2 -vf createTables.sql
  3. Populate the tables with data using the SQL script provided in the GraphicalDataMappingRetailSampleDatabase sample project. From the DB2 command prompt, change to the directory that contains the insertDbData.sql script, and run the following command:
    db2 -vf insertDbData.sql

Setting up the database environment for the broker:

  1. Use the following command to create the username and password security credentials that will enable the broker to access the database. Provide the database user ID and password in place of <user ID> and <password>:

    mqsisetdbparms MB8BROKER -n jdbc::RETAILDB -u <user ID> -p <password>
  2. Create the JDBC configurable service (using the command shown in step 3) to resolve the data source name "RETAILDB" from the map to the physical database that is to be used by the broker at run time.

    For more information about creating JDBC Configurable Services, see Setting up a JDBC provider for type 4 connections.

    Note that the "maxConnectionPoolSize" parameter is set to a non-zero value. This will create a JDBC connection pool to be used by the sample. For more information about JDBC connection pooling, see Using a JDBC connection pool to manage database resources used by an execution group.

    The "databaseSchemaNames" parameter is set to "". This empty string is to indicate not to use the database schema name in the sample map, since it will not match your local database schema name.

    The WebSphere Message Broker Explorer provides a simple user interface for creating JDBC configurable services from a template for each type of database provider, as an alternative to the command shown in the next step.

    The RETAILDB JDBC configurable service used in this sample requires the following settings, actual values may vary as noted below:



    <configurableservice connectionUrlFormat="jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];" connectionUrlFormatAttr1="" connectionUrlFormatAttr2="" connectionUrlFormatAttr3="" connectionUrlFormatAttr4="" connectionUrlFormatAttr5="" csName="RETAILDB" csType="JDBCProviders" databaseName="RETAILDB" databaseType="DB2 Universal Database" databaseVersion="9.1" databaseSchemaNames="" description="Graphical Data Mapping Retail sample Database" environmentParms="default_none" jarsURL="<DB2 install dir>\SQLLIB\java" maxConnectionPoolSize="2" portNumber="50000" securityIdentity="RETAILDB" serverName="localhost" type4DatasourceClassName="com.ibm.db2.jcc.DB2XADataSource" type4DriverClassName="com.ibm.db2.jcc.DB2Driver"/> 

  3. Enter the mqsicreateconfigurableservice command, ensuring that you review and possibly update values, shown in angle brackets (<>), with appropriate values to match your system. In the command the "-n" parameter is followed with a list of properties, who's values are then set via the corresponding list of values following the "-v" parameter.

    Note that the "connectionUrlFormat" parameter value intentionally has variable names in square brackets ([]), and these must not be changed.
    mqsicreateconfigurableservice
    < MB8BROKER > -c JDBCProviders -o RETAILDB -n
    connectionUrlFormat,databaseName,databaseType,databaseSchemaNames,description,jarsURL,
    maxConnectionPoolSize,portNumber,serverName,securityIdentity,
    type4DatasourceClassName,type4DriverClassName
    -v
    "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];",
    RETAILDB,"DB2 Universal Database","","Graphical Data Mapping Retail sample Database",
    "< C:\Program Files\IBM\SQLLIB\java >",2,< 50000 >,< localhost >,"RETAILDB"
    com.ibm.db2.jcc.DB2XADataSource,com.ibm.db2.jcc.DB2Driver
    
    mqsicreateconfigurableservice
    MB8BROKER -c JDBCProviders -o RETAILDB -n
    connectionUrlFormat,databaseName,databaseType,databaseSchemaNames,description,
    jarsURL,maxConnectionPoolSize,portNumber,serverName,securityIdentity,
    type4DatasourceClassName,type4DriverClassName
    -v
    "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];",
    RETAILDB,"DB2 Universal Database","","Graphical Data Mapping Retail sample Database",
    "C:\Program Files\IBM\SQLLIB\java",2,50000,localhost,RETAILDB,
    com.ibm.db2.jcc.DB2XADataSource,com.ibm.db2.jcc.DB2Driver
    
    mqsicreateconfigurableservice
    MB8BROKER -c JDBCProviders -o RETAILDB -n
    connectionUrlFormat,databaseName,databaseType,databaseSchemaNames,description,jarsURL,
    maxConnectionPoolSize,portNumber,serverName,securityIdentity,type4DatasourceClassName,type4DriverClassName
    -v
    "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];",
    RETAILDB,"DB2 Universal Database","",
    "Graphical Data Mapping Retail sample Database",/home/db2admin/sqllib/java,
    2,50000,localhost,RETAILDB,com.ibm.db2.jcc.DB2XADataSource,com.ibm.db2.jcc.DB2Driver
    
  4. Enter the following command to check whether all the configurable service properties are set correctly: mqsireportproperties MB8BROKER -c JDBCProviders -r -o RETAILDB
  5. Create the following directories:
  6. Ensure that your broker has been created and is running.
  7. Set the MQSI_FILENODES_ROOT_DIRECTORY environment variable to the root directory:
    1. Follow the instructions at Setting up a command environment to create a command environment and a command file that contains the following line (ensuring that there is no white space around the equal (=) sign):
      • On Windows:
        SET MQSI_FILENODES_ROOT_DIRECTORY=c:\MQSIFileOutput
      • On Linux:
        export MQSI_FILENODES_ROOT_DIRECTORY="/tmp/MQSIFileOutput"
    2. Stop and restart your broker. See Starting and stopping a broker.

Back to sample home