Work of an information systems' developer is not only creation of challenging, innovative, advanced in algorithms new applications. It also includes creation and maintenance of support routines like reliable copy of data from one source to another, handling different exceptions, timeouts, hanging transactions, shifting timezones, etc. You're lucky if all software in your system is from single vendor and is still supported. In this case it's likely that there are tools that can move data from one source to another, for example, from a database to a message queue. But if your task is to move data between IBM Websphere MQ with SSL-encrypted channels, MS SQL or Oracle databases and Tumbleweed FTP server, you're likely to come across some vendor-specific implementation issues. Even if working prototype is finished in several days, it'll take some more time to test it before it can be used in production.
One of possible use cases of SOA Expressway (www.dynamicperimeter.com) is rapid creation of such small support workflows. They are created in Services Designer using drag and drop. SOA Expressway can execute tens of such workflows at once - just add one more application to the active configuration and save it.
I'll show how to easily create a workflow that copies data from IBM Websphere MQ to MS SQL database.
- Launch Services designer
- Create an empty project
- Create an empty workflow in this project
- Add 'Receive' and 'SQL Statement' activities
- Configure 'Receive'
- Choose 'JMS' as transport protocol
- Choose ‘No output (one-way)’ in Response data – we’re not doing to reply to JMS in this example
- Configure other fields in JMS receive activity. For IBM Websphere MQ Initial context factory class should be 'com.ibm.mq.jms.context.WMQInitialContextFactory' . Other fields like queue name, connection factory name, JNDI URL are installation specific. You can choose an existing queue or create a new one using Websphere MQ Explorer.
- If your Websphere MQ server requires additional connection parameters like MQCONTEXTMODE - put them to 'Optional custom parameters field'.
- Configure SQL action
- Enter JDBC datasource name. You’ll have to configure datasource with this name in web interface of SOA Expressway. I’ll use ‘jdbc/mssql’.
- Write SQL statement to be executed, for example
‘insert into test(message) values (?)’ . Make sure that the target table exists and the query is valid.
- Add one parameter to the statement, choose ‘Xpath’ type and choose Xpath expression to be used as parameter value. To insert whole message received from JMS use ‘$Receive.body’
The resulted workflow will look like:
You’ll have to add vendor-specific .jar files to SOA Expressway before your workflow can communicate with third-party software like databases, JMS servers, etc. We have IBM message queue and Microsoft database so we should create 2 providers – one provider with client .jar files for Websphere, type = JMS and another – with client .jar files for MS SQL, type = JDBC. This should be done only once after SOA Expressway installation – you don’t have to upload those files again when you create a new or modify an existing workflow. This upload is done in ‘Configuration’ -> ‘System configuration’ menu in the Web interface. Choose ‘Providers’ -> ‘Add’, create provider named ‘websphere’ with type ‘JMS’ and upload all client .jar files corresponding to your Websphere MQ installation. After this create ‘mssql’ provider with type ‘JDBC’ and upload MS SQL client .jar file to it (for example, sqljdbc4.jar).
Now SOA Expressway is ready to work with IBM Websphere MQ and Microsoft SQL server. You can deploy your application directly from the Services Designer or export workflow from the Designer and upload it using web interface. In any case you’ll have to set up configuration in WI before the application actually starts working:
- Enter your JMS server’s host, port and user credentials in ‘JNDI properties section’ of Input Server
- Create JDBC recource with name ‘jdbc/mssql’ and put host, port, database name, database user name and password to it
That’s all. Now close and activate this configuration and data you put to the JMS queue will be transferred the to the database.
This example is intentionally made as simple as possible to make step-by-step instructions clear and not too boring. It can be extended in several ways:
- Make JMS input server not one-way, but two-way. In this case you can put result of database operation - success marker or newly created record ID to JMS reply queue
- Make interaction of JMS and database transactional. SOA Expressway supports XA transactions ( http://en.wikipedia.org/wiki/X/Open_XA ). In current workflow if a database error happens, JMS message is lost – it’s taken from the queue and is not put to the database. You can set ‘Enable XA’ flag in ‘Receive’ properties in the Services Designer and add ‘XA Commit’ activity to the end of workflow. In this case if database query fails, message will be returned to JMS queue and workflow will pick it up for processing later.
Let’s get real
Sceptical readers may think ‘OK, this all sounds good as usual in ads, but it doesn’t have anything common with setup I have at work’. Let’s add some complexity and security. Low level technical details are omitted – feel free to ask in comments.
I'll add modify this workflow:
- Put message from incoming queue to one of 2 queues based on its content
- Add SSL to JDBC connection
- ‘if’ switch with expression ‘string($Receive.body) = 'BLACK'
- Insertion in one queue (BLACK_QUEUE) if this is true
- Insertion in another queue (WHITE_QUEUE) otherwise
Insertion into queue is ‘Invoke’ activity, request data is ‘$Request.body’, response data is ‘No output (one-way)’, all JMS connection parameters are same as in ‘Receive’ activity.
Save application and deploy it. If you think I’m cheating because I promised to add SSL – you’re really reading this, thank you. SSL connection is just configuration issue – we’ll configure it in the Web interface.
After you deployed application, edit JDBC datasource settings to enable SSL. Connection properties vary from vendor to vendor, for MS SQL use ‘encrypt=true;trustServerCertificate=true’ . If you set up this application using another database, find similar properties in manual. Save and activate application. Now if you put message ‘BLACK’ in incoming queue, it’ll be put to database and moved to ‘BLACK_QUEUE’, all other messages will be put to ‘WHITE_QUEUE’. How can you make sure that JDBC connection is really encrypted? You can just trust documentation – if it says that encryption is turned on with those parameters, it’ll work. If you don’t trust it like me – you can watch the traffic between the SOA Expressway host and the database host. If encryption is not enabled, you can see queries in plain text. If it is enabled, you don’t see any SQL expressions.
Happy escape from routine :)