How To Access Database Using Compute Node ESQL in Message Broker

Ok you need to access Database ( DB2 or Oracle or any other ) from your message flow. there are 2 simple steps to configured..


Step 1 : Create a data source name on your machine if you are using windows ,create ODBC data source name for DB, or if you are configuring on unix then create DSN for your database first.

Step 2: Now register your data source name (DSN) with your runtime message broker by using below command 

mqsisetdbparms IB9NODE -n <DSN> -u db2admin -p <password>    ( Remove <> signs >

Step 3:-  Using Compute Node , First you need to configured your compute node by proving datasource name. 

Step 4 : - Use select or insert whatever you are doing statement in ESQL like below 

select from datasourcename.tablename;

Step 5: - Deploy and run your message flow.


Do let me know if you have any doubt in comment box below. 

How To Add CDATA Section in XML using ESQL

CDATA - CDATA is a property which we can use to transport any type of data lets say XML which is not formatted properly ie.

C Data tells XML parser not to parse the XML (data) inside these tags , A CDATA section starts with "<![CDATA[" and ends with "]]>" in Message broker or IIB we can achieve this using below code


DECLARE requestBlob BLOB ASBITSTREAM(outRef,OutputRoot.Properties.Encoding,OutputRoot.Properties.CodedCharSetId);
        DECLARE requestChar CHAR CAST(requestBlob AS CHARACTER CCSID OutputRoot.Properties.CodedCharSetId);
        DELETE FIELD OutputRoot.XMLNSC.soapenv:Envelope.soapenv:Body.ser:serviceCall.xsd:requestBody.xsd:request;
        SET OutputRoot.XMLNSC.soapenv:Envelope.soapenv:Body.ser:serviceCall.xsd:requestBody.(XMLNSC.CDataField)xsd:request = requestChar;

The output of above ESQL will be like below

<NS3:requestBody xmlns:NS3="example.com">
                    <NS3:request>
                        <![CDATA[<RequestXml><TxnRefNo>12342233</TxnRefNo><other data></RequestXml>]]></NS3:request>
                </NS3:requestBody>

By looking at above example you can format your ESQL, hope this help you, do let me know in comment if any further queries in the comment section below. Enjoy.