ESQL COMPUTE NODE CODING & STANDARDS

1.Declaration:

DECLARE i INTEGER 1;

DECLARE A CHARACTER 'software systems';

DECLARE i INTEGER CARDINALITY(someinput); // Gives total number of records in the input

DECLARE i INTEGER SUM(someinput);

2.Assigning a input reference to a variable or an assignment operation:

DECLARE inRef REFERENCE TO InputRoot.XMLNSC.DETAILS;

SET outRef.EMP[i].ENAME = inRef.EMP[i].ENAME;

3.Defining a while loop:

WHILE i <=  count  DO

  SET outRef.EMP[i].ENAME = inRef.EMP[i].ENAME;

  SET outRef.EMP[i].LOCATION = inRef.EMP[i].LOCATION;

  SET outRef.EMP[i].BATCH = inRef.EMP[i].BATCH;

  SET i = i + 1;

END WHILE;

4.Creating a xml Field:

CREATE FIELD OutputRoot.XMLNSC.DETAILS.EMP;

-Its always a best practise to use reference to the field created

DECLARE forOutRef REFERENCE TO OutputRoot.XMLNSC.DETAILS.EMP;

5.Creating a next sibling to a xml Field:

CREATE NEXTSIBLING OF forOutRef AS forOutRef

CREATE NEXTSIBLING OF OutputRoot.XMLNSC.Order.Summary.CustomerDetails

NAME 'Address';

6.Deleting a lastchild in a xml:

DELETE LASTCHILD OF OutputRoot.XMLNSC.DETAILS;

7.Creating a Procedure:

CREATE PROCEDURE  mapping (IN inputRef REFERENCE, INOUT inoutRef  REFERENCE )

BEGIN

  

  SET inoutRef.Designation = 'Senior Employee';

  SET inoutRef.Nationality = 'Indian';

  SET inoutRef.Company     = inputRef.COMPANY;

    

END;

8.Defining a for loop:

FOR forRef AS inRef.EMP[]  DO

  SET forOutRef.ENAME    = forRef.ENAME;

  SET forOutRef.LOCATION = forRef.LOCATION;

  SET forOutRef.BATCH    = forRef.BATCH;

        

-- Try to create procedures for reusable of  code.

  CALL mapping(forRef,forOutRef);

  CREATE NEXTSIBLING OF forOutRef AS forOutRef REPEAT;          

END FOR;

9.Defining a Row and Select Statement to access a row in xml:

DECLARE Record ROW;

SET Record.val[] =  SELECT A.BATCH  FROM  OutputRoot.XMLNSC.DETAILS.EMP[]AS A;

10.Casting:

CAST(A.BATCH AS INTEGER)

