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.
That is:
- 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.url=jdbc:db2://sidb.demos.ibm.int:50000/SI52DEV
db2Pool_dev.catalog=SI52DEV
db2Pool_dev.dbname=SI52DEV
db2Pool_dev.varDataClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.DB2VarData
db2Pool_dev.user=db2inst1
db2Pool_dev.password=password
#db2Pool_dev.maxconn=20
db2Pool_dev.schema=DB2INST1
db2Pool_dev.storedProcClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery
db2Pool_dev.type=local
db2Pool_dev.transaction=false
db2Pool_dev.testOnReserve=true
db2Pool_dev.testOnReserveQuery=SELECT VALUE from SIDEV_TEST where ID = 1
db2Pool_dev.testOnReserveInterval=60000
db2Pool_dev.maxRetries=100
db2Pool_dev.blobPageSize=1024000
db2Pool_dev.compressBlob=true
db2Pool_dev.dbvendor=db2
db2Pool_dev.buffersize=500
db2Pool_dev.maxsize=30
#db2Pool_dev.initsize=0
db2Pool_dev.initsize=1
db2Pool_dev.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory
db2Pool_dev.behaviour=2
db2Pool_dev.lifespan=0
db2Pool_dev.idletimeout=86400000
db2Pool_dev.housekeepinginterval=3600000
db2Pool_dev.waittime=1000
db2Pool_dev.errorMissingTable=-204
db2Pool_dev.defaultIsolationLevel=2
db2Pool_dev.systemPool=true
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 select to use the IBM Data Server Driver for ODBC.
Give the new Data Source a name. This name however can not be whatever. It has to be identical to the DB Pool name in the Sterling B2B Integrator.
Then Add Database alias, user ID and Password.
This is it for the ODBC setup.
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.
Click OK, and select the Table(s) that should be made available in the map.
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.
Click OK. Right Click on the Output record created, and select Generate Fields. Select the Value Field, since the ID is created in the Database as a auto incremented value.
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.
The input file in my example was a simple text file as below where “L01” was the record Tag and the value was “INSERTED VALUE 0NN”:
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):
Warning
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.
Hi Erland,
Thank you for this how-to. I am creating a Sterling map to translate an EDI 850 to a DB2 database and am stuck on the EDI side (Input) where I want to query a table in the database to cross reference a value from the EDI file. This query will not work unless I hardcode the value. I’m sure this must be a way around this in Sterling, but have not had any luck yet.
Can you provide any direction? Any assistance would be greatly appreciated!
Hi Karen,
I am not working for IBM any more, and thus I unfortunately don’t have access to the IBM Sterling software any more either. So I can’t test this. But as far as I know, there is actually not possible with dynamic SQL queries in a Sterling map. You then have to go via the BP layer (post the value to a variable in the BP, do the SQL in the BP and then get it back to the map). Then it would require to run two map’s, so a bit overhead. And since the strategic mapping engine from IBM is ITX and not Sterling Mapper, I guess it will not be implemented in the Sterling Mapper. So I believe you either need to try the way via BP, or upgrade to ITX (I believe it would work with ITX as mapper, since it is more powerful).
Hi Erlend, im trying this but with SQL Server Database, I did the same steps but using the .map in a XML Encoder in Sterling Integrator, when I ran the .bp I get a response in process data but when I look the database, no record is inserted, do you have an idea what happend?
Hi Erlend,
If there are millions of records to be extracted from database using map…what should be the best way to handle using map, considering performance criteria..