Andrew Coleman – IBM Integration Bus Development

June 2013

IBM Integration Bus Version 9 Beta

Mapping Node

© 2013 IBM Corporation

Disclaimer

Information regarding potential future products is intended to

outline our general product direction and it should not be relied
on in making a purchasing decision.

The information mentioned regarding potential future products is

not a commitment, promise, or legal obligation to deliver any
material, code or functionality.

Information about potential future products may not be

incorporated into any contract.

The development, release, and timing of any future features or

functionality described for our products remains at our sole
discretion

2 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Agenda

Mapping Node recap

Hints and Tips – Good map design

The power of XPath 2.0

Database mapping – Insert / Update / Delete

Invoking DB Stored Procedures

‘Assisted Convert’ of version 6.x / 7 maps

Other new features

3 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Much Ado About Mapping

Improved usability
– Simple learning curve
– Simple transformations are simple to create
– Complex transformations build on concepts previously mastered

Improved capability
– Builds upon ‘best of breed’ mapping technologies in IBM products
– Full XPath 2.0 expression support
• Standards-based language designed for hierarchical data structures

Improved performance
– Dedicated runtime engine

4 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

5 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Input / Source tree

6 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Output / Target tree

7 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Properties editor

8 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Transform

9 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Toolbar

10 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Name Type
+ Expand Occurrence
- Collapse
11 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation
The Mapping Editor

Find elements
anywhere in the tree

Click <Show all>

To remove filter

12 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The Mapping Editor

Help system

13 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Transform types

Local
Move

For each
Assign

If / Else
Convert

Submap
XPath function - categorised

Create
Custom XPath expression

Join
Java – user defined

Append
ESQL – user defined

Group

14 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Transform properties

Each transform can be configured in the properties pane

Standard pages
– General – transform specific configuration
– Variables – customise variable names used in expressions
– Condition – XPath predicate determines if transform is performed
– Order – can re-order the input and output connections
– Documentation – freeform text

For repeating inputs
– Cardinality – specify which elements in array to transform
– Filter Inputs – only process elements according to XPath predicate
– Sort – re-order the input array

15 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Properties? – What Properties?

When working in ‘full screen mode’ – the map takes the whole area
– Good for working with large, complex maps, but…
– How do I see the properties?

– Press ‘Alt-Enter’

Or click here

16 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Map Navigation

Breadcrumb trail
Up one level

Down one level

Outline view
Linked to map

17 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 Map Design – Best Practices

18 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Structured Mappings

19 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

What not to do

20 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

What not to do

21 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

What not to do

22 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

The nested approach

23 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Controlling the output

Use ‘Local’ transform to map complex source to complex target

If elements are optional, set a condition property
– E.g. only create output if input exists

24 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Map Refactoring

Insert new levels in the hierarchy

– E.g. Insert a ‘Local’ transform between
structures that are already mapped
– Error marker: Parent element already mapped!
– Quick fix: Nest transform within parent map
– Results in more structured mapping

Refactor to submap
– Convert a structural transform
to a submap
– Allows existing structural
transforms to be reusable

25 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Message Assembly – Adding and Deleting Headers

26 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Message Assembly – Adding and Deleting Headers

Selected headers are added into the

map editor.

Headers in the input message that
don’t appear in the target tree are
copied to the output message.

Headers not in the input message
can be added to the output message
by adding them to the target tree and
mapping to them.

Headers in the input message can be
deleted by adding them to the target
tree and not mapping to them.

27 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Casting elements

xsd:any elements can be cast to

any simple or complex type

Other elements can be cast to a
derived type

Use to map variables in
LocalEnvironment

Use to map header and body in
SOAP message

28 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Mapping a SOAP message

2) Right click -> Cast…

select required element
1) Create the map using the
built in SOAP schema.

3) Map the cast elements

29 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Runtime trace

WMB ‘user trace’ will track the progress of the message though the map

Logs messages when
– individual transforms are entered and exited
– The input tree is navigated
– Output tree elements are created
– Values assigned to the output
– Variables are assigned/updated or go out of scope
– Functions are invoked, including user defined functions

…
2012-03-12 12:07:53.044555 96400 UserTrace BIP3962I: The Mapping node is assigning the value ''Twister'' to
the current output element.
2012-03-12 12:07:53.044647 96400 UserTrace BIP3956I: The Mapping node is exiting the transform ''Move''.
2012-03-12 12:07:53.044689 96400 UserTrace BIP3955I: The Mapping node is entering the transform ''Move''.
2012-03-12 12:07:53.044815 96400 UserTrace BIP3959I: The Mapping node is traversing the input tree by using
the nodetest ''element(Price)'' and the relationship ''child''. The number of matching elements is ''1''.
2012-03-12 12:07:53.044876 96400 UserTrace BIP3960I: The Mapping node is adding a new element with name
''out:Cost'' into the output tree.
…

