WMB Best Practices and ESQL Development Guidelines

Integration Standards WMB Best Practices & ESQL Development Guidelines v1.0 April 7, 2016

Printed on 4/7/2016

WMB Best Practices & ESQL Development Guidelines

Document History Revision History Version No.

0.1 1.0

Revision Date

Summary of Changes

Revision By

22-May-07 01-Dec-07

First Draft version First Released version

Santanu Mitra Santanu Mitra

Document Distribution List Name

Document: 312640737.doc

Title

Printed on 4/7/2016

Date: 4/7/2016 Page 2 of 24


Table of Contents 1

Introduction................................................................................................................ 5

2

Purpose..................................................................................................................... 5

3

Assumptions.............................................................................................................. 5

4

WebSphere MB Best Practices..................................................................................6

5

4.1

Understand the Interface Business Requirement.............................................................6

4.2

Interface Design Layout.................................................................................................... 6

4.3

Common Characteristics of Interfaces..............................................................................6

4.4

Common Error/Exception Handling..................................................................................6

4.5

Common Control Handling............................................................................................... 6

4.6

Common Routines and Sub-Flows...................................................................................7

4.7

Location of Integration Logic............................................................................................ 7

4.8

Understand the Input and Output Messages....................................................................7

4.9

Determine how many Message Flows are needed...........................................................7

4.10

Complete the processing with as few nodes as possible..........................................8

4.11

Message Routing within a flow..................................................................................8

4.12

SubFlows................................................................................................................... 8

4.13

Transactionality and Persistence...............................................................................8

4.14

Use of CARDINALITY............................................................................................... 9

4.15

ESQL REFERENCE Variable....................................................................................9

4.16

Use of MOVE & LASTMOVE.....................................................................................9

4.17

Backout Processing Considerations........................................................................10

4.18

WBI MB Message Flow Standard Unit Test Conditions...........................................10

ESQL Development Guidelines................................................................................11 5.1

Line Length..................................................................................................................... 11

5.2

Wrapping Lines............................................................................................................... 11

5.3

Comments...................................................................................................................... 11

5.4

Flower Box Comments.................................................................................................... 11

5.5

Block Comments............................................................................................................ 13

5.6

Internal and End-of-Line Comments...............................................................................13

5.7

Comments for CR/DR..................................................................................................... 13

5.8

Statements..................................................................................................................... 14

5.8.1

WHILE Statement.................................................................................................... 14


Printed on 4/7/2016

Date: 4/7/2016 Page 3 of 24

WMB Best Practices & ESQL Development Guidelines 5.8.2

IF Statement............................................................................................................ 14

5.8.3

CASE Statement..................................................................................................... 15

5.8.4

SELECT Statement.................................................................................................15

5.8.5

INSERT Statement.................................................................................................. 15

5.8.6

Update Statement................................................................................................... 16

5.8.7

DELETE Statement.................................................................................................16

5.9

6

Declarations.................................................................................................................... 16

Appendix 1. Important Structures............................................................................17 6.1

MQMD – Message Descriptor........................................................................................ 17

6.2

MQRFH2 – Rules and Formatting Header.....................................................................18

6.3

Properties – Message Flow............................................................................................ 18

7

Appendix 2. Debug Tracing......................................................................................20

8

A Sample ESQL.......................................................................................................21


Printed on 4/7/2016

Date: 4/7/2016 Page 4 of 24


1 Introduction This document presents some findings and recommendations concerning the use of WebSphere Message Broker (WMB). These findings were developed from previous experiences of various integration projects. It covers the aspects of WMB development encompassing Message Set guidelines, Message Flow guidelines and Deployment guidelines.

2 Purpose This document can serve as a baseline for Integration Projects using WMB with a little or no modifications as appropriate.

3 Assumptions 

Prior working level of knowledge on WMB for the readers of this document.


Printed on 4/7/2016

Date: 4/7/2016 Page 5 of 24


