|
home /
infca / mb_esql
(navigation links)
|
Pase lo que pase, no pierdas la inocencia.
|
MB ESQL
Consejos / tips
Los nombres de las colas deben ser independientes del entorno
La manera más barata de escribir a un fichero desde un flujo de mensajes es con un nodo TRACE.
Pero después hace falta un programa/script para re-escribir el fichero en el formato deseado.
ESQL
code conventions
Embedding keywords
The configuration manager scans through each file looking for
a predefined eye catcher that describes each keyword and its value.
$MQSI keyword = value MQSI$
Each time one of these strings appears in the file being deployed,
it is associated with the CM's record for that file.
When browsing the list of deployed objects
in the Broker Administration perpective,
each keyword/value pair is displayed.
Player,
PDF.
The effect of setting a field to NULL
Take care when assigning a null value to a field.
For example, the following command deletes the Name field:
SET OutputRoot.XML.Msg.Data.Name = NULL; -- this deletes the field
The correct way to assign a null value to a field is as follows:
SET OutputRoot.XML.Msg.Data.Name VALUE = NULL; -- this assigns a NULL value to a field without deleting it
ESQL Reference, paga 165 [173/378]
shall we copy headers only ?
CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
MB Routing Table in memory
The use of the table is to get a MQ queue name based on an Event Type.
Its definition is:
DECLARE RoutingTable SHARED ROW NULL ; -- flow shared table
DECLARE Initialized SHARED BOOLEAN FALSE ; --
read the table from a MQ message
This is the way to go, of course (maybe HTTP Request ?)
The MQ message contents - with the table data - is :
<AdminMsg><Command>ReadMQmsg2Table</Command>
<row><EventType>event_TPV</EventType><QueueName>cua_A1</QueueName><QueueName>cua_B1</QueueName></row>
<row><EventType>Evento_Cajero</EventType><QueueName>QL.ADE.cola_10</QueueName><QueueName>QL.ADE.cola_25</QueueName></row>
</AdminMsg>
The ESQL code to put it in memory is :
set RoutingTable.DFDL.EventQueues.row[] = InputRoot.XMLNSC.AdminMsg.row[] ;
set Initialized = TRUE ;
The table we get in memory is :
set OutputRoot.XMLNSC.rsp.id = 'MBA-R005 - Routing table in memory. ';
set OutputRoot.XMLNSC.rsp.nRows[] = RoutingTable.DFDL.EventQueues.row[] ;
<rsp><id>MBA-R005 - Routing table in memory. </id>
<nRows><EventType>event_TPV</EventType><QueueName>cua_A1</QueueName><QueueName>cua_B1</QueueName></nRows>
<nRows><EventType>Evento_Cajero</EventType><QueueName>QL.ADE.cola_10</QueueName><QueueName>QL.ADE.cola_25</QueueName></nRows>
</rsp>
And its cardinality is :
set OutputRoot.XMLNSC.rsp.id = 'MBA-R002 - Table has [' || cast( CARDINALITY( RoutingTable.DFDL.EventQueues.row[] ) as CHARACTER ) || '] rows.' ;
We can access it using a numeric index:
SET Environment.QueueList[] = RoutingTable.DFDL.EventQueues.row[CAST(InputRoot.XMLNSC.Mensaje.Evento as INTEGER)].QueueName[];
Or indexing from a MQRFH2 header field:
-- numeric
SET Environment.QueueList[] = RoutingTable.DFDL.EventQueues.row[CAST(InputRoot.MQRFH2.usr.Event as INTEGER)].QueueName[];
-- string
SET Environment.QueueList[] = SELECT T.QueueName[] FROM RoutingTable.DFDL.EventQueues.row[] AS T WHERE T.EventType = InputRoot.MQRFH2.usr.Event;
applying routing table to data message
The input MQ message contents is :
<Mensaje> <Evento> Event_Faixa </Evento> </Mensaje>
The ESQL code we apply to it is :
SET Environment.QueueList[] = SELECT T.QueueName[] FROM RoutingTable.DFDL.EventQueues.row[] AS T WHERE T.EventType = InputRoot.XMLNSC.Mensaje.Evento;
-- en tiempo de ejecucin asigno el nombre de la(s) cola(s) destino
declare cursor reference to Environment.QueueList.QueueName[>];
declare i integer 1;
while (lastmove(cursor)) do
Set OutputLocalEnvironment.Destination.MQ.DestinationData[i].queueName = cursor;
MOVE cursor NEXTSIBLING;
set i = i + 1;
end while;
RETURN TRUE;
And the result is :
=== Local Envir (MQ and MB data)
( ['MQROOT' : 0x2d0d6e80]
(0x01000000:Name):Destination = (
(0x01000000:Name):MQ = (
(0x01000000:Name):DestinationData = (
(0x03000000:NameValue):queueName = 'QL.ADE.cola_10' (CHARACTER)
)
(0x01000000:Name):DestinationData = (
(0x03000000:NameValue):queueName = 'QL.ADE.cola_25' (CHARACTER)
)
)
)
)
read the table from a BBDD
DECLARE RoutingTable SHARED ROW NULL; -- flow shared table
DECLARE Initialized SHARED BOOLEAN FALSE;
CREATE COMPUTE MODULE SetDestination
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE QueueName CHARACTER;
IF NOT Initialized THEN -- shared variable appear uninitialized
BEGIN ATOMIC -- controla el aceso concurrente a variables compartidas
IF NOT Initialized THEN -- shared variables under lock
SET Initialized = TRUE;
SET RoutingTable = ROW(SELECT R.EVENT_TYPE, R.QUEUE_NAME FROM Database.ADMIN.ROUTING as R);
END IF;
END;
END IF;
-- Copio el mensaje de entrada
SET OutputRoot = InputRoot;
-- Con variables de tipo ROW puedo hacer una SELECT para buscar un elemento. En funcin del tipo de evento obtengo el nombre de la cola
SET QueueName = THE(SELECT ITEM T.QUEUE_NAME FROM RoutingTable.*[] AS T WHERE T.EVENT_TYPE = InputRoot.XMLNSC.Mensaje.Evento);
-- En tiempo de ejecucin asigno el nombre de la cola destino (o de las colas si el evento tiene que ser procesado por varios flujos)
Set OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = TRIM(QueueName);
RETURN TRUE;
END;
END MODULE;
read the table from a text file
If the text file has this contents and format :
Evento_DEC=QL.ADE.cola_001,QL.ADE.cola_22
Evento_Cajero=QL.ADE.cola_201,QL.ADE.cola_25
HostEvt=QL.ADE.TO.FILE.INPUT.301,QL.ADE.TO.WS.INPUT
Evento_Tablet=QL.ADE.cola_401,QL.ADE.cola_2
This code gets it into memory as a BLOB and then it is parsed as DFDL :
declare table blob readFile('c:\EventTable.txt');
CREATE LASTCHILD OF RoutingTable DOMAIN('DFDL') PARSE(table TYPE 'EventQueues');
With this format
<EventType>Evento_DEC</EventType><QueueName>QL.ADE.cola_001</QueueName><QueueName>QL.ADE.cola_22</QueueName>
<EventType>Evento_Cajero</EventType><QueueName>QL.ADE.cola_201</QueueName><QueueName>QL.ADE.cola_25</QueueName>
<EventType>HostEvt</EventType><QueueName>QL.ADE.TO.FILE.INPUT.301</QueueName><QueueName>QL.ADE.TO.WS.INPUT</QueueName>
<EventType>Evento_Tablet</EventType><QueueName>QL.ADE.cola_401</QueueName><QueueName>QL.ADE.cola_2</QueueName>
The Java code is:
package com.caixa.iib;
import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
public class Utils {
public static byte[] readFile(String fileName) {
ByteArrayOutputStream result = new ByteArrayOutputStream();
try {
// Se abre el fichero para lectura
FileInputStream fileInput = new FileInputStream(fileName);
BufferedInputStream bufferedInput = new BufferedInputStream(fileInput);
// Bucle para leer de un fichero y escribir en el ByteArrayOutputStream
byte [] array = new byte[1024*4];
int leidos = bufferedInput.read(array);
while (leidos > 0) {
result.write(array, 0, leidos);
leidos=bufferedInput.read(array);
}
// Cierro el fichero
bufferedInput.close();
}
catch (Exception e) {
e.printStackTrace();
}
return result.toByteArray();
}
}
And the XSD file is:
EventQueues = sequence {row} ;
row = sequence { EventType [1] , QueueName [n] }
MB admin agent flow
<MQ Input node> -> <ProcessAgentCommand compute node> -> <MQ Response node>
Other outputs are available in case more actions are required
What can the agent do ?
- get and increase/decrease queue priority - CLWLPRTY : "alter QLOCAL(q-name) CLWLPRTY(number)", only [1..9]
- get and change a queue parameter : "alter QLOCAL(q-name) GET(DISABLED/ENABLED)" - also PUT(DISABLED/ENABLED)
- create a cluster topic : "define TOPIC(t-name) CLUSTER(cluster-name)"
- create a local queue : "define QL(q-name)"
- start/stop a timer, so on timeout, it can scan few queues for rejected messages. The queues list is given in a table that is loaded using a MQ message and another agent command.
- move a message from one queue to another
-
- anwer "hostname" or "ipconfig" or "dspmqver" or "dmpmqcfg" ...
- ... or just execute a given command from a shell and send the result (file) back
Implementation details
The MQ Input node must have XMLNSC parser assigned
The input message must be in this format, and can be sent using AMQSPUT
<cmd><Command>Publish</Command></cmd>
The Compute node must propagate just LocalEnvironment, not Message
DECLARE RoutingTable SHARED BLOB NULL ; -- flow shared table
-- control message should be as <cmd><Command>Publish</Command></cmd>
CREATE COMPUTE MODULE ProcessCommand
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CASE InputRoot.XMLNSC.cmd.Command
WHEN 'Refresh' THEN
SET Initialized = FALSE;
-- SET RoutingTable = InputRoot.BLOB.BLOB ;
set OutputRoot.XMLNSC.rsp.id = 'MBA-R001 - Refresh OK';
WHEN 'ShowInMemoryTable' THEN
-- set OutputRoot.XMLNSC.rsp.Value[] = RoutingTable.*[];
set OutputRoot.BLOB.BLOB = RoutingTable;
WHEN 'ShowMemoryTableSize' THEN
set OutputRoot.XMLNSC.rsp.id = 'MBA-R002 - Table has [' || cast( CARDINALITY( RoutingTable.DFDL.EventQueues.row[] ) as CHARACTER ) || '] rows.' ;
WHEN 'Publish' THEN
set OutputRoot.XMLNSC.rsp.id = 'MBA-R003 - Published.';
else
set OutputRoot.XMLNSC.rsp.id = 'MBA-R000 - Invalid Command [' || InputRoot.XMLNSC.cmd.Command || ']' ;
END CASE;
SET OutputRoot.XMLNSC.rsp.TimeStamp = CAST ( CURRENT_TIMESTAMP AS CHARACTER FORMAT 'HHmmss' ) ; // echo timestamp
RETURN TRUE;
END;
END MODULE;
MB admin agent as web service
If we have a HttpInput node (with XMLNSC parsing) and a Compute node,
then we can code like this:
-- Input is CWF, output is SOAP/XML
SET OutputRoot.Properties = InputRoot.Properties;
SET OutputRoot.Properties.MessageType = 'Envelope';
SET OutputRoot.Properties.MessageFormat = 'XML1';
CASE InputRoot.XMLNSC.AdminMsg.Command
WHEN 'EcoTimestamp' THEN -- <AdminMsg><Command>EcoTimestamp</Command></AdminMsg>
-- do nothing but echo the time
SET OutputRoot.XMLNSC.rsp.id = 'Refreshed. ';
SET OutputRoot.XMLNSC.rsp.TimeStamp = CURRENT_TIMESTAMP ;
MB admin agent - run PCF commands
The ESQL code shall look like this {thanks, Nadav}:
// after ESQL function, send the message to SYSTEM.ADMIN.COMMAND.QUEUE
// read the PCF response from the 'ReplyToQ'
CREATE PROCEDURE QUEUE_CONTROL_PCF (IN QName CHARACTER, IN QType INTEGER, IN Flag INTEGER)
BEGIN
/* PCF header is following the MQMD header. */
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
SET OutputRoot.MQMD.ReplyToQ = 'enter your ReplyToQ';
SET OutputRoot.MQMD.MsgSeqNumber = 1;
SET OutputRoot.MQMD.Encoding = 546;
CREATE FIELD OutputRoot.MQPCF;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF;
SET refRequest.Type = MQCFT_COMMAND;
SET refRequest.StrucLength = MQCFH_STRUC_LENGTH;
SET refRequest.Version = MQCFH_CURRENT_VERSION;
SET refRequest.Command = MQCMD_CHANGE_Q ; // cmqcfc.h
SET refRequest.MsgSeqNumber = 1;
SET refRequest.Control = MQCFC_LAST;
/* First parameter: Queue Name. */
SET refRequest.Parameter[1] = MQCA_Q_NAME;
SET refRequest.Parameter[1].* = QName;
/* Second parameter: Queue Type. */
SET refRequest.Parameter[2] = MQIA_Q_TYPE;
SET refRequest.Parameter[2].* = QType ; // or MQQT_LOCAL
/* Third parameter: Allow/Inhibit GET.*/
SET refRequest.Parameter[3] = MQIA_INHIBIT_GET;
SET refRequest.Parameter[3].* = Flag;
// or maybe we want to change the description
SET refRequest.Parameter[3] = MQCA_Q_DESC;
SET refRequest.Parameter[3].* = 'The new description';
SET OutputRoot.BLOB.BLOB = asbitstream(OutputRoot.MQPCF);
SET OutputRoot.MQPCF = null;
END;
Another version :
//
CREATE NEXTSIBLING OF OutputRoot.Properties DOMAIN 'MQMD';
CREATE NEXTSIBLING OF OutputRoot.MQMD DOMAIN 'MQADMIN' NAME 'MQPCF';
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
SET OutputRoot.MQMD.ReplyToQ = 'OUT';
SET OutputRoot.MQMD.MsgSeqNumber = 1;
SET OutputRoot.MQMD.Encoding = 546;
CREATE FIELD OutputRoot.MQPCF;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF;
SET refRequest.Type = 16;
--SET refRequest.StrucLength = MQCFH_STRUC_LENGTH;
SET refRequest.Version = 3;
SET refRequest.Command = MQCMD_CHANGE_Q ;
SET refRequest.MsgSeqNumber = 1;
SET refRequest.Control = MQCFC_LAST;
/* First parameter: Queue Name. */
SET refRequest.Parameter[1] = MQCA_Q_NAME;
SET refRequest.Parameter[1].* = 'queue1';
/* Second parameter: Queue Type. */
SET refRequest.Parameter[2] = MQIA_Q_TYPE;
SET refRequest.Parameter[2].* = MQQT_LOCAL ;
/* Third parameter: Allow/Inhibit GET.*/
SET refRequest.Parameter[3] = MQIA_INHIBIT_GET;
SET refRequest.Parameter[3].* = MQQA_GET_INHIBITED;
SET OutputRoot.BLOB.BLOB = asbitstream(OutputRoot.MQPCF);
SET OutputRoot.MQPCF = null;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
mqseries.net
or
publib 6.1
(PCF+ESQL) set queue parameter
/* Assembles PCF command to inhibit GET's from a queue */
CALL CopyMessageHeaders();
/* PCF header is following the MQMD header. */
-- SET OutputRoot.Properties.Encoding = 546;
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
SET OutputRoot.MQMD.ReplyToQ = 'TEST.OUT';
SET OutputRoot.MQMD.MsgSeqNumber = 1;
SET OutputRoot.MQMD.Encoding = 546; -- sag
/* Command is 'Change Queue: Inhibit GET from queue'. */
CREATE FIELD OutputRoot.MQPCF;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF;
SET refRequest.Type = MQCFT_COMMAND;
SET refRequest.StrucLength = MQCFH_STRUC_LENGTH;
SET refRequest.Version = MQCFH_CURRENT_VERSION;
SET refRequest.Command = MQCMD_CHANGE_Q ;
SET refRequest.MsgSeqNumber = 1;
SET refRequest.Control = MQCFC_LAST;
/* First parameter: Queue Name. */
SET refRequest.Parameter[1] = MQCA_Q_NAME;
SET refRequest.Parameter[1].* ='TEST.IN';
/* Second parameter: Queue Type. */
SET refRequest.Parameter[2] = MQIA_Q_TYPE;
SET refRequest.Parameter[2].* = MQQT_LOCAL;
/* Third parameter: Allow/Inhibit GET.*/
SET refRequest.Parameter[3] = MQIA_INHIBIT_GET;
SET refRequest.Parameter[3].* = MQQA_GET_INHIBITED;
-- SET refRequest.Parameter[3] = MQCA_Q_DESC;
-- SET refRequest.Parameter[3].* = 'Joseph was here';
set OutputRoot.BLOB.BLOB = asbitstream(OutputRoot.MQPCF);
set OutputRoot.MQPCF = null;
set OutputRoot.MQRFH2 = null;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
RETURN TRUE;
ESQL
(PCF+ESQL) get queue depth
CREATE PROCEDURE PCF_GET_ACTUAL_Q_DEPTH ( IN QName CHARACTER, IN RespQ CHARACTER )
BEGIN
CALL CopyMessageHeaders();
/* PCF header is following the MQMD header */
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
SET OutputRoot.MQMD.ReplyToQ = RespQ ;
SET OutputRoot.MQMD.MsgSeqNumber = 1;
SET OutputRoot.MQMD.Encoding = 546;
CREATE FIELD OutputRoot.MQPCF;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF;
SET refRequest.Type = MQCFT_COMMAND;
SET refRequest.StrucLength = MQCFH_STRUC_LENGTH;
SET refRequest.Version = MQCFH_CURRENT_VERSION;
SET refRequest.Command = MQCMD_INQUIRE_Q ; -- cmd := inquiry queue ;
SET refRequest.MsgSeqNumber = 1;
SET refRequest.Control = MQCFC_LAST;
/* First parameter: Queue Name */
SET refRequest.Parameter[1] = MQCA_Q_NAME;
SET refRequest.Parameter[1].* = QName ;
set refRequest.ParameterList[1] = MQIACF_Q_ATTRS ; -- we ask for a queue attribute
set refRequest.ParameterList[1].* = MQIA_CURRENT_Q_DEPTH ; -- ... the actual queue depth
SET OutputRoot.BLOB.BLOB = asbitstream(OutputRoot.MQPCF);
SET OutputRoot.MQPCF = null;
SET OutputRoot.MQRFH2 = null; -- mqseries.net
SET OutputRoot.MQMD.Format = MQFMT_ADMIN; -- mqseries.net
END; -- PCF_GET_ACTUAL_Q_DEPTH
ESQL
&
publib
The reply from Command Server is quite tricky to parse:
00000000: 0200 0000 2400 0000 0100 0000 0D00 0000 '....$...........
00000010: 0100 0000 0100 0000 0000 0000 0000 0000 '................
00000020: 0300 0000 0400 0000 4400 0000 E007 0000 '........D...?...
00000030: 0000 0000 3000 0000 514C 2E54 4553 5420 '....0...QL.TEST
00000040: 2020 2020 2020 2020 2020 2020 2020 2020 '
00000050: 2020 2020 2020 2020 2020 2020 2020 2020 '
00000060: 2020 2020 2020 2020 0300 0000 1000 0000 ' ........
00000070: 1400 0000 0100 0000 0300 0000 1000 0000 '................
00000080: 0300 0000 0800 0000 '........
Split in 4 parts is
0200 0000 2400 0000 0100 0000 0D00 0000 0100 0000 0100 0000 0000 0000 0000 0000 0300 0000
0400 0000 4400 0000 E007 0000 0000 0000 3000 0000 514C 2E54 4553 5420 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020
0300 0000 1000 0000 1400 0000 0100 0000
0300 0000 1000 0000 0300 0000 0800 0000
Sub-structures:
- MQCFH :
struct tagMQCFH {
MQLONG Type; /* Structure type */ -- 0x0002
MQLONG StrucLength; /* Structure length */ -- 0x0024
MQLONG Version; /* Structure version number */ -- 0x0001
MQLONG Command; /* Command identifier */ -- 0x000D -- #define MQCMD_INQUIRE_Q 13
MQLONG MsgSeqNumber; /* Message sequence number */ -- 0x0001
MQLONG Control; /* Control options */ -- 0x0001
MQLONG CompCode; /* Completion code */ -- 0x0000
MQLONG Reason; /* Reason code qualifying completion code */ -- 0x0000
MQLONG ParameterCount; /* Count of parameter structures */ -- 0x0003
};
- 1x MQCFST :
struct tagMQCFST {
MQLONG Type; /* Structure type */ -- 0x0004
MQLONG StrucLength; /* Structure length */ -- 0x0044
MQLONG Parameter; /* Parameter identifier */ -- 0x07E0 = 0d'2016 = MQCA_Q_NAME
- 2x MQCFIN :
struct tagMQCFIN {
MQLONG Type; /* Structure type */ -- 0x0003 -- 0x0003
MQLONG StrucLength; /* Structure length */ -- 0x0010 -- 0x0010
MQLONG Parameter; /* Parameter identifier */ -- 0x0014 #define MQIA_Q_TYPE 20 -- 0x0003 #define MQIA_CURRENT_Q_DEPTH 3
MQLONG Value; /* Parameter value */ -- 0x0001 local -- 0x0008 actual queue depth = 8 msgs
};
Sub-components:
- MQCFH = 'x24 bytes, with 3 structures following
- MQCFST (0x04) string parameter, 0x44 long, queue name
- 2x MQCFIN (0x03) integer parameter, 0x10 long
- 0x14 (MQIA_Q_TYPE) = queue type, 0x01 = qlocal
- 0x03 (MQIA_CURRENT_Q_DEPTH) = queue depth, 0x08 = current depth
Mi colega Jaime Martin Talavera nos trae la solucion :
DECLARE depth CHARACTER;
SET depth = 'Queue length is ' || CAST(InputRoot.MQPCF.Parameter[3].*[1] AS CHARACTER);
SET OutputRoot.XMLNSC.Depth = depth;
RETURN TRUE;
Gracias, Jaime !
Andrea's approach is:
BEGIN
CALL CopyMessageHeaders();
DECLARE pcfRef REFERENCE TO InputRoot.MQPCF;
MOVE pcfRef LASTCHILD;
MOVE pcfRef LASTCHILD;
SET OutputRoot.XMLNSC.Data = 'Queue depth: ' || CAST ( pcfRef AS CHARACTER ) ;
RETURN TRUE;
END;
Emir's code
Emir intenta un acceso directo:
IF InputRoot.MQPCF.Reason <> 0 THEN THROW ... (el commando fall)
IF InputRoot.MQPCF.Parameter[1] <> MQCA_Q_NAME THEN THROW ... (not a queue name - MQCA_Q_NAME = 2016)
DECLARE qname CHARACTER InputRoot.MQPCF.Parameter[1].*;
IF InputRoot.MQPCF.Parameter[3] <> MQIA_CURRENT_Q_DEPTH THEN THROW ... (not queue depth - MQIA_CURRENT_QUEUE_DEPTH = 3)
set curdepth = InputRoot.MQPCF.Parameter[3].* ;
Atencio:
El nodo MQInput tiene "MQPCF" en "Input Message Parsing" - no est en la lista pero se puede poner
No importa qu pongas en "Input Message Parsing".
Creo que lo mejor es dejarlo en blanco.
La razn por la que no importa es que MQMD.Format = 'MQADMIN'.
Y eso llama al parser MQPCF.
Crear PCF:
SET OutputRoot.Properties = InputRoot.Properties;
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN;
SET OutputRoot.MQMD.ReplyToQ = 'Q2' ;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF;
CREATE LASTCHILD OF OutputRoot AS refRequest DOMAIN('MQADMIN') NAME 'MQPCF';
SET refRequest.Type = MQCFT_COMMAND;
SET refRequest.StrucLength = MQCFH_STRUC_LENGTH;
SET refRequest.Version = MQCFH_CURRENT_VERSION;
SET refRequest.Command = MQCMD_INQUIRE_Q ; -- cmd := inquiry queue ;
SET refRequest.MsgSeqNumber = 1;
SET refRequest.Control = MQCFC_LAST;
/* First parameter: Queue Name */
SET refRequest.Parameter[1] = MQCA_Q_NAME;
SET refRequest.Parameter[1].* = 'Q3' ;
set refRequest.ParameterList[1] = MQIACF_Q_ATTRS ;
set refRequest.ParameterList[1].* = MQIA_CURRENT_Q_DEPTH ; -- we want to know current queue depth
Crear PCF:
SET OutputRoot.Properties = InputRoot.Properties;
SET OutputRoot.MQMD = InputRoot.MQMD;
-- this does not copy the parameter values
SET OutputRoot.XMLNSC.MQPCF = InputRoot.MQPCF;
-- copy parameter values
/* Parameter values are the FIRSTCHILD of each parameter. For example:
(0x01000000:Name):MQPCF = ( ['MQPCF' : 0xdebb200]
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Version = 1 (INTEGER)
(0x03000000:NameValue):Command = 13 (INTEGER)
(0x03000000:NameValue):MsgSeqNumber = 1 (INTEGER)
(0x03000000:NameValue):Control = 1 (INTEGER)
(0x03000000:NameValue):CompCode = 0 (INTEGER)
(0x03000000:NameValue):Reason = 0 (INTEGER)
(0x03000000:NameValue):Parameter = 2016 (INTEGER) *** This is Parameter[1] (in this case, queue name) ***
(
(0x02000000:Value): = 'QL.TEST ' (CHARACTER) *** This is the value (FIRSTCHILD of Parameter[1] ***
)
(0x03000000:NameValue):Parameter = 20 (INTEGER)
(
(0x02000000:Value): = 1 (INTEGER)
)
(0x03000000:NameValue):Parameter = 3 (INTEGER) *** This is Parameter[3] (in this case, current queue depth) ***
(
(0x02000000:Value): = 22 (INTEGER) *** This is the value (FIRSTCHILD of Parameter[3] ***
)
*/
DECLARE inParmRef REFERENCE TO InputRoot.MQPCF.Parameter[1] ; -- point to the first input parameter
DECLARE outParmRef REFERENCE TO OutputRoot.XMLNSC.MQPCF.Parameter[1] ; -- point to the first output parameter
WHILE LASTMOVE(inParmRef) DO
MOVE inParmRef FIRSTCHILD; -- move to the value in FIRSTCHILD
CREATE NEXTSIBLING OF outParmRef NAME 'ParameterValue' VALUE inParmRef; -- put it in the output message next to its parameter
MOVE outParmRef NEXTSIBLING REPEAT NAME; -- move to the next "Parameter" field in the output message. Must use REPEAT NAME.
MOVE inParmRef PARENT; -- move back to Parameter[n]
MOVE inParmRef NEXTSIBLING; -- increment reference to Parameter[n+1]
END WHILE;
RETURN TRUE;
Convert PCF into XML
coded this to convert the PCF response back into an xml message:
CREATE COMPUTE MODULE Covert_PCF_to_XML
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE creationPtr REFERENCE TO OutputRoot;
CREATE LASTCHILD OF creationPtr DOMAIN('MQPCF') PARSE(InputRoot.BLOB.BLOB, InputProperties.Encoding, InputProperties.CodedCharSetId);
set OutputRoot.XML.PCFCMD.ReturnCode = creationPtr.MQPCF.Reason;
set OutputRoot.XML.PCFCMD.CompCode = creationPtr.MQPCF.CompCode;
set OutputRoot.MQPCF = null;
set OutputRoot.MQMD.Format = MQFMT_STRING;
RETURN TRUE;
END;
Create (clustered) Topic ESQL code
PCF Command := MQCMD_CREATE_TOPIC (d'172)
-
TopicName (MQCFST, required)
The name of the administrative topic definition to be created
(parameter identifier: MQCA_TOPIC_NAME, d'2092).
The maximum length of the string is MQ_TOPIC_NAME_LENGTH.
-
TopicString (MQCFST, required)
The topic string
(parameter identifier: MQCA_TOPIC_STRING, d'2094).
The maximum length of the string is MQ_TOPIC_STR_LENGTH.
-
ClusterName (MQCFST, optional)
The name of the cluster to which this topic belongs
(parameter identifier: MQCA_CLUSTER_NAME, d'2029).
The maximum length of the string is MQ_CLUSTER_NAME_LENGTH.
infocenter,
mqseries.net
CREATE PROCEDURE PCF_CREATE_TOPIC ( IN TopicName CHARACTER, IN RespQ CHARACTER )
BEGIN
CALL CopyMessageHeaders();
SET OutputRoot.MQMD.MsgType = MQMT_REQUEST ;
SET OutputRoot.MQMD.Format = MQFMT_ADMIN ;
SET OutputRoot.MQMD.ReplyToQ = RespQ ;
SET OutputRoot.MQMD.MsgSeqNumber = 1 ;
SET OutputRoot.MQMD.Encoding = 546 ;
CREATE FIELD OutputRoot.MQPCF ;
DECLARE refRequest REFERENCE TO OutputRoot.MQPCF ;
SET refRequest.Type = MQCFT_COMMAND ;
SET refRequest.StrucLength = MQCFH_STRUC_LENGTH ;
SET refRequest.Version = MQCFH_CURRENT_VERSION ;
SET refRequest.Command = 172 ; -- MQCMD_CREATE_TOPIC ; -- cmd := create topic ;
SET refRequest.MsgSeqNumber = 1 ;
SET refRequest.Control = MQCFC_LAST ;
SET refRequest.Parameter[1] = 2092 ; -- MQCA_TOPIC_NAME ; -- TopicName :=
SET refRequest.Parameter[1].* = TopicName ; -- ... input parameter ;
set refRequest.Parameter[2] = 2094 ; -- MQCA_TOPIC_STRING ; -- TopicString :=
set refRequest.Parameter[2].* = TopicName ; -- ... (same) input parameter ;
set refRequest.Parameter[3] = MQCA_CLUSTER_NAME ; -- Cluster := -- 2029
set refRequest.Parameter[3].* = 'CLUS_PUBSUB' ; -- ... fixed value ;
SET OutputRoot.BLOB.BLOB = asbitstream(OutputRoot.MQPCF); -- mandatory !
SET OutputRoot.MQPCF = null;
SET OutputRoot.MQRFH2 = null; -- mqseries.net recomendation
SET OutputRoot.MQMD.Format = MQFMT_ADMIN; -- mqseries.net recomendation
END; -- PCF_CREATE_TOPIC
Job Execution node SupportPac
Also from the book of poorly thought out ideas,
one could use the Job Execution node SupportPac
to execute runmqsc instead of having to write PCF messages {mqjeff}
IA9Z -
the JobExecutionNode allows users to run operating system commands,
windows batch files, unix shell scripts (henceforth referred as job) using WebSphere Message Broker.
Jobs can also be scheduled for specific time and interval using Timer nodes.
JobExecutionNode node handles messages in the XML format only.
Command Source : MESSAGE
When the command source is selected as MESSAGE,
then the input message must have following elements
in order in the incoming XML message into the JobExecutionNode.
<message> <command> "job to be executed" </command> </message>
Propagate Result : YES
Message will be held in the Job Execution Node
and will be propagated after execution of Job is complete
and result appended to the message.
Com se li passen parametres a aquest node ?
Publishing a message
<MQ Input node> -> <Set Topic compute node> -> <Publication node> (in Routing folder)
CREATE COMPUTE MODULE Set_Topic
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
SET OutputRoot.Properties.Topic = 'DEC';
RETURN TRUE;
END;
END MODULE;
COALESCE
Permite conocer si un elemento existe.
locura
IF A < > NULL
A . NULL . unknown
A . 1 . unknown
Hay que poner
IF A IS NOT NULL
Anonymous field references
[<] means
[<] : last child
[<1] : significa
[<2] : penultimate child
[<2] : significa "2 hijos antes del último"
El nombre del Queue Manager no debe aparecer nunca en el message flow
-
En nodos de entrada no va:
la cola siempre pertenece al Queue Manager
al cual está conectado el Broker.
-
En nodos de salida, si uno deja el Queue Manager en blanco,
quiere decir "el Queue Manager al cual está conectado el Broker".
Cuando queremos mandar un mensaje a una cola remota
(por ejemplo, Queue=Q1, Queue Manager=QMGRX,
y el broker no está conectado a QMGRX),
es mejor definir una cola remota en el queue manager del broker:
DEFINE QREMOTE(REMOTE.Q1) RNAME(Q1) RQMNAME(QMGRX)
Y poner en el nodo de salida "REMOTE.Q1",
dejando Queue Manager Name en blanco.
Variable shared
Supongamos que
usamos el nodo HTTP Request para invocar un Web Service.
Por supuesto, los URL son distintos en cada entorno :
cambia, por lo menos, el hostname.
Y tenemos muchos nodos, asi que cambiarlos en el BAR file es engorroso.
Solución:
tener una tabla en la bases de datos del Broker,
con los valores para cada entorno.
Usamos una variable SHARED ROW,
y cargamos la tabla en memoria (en la variable)
cuando llega el primer mensaje.
Todos los mensajes que siguen consultan la variable en memoria
para ir más rápido.
O sea,
toman el nombre de la cola de la tabla y la ponen en el LocalEnvironment
(Destination List).
Este es el código ESQL de la rutina de cargar y buscar los valores :
/* function to create the HTTP url */
--@ env Environment for error handling
--@ devEnvironment To determine if it is DEV, FTE, PROD or TRAINING
--@ package Namespace of the interface (example = sbsa.com%3Asap.bp%3Aget_bp)
--@ interface The name of the service interface
--@ service The source application number of system that will execute the service
--@ synchronous If the value is TRUE,
--@ send a response back to the requestor if something went wrong
DECLARE EnvironmentTable_Loaded SHARED BOOLEAN FALSE;
DECLARE EnvironmentTable SHARED ROW NULL;
DECLARE SourceSystemTable_Loaded SHARED BOOLEAN FALSE;
DECLARE SourceSystemTable SHARED ROW NULL;
CREATE FUNCTION getURL(IN env REFERENCE,
IN devEnvironment CHARACTER,
IN package CHARACTER,
IN interface CHARACTER,
IN sourceApplicationNumber CHARACTER,
IN synchronous BOOLEAN)
RETURNS CHARACTER
BEGIN
IF NOT EnvironmentTable_Loaded THEN
BEGIN
ATOMIC
IF NOT EnvironmentTable_Loaded THEN
SET EnvironmentTable.Values[] = SELECT * FROM Database.GTSENVIRONMENT AS E
WHERE E.ENVIRONMENT = UPPER(devEnvironment);
SET EnvironmentTable_Loaded = TRUE;
END IF;
END;
END IF;
IF NOT SourceSystemTable_Loaded THEN
BEGIN
ATOMIC
IF NOT SourceSystemTable_Loaded THEN
SET SourceSystemTable.Values[] = SELECT * FROM Database.SOURCESYSTEMS;
SET SourceSystemTable_Loaded = TRUE;
END IF;
END;
END IF;
-- declare the variables
DECLARE url CHARACTER;
DECLARE sourceSystem CHARACTER NULL;
-- get the source system name
SET sourceSystem = THE (SELECT ITEM S.SOURCE_SYSTEM_NAME FROM SourceSystemTable.Values[] AS S
WHERE S.SOURCE_SYSTEM_NUMBER = sourceApplicationNumber);
IF sourceSystem IS NULL THEN
IF synchronous THEN
CALL throwUserException(env, 'E', 'ERROR', '000', 'Values for HTTP not found');
END IF;
END IF;
-- create the url
SET url = 'http://' || EnvironmentTable.Values.HOSTNAME ||
':' ||
CAST(EnvironmentTable.Values.PORT AS CHARACTER) ||
'/sap/xi/adapter_plain?namespace=urn%3A' || package ||
'&interface=' || interface ||
'&service=' || sourceSystem ||
'&party=&agency=&scheme=&QOS=BE' ||
'&sap-client=' || CAST(EnvironmentTable.Values.XICLIENT AS CHARACTER) ||
'&sap-language=EN' ||
'&sap-user=' || EnvironmentTable.Values.XIUSERNAME ||
'&sap-password=' || EnvironmentTable.Values.XIPASSWORD;
RETURN url;
END;
La tabla tiene estos valores:
db2 => select * from GTSENVIRONMENT
ENVIRONMENT HOSTNAME PORT XICLIENT XIUSERNAME
----------- --------------------------------------- ----------- ----------- ------------
LOCAL localhost 7080 250 WBRKXISYSTEM
DEV sapdevbp.sbic.co.sag 8051 250 WBRKXISYSTEM
SIT sapsitbp.xxxxxxxxbank.co.sag 8061 250 WBRKXISYSTEM
UAT sapua1bp.xxxxxxxxbank.co.sag 8061 250 WBRKXISYSTEM
4 record(s) selected.
Accessing the ExceptionList tree using ESQL
CREATE PROCEDURE getLastExceptionDetail ( IN InputTree reference, OUT messageNumber integer, OUT messageText char )
/****************************************************************************
* A procedure that will get the details of the last exception from a message
* IN InputTree: incoming exception list
* OUT messageNumber: last message number
* OUT messageText: last message text
*****************************************************************************/
BEGIN
-- Create a reference to the first child of the exception list
declare ptrException reference to InputTree.*[1];
-- keep looping while the moves to the child of exception list work
WHILE lastmove( ptrException ) DO
-- store the current values for the error number and text
IF ptrException.Number is not null THEN
SET messageNumber = ptrException.Number;
SET messageText = ptrException.Text;
END IF;
-- now move to the last child which should be the next exceptionlist
move ptrException lastchild;
END WHILE;
END;
url
Few interesting hints
Save a header :
// from WSCLIENT_WSCLIENT1.esql
-- Save the MQMD; sending to HTTP later in the flow destroys it
SET Environment.MQMD = InputRoot.MQMD;
Remove a header :
set OutputRoot.MQRFH2 = null ;
set OutputRoot.MQPCF = null ;
Format timestamp
DECLARE now CHARACTER ;
SET now = CAST ( CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyyMMdd-HHmmssSSS' ) ;
Save Envir also
CREATE PROCEDURE CopyEntireMessage_and_Environment() BEGIN
SET OutputRoot = InputRoot;
set OutputLocalEnvironment = InputLocalEnvironment;
END;
Prepare CRLF string
DECLARE CRLF CHAR CAST(X'0D0A' AS CHAR CCSID 1208);
Good ESQL samples
Dubtes ESQL
-
si poso un node Compute i vull que el seu ESQL estigui en un fitxer propi, faig servir "New ESQL",
pero el fitxer es buid.
Com es fa per a que inclogui "CopyHeaders" ?
Si n'escullo un que ja existeix, he de crear aquest codi manualment ?
CREATE COMPUTE MODULE nom_del_modul
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
RETURN TRUE;
END;
- com és que això funciona
SET OutputRoot.XMLNSC.rsp.id = 'MBA-R007 - LK rx event [' || Environment.MyEventoInput || '].' ;
SET OutputRoot.XMLNSC.rsp.TimeStamp = CAST ( CURRENT_TIMESTAMP AS CHARACTER FORMAT 'HHmmss' ) ;
PROPAGATE TO TERMINAL 'out2' ; -- send message to trace output node
pero aixo no
SET OutputRoot.XMLNSC.rsp.id = 'MBA-R007 - LK rx event [' || Environment.MyEventoInput || '].' ;
PROPAGATE TO TERMINAL 'out2' ; -- send message to trace output node
ESQL links
ESQL code
conventions
Emir &
cache
running samples / MB ESQL repository
Updated 20141201 (a)