30 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 XPath 2.0 – Quick Guide

31 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

XPath 2.0

The expression syntax used by properties in the mapping node

Specifies location paths for a tree structure
– E.g. $Order/Item[1]/Description

Type system based on XML Schema (and hence DFDL)

Rich function library

Expressive syntax supporting complex calculations with minimal code

Read-only ‘functional’ language – no side effects

‘Variables’ refer to elements in the input tree

High level programming constructs including ‘for’ loops, ‘if-then-else’
conditions

http://www.w3.org/TR/xpath20/
32 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation
XPath 2.0 in the mapper

Much more than a ‘path’ syntax

– Path navigation using XPath is rarely done in the mapper
– Navigating the tree is done graphically

Most XPath expressions in the mapper are ‘predicates’
– Used within the ‘condition’ property of transforms
– If the expression evaluates to ‘true’, then the transform is evaluated and the
target is produced

Other common uses involve numeric calculation
– All the usual mathematical operators are built into the XPath syntax
– Numeric function library – e.g. sum, count, round, max, min, …

Strong support for string manipulation
– Extensive function library for string splitting, joining, comparing, etc.

33 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Everything is a sequence… What is a sequence?

XPath 2.0 naturally handles repeating elements and values

A path expression can match multiple elements
– Returns a ‘sequence’ of elements (nodes)

It might only match one element
– It still returns a sequence, containing one element

Some expressions expect to work with data values, not elements
– E.g. sum($books/book/price)
– No problem, it will automatically extract the values from the elements
• This is called atomization and results in a sequence of values (e.g. decimals)

Some expressions expect a single value, not a sequence

– E.g. $quantity * 3
– A single value is equivalent to sequence containing one value
– If the expression is passed a sequence of more than one value, an error is thrown

34 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

(Sequences, Sequences, Sequences)

A sequence is an ordered collection of items

– An item is either a node (in the XML sense) or a value (of a schema type)

What if we have a path expression that matches nothing?
– Then it returns the ‘empty sequence’ – a sequence containing zero items

Sequences are returned by expressions and functions
– They can also be constructed as literals in an expression
– E.g. (1,2,3) produces a sequence containing the numbers ‘1’, ‘2’, and ‘3’
– (1 to 3) does the same
– () produces the empty sequence

Sequences can be constructed from other sequences

– E.g. ($var1,$var2,"str")

Sequences are always ‘flattened’

– i.e. (1,2,(3,4),(5),(),6) produces the sequence (1, 2, 3, 4, 5, 6)

35 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Consequences of sequences

Sequences can be picked apart

– ("one", "two", "three")[2] → “two”
– subsequence((1 to 10), 3, 4)) → (3, 4, 5, 6)

Remember that sequences are always flattened

– ((), "two", "three")[2] → “three”
– ($var/missing, "default")[1] → “default”
• Assuming that <missing> doesn’t exist
• Just like the ESQL function coalesce()

A sequence can be assigned to a repeating output element

– Multiple elements created; each value in the sequence will be assigned to
each element

36 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Predicates

A predicate is an expression that returns Boolean ‘true’ or ‘false’

In XPath, all values have an ‘Effective Boolean Value’ (EBV)

Therefore, all expressions can be used as predicates

The following values have EBV false
– The xs:boolean value false
– The numbers 0 and NaN
– The empty string ""
– The empty sequence ()

Everything else has EBV true

Booleans can be produced by comparison and logical operators
=, !=, <, >, <=, >=, eq, ne, lt, gt, le, ge
and, or, not

37 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Number crunching

XPath syntax has numeric operators

+, -, *, div (can’t use / - path separator), idiv, mod

$book/price * 1.2
– The result of the path expression is atomized to extract the value as its xsd type
– Works provided the ‘price’ element is defined as a numeric type in the schema
– Otherwise it throws a ‘type error’ (XPTY0004)
– If it’s a xs:string, can explicitly cast it – xs:decimal($book/price) * 1.2

$books/book/price * 1.2
– Will throw a type error because ‘book’ is repeating

$books/book/(price * 1.2)
– Will work though – it returns a sequence of numbers