4 WebSphere MB Best Practices 4.1 Understand the Interface Business Requirement The first key to successful Technical Design and Interface Development is a firm knowledge of the intended business logic and structure. The source to the Business Requirement is to acquire a firm and defined Functional Specification. To some degree, the Functional Specification may undergo a few minor amendments while the task of Technical Specification is underway. However, it is expected that the Functional Specification has defined all the messages, components and processes that are required for the interface before the commencement of Technical Design. There may be (and usually is) multiple components to each interface, and in WMB terms, that boils down to Adapters and Message Flows. The Functional Specification is used to define where the Business Requirements are met in terms of various components. It is required to consider the various options available and create a design that is based on workable functionality of WMB, and to hopefully end up with a reasonable level of maintainability and performance as well.

4.2 Interface Design Layout The WMB interfaces to be implemented should have certain common characteristics so that reuse of components is optimized. The design of the message flows should adhere to one or more of the pattern templates laid out for the project. However, there can be cases where the requirement of the interface is such that there is no pattern template matching to lay out the design of that interface. This case is to be treated as a special case and a new pattern template for this case should be first designed before moving for the technical design of the interface. However, occurrence of such event is least expected and if occurs, definition of a new pattern template should be the last chosen option.

4.3 Common Characteristics of Interfaces Each WMB Interface designed for the project should have certain characteristics which are assumed to be common across all implemented interfaces. These common characteristics generally refer to some common routines and flows or sub-flows which are recommended to be used globally across all interfaces in that project.

4.4 Common Error/Exception Handling Each WMB interface shall have a mechanism to handle errors/exceptions. These errors/exceptions refer to both Systems generated ones as well as User Generated ones. It is recommended to have a common Error Handling Procedure/Module to handle all errors/exceptions that arise during execution of an interface. This Procedure/Module shall be extensively re-used by all interfaces of the project.


Printed on 4/7/2016

Date: 4/7/2016 Page 6 of 24


4.5 Common Control Handling The flow of each message through a WMB interface should be ‘Control Handled’ for audit and logging purpose. For this reason, there is a requirement to have a ‘Control Mechanism’ in each and every message flow. It is recommended to have a ‘Common Control Handling’ routine/component that can be re-used extensively by all interfaces of the project.

4.6 Common Routines and Sub-Flows Many message flows require to perform certain functions like re-formatting a date, getting current date, getting a unique identifier, etc which can be laid down as common routines for re-use by the interfaces. Also there may be certain processes like adding an RFH2 header to a message, etc which can also be laid down as common sub-flows for re-use by interfaces. It is recommended to have a set of common routines and sub-flows for the project that can be extensively re-used by the interfaces. The contents of this set cannot be defined exhaustively at any point of time. This set shall continually be gaining members from time to time in order to meet requirements.

4.7 Location of Integration Logic An interface is comprised of Adapters and Message Flows. The integration logic is therefore contained wholly within the adapters and message flows. The interface designer has the option of building any required integration logic in the adapters or message flows. It is recommended that as much business logic as possible will be performed in message flows rather than in the adapters. In essence logic is only to be located in adapters if that is the only option for the interface. In other words, adapters will me made as simple as possible. The core of the business logic should be kept in WMB. The benefits of this approach are: 

The interface solutions are pattern based, with the patterns represented by WMB message flows. Being pattern based, enhances reusability.



Scaling and resilience is simplified with fewer complex components to consider.



Maintenance requirements are easier to define and execute, by largely avoiding the need to change one component to accommodate the change in another requirement.

4.8 Understand the Input and Output Messages It should be possible to examine the Functional Specification and determine what the input is, and what the output would be. From this standpoint, the WMB message flow can be regarded as the instrument to convert the input message into output message(s).

4.9 Determine how many Message Flows are needed First, at an application system level, it must be determined how many flows will be required for an interface. Out of this determination, it will be possible to determine how many types of messages a flow must handle. Sometimes there is just one input message format and one output message Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 7 of 24

WMB Best Practices & ESQL Development Guidelines format. Other times there are multiple messages for a given flow and there must be logic within the flow to sort them out.

4.10 Complete the processing with as few nodes as possible The most fundamental recommendation to consider when writing a message flow is to be as concise as possible. There is a performance cost associated with passing through each of the built-in nodes of WMB, so it is best to write message flows in as few nodes as possible. In other words, there should be as few as possible nodes between the input and output formats for a given business requirement.