Cast(InputRoot.XMLNSC.Order.Items.Item[i].Price AS DECIMAL

CCSID InputRoot.MQMD.CodedCharSetId

11.Get the FieldNames and FieldValues from xml:

FIELDNAME(InputRoot.XMLNSC.DETAILS.EMP.ENAME);

FIELDVALUE(InputRoot.XMLNSC.DETAILS.EMP.ENAME);

12.Current Date:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

13.Switch Case:

-- Calculating the Tax based on state name --

CASE state

WHEN 'NJ' THEN

SET tax=(sum*7)/100;

WHEN 'NY' THEN

SET tax=(sum*9)/100;

WHEN 'CY' THEN

SET tax=(sum*8)/100;

WHEN 'TX' THEN

SET tax=0;

ELSE

SET tax=NULL;

END CASE;

14.Create CHILD With a name

CREATE FIRSTCHILD OF OutputRoot.XMLNSC.Order NAME 'Summary';

15.Create Attributes for Tag of XML

DECLARE ref2 REFERENCE TO OutputRoot.XMLNSC.Order.Summary.Address;

SET ref2.(XMLNSC.Attribute)ccode =ref3.CustomerID;

SET ref2.(XMLNSC.Attribute)State =state;

15.Functions:

LENGTH(A)

LCASE(A)

UCASE(A)

LEFT(A,2)

RIGHT(A,2)

TRIM(A)

SUBSTRING(A FROM 2 FOR 3)

POSITION('i' IN A)

LTRIM(A)

RTRIM(A)

OVERLAY(A PLACING 'ss' FROM 2 FOR 2)

REPLACE(A,'s','ssss')

REPLICATE(A,3)

TRANSLATE(A,'Bhanu','Address')

16.Sending the same data to multiple queues

   

SET OutputLocalEnvironment.Destination.MQ.DestinationData[1].queueName = 'Q3';    

SET OutputLocalEnvironment.Destination.MQ.DestinationData[2.queueName = 'Q4;    

17.Sending the data to another terminal without deleting the original data

   

PROPAGATE TO TERMINAL 'out1' DELETE NONE;

18.Calling External Java Class in ESQL :

CALL ADD() INTO A; -- Function call

CREATE PROCEDURE ADD ( IN A INTEGER,IN B INTEGER) RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME "com.training.add.Addition.Add";; -- CallMe is a methodName in JavaClass

Sample Scenario For combining the Temporary message in the Queue with the new message that is obtained in the middle of the flow using the MQGet node

MQGET:-

we know MQGet node can be used anywhere in a message flow to store message in intermediate state & afterwords in another thread of flow we can aggregate this temporary result to form final output (Getting message by correl/message id..)

Following example illustrates how an intermediate message can be combined to incoming message to form final message.

First Flow : IN (MQInput) ,Compute, TEMP (MQOutput)

Here the Compute node copies the message id of the incoming message to correlation id of output message, so that we can retrieve this intermediate message based on correlation id.

SET OutputRoot.MQMD.CorrelId  =  InputRoot.MQMD.MsgId;

Second Flow :  IN2 (MQInput),MQGet (MQGet), OUT (MQOutput)

Set the properties of MQGet Node as

INPUT MESSAGE FOR FIRST FLOW IN IS :- <WORKED_FOR><PROJECT>IRCTC_MB_NEW</PROJECT></WORKED_FOR>

INPUT MESSAGE FOR SECOND FLOW IN2 IS:-

<COMPANY>

<NAME>NEW MB WAVES</NAME>

<PROJECTDETAILS>----------</PROJECTDETAILS>

</COMPANY>

Motto:-

Now i am trying to combine both the messages from IN,IN2 and make a single output message so for this purpose i will make the MQGet Node properties be set as

Give the queue name for Temp Node in First flow and MQGet node in second flow as similar

(for example let it be as :-OUT)

The final result obtained at the end of the flow is

<COMPANY>

<NAME>NEW MB WAVES</NAME>

<PROJECTDETAILS>IRCTC_MB_NEW</PROJECTDETAILS>

</COMPANY>

ERROR HANDLING SCENARIO

1.INPUT

2. SUB-FLOW

3.BACK TO MAIN FLOW

4.UPDATES THE ERROR IN THE DATABASE WITHIN SUBFLOW AND ROLLS BACK BY THROW

5.RETURNS TO CATCH TERMINAL OF INPUT UPON ROLLBACK AND UPDATES OUTPUT1 QUEUE WITH THE FAILED MESSAGE

NOTE: IF THE CONDITION Body.Staff.StaffNumber <='10' IN CheckValidStaffNumber NODE IS SATISFIED THEN UpdateStaffDatabase node hits and computes accordingly.

WebSphere MQSC commands for MQ

 Start MQ Explorer                                                -            STRMQCFG

Display Version Information                                 -            DSPMQVER

Display Queue Managers                                    -            DSPMQ

Display command server                                     -            DSPMQCSV (Queue Manager Name)

(display the status of the command server

for the specified queue manager;Starting,Ending

Running,Stopped)

End command server                                          -            ENDMQCSV (Queue Manager Name)

START command server                                     -           STRMQCSV (Queue Manager Name)

Create Queue Manager                                      -            CRTMQM (Queue Manager Name)

Delete queue manager                                       -            DLTMQM (Queue Manager Name)

Start Queue Manager                                  -           STRMQM (Queue Manager Name)

End Queue Manager                                   -           ENDMQM (Queue Manager Name)

Run MQSC commands                               -           RUNMQSC (Queue Manager Name)

(defining, altering, or deleting a local queue object)

Define a Local Queue                                  -              DEFINE QLOCAL  (Queue Name)

Define a Transmission Queue            -   DEFINE QLOCAL (Transmission queue Name)  

                                                                                                            USAGE  (XMITQ)

Define a Remote Queue Definition   -                    DEFINE QREMOTE (Remote Queue   

       Definition Name) RQMNAME (Remote Queue Manager Name)  RNAME (Remote

                                                       Queue Name)  XMITQ(Transmission Queue Name)

Run Channel Initiator                                    -   RUNMQCHI [-m QMgrName] [-q InitQ]

Define a Sender Channel    -                                  DEFINE CHANNEL (Channel name)

          CHLTYPE (SDR)  TRPTYPE (TCP) CONNAME (‘LOCALHOST(Remote Queue

                                              Manager Listener)’) XMITQ (Transmission Queue Name)

Define a Receiver Channel  -               DEFINE CHANNEL (Channel name) CHLTYPE

                                                                                             (RCVR)   TRPTYPE(TCP)

                                                                                                               

Define a Listener                  -              DEFINE LISTENER (Listener Name) TRPTYPE

                                                                                         (TCP)  PORT (Port Number)                        

Start a Listener                     -             RUNMQLSR -t (TCP)

End Listener                         -             ENDMQLSR

Start Trigger Monitor            -              RUNMQTRM -q(Initiation Queue Name)

                                                                              -m(Queue Manager Name)

Run Dead-Letter Queue Handler                      -          RUNMQDLQ

Start Trace                                                           -          STRMQTRC

End Trace                                                            -          ENDMQTRC

Display Formatted Trace                                     -           DSPMQTRC

HTTP Nodes

HTTP  :

HTTPInput node :

Use the HTTPInput node to receive an HTTP message from an HTTP client for processing by a message flow.

-When the HTTPInput node receives a message from a web service client, the node starts the appropriate parsers to interpret the headers and the body of the message, and to create the message tree that is used internally by the message flow. The node creates a unique identifier for the input message and stores it as a binary array of 24 bytes in the local environment tree at LocalEnvironment.Destination.HTTP.RequestIdentifer. This value is used by the HTTPReply node, therefore you must not modify it.

- HTTP messages are always non-persistent, and have no associated order.

- HTTP uses port 7080, which is the default HTTP port for the broker-wide listener.

- The default port numbers for the embedded execution group listener are 7800 for HTTP and 7843 for HTTPS.

Note:You can change these port numbers, and port ranges used by the execution group listeners, by using the mqsichangeproperties command.

Terminals:

Failure : The output terminal to which the message is routed if an error occurs(If message validation fails).If you have not connected the Failure terminal, the message is discarded, the Maximum client wait time expires, and an error is returned to the client.

Out : The output terminal to which the message is routed if it is successfully retrieved.

Catch : The output terminal to which the message is routed if an exception is thrown downstream and caught by this node.If you have not connected the Catch terminal, the message is discarded, the Maximum client wait time expires, and an error is returned to the client.

HTTP Timeout : The output terminal to which a timeout fault message is routed if the HTTP Reply node that is connected to the Out terminal does not respond within the time interval specified by the Maximum client wait time property.

Path suffix for URL :

This property identifies the location from where web service requests are retrieved. Do not use the full URL. If the URL that you want is http://hostname[:port]/[path], specify either /path or /path fragment/* where * is a wildcard that you can use to mean match any.

Use HTTPS :This property identifies whether the node is to accept secure HTTP. If the node is to accept secure HTTP, select the check box.

Advanced Properties:

• Set destination list : This property specifies whether to add the method binding name to the route to label destination list. If you select this check box, the method binding name is added so that you can use a RouteToLabel node in the message flow after the HTTPInput node.

• Label prefix : The prefix to add to the method name when routing to label. Add a label prefix to avoid a clash of corresponding label nodes when you include multiple WebSphere Message Broker input nodes in the same message flow. By default, there is no label prefix, therefore the method name and label name are identical.
• Parse Query String  : This property causes any query string that is present with an incoming message to be parsed and decoded (according to http://tools.ietf.org/html/rfc3986) into the following location in the local environment as a series of name-value elements that match the names and values present in the query string:

          LocalEnvironment.HTTP.Input.QueryString

         For example, for this query string:

         ?myParam1=my%22Value%221&myParam2=my%22Value%222

           -The following elements are placed into the local environment under the QueryString

            folder:

            - myParam1 with a value of my"Value"1

            - myParam2 with a value of my"Value"2

• Decompress input message :This property indicates whether an inbound HTTP request is decompressed or not.If this option is selected, and the HTTP header Content-Encoding field is "gzip" or "deflate", the input message is decompressed and propagated to the Out terminal, and the Content-Encoding field is removed.

Note : “Input Message Parsing” , “Parse Options” & Validation for HttpInput node are same as MQInput node.

Error handling properties :

• Maximum client wait time (sec) : Default(180)

           -The length of time, in seconds, for which the TCP/IP listener that received the input message from the web service client waits for a response from the HTTPReply node in the message flow. The valid range is zero (which means a short wait) through (231)-1. If a response is received within this time, the listener propagates the response to the client. If a response is not received in this time, a fault message is generated indicating that the timeout has expired. This fault message is either sent by the listener or timeout terminal processing.

• Fault format : The format of any HTTP errors that are returned to the client. Valid values are SOAP 1.1, SOAP 1.2, and HTML.

HTTP Reply node :

--Use the HTTPReply node to return a response from the message flow to an HTTP client. This node generates the response to an HTTP client from which the input message was received by the HTTPInput node, and waits for confirmation that it has been sent.

-The HTTPReply node constructs a reply message for the Web service client from the entire input message tree, and returns it to the requester. If the message was initially received by an HTTPInput node in another message flow, the response is associated with the reply by a request identifier that is stored in the local environment of the message by the HTTPInput node.

Terminals:

Failure : The output terminal to which the message is routed if a failure is detected when the message is propagated.

In : The input terminal that accepts a message for processing by the node.

Out : The output terminal to which the message is routed if it has been propagated successfully, and if further processing is required in this message flow.

Ignore transport failures : Select Ignore transport failures if you want transport-related failures to be ignored (for example, if the client is disconnected). If you clear the check box, and a transport-related error occurs, the input message is propagated to the Failure terminal. If you clear the check box, you must supply a value for Reply send timeout (sec).

Reply send timeout (sec) :Set the Reply send timeout (sec) value if you are not ignoring transport failures. This property specifies the length of time, in seconds, that the node waits for an acknowledgment that the client has received the reply. If the acknowledgment is received within this time, the input message is propagated through the Out terminal to the rest of the message flow, if it is connected. If an acknowledgment is not received within this time, the input message is propagated through the Failure terminal, if it is connected. If the Failure terminal is not connected, and an acknowledgment is not received in time, an exception is generated.

Generate default HTTP headers from reply or response : Select Generate default HTTP headers from reply or response if you want the default Web service headers to be created using values from the HTTPReplyHeader or the HTTPResponseHeader. If the appropriate header is not present in the input message, default values are used.

HTTP Request node :

-The HTTPRequest node interacts with a web service, using all or part of the input message as the request that is sent to that service.You can also configure the node to create an output message from the contents of the input message, augmented by the contents of the web service response/reply, before you propagate the message to subsequent nodes in the message flow.

-Depending on the configuration, this node constructs an HTTP or an HTTP over SSL (HTTPS) request from the specified contents of the input message, and sends this request to the web service. The node receives the response from the web service, and parses the response for inclusion in the output tree. The node generates HTTP headers if they are required by your configuration.

Note: You can use this node in a message flow that does or does not contain an HTTPInput or HTTPReply node because provider flow can be anywhere but only WebService URL to access that provider is required.

- An HTTP request has two parts:

1. The URL of a service.
2. A stream of data that the remote server processes, then sends back a response, which is often a SOAP or other web service message in XML.The data must be in CCSID 1208 format for most requests.

Terminals:

Out : If the request is successful, the HTTPResponse is inserted into the front of the message tree, the reply placed in the specified location in the tree, and the request propagated to the Out terminal.

Failure :If the HTTPRequest node is not able to issue the request, an ExceptionList is inserted into the message tree and the tree is propagated to the Failure terminal.You can specify a timeout interval, so that if the request takes longer than the specified duration, the request is propagated to the Failure terminal with an appropriate message

Error : If the request is sent successfully by the HTTPRequest node, but the web service is not successful, the HTTPResponse is inserted into the message tree, and propagated to the Error terminal.

Handling errors :

The node interacts directly with an external service using TCP/IP; it can, therefore, experience the following types of error:

- Errors that are generated by TCP/IP, for example no route to host or connection refused.

If the node detects these errors, it generates an exception, populates the exception list with the error information that is received, and routes the input message unchanged to the Failure terminal.

-Errors that are returned by the web server. These errors are represented by HTTP status codes that are outside the range 100 - 299. If the node detects these errors, it routes the reply to the Error terminal while following the properties specified on the Error tab.

Replace input with error : For the web service error message to be included in the output message with part of the input message content, clear Replace input with error and set the Error message location property(say,OutputRoot.XMLNSC.ABC.DEF).

HTTP Response Codes :

100 - 'continue' response

200 - success

300 - redirection

HTTP Settings :

• HTTP(S) proxy location : set the location of the proxy server to which requests are sent.
• Follow HTTP(S) redirection : To specify how the node handles web service responses that contain an HTTP status code of 300 to 399(If you select the check box, the node follows the redirection that is provided in the response, and reissues the web service request to the new URL (included in the message content).)
• Use Compression : To specify the compression of the content of the HTTP request.

Advanced Properties :

Use whole input message as request : For the request message to be the whole input message body, leave Use whole input message as request selected (the default setting).

Request message location in tree : For the request message to contain a subset of the input message, clear Use whole input message as request and set the Request message location in tree property(Say,InputRoot.XMLNSC.ABC).

Replace input message with web-service response : For the whole web service response message to be propagated as the output message, leave Replace input message with web-service response selected (the default setting).

Response message location in tree : For the web service response message to be included in the output message with part of the input message content, clear Replace input message with web-service response and set the Response message location in tree property(say,OutputRoot.XMLNSC.ABC.DEF).

Accept compressed responses : Select the Accept compressed responses by default property to indicate whether the request accepts compressed responses. If you select this option, the request can receive responses with a Content-Encoding of gzip or deflate.