sum($books/book/(price * quantity))
– The sum() function takes a sequence of numbers as its parameter

38 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

String theory

Most transformations involve significant string

manipulation

XPath has a rich function library to support this
– Available in the transform ‘picker’
– Parameters automatically configured from wired inputs
– These parameters are themselves XPath expressions
that can invoke other string functions
– Usage documentation in property page

More complex combinations of functions and
expressions can be written in a custom XPath
transform
– Content assist and wizard based builder available

39 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 What’s new in v8.0.0.1, v8.0.0.2 and v9.0

40 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Mapping

Mapper can work with data in a relational database

– Supports DB Select, Insert, Update, Delete & Stored Procedures

Can select fields from DB tables based on data in the input message

Can modify DB tables using values defined in the map

Graphical SQL ‘WHERE’ clause and ResultSet generator
– Can contain parameter placeholders to reference the input message tree
– Parameter values specified using XPath

ResultSets are presented as source trees in the map editor

Input parameters are presented as target trees

41 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Mapping – JDBC configuration

Create JDBC Configurable Service

Set security credentials
– mqsisetdbparms

Toolkit and Runtime both connect to
the DB using JDBC

Now possible to specify a different
database schema at runtime from
the one specified in the map editor

JDBC Configurable service
– databaseSchemaNames property 8.0.0.1
– Specifies the database schema to
use at runtime for all DB operations
or a delimited list of table/schema
names
– If left unset, uses DB default schema
name (as for v8 GA mapper)

42 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database SELECT

43 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database SELECT

Source tree representing

The DB Select result set

44 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.1
Database Insert

Inserts a row into a Database table

Connects via JDBC
– WMB configurable service

DB table presented as target tree
– Transforms mapped to target tree specify
values to be inserted

Can also map to output message if DB
Insert succeeds
– Inside the ‘Return’ transform
– Can also do further DB operations here

Can map SQL error code to output
message if DB Insert fails

45 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Insert

46 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Insert

47 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Insert

48 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.1
Database Update

Updates row(s) in a Database table

SQL ‘where’ clause edited in same way as ‘Select’ transform

DB table presented as target tree
– Transforms mapped to target tree specify values to be updated

Can also map to output message if DB Update succeeds
– Inside the ‘Return’ transform
– ‘number of rows updated’ is available to be mapped
– Can also do further DB operations here

Can map SQL error code to output message if DB Update fails

Option to insert new row if no existing rows match the ‘where’ criterion

49 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Update

50 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.1
Database Delete

Deletes row(s) from a Database table

SQL ‘where’ clause edited in same way as ‘Select / Update’ transform

DB table presented as target
– No field members are shown under the target
– Nothing to map – rows that match the where clause are deleted from the table

Can map to output message if DB Delete succeeds
– Inside the ‘Return’ transform
– ‘number of rows deleted’ is available to be mapped
– Can also do further DB operations here

Can map SQL error code to output message if DB Delete fails

53 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.2
Database Policy – missing, empty & nil data

56 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 9.0
Database Stored Procedures

Invoke a database Stored Procedure

Presented as a ‘complex’ transform (function)

Tools to discover Stored Proc metadata

Presents ‘IN’ and INOUT’ parameters as target tree in nested map
– Wire inputs to stored procedure

‘INOUT’, ‘OUT’, ResultSet(s) & Return value as source tree in nested map

Supports DB2 (GA), Oracle (soon)

57 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Stored Procedures

58 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Stored Procedures

CREATE PROCEDURE ADD_CUSTOMER ( INOUT NAME VARCHAR(20),

IN ADDRESS VARCHAR(50),
OUT IDENT INTEGER )
P1: BEGIN

set ADD_CUSTOMER.IDENT = (
select id from new table (
insert into customers (name, address)
values (ADD_CUSTOMER.NAME,ADD_CUSTOMER.ADDRESS)
)
);
SET ADD_CUSTOMER.NAME = UPPER(ADD_CUSTOMER.NAME);
END P1

59 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Stored Procedures

60 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Database Stored Procedures

61 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Stored Procedures – Returning ResultSet(s)

62 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Stored Procedures – Returning ResultSet(s)
CREATE PROCEDURE ADDBOOK ( IN pISBN CHARACTER(32),
IN pTITLE CHARACTER(128),
INOUT pCOPIES INTEGER,
OUT pDUPLICATE BOOLEAN )
DYNAMIC RESULT SETS 2
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT TITLE, ISBN, COPIES
FROM BOOKS2
WHERE ISBN = ADDBOOK.pISBN;