4.11 Message Routing within a flow Routing within the flow should be minimized where possible. When routing is required, then the rule about minimizing the number of nodes still applies. For a number of reasons, some message flows process more than one message type. In this case it is important to understand the distribution of these different message types and their importance so that the most critical or most frequent messages go through the cheapest path; i.e. the one with fewest and simplest nodes. When more than one message is processed in a flow, or when there is a requirement for dynamic logic inside a flow based on message content, there are at least two ways to direct the processing logic inside the flow. One way is through the use of RouteToLabel node. This node makes use of the DestinationList array of the LocalEnvironment Tree of a message flow. When the message arrives, the DestinationList is populated according to the business requirement, and the RouteToLabel and Label nodes are used to direct subsequent processing. Another way is by using the Filter node. One or more Filter nodes each containing a Boolean filter expression is set up to control logic branches. If there are many possible paths, the Filter nodes can be set up a cascade, with the most common message being directed along the earliest path. The cost of using the DestinationList is in setting it up in the first place. The cost of using Filters is in the number of nodes that are required for a complex routing evaluation. Once again, it is important to minimize the number of routing within a message flow as much as possible. Another variation of the routing theme is to deliver data to one or more destination systems. In this scenario, it is desirable to allow the target systems to be identified through a database table driven mechanism.

4.12 SubFlows Message Flows can be divided into SubFlows. Essentially, a Subflow is a sequence of nodes that begins with an Input node and ends in an Output node. The object of using SubFlows is that functions can be logically segregated in a way that increases maintainability and offers opportunity for reuse. The use of SubFlows does not have an adverse performance effect because at deploy time they are simply folded into the flow to which they are assigned. It is important that the overall number of nodes should not be increased when using SubFlows.


Printed on 4/7/2016

Date: 4/7/2016 Page 8 of 24


4.13 Transactionality and Persistence As a rule, all messages should be handled as a Transaction. In the Input, Output and Compute nodes, the Transactionality should always be set to ‘Automatic’. This practice allows the WebSphere MQ message attributes to control the Transactionality of the flow; i.e. if a message is read from a queue with an attribute of ‘Persistent’, WBI MB will supply the appropriate logging to guarantee the Transactionality of the message. It must be considered that overhead is incurred by making a message transactional. This is caused by the need to save all the data necessary to enable a roll back should a failure condition occur in a message flow.

4.14 Use of CARDINALITY The use of the CARDINALITY function should be restricted to minimum as it requires the parser to process a large portion of the message and thereby hindering performance. This built-in function should not be used inside a loop unless it is inevitable.

4.15 ESQL REFERENCE Variable Reference variables can be used in ESQL to store a part or an entire message tree location and can be thought of as being similar to a C/C++ pointer to a message element. The use of REFERENCE variable is recommended in ESQL for two reasons: 

They reduce coding considerably, especially, when large message tree with deep hierarchy has been defined.



They make ESQL more efficient when used appropriately.

Reference variables can be used to store locations in any type of message tree (InputRoot, OutputRoot, LocalEnvironment, Environment, etc).

4.16 Use of MOVE & LASTMOVE The MOVE and LASTMOVE combination should be used to move the reference through repeating instance of fields/subtree. This is because MOVE and LASTMOVE index the message tree in a relative fashion as opposed to the explicit way in which incremented indices reference the field. For example, the following code checks that the source reference is on a field that exists and then maps across values from the source to the target, creating new fields in the target as it increments through the field repetitions. WHILE LASTMOVE(refSourceReference) DO SET refTargetRef.intItemNumber = refSourceReference.intItem; SET refTargetRef.chrDescription = refSourceReference.chrDesc; SET refTargetRef.fltPrice = refSourceReference.fltPrice; CREATE NEXTSIBLING OF refTargetRef AS refTargetRef REPEAT; MOVE refSourceReference NEXTSIBLING REPEAT TYPE NAME; END WHILE; Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 9 of 24


