The Sterling Map Editor can have SQL on the input or output side of the map. That enables to either select something from a Database or to insert, update or delete something in a database.
Sterling B2B Integrator can connect to a Database either through a map or by using a JDBC adapter in a Business Process. As I see it there is different scenarios best suited for the different ways of connecting to a Database. The strength of using a map is if the result from a query should be translated to some kind of file format, or if a file should insert or update multiple rows in a Database. The JDBC Service in a Business Process is as far as I see it more suitable for single selects or inserts/updates. But in the end, it is up to whoever develops to choose what is the best fit for each scenario.
In this Blog Post I will describe what needs to be done prior to building a SQL Map, and also build a very simple map to test it.
- Configure Sterling B2B Integrator with a new DB Pool
- Install ODBC on the computer where Sterling Map Editor is running
- Create a simple map.
CONFIGURE STERLING B2B INTEGRATOR
Sterling B2B Integrator uses DB Pools to connect to databases. The default DB Pools is configured in jdbc.properties.in and propagated to jdbc.properties under the <install dir>/install/properties/ folder.
When creating other databases that should be made available for Sterling B2B Integrator, this is done in jdbc_customer.properties.in and propagated to jdbc_customer.properties. It can in theory also be configured in the jdbc.properties.in file, but by putting it in the jdbc_customer.properties.in file it will not be overwritten when patching or upgrading Sterling B2B Integrator since this is a properties file for customer specific JDBC properties.
I am using a database I created on the same Database Server used for my Sterling B2B Integrator Environment. I named the new Database “SI52DEV”.The simplest thing to do is to open the jdbc.properties.in file and find the settings for db2Pool_local (other name if other DB is used). I copy all the db2Pool_local parameters from jdbc.properties.in and edit them in a text-editor. I then search and replace “db2Pool_local” with “db2Pool_dev” (might call it whatever). I then change the parameters that is specific for the database. That is the url, catalog and dbname. I used the same Schema and the same User and Password. There is also a parameter called “testOnReserveQuery” that is used to check if the database is available. There I put a query to the new database that I know I will get a response to when queried (I just created a table with a record that gives a result when queried).
Copy the edited configuration to jdbc_customer.properties.in file. Shut down Sterling B2B Integrator, run setupFiles.sh and start the Sterling B2B Integrator again. By opening jdbc_customer.properties, the new configuration should be propagated there. I then have a new Database Pool available in Sterling B2B Integrator to play around with.
My customer_jdbc.properties file ends up like this:
db2Pool_dev.testOnReserveQuery=SELECT VALUE from SIDEV_TEST where ID = 1
INSTALL DB2 ODBC
I am running Sterling Map Editor on a PC with Windows 7 64-bit OS. Thus I need a DB2 ODBC for Windows. As mentioned, I am connecting to the DB2 Database I installed previously in my Demo Environment.
Start by downloading the IBM Data Server Driver for ODBC and CLI for DB2 from IBM Support.
Unzip the file and install the driver through Command Line.
This should make the ODBC Driver available in the ODBC Data Source Administrator in Windows.
There is however one thing to mention here. Since I have a 64-bit laptop, but the Sterling Map Editor is 32-bit, I need to use the 32-bit ODBC Data Source Administrator to create the User DNS for use in the Map Editor. So instead of going to the Control Panel and open the ODBC Data Source Administrator from there, I have to go to C:\Windows\SysWOW64 and start odbcad32.exe from there to get the 32-bit version of the Administrator.
Now, lets get ready for some Screenshot Bonanza!
In the ODBC Data Source Administrator (the 32-bit edition), select User DNS and Add.
Then Add Database alias, user ID and Password.
Create the Map
Next, and last thing is to create the map itself.
I will just create a very simple map that takes a Positional flat file with one data field per record and inserts it to a table.
Select New Map, give it a name and select Input format Positional and Output Format SQL. You then end up with an initial map like below.
Then go to the output side and select Properties. Select ODBC Data Sources -> Machine Data Source and the ODBC DSN created earlier (db2Pool_dev).
By some reason, there is a bug here (at least in my case). Under the ODBC Connection parameters, the DSN name is selected with Uppercase Letters only. That DSN name should match the name in the Sterling B2B Integrator properties, and is Case Sensitive. So I just edited that DSN name to fit my configuration files.
The next step is to add a Output record in the map. Click Create Sub and Output record. Give it a name and choose SQL Operations. Select Data Source and Table/View from the Drop Downs. And Operation Insert. Set Looping to 1000 to enable insertion of multiple records.
On the Input side of the map, create a record with Tag “L01” and Looping set to 1000 to fit the Output side. And a field that fits the Input Testfile value field. Link the input filed to the output field. Compile the map, and test it.
L01INSERTED VALUE 001
L01INSERTED VALUE 002
L01INSERTED VALUE 003
L01INSERTED VALUE 004
L01INSERTED VALUE 005
L01INSERTED VALUE 006
L01INSERTED VALUE 007
L01INSERTED VALUE 008
L01INSERTED VALUE 009
L01INSERTED VALUE 010
In the Database it looks like below after a test-run (from ID 4 and out since I done another test first):
This kind of SQL maps (and also JDBC Services in a Business Process) should only be done towards databases that is not the Sterling Database. Do not create new tables in the Sterling Database itself or insert, delete or update the Sterling Database tables, since that might make the Sterling Database corrupt. And also changes in the Sterling Database itself might be overwritten when patching and upgrading Sterling B2B Integrator. Create a new Database for this kind of use.