If you’re like us working on WMB since v6.1, we all know how much the product has changed so far. We’re working on the latest version ACEv12 for a migration project which is to migrate from IIBv10 to ACEv12. The client is using a custom application for all administrative tasks and is completely built using IBM Integration JAVA API.
As the product architecture has changed dramatically in ACE, IIB v10 APIs are deprecated so the current custom application won’t work AS IS. So, it has be re-written from scratch and today we’re able to get the ActivityLog working.
Here is the screenprint showing all message flows and one of the flow’s activity log.
ACE Explorer
Activity Log
The current version ACE12.0.3 doesn’t support deploying a bar file with a overriding property file. But, we added this feature to simplify the life of both developers/administrators. All, you’ve to do is, choose the Integration server in which you’d like to deploy, drag the bar file and properties file then click the Deploy Bar button located at the bottom.
If you’re using IIBv10 Integration API and looking for any assistance, please contact us at support@vaithu.com.
Also, we’d like to know the features you’re expecting in the Web UI. You can put them in the comments section. We’ll try our best to get them into ACE Buddy. Those who provide good suggestions would get ACE Buddy free license for ONE year.
Can someone master ESQL in just one day? YES, ESQL is not like any other programming language which has lot of statements and libraries to know. It is the most simplest language that I’ve ever seen.
The statements are limited, syntax is simple and learning is super easy. As an ACE/IIB developer, what do you do most of the time? Writing ESQL code right? Then, are you harvesting the real power of ESQL?
This one day training program is going to show you the real power of ESQL. It is going to explain you from the basics to advanced statements with examples
Here is what we’re going to cover in the course.
Introduction
Data Types
Boolean
TRUE
FALSE
UNKNOWN
DateTime
NULL
Numeric
DECIMAL
FLOAT
INTEGER
Reference
String
BIT
BLOB
CHARACTER
Correlation Names
Root
FirstChild
LastChild
FirstSibling
NextSibling
PreviousSibling
LastSibling
Message Components
Properties
Message Domain
Environment
Local Environment
Exception List
Variables
Declaration
Normal
External
Shared
Scope
Operators
Simple Comparison
>, >=, <, <=, <> & =
Complex Comparison
BETWEEN
EXISTS
IN
IS
LIKE
SINGULAR
Logical
AND
OR
NOT
Numeric
String
Field References
Field Types
NAME
TYPE
NAMEVALUE
Conditional Statements
IF….ELSEIF…..ELSE
CASE
Simple
Searched
Looping Statements
REPEAT UNTIL
WHILE
FOR
LABELED LOOP
ITERATE
LEAVE
RETURN
Error Handling
THROW
DECLARE HANDLER
Modules
Procedures
Functions
CALL
MOVE
LASTMOVE
Handling NULL values
Propagating Multiple Messages
List Functions
CARDINALITY
EXISTS
SINGULAR
THE
ITEM
Complex Functions
CAST
FORMAT
SELECT
Simple
Complex
ROW
LIST
ROW & LIST Combined
Working with Database
INSERT
SELECT
UPDATE
DELETE
PASSTHRU
Stored Procedures
Manipulating repeating fields
Calling Java methods
String Functions
CONTAINS
ENDSWITH
LEFT
LENGTH
LOWER
LTRIM
OVERLAY
POSITION
REPLACE
REPLICATE
RIGHT
RTRIM
SPACE
STARTSWITH
SUBSTRING
TRANSLATE
TRIM
UPPER
Miscellaneous
ATTACH
DETACH
PATH
BROKER SCHEMA
DECLARE
CREATE FIELD
DELETE FIELD
COALESCE
SLEEP
UUIDASCHAR
ASBITSTREAM
EXTRACT
As the trainer has more than 15+ years of experience in ESQL, you can ask him any questions related to ESQL. If you’ve complex requirement, ask him how to resolve it during the training. So, why wait ?
Enroll into the training program and become a Master in ESQL in One Day.
Event Address: Online Contact us at (612) 305-8684 or support@vaithu.com
Recently we’ve started working on migrating stuffs from WMBv7 to ACEv12. The biggest challenge we’ve encountered is so far is, converting the message sets to DFDLs. Please note I’m talking about Custom Wire Formats not Cobol copy books.
I’d say it is one of the most difficult task I’ve encountered so far because the messages sets are all EDIFACT ( IBM 500 ) messages with different initiators, separators and terminators along with various padding characters and justification configuration.
So, thought of finding some automatic solution in Google but no luck. There are 100+ models need to be converted in next couple of months. All I can do so far is, write a java program to read the MXSD files and clean up the unnecessary stuffs. Also, tried to have the group indicators/terminators and delimiters converted into DFDL annotations.
This is not a 100% working solution but definitely helping a lot to quickly have the DFDL created. If you’ve any better solution, please feel free to add in the comments.
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileSystemView;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
public class TDSCleaner {
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
JFileChooser jfc = new JFileChooser(FileSystemView.getFileSystemView().getHomeDirectory());
int returnValue = jfc.showOpenDialog(null);
if (returnValue == JFileChooser.APPROVE_OPTION) {
File selectedFile = jfc.getSelectedFile();
System.out.println(selectedFile.getAbsolutePath());
Path path = Paths.get(selectedFile.getAbsolutePath());
List<String> lines = Files.readAllLines(path, StandardCharsets.UTF_8);
List<String> newLine = new ArrayList<>();
/*String rootElm = JOptionPane.showInputDialog("Enter the root element name");
if (rootElm != null) {
rootElm = " 01 "+FilenameUtils.getBaseName(selectedFile.getAbsolutePath())+".";
newLine.add(rootElm);
}
String rootElm = " 01 "+FilenameUtils.getBaseName(selectedFile.getAbsolutePath())+".";
newLine.add(rootElm);*/
String delimiter,groupTerminator,groupIndicator;
delimiter = null;
groupTerminator = null;
groupIndicator = null;
for (String line : lines) {
if (!StringUtils.containsAny(line, "annotation","appinfo",
"MRMessage","MRMessage","MRComplexType","tdsElemRep")) {
if (line.contains("tdsStructRep")) {
line = StringUtils.replace(line, "&lt;U+001D&gt;", "%GS;");
line = StringUtils.replace(line, "&lt;U+001C&gt;", "%FS;");
if (line.contains("groupIndicator")) {
groupIndicator = "dfdl:initiator="+StringUtils.substringBetween(line, "groupIndicator=", " ");
}
if (line.contains("groupTerminator")) {
groupTerminator = "dfdl:terminator="+StringUtils.substringBetween(line, "groupTerminator=", " ");
}
if (line.contains("delimiter")) {
delimiter = "dfdl:separator="+StringUtils.substringBetween(line, "delimiter=", " ");
}
continue;
}
if (line.contains("<xsd:sequence")) {
if (delimiter != null) {
line = StringUtils.replace(line, ">", " ")+delimiter+">";
}
if (groupIndicator != null) {
line = StringUtils.replace(line, ">", " ")+groupIndicator+">";
}
if (groupTerminator != null) {
line = StringUtils.replace(line, ">", " ")+groupTerminator+">";
}
}
delimiter = null;
groupTerminator = null;
groupIndicator = null;
newLine.add(line);
System.out.println(line);
}
}
if (!lines.isEmpty()) {
String cpyFileName = FilenameUtils.getFullPath(selectedFile.getAbsolutePath())
+"Modified_"+FilenameUtils.getBaseName(selectedFile.getName())+".xsd";
System.out.println(cpyFileName);
FileUtils.writeLines(new File(cpyFileName), newLine);
JOptionPane.showMessageDialog(null, "File created successfully");
} else {
JOptionPane.showMessageDialog(null, "Lines are empty check your source file");
}
}
}
}
Some of the notes we’ve taken while working on a migration project from IIB to ACE. If you see anything has changed or provided incorrectly, feel free put those in the comments and we’d update this post with your comments.
App Connect Enterprise v11 (first released 2018):
Onpremise + Cloud
IIB + App Connect Professional
Local -> Toolkit ; Cloud -> Designer
Integration Node is optional
Integration Servers can run independently
Configurable services are replaced with Policy files
Local queue manager is required if Collector, Timer and Aggregate nodes are used.
One Web UI can show any number of Integration Servers & Nodes
Not all features available in v10 Web UI are available in ACE V11 but soon they will be available
Flow and Resource Statistics are turned on by default
Some mqsi commands are not available like mqsiformatlog, mqsireadlog, mqsimigratecomponents, mqsideleteconfigurableservice
IIB End of Support is announced as Apr 2022
ACE can run in on-premise and cloud
Use TransformationAdvisor for the migration plan and impact
App Connect Standard is effectively IIB standard with the addition of the Salesforce Request node whereas App Connect Enterprise is the official successor to IIB and includes the ACE V11 software, App Connect Professional V7.5.2 as well as the IBM App Connect on Cloud Service.
A new administrative REST APIv2 for configuring App Connect Enterprise servers
All Runtime node properties can be modified using server.conf.yml file
IBM App Connect professional has 100+ connectors
If there is any project created in older versions like WMB v6/v7 or anything as integration project, those would be deployed into a default Application project.
Integration projects are no longer encouraged. Any attempt to subsequent redeploy independent resources will replace the entire content of the default application of an integration server with the new BAR file’s independent resources.
New REST API testing utility in admin Web UI
Migration
Parallel Migration
Install your ACEv11 environment,
Configure the environment manually from scratch or reuse command scripts from IIBv10
Take a branch in version control and then deploy your BAR files to the new environment
Parallel Migration using extraction to help
Install your ACEv11 environment
Use mqsiextractcomponents to configure the v11 environment (this will help to create policies for you from config services for example) to help ensure you don’t forget configuration from your v10 system. Discard the run directory contents, because instead you are going to deploy items to the new system one by one as the next action
Take a branch in version control and then deploy your BAR files to the new environment.
Big Bang using extraction
Install your ACEv11 environment
Use mqsiextractcomponents to configure the v11 environment (this will help to create policies for you from config services for example) to help ensure you don’t forget configuration from your v10 system. Don’t discard the run directory contents
Restart the integration node.
There is no change in the project types. It is same as v10.
IBM has provided lot of ways to capture the messages flowing through our message flows. In this post, we are going to discuss about one of them and it is also one of the most unnoticed.
IIB allows you to capture the messages by enabling monitoring profile or by writing custom subflows or using trace nodes.
The most frequently used one is subflows and now a days people use monitoring profile too a lot. There are companies which do not have logs at all ( don’t know how they survive).
Have you ever used Record & Replay? If not, just google to get some basic idea on how that works.
This post is also utilizing the same Record & Replay concept but it does on the fly. I do not have any database setup to store the messages permanently. Just going with built-in capacity of the Integration node.
Let me put here the sequence of steps to achieve this
Enable recording
Enable Injection
Retrieve Recorded Message
Let me put here the Java APIs used here
public void enableRecording(ExecutionGroupProxy proxy, boolean status) throws ConfigManagerProxyLoggedException, ConfigManagerProxyPropertyNotInitializedException {
if (status) {
proxy.setTestRecordMode(AttributeConstants.MODE_ENABLED);
}else {
// clear recorded data and reset server to turn off recording and injection
proxy.clearRecordedTestData();
proxy.setTestRecordMode(AttributeConstants.MODE_DISABLED);
}
enableInjection(proxy, status);
}
public void enableInjection(ExecutionGroupProxy proxy, boolean status) throws ConfigManagerProxyLoggedException, ConfigManagerProxyPropertyNotInitializedException {
if (status) {
proxy.setInjectionMode(AttributeConstants.MODE_ENABLED);
}else {
// clear recorded data and reset server to turn off recording and injection
proxy.setInjectionMode(AttributeConstants.MODE_DISABLED);
}
}
public List<RecordedTestData> retreiveRecordedMessage(MessageFlowProxy mf) throws ConfigManagerProxyPropertyNotInitializedException, ConfigManagerProxyLoggedException {
Properties filterProps = new Properties();
String name = getApplication(mf).getName();
// System.out.println(name);
if (name != null) {
filterProps.setProperty(Checkpoint.PROPERTY_APPLICATION_NAME,name);
}else {
name = getLibraryProxy(mf).getName();
if (name != null) {
filterProps.setProperty(Checkpoint.PROPERTY_LIBRARY_NAME,name);
}
}
// System.out.println(mf.getName());
filterProps.setProperty(Checkpoint.PROPERTY_MESSAGE_FLOW_NAME,mf.getName());
// ExecutionGroupProxy egProxy = mf.getExecutionGroup();
// List<RecordedTestData> dataList = egProxy.getRecordedTestData(filterProps);
return mf.getExecutionGroup().getRecordedTestData(filterProps);
}
That’s it. Once you enable recording, the message flow automatically turns into like this
The recorded message is here (I’ve a UI to make this easier to read)
By this way, if you’ve a need to immediately see what is flowing through your messages, simply implement this solution and see the messages on the fly. Once done, clear up the recordings.
If you’re interested to know more about this solution, feel free to contact us at support@vaithu.com. WhatsApp +1 6123058684.
Sometimes, we need to read excel files and there is no native connector in IBM Integration Bus. So, the obvious solution is to read them using Java. The most commonly used library is Apache POI.
Here I’ve written a simple program which reads the sheets ( assuming it has a header and multiple rows) and attach them to Environment tree. The java code is
public class ReadExcel_JCN extends MbJavaComputeNode {
public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = inAssembly.getMessage();
MbMessageAssembly outAssembly = null;
try {
// create new message as a copy of the input
MbMessage outMessage = new MbMessage(inMessage);
outAssembly = new MbMessageAssembly(inAssembly, outMessage);
// ----------------------------------------------------------
// Add user code below
MbElement environment = inAssembly.getGlobalEnvironment().getRootElement();
File excelFiles = new File((String) getUserDefinedAttribute("ExcelDir"));
Workbook workbook = null;
DataFormatter dataFormatter = new DataFormatter();
List<String> headerNames = null;
String cellValue = null;
MbElement cellElm = null;
for (File excel : excelFiles.listFiles()) {
workbook = WorkbookFactory.create(excel);
for (Sheet sheet : workbook) {
MbElement sheetElm = environment.createElementAsLastChild(MbElement.TYPE_NAME, sheet.getSheetName(), null);
headerNames = new ArrayList<>();
for (Row row : sheet) {
if (row.getRowNum() != 0) {
cellElm = sheetElm.createElementAsLastChild(MbElement.TYPE_NAME, "rows", null);
}
for (Cell cell : row) {
cellValue = dataFormatter.formatCellValue(cell);
if (row.getRowNum() == 0) {
headerNames.add(cellValue);
} else {
cellElm.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, headerNames.get(cell.getColumnIndex()), cellValue);
}
}
}
if (sheetElm.getLastChild() == null) {
sheetElm.detach();
}
}
workbook.close();
}
// End of user code
// ----------------------------------------------------------
} catch (MbException e) {
// Re-throw to allow Broker handling of MbException
throw e;
} catch (RuntimeException e) {
// Re-throw to allow Broker handling of RuntimeException
throw e;
} catch (Exception e) {
// Consider replacing Exception with type(s) thrown by user code
// Example handling ensures all exceptions are re-thrown to be handled in the flow
throw new MbUserException(this, "evaluate()", "", "", e.toString(),
null);
}
// The following should only be changed
// if not propagating message to the 'out' terminal
out.propagate(outAssembly);
}
}
If you’re looking for any support in IIB, please contact us support@vaithu.com.
About the Author
He has worked for various clients and developed more than 800+ message flows. He is an expert in ESQL, Java Compute Node and DFDL. If you’ve any complex code to be written in ESQL, feel free to contact him at +1 6123058684.