4.17 Backout Processing Considerations When an exception is thrown within a message flow and is not caught by inclusion of a TryCatch Node, the Input Node for the message flow catches it. If the catch terminal of the MQInput Node is connected, the message is propagated to this terminal and is processed according to the message flow logic. If the Catch terminal of the MQInput Node is not connected, the transaction is rolled back. If the message was read under a syncpoint the original remains in the queue but WebSphere MQ increments the MQMD Backout count. The MQInput Node then reads the message once again. The MQMD Backout count is examined before the message is processed again. If it is not zero then it implies that the message received by the message flow is a backed out message and the broker then performs backout processing. 

If the MQMD backout count is less than the Backout Requeue Threshold attribute specified in the queue definition, the message is propagated through the output terminal of the MQInput Node for normal processing once again.



If the MQMD backout count is not less than the Backout Requeue Threshold attribute specified in the queue definition, the message is propagated through the Failure terminal of the MQInput Node.



If the Failure terminal is wired then it follows that path but, if it is not wired the Backout Requeue Name attribute is looked for and if a queue name found in this attribute, the message is put into that queue.



If no name is specified in the Backout Requeue Name attribute, the message is written to the default Dead Letter Queue defined for the queue manager.



If the message could not be written to the Dead Letter Queue then it remains in the Input Queue.

4.18 WBI MB Message Flow Standard Unit Test Conditions The following are the standard Unit Test Conditions that should be considered by a developer while performing Unit Test on an interface: 

When a valid Test Data is passed through the message flow one or more output message(s) are created on the output queue(s).



When an invalid Test Data is passed through the message flow the message is propagated to the error handling queue for error processing.



All fields of the output message are of correct length and have been correctly formatted according to the requirements.



Where the message has repeating fields or structure, the interface works correctly for both single and multiple instances of the field or structure.



Where the message has an optional field or structure, the interface works correctly for both with and without the field or structure in the message.


Printed on 4/7/2016

Date: 4/7/2016 Page 10 of 24


5 ESQL Development Guidelines 5.1 Line Length It must be possible to view an ESQL file in a text editor without scrolling to the right, and to print it out on A4 portrait without line truncation or wrapping. In practice, this means to limit a line to a boundary of 80 characters approximately.

5.2 Wrapping Lines When an expression does not fit in a single line, it is recommended to break the line according to the following general principles: 

Break after a comma.



Break after an operator.



Prefer high-level breaks to low-level breaks.



Align the new line with the beginning of the same level expression on the previous line.

If the above rules lead to confusing code or code that is squished up against the right margin, just indent 8 spaces instead.

5.3 Comments The ESQL code should be self-explanatory as far as possible, for example, by use of meaningful variable names. Comments are needed however, to explain what the ESQL code is doing or why is it doing that way if it is not obvious. Underlying assumptions should be noted. Use the more standard format ‘/* Comment */’ for headers and multi-line comments, and ‘-Comment’ for single line comments. Comments must be in coherent grammatical English starting with a capital letter. Multiline comments should be above the line of codes they refer to, indented to the same level as the code itself and be preceded by a blank line. A comment is recommended for each logic construct as this tends to be where business logic can be explained.

5.4 Flower Box Comments Flower box comments must appear at the start of every ESQL File, Module, Procedures and Functions. The format should be as follows: ESQL File Flower Box /********************************************************************** * COPYRIGHT. . ALL RIGHTS RESERVED. NO PART OF * * THIS SOURCE CODE MAY BE REPRODUCED, STORED IN A RETRIEVAL SYSTEM, OR* * TRANSMITTED, IN ANY FORM BY ANY MEANS, ELECTRONIC, MECHANICAL, PHOTO* Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 11 of 24

WMB Best Practices & ESQL Development Guidelines * COPYING, RECORDING OR OTHERWISE, WITHOUT THE PRIOR WRITTEN PERMISS * * ION OF * **********************************************************************/ ESQL Module Flower Box /********************************************************************** * Node Name:

*

* Module Name:

*

* Description:

*

* * * Version

Date

Author

Description *

* =============== =========== =========== ============================* *
*

* Input Parameters:

*

* Output Parameters: * * Description: * * Version

Date

Author

Description *

* =============== =========== =========== ============================* *

Printed on 4/7/2016

Date: 4/7/2016 Page 12 of 24


5.5 Block Comments Block comments inside a statement should be indented to the same level as the code they describe. A block comment should be preceded by a blank line to set it apart from rest of the code. /* * Here is a block comment * They are usually more than one line long * and are preceded by a blank line. */ SET OutputRoot.XML.MyMsg.MyFld01 = ‘Value01’; SET OutputRoot.XML.MyMsg.MyFld02 = ‘Value02’; SET OutputRoot.XML.MyMsg.MyFld03 = intValue03;