DECLARE cursor2 CURSOR WITH RETURN FOR

SELECT TITLE, ISBN, COPIES
FROM BOOKS2;

merge into BOOKS2 using

table (values (ADDBOOK.pISBN, ADDBOOK.pTITLE, ADDBOOK.pCOPIES))
as tBOOK (ISBN, TITLE, COPIES)
on BOOKS2.ISBN = tBOOK.ISBN
when matched then
update set COPIES = COPIES + tBOOK.COPIES
when not matched then
insert (ISBN, TITLE, COPIES)
values (tBOOK.ISBN, tBOOK.TITLE, tBOOK.COPIES);

select COPIES INTO pCOPIES from BOOKS2 WHERE ISBN = ADDBOOK.pISBN;

set pDUPLICATE = true;

OPEN cursor1;
OPEN cursor2;
63 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation
END P1
Stored Procedures – Returning ResultSet(s)

64 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Stored Procedures – Returning ResultSet(s)

65 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.1
Version 7 Maps
8.0.0.2

Version 7 maps can be viewed in Version 8/9 toolkit

– Read-only – cannot be edited

Version 7 maps can still be executed in Version 8/9 GA runtime
– Converted in the toolkit into an ESQL Compute node
– Executed in the ESQL runtime engine

Can ‘convert’ V7 maps to V8 maps (requires version 8.0.0.1)
– .msgmap -> .map file format conversion
– Can then be opened in V8 mapping editor and modified etc.
– Will then be executed in the V8 mapping runtime engine

‘Assisted Convert’
– Fundamentally different technologies
– Cannot guarantee 100% accurate ‘migration’
– Inserts annotations or ‘Task’ markers to prompt user review or fix up

66 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Version 7 Maps

67 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Assisted convert

68 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Converted Map

69 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Map annotations

70 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

 8.0.0.1
Tasks

71 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Repeating model groups

72 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

‘For each’ – Multiple outputs 8.0.0.2

<library>
<parameters>
<name>FRED BLOGS</name>
<value>16</value>
<name>JOE SMITH</name>
<value>17</value>
<name>MARY BERRY</name>
<value>18</value>
</parameters>
</library>

73 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

‘For each’ – Allow empty input 8.0.0.2

When checked
– If input array is empty
– or no inputs match filter condition
– Then the transform runs once
• Primary input in nested transform will be missing
• The index variable will be set to zero

Can be used to construct left, right and full outer joins

– Using nested ‘For each’ transforms with this option

74 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

‘Create’ Transform 8.0.0.2

Creates a new output element without any input

– Either simple element or complex structure

Used for creating empty or ‘nil’ elements

For simple elements can also assign ‘default’ or ‘fixed’ content based on
schema definition
– As opposed to ‘Assign’ transform which assigns a fixed value

75 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

‘Group’ Transform 9.0

Takes a repeating input structure produces multiple structures grouped by

distinct keys <countries>
<country name="France">
<city>Paris</city>

E.g <city>Nice</city>
<city>Lyon</city>
<cities> </country>
<city name="Paris" country="France"/> <country name="Italia">
<city name="Roma" country="Italia"/> <city>Roma</city>
<city name="Nice" country="France"/> <city>Milano</city>
<city name="Madrid" country="Espana"/> <city>Firenze</city>
<city name="Milano" country="Italia"/> <city>Napoli</city>
<city name="Firenze" country="Italia"/> </country>
<city name="Napoli" country="Italia"/> <country name="Espana">
<city name="Lyon" country="France"/> <city>Madrid</city>
<city name="Barcelona" country="Espana"/> <city>Barcelona</city>
</cities> </country>
</countries>

76 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Other new features

Fewer restrictions on ‘head to head’ mappings

– Required to support v7 map conversion

Submap now supports multiple inputs

Added xs:<type> functions for casting values to
anySimpleType target
– Now available in transform picker
– Saves having to write custom XPath

77 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation

Summary

New v8 Mapping Node

– Improved usability, capability and performance

Good Map Design
– Hierarchical mapping editor sub-divides complex transformations
– Remember to use ‘Local’ transform between structures
– Add conditions to control creation of target
– Use refactoring tools to restructure mappings

XPath 2.0 allows complex transformation logic
– Highly expressive syntax
– W3C open standard

Enhanced Database support
– Select, Update, Insert, Delete, Stored Procedures

Comprehensive ‘assisted convert’ tool

78 IBM Integration Bus V9 Beta Program © 2013 IBM Corporation