Adding Certificates to trustore

1)Open ikeyman utility : C:\Program Files\IBM\IIB\10.0.0.9\common\jdk\jre\bin

2)Clink Open, then Browse:

3)Navigate to Truststore, then Open: (trustore.jks)

4)click OK

5) Enter pwd then click Ok

6)Select signer certificate from dropdown.

7) Lcisk Add then browse

8) Select Cert to add then click open.

9) Click Ok.

10) Enter a label then OK

11) done, new cert has been added.

Reference: https://www.ibm.com/support/knowledgecenter/en/SSKM8N_8.0.0/com.ibm.etools.mft.doc/ap34020_.htm

Create, Call procedures Websphere IBM IIB ESQL

1) CALL Schema.StatusOut (Inputroot.xmlnsc.status);

 -- Translate Status For SOAP Response
CREATE PROCEDURE StatusOut (IN ParmIn CHARACTER)
    RETURNS CHARACTER
BEGIN
        CASE ParmIn
        WHEN 'S' THEN
        RETURN 'SUCCESS';
        WHEN 'F' THEN
        RETURN 'FAILURE';
        WHEN 'P' THEN
        RETURN 'PARTIAL';
        END CASE;
END;

-- Translate SeverityLevel For SOAP Response
CREATE PROCEDURE SeverityLevelOut (IN ParmIn CHARACTER)
    RETURNS CHARACTER
BEGIN
        CASE ParmIn
        WHEN 'S' THEN
        RETURN 'CRITICAL';
        WHEN 'E' THEN
        RETURN 'ERROR';
        WHEN 'W' THEN
        RETURN 'WARNING';
        WHEN 'I' THEN
        RETURN 'INFORMATIONAL';
        END CASE;
END;

-- Translate SeverityLevel To Status For SOAP Response
CREATE PROCEDURE SeverityLevelToStatusOut (IN ParmIn CHARACTER)
    RETURNS CHARACTER
BEGIN
        CASE ParmIn
        WHEN 'S' THEN
        RETURN 'FAILURE';
        WHEN 'E' THEN
        RETURN 'FAILURE';
        WHEN 'W' THEN
        RETURN 'SUCCESS';
        WHEN 'I' THEN
        RETURN 'SUCCESS';
            ELSE
        RETURN 'SUCCESS';
        END CASE;
END;


2)
DECLARE inputref REFERENCE TO InputRoot.JSON.Hdr;
DECLARE outputref REFERENCE TO OutputRoot.SOAP.Hdr;

CALL TRReqHdr_JSONtoSOAP(inputref, outputref );

CREATE PROCEDURE TRReqHdr_JSONtoSOAP(IN InRef_trReqHdr REFERENCE, INOUT OutRef_TRReqHdr REFERENCE) BEGIN

------------
field level mapping
------------

END;

3) calling db/java procedures..
step1) create procedure in the esql module,

CREATE PROCEDURE DoSomething (IN in1 CHARACTER, OUT out1 CHARACTER, OUT out2 CHARACTER)
    LANGUAGE DATABASE
    EXTERNAL NAME "DoSomething";

step2) Now you can call this function from your ESQL main function as below:

CALL DoSomething(in,out1,out2) IN Database.yourschema.yourDB;

How to call jar using ESQL and Java Compute Node in IIB/WMB

Compute Node in IIB :-

The Compute node is contained in the Transformation drawer of the palette. The following can be achieved using the Node:-

• Transformation of message
• Routing
• Build a new message

Java Compute Node in IIB

The JavaCompute node is contained in the Transformation drawer of the palette. The following can be achieved using the Node:-

• Use Java to examine an incoming message and, depending on its content, propagate it unchanged to one of the two output terminals of the node. The node behaves in a similar way to a Filter node, but uses Java instead of ESQL to determine which output terminal to use.
• Use Java to change part of an incoming message and propagate the changed message to one of the output terminals.
• Use Java to create and build a new output message that is independent of the input message.
• Use Java to create a map in a global cache, and to add and retrieve data from that map. By storing data in the global cache, that data is available to other JavaCompute nodes or message flows.

How to consume jar using ESQL and Java Compute Node

1. Switch to java View :-
2. 
3. File => New => Java Project
4. Add jar to Libraries(External Libraries)

5.Add the JAR files to the following directory:

• ForWindows

workpath\shared-classes

• ForLinux, UNIX and z/OS

workpath/shared-classes

Example :-  C:\ProgramData\IBM\MQSI\shared-classes

Note : Jar files can also be placed in specific folders to be accessed by particular broker.

6. Add reference of the java project to the Application

7. Restart the broker.

The above steps are common for both

How to call the function from COMPUTE NODE :-

1. Now in the compute node, create a method which will refer to the method in jar.

CREATE FUNCTION Multiply(IN input1 INTEGER, IN input2 INTEGER)    RETURNS INTEGER       LANGUAGE JAVA EXTERNAL NAME “sample.calculate.Result”;

2. Now call the function from the main finction :-

SET Multresult = Multiply(2,3);

How to call the function from JAVA COMPUTE NODE :-

1. Import the class(for example Result Class in this case)

  import sample.calculate.Result;

2. Now create an object of the class to call the method.

Result obj1 = new Result();

String Res = obj1. Multiply(2,3);

Convert Integer to date ESQL

DECLARE source INTEGER 20150401;
--DECLARE target CHARACTER;
--DECLARE pattern CHARACTER '00000000';
SET OutputRoot.XMLNSC.target = CAST(CAST(CAST(source AS CHARACTER FORMAT '00000000')AS DATE FORMAT 'yyyyMMdd')AS CHARACTER FORMAT 'yyyy-MM-dd') ||'T00:00:00.000';