5.6 Internal and End-of-Line Comments Short comments can (if needed) appear on a single line indented to the level of the code that follows. A blank line should precede a single line comment. Set Customer First Name SET OutputRoot.XML.MyMsg.Customer.FirstName = ‘John’;

Set Customer Last Name SET OutputRoot.XML.myMsg.Customer.LastName = ‘Smith’; Very short comments can (if needed) appear on the same line of the code they describe, but should be shifted far enough to the right to separate them from the statement. If more than one short comment appears in a chunk of code, they should all be indented to the same tab setting. IF InputRoot.XML.MyMsg.Agent.Dept = TOUPPER(‘SALES’) THEN SET OutputRoot.XML.MyMsg.BooleanValue = ‘FALSE’; Expected Case ELSE SET OutputRoot.XML.MyMsg.BooleanValue = ‘TRUE’; Special Case END IF;

5.7 Comments for CR/DR Besides comments about Change Request or Defect Fix, at the beginning in the flower box, it is necessary to mention the CR Number or Defect Number at the site of the change. A good practice is to comment out the earlier code statement(s) and write the new statement(s). In time when the code becomes clumsy the old commented codes may be deleted. CR02577 Start Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 13 of 24

WMB Best Practices & ESQL Development Guidelines /* Old Code stuff..... ..... */ New Code stuff ..... ..... CR02577 End

5.8 Statements Logical constructs such as WHILE, IF, CASE and database actions should have a comment preceding them. These are the blocks where business logic is performed and the most likely stumbling point when it comes to others trying to understand the code. Specific rules applying to these statements follows, although in many circumstances, common sense will prevail.

5.8.1 WHILE Statement /* * Comment here for the * WHILE Block */ WHILE DO END WHILE;

5.8.2 IF Statement /* * Comment here for the * IFELSE Block */ IF THEN ELSEIF THEN ELSE END IF;


Printed on 4/7/2016

Date: 4/7/2016 Page 14 of 24


5.8.3 CASE Statement /* * Comment here for the * CASE Statement Block */ SET intGrade = CASE InputRoot.MyMsg.Student.Marks WHEN > 90 THEN 1 WHEN > 80 THEN 2 WHEN > 70 THEN 3 WHEN > 60 THEN 4 ELSE 5 END;

5.8.4 SELECT Statement /* * Comment here for the * INSERT Block */ SET Environment.Variables.EmpList[] = (SELECT

T.EMP_ID, T.EMP_NAME, T.EMP_AGE

FROM

Database.Schema.T_EMP AS T

WHERE

T.EMP_ID IN (1, 2, 3, 4, 5)

AND

T.EMP_AGE >= intMinAge);

5.8.5 INSERT Statement /* * Comment here for the * INSERT Block */ INSERT INTO

Database.Schema.T_EMP (EMP_ID, EMP_NAME, EMP_AGE)

VALUES Document: 312640737.doc

intEmp_ID, Printed on 4/7/2016

Date: 4/7/2016 Page 15 of 24

WMB Best Practices & ESQL Development Guidelines chrEmpName, intEmpAge;

5.8.6 Update Statement /* * Comment here for the * Update Block */ Update

Database.Schema.T_Emp AS T

SET

EMP_NAME = chrEmpName, EMP_AGE = 25

WHERE

T.EMP_ID = intEmpID;

5.8.7 DELETE Statement /* * Comment here for the * DELETE Block */ DELETE FROM Database.Schema.T_EMP AS T WHERE

T.EMP_ID = intEmpId;

