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:
On Windows:
Open a DB2 command prompt and create the database.Click Start > IBM DB2 > databaseInstance > Command Line Tools, and select Command Window, where databaseInstance is the DB2 installation name.
Run the following commands:C:\Program Files\IBM\SQLLIB\BIN>DB2 CREATE DB RETAILDB
C:\Program Files\IBM\SQLLIB\BIN>DB2 CONNECT TO RETAILDB
On Linux:
Create the DB2 instance and then log in as the user for which the DB2 instance was created:$db2 CREATE DB RETAILDB
$db2 CONNECT TO RETAILDB
Check whether you are able to connect to RETAILDB successfully.
db2 -vf createTables.sql
db2 -vf insertDbData.sql
Setting up the database environment for the broker:
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"/>
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
SET MQSI_FILENODES_ROOT_DIRECTORY=c:\MQSIFileOutput
export MQSI_FILENODES_ROOT_DIRECTORY="/tmp/MQSIFileOutput"