5.9 Declarations Declaration of variables must be made at the beginning of each and every procedure or function. One variable declaration per line is recommended. Declarations should be grouped by variable type and a blank line should separate two groups. Also it is recommended not to initialize a variable during declaration. Initialization should be explicitly made as a separate assignment statement. DECLARE intVar01

INTEGER;

DECLARE intVar02

INTEGER;

DECLARE intVar03 INTEGER;

DECLARE chrVar01

CHARACTER;

DECLARE chrVar02

CHARACTER;

DECLARE blnVar01

BOOLEAN;


Printed on 4/7/2016

Date: 4/7/2016 Page 16 of 24


6 Appendix 1. Important Structures 6.1 MQMD – Message Descriptor The MQMD structure contains the control information that accompanies the application data when a message travels from one queue to another. The following table summarizes the fields in the structure: Field

Description

StrucId

Structure Identifier

Version

Structure Version Number

Report

Option for report messages

MsgType

Message Type

Expiry

Message Lifetime

Feedback

Feedback or Reason Code

Encoding

Numeric Encoding of message Data

CodedCharSetId

Character set identifier of message data

Format

Format name of message data

Priority

Priority of message data

Persistence

Message Persistence

MsgId

Message Identifier

CorrelId

Correlation Identifier

BackoutCount

Backout Counter

ReplyToQ

Name of Reply Queue

ReplyToQMgr

Name of Reply Queue Manager

User Identifier

User Identifier

AccountingToken

Accounting Token

ApplIdentityData

Application data relating to identity

PutApplType

Type of application that put the message

PutApplName

Name of application that put the message

PutDate

Date when message was put

PutTime

Time when message was put

ApplOriginData

Application Data relating to origin

GroupId

Group Identifier

MsgSeqNumber

Sequence number of logical message within group

Offset

Offset of data in physical message from the start of logical message

MsgFlags

Message Flags


Printed on 4/7/2016

Date: 4/7/2016 Page 17 of 24


OriginalLength

Length of original message

6.2 MQRFH2 – Rules and Formatting Header The MQRFH2 structure defines the layout of the rules and formatting header. This header can be used to send string data that has been encoded using an XML-like syntax. It allows Unicode string to be transported without translations, and it can carry numeric data-types. The following table summarizes the fields in the structure: Field

Description

StrucId

Structure Identifier

Version

Structure Version Number

StrucLength

The total length of MQRFH2

Encoding

Numeric encoding of data

CodedCharSetId

Character set identifier of data

Format

Format Name of data

Flags

Flags

NameValueCCSID

Character set identifier of Name Value data

NameValueLength

Length of Name Value data

NameValueData

Name/Value data

6.3 Properties – Message Flow The Properties tree is the first element of the message tree and holds information about the characteristics of the message. Field

Description

MessageSet

The Message Set Identifier

MessageType

The Message Name

MessageFormat

The Physical format of the message

Encoding

Numeric encoding of data

CodedCharSetId

Character set identifier of data

Transactional

Boolean flag to indicate Transactionality

Persistence

Boolean flag to indicate Persistency

CreationTime

The put time of the message

ExpirationTime

The expiration time of the message

Priority

The priority of the message


Printed on 4/7/2016

Date: 4/7/2016 Page 18 of 24

WMB Best Practices & ESQL Development Guidelines ReplyIdentifier

The reply identifier

ReplyProtocol

The reply protocol


Printed on 4/7/2016

Date: 4/7/2016 Page 19 of 24


7 Appendix 2. Debug Tracing In the event of an error in a message flow, a debug level trace should be set up for the flow. This is done with the help of a set of commands to generate the debug trace file for diagnostics. 1. Open a command prompt window. 2. Execute the command mqsichangetrace -u -e -l debug -f -r 3. Execute the message flow with the message that you want to trace. 4. Execute the command mqsireadlog -u -e -l debug -f -o 5. Execute the command mqsiformatlog –i -o Open the generated Trace file in a text editor and perform diagnosis.


Printed on 4/7/2016

Date: 4/7/2016 Page 20 of 24


8 A Sample ESQL /********************************************************************** * COPYRIGHT. ALL RIGHTS RESERVED. NO PART OF * * THIS SOURCE CODE MAY BE REPRODUCED, STORED IN A RETRIEVAL SYSTEM, OR* * TRANSMITTED, IN ANY FORM BY ANY MEANS, ELECTRONIC, MECHANICAL, PHOTO* * COPYING, RECORDING OR OTHERWISE, WITHOUT THE PRIOR WRITTEN PERMISS * * ION OF * **********************************************************************/

/********************************************************************** * Node Name:

Get Employee Information *

* Module Name:

IDD111_EmployeeInfo *

* Description:

This module searches for information about the*

* Employee from the Employee Database. * * * * Version

Date

Author

Description *

* =============== =========== =========== ============================* * 1.00 25Feb2006 A. Program The initial version. * **********************************************************************/ CREATE COMPUTE MODULE IDD111_EmployeeInfo /**************************************************************** * Function Name:

Main *

* Input Parameters:

None *

* Output Parameters: Boolean Return * * Description: The Main entrypoint to the module * * Version

Date

Author

Description *

* =============== =========== =========== ======================* * 1.00 25Feb2006 A. Program The initial version * *****************************************************************************/ CREATE FUNCTION Main() RETURNS BOOLEAN BEGIN Copy input headers to output CALL prcCopyMessageHeaders(); Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 21 of 24


Create the output message CALL prcCreateOutputMessage();

Return success RETURN TRUE; END;

/**************************************************************** * Procedure Name:

prcCopyMessageHeaders *

* Input Parameters:

None *

* Output Parameters: None * * Description: Copies input message headers to * * the output message * * Version

Date

Author

Description *

* =============== =========== =========== ======================* * 1.00 25Feb2006 A. Program The initial version * *****************************************************************************/ CREATE PROCEDURE prcCopyMessageHeaders() BEGIN DECLARE intIdx

INTEGER;

An Index Pointer

DECLARE intCounter

INTEGER;

A counter

Initialize Index to 1 SET intIdx = 1;

Initialize counter to cardinality of InputRoot SET intCounter = CARDINALITY(InputRoot.*[]);

/* Iterate through each subtree of InputRoot and copy the Subtree to the OutputRoot until the Last one. */ WHILE intIdx < intCounter DO SET OutputRoot.*[intIdx] = InputRoot.*[intIdx]; Document: 312640737.doc

Printed on 4/7/2016

Date: 4/7/2016 Page 22 of 24

WMB Best Practices & ESQL Development Guidelines SET intIdx = intIdx + 1; END WHILE; END;

/**************************************************************** * Procedure Name:

prcCreateOutputMessage *

* Input Parameters:

None *

* Output Parameters: None * * Description: Creates the Output Message * * Version

Date

Author

Description *

* =============== =========== =========== ======================* * 1.00 25Feb2006 A. Program The initial version * *****************************************************************************/ CREATE PROCEDURE prcCreateOutputMessage() BEGIN DECLARE chrEmpId

CHARACTER; Employee Id

Initialize chrEmpId SET chrEmpId = InputRoot.XML.Employee.Id;

Check that the EmpId is not NULL to proceed IF chrEmpId IS NULL THEN Throw an Exception THROW USER EXCEPTION VALUES (‘The Employee Id Received in the Input is NULL’); END IF;

Retrieve the Employee Info from Database SET OutputRoot.XML.Employee.EmployeeInfo[] = (SELECT


T.EMP_ID

AS EmployeeID,

T.EMP_NAME

AS EmployeeName,

T.EMP_DOJ

AS EmployeeDOJ,

T.EMP_CURR_SAL

AS EmployeeSalary

FROM

Database.HRM.T_EMP_MASTER AS T

WHERE

T.EMP_ID = chrEmpId); Printed on 4/7/2016

Date: 4/7/2016 Page 23 of 24


Check If any record is retrieved IF CARDINALITY(OutputRoot.XML.Employee.EmployeeInfo[]) IS NULL THEN Throw an Exception THROW USER EXCEPTION VALUES (‘Employee Number ‘ || chrEmpId || ‘ does not exist.’); END IF; END; END MODULE;


Printed on 4/7/2016

Date: 4/7/2016 Page 24 of 24

WMB Best Practices and ESQL Development Guidelines

Recommend Documents