One of the frustrations of development is a hanging map. It may be trying to create too many iterations, or it may be stuck in an infinite loop (or should we say 'infamous' loop haha). Some of the first things I check are:

Common Mapper

'While...do' loops that may not be terminating. They will cause the translation (frustration) to go on forever until the translation is killed. These rules can be commented out and quickly confirmed or ruled out as the problem. If you narrow it down to a “while..do” rule, you can research why it does not update. For instance, there may be an if condition that is never true. Messageboxes can help verify this.

Another common culprit are the 'invalid links'. An example is a field from a single occurring record linked to a field in a record with a large maximum loop setting. This will try to create as many output records as the maximum occurrence setting will allow! It will eventually complete, but this might be of little solace when the output record can loop 999,999 times. You should receive a warning if a link is invalid (make sure you have warnings turned on in the Options → Preferences → Confirmations tab).

Outbound SQL map cursor is not advancing to the next row:

One cause my be that an input record Key field does not match on a record with the cursor operation set to automatically advance to the next row of the result set. This may be fixed by adding an 'Order by' in the query, and using string type fields for the key fields.

In Gentran:Server for Windows

Outbound ODBC maps may hang if:

The Partner cannot be found. Please consider utilizing the Update process from the Outbound ODBC Mapping No Partner Found whitepaper.

Stop the services for the instance, and from a command line, install using the install3rdParty(.cmd for windows or .sh for unix).

Change to the <install_dir>\bin directory and do the following command (change .cmd to .sh for unix):

install3rdParty.cmd IBM_DBAccess 1_0 -j <path-to-jar-file>

The jar file can be placed in the bin directory or a directory of your choice. It may be removed once the install is complete. You can verify the install by viewing existence of

<install_dir>/jar/IBM_DBAccess/1_0/SCI_MapUserExit.jar

I won’t go into the details behind creating JDBC pools here, but this user exit will need a pool, to connect to the database. It’s the exact same kind of pool you’ll use for a SQL map, or (lightweight)JDBC adapter.

Once you have the pool setup, restart your services.

There’s not much to the extended rules, to execute the user exit.

1. Declare an object to call the user exit

2. Declare strings to hold the SQL statement and result

3. Initialize the variables

4. Create a string for your SQL statement

5. Execute the SQL statement

Ex:

//declare

object dbutil;

string[100] return_value, sql_string;

//initialize

dbutil = new("com.ibm.mapping.dbaccess.SqlAccess");

return_value = “”;

sql_string = “”;

//create

sql_string = "select FIELD from TABLE where OTHER_FIELD = 'VALUE'";

//execute

return_value = dbutil.executeSQL(sql_string,"Pool_Name");

The initialization line for the object needs to be exactly as shown here.

The sql_string is just a string variable, and can be created with hardcoded values as in this example, or can be created using other string variables or string fields.

The Pool_Name in the execute line, should be replaced with the actual JDBC pool name.

The return_value could also be replaced with #string_field.

That should be all you need, to take it and run with it.

If you want to execute an update/insert/delete statement, it’s the exact same as above.

The only difference is that return_value will contain the number of rows affected.

Now here’s more information than you probably want to know…

The .java used to create the .jar contains the following:

package com.ibm.mapping.dbaccess;

/*

* Created on September 13, 2013

*/

import java.sql.*; // import java.net.*;

import com.sterlingcommerce.woodstock.util.frame.Manager;

import com.sterlingcommerce.woodstock.util.frame.jdbc.JDBCService;

/* JDBCService is listed as deprecated but per support it is still in use */

Replace the "Pool Name" and "select query" with the corresponding Pool Name and query to run

As I mentioned at the beginning, customer support does not support custom user exits, so use this at your own risk. If you need additional help, you can always talk to sales about getting a consultant or ask on some of the user forums or email groups.

Storing values into process data can be real useful. However, when you “update” process data, it is an update TO process data. It is not an update OF process data. If you have multiple updates, it will create multiple entries. Multiple updates will NOT overwrite the value in process data.

Doing a “select” will return the first value from process data. Selecting a value, manipulating the value, then updating process data with the new value will create multiple entries. Every time you do a select from process data, it will return the initial value.

This is fine if you’re only using two values. If you have more than two values, then you need to use the last function within your xpath.

Here’s an example of concatenating a list of values separated by commas:

string[100] test_string;

test_string = "";

select xpathresult into test_string from processdata where xpath = "//test[last()]";

This rule does a select to get the current value. If the length is greater than 0, it means there already was a value. Since the select returns a string, we need to convert it to an integer first with atoi.

We then increment the value that was returned.

If the length is 0, there was no value in process data yet. We assign a value of 1, to the integer variable instead.

We then convert the value back to a string, and update process data with the new value.

Process data gets updated and looks like this:

<next_num>1</next_num>

<next_num>2</next_num>

<next_num>3</next_num>

<next_num>4</next_num>

You can see how it just increments the counter for each update to process data.

That’s really all there is to it. From the little testing I’ve done, you can use full paths in your xpath, or the //<node name>, just as long as you add [last()] to the end, it returns the last. I’ve seen some rather complex xpath statements, so I’m not sure how well it works with the more complex ones, or what tweaks you would need. If you come across a statement that doesn’t seem to work, let us know in the comments.

In both of these examples, we’re just updating right back to process data and that’s it. In real world scenarios, you’re going to probably use the variables elsewhere in your extended rules or replace the variable with a #FIELD to store the value directly into a field.

The next time you need to keep a running variable in process data, you can simply use the [last()] function and it’ll make your mapping life that much easier.

Most people understand the use of keyfields for the
input side of the map. (If you don’t,
let me know, we can do an entry on that)

But there seems to be some confusion on using
keyfields on the output side of the map.

Some may think it’s used to skip unwanted/empty
iterations of a repeating structure, but that’s not the case.

The option to add a keyfield on the output side of
a map was necessary for the first versions of the translator, thru Gentran
Server for Windows 2.x.

In those early versions, it was not necessary for
a link to contain data, to cause the map to trigger the output record. As long as data was present at that level,
then it would trigger the output record.
Any other rules on the output record would run, and you would have
unwanted records.

The keyfield was included, to specify that you
only wanted those records that had data in the selected field. The field selected for the keyfield, would be
a mandatory field, or a field that you would expect to be in the output data.

It is NOT recommend to use the following setting,
but you can simulate the behavior of the early versions of the translator by
checking this box in the Details of the map:

That option was added in Gentran Server for Windows
3.0, to be backwards compatible, for any users that relied on the translator to
not need data within links, to trigger an output record.It is still present today, for the same
reason, but is strongly discouraged from being used in any of the current
versions.

With that said, here are examples of what the
keyfield on the output side was originally used for, when it was needed.

Here is a simple map, a header record with 2
records inside a repeating group.The 2nd
fields on the output side, have extended rules that just set the field to
“test”.

The input data contains:

HDRHeader

01one a

02two a

01one b

01one c

01one d

01one e

02two e

01one f

01one g

Note that the 02 record is only present for “a”
and “e”. There is no 02 record for
“b”,”c”,”d’,”f” and “g”.

With the way 2.x translators behaved, you would
get the following output.

HDRHeader

01one a
test

02two a
test

01one b
test

02
test

01one c
test

02
test

01one d
test

02
test

01one e
test

02two e
test

01one f
test

02
test

01one g
test

02
test

Notice how you get an 02 record in the output for
every iteration of the Group that was read in.

There were 7 iterations of the group, so 7 02s
were written out. The 8th
iteration was empty, so the translator stopped processing then.

This obviously wasn’t acceptable behavior, so the
keyfield on the output side was added.

Select a field that will have data, in the
properties of the ouput reccord:

When you run the map with the keyfield selected,
you get the same behavior as you expect today:

HDRHeader

01one a
test

02two a
test

01one b
test

01one c
test

01one d test

01one e
test

02two e
test

01one f
test

01one g
test

The only 02 records are those that have data
linked across to the field in the keyfield.

With the enhancements done for Gentran Server for
Windows 3.x and newer (including all
versions of GIS/SI). The
translator was modified to only trigger the output record, when data is present
in at least one field that is linked to the output record.

With the current translators, the only time the
keyfield will come into play, is when there are multiple fields linked:

With the data:

HDRHeader

01one a

02two a
two aa

01one b

02
two bb

01one c

02two c

Note that “a” and “aa” are present, “b” is not
present, but “bb” is, “c” is present but “cc” is not present.

With the current translators, as long at least 1
link has data, it will trigger the output record:

HDRHeader

01one a
test

02two a
two aa

01one b
test

02
two bb

01one c
test

02two c

When
you add a keyfield on the output record:

This will only output the 02, where the first field
is present:

HDRHeader

01one a
test

02two a
two aa

01one b
test

01one c
test

02two c

Note the 02 for “bb” is not in the output, since there
was no “two b”.

The use of the keyfield in this aspect, is getting
away from the original design of why the keyfield was added to the
product. As I mentioned in the
beginning, the 2.x check box and the keyfield on the output are still in the
product for backward compatibility.

Looking at all these examples, it may look like
it’s skipping the unwanted/empty iterations of the 02 records. But this is all due to the fact that the 01
and 02 are in a repeating group.

To expanded on how the translator processes data,
it will continue to evaluate the links for the output records, until it finds
an empty iteration for the current REPEATING STRUCTURE on the output side.

So in this case, the repeating structure is the
Group. As long as those 01 records are
present, it will continue to evaluate the 02 record as well.

As soon as there are no 01 or 02 records for the
output, then it will stop evaluating, and leave the current repeating
structure.

If you have a repeating record, instead of a
repeating group:

With input data:

HDRHeader

01one a
two a

01one b
two b

01one c

01one d
two d

01one e

01one f
two f

01one g
two g

It will evaluate all 8 iterations, for 2.x,
without a keyfield:

HDRHeader

01test
two a

01test
two b

01test

01test
two d

01test

01test
two f

01test
two g

Again, because there just has to be data read in
for each level.

If you add a keyfield or test it both with and
without a keyfield with the current translator, it stops at the empty
iteration:

HDRHeader

01test
two a

01test
two b

The same holds true, for when a repeating record
has multiple links to it.

As soon as the keyfield does not have data in it,
it will cause an empty iteration.

So, the keyfield is not allowing the translator to
skip unwanted/empty iterations. As soon
as it finds an empty iteration, it stops evaluating the rest of the maximum for
the repeating structure.

Hopefully this clears up the design and use of the
keyfield on the output side of the map.

It really is only still in the product, because
they have to keep the backwards compatibility as an option.

The same goes for the “2.x compatible” check box.

The next time you come across the keyfield on the
output side of the map, you’ll have a better understanding as to what it’s
doing. I usually end with saying that it’ll
make your mapping life that much easier, but if you were hoping for it to allow
you to skip unwanted iterations, that’s not the case.

I’m going to dip back down to another basic mapping concept, only because it’s come up a few times in the past week.I’ll try to do something more complex next time.

When designing a map, there are only a few times when you should create a record without a tag.

Any records that do not have a tag, will read the next line of data into that record, regardless of what it is.

There are some situations with specialty maps, like document extraction maps, where you may want to do this, but in general, it’s not the best of mapping practices.

The biggest offenders of being tagless, are temporary records.It’s easy to forget to add the $$$ in, when you’re testing a quick concept, to see if it works.

If data is not behaving like it should, or you’re seeing data written out in places that you’re not expecting it, chances are you have a tagless record.

Luckily, there are a couple easy ways to determine this, and find the culprit.

Run TXTrace, each product is different as to how to run TXTrace, so I won’t go into the boring details here.If you need help, feel free to comment or googling it should result in a few good results.

The TXTrace log will show which lines of data are read into which records.

Print out the mapping report.If you choose File > Print to file, and select “Print record details”, this will create a text file with… wait for it… the record details (shocking, I know ).

In addition to other information, this shows the record name and the tag:

Record HEADER*Tag HDR at 1Conditional

You can do a search for the word Tag, and quickly go thru and see if any are blank (in most editors, F3 is your friend, it will just go to the next):

Record HEADER*Tagat 1Conditional

If you find yourself in a situation where there just isn’t a tag you can add, and you don’t know how many records there are going to be, if any, there’s a way around that.

For simplicity, we’ll assume we have a data file with:

HDR <data>

<misc data>

<misc data>

<misc data>

SUM <data>

You want to define a Header record, a catch all record (that will have extended rules to do whatever with the data), and then a Summary record.

The problem with leaving off the tag for the 2nd record, is that the Summary line of data will be read into it as well.In this simplified example, it wouldn’t be too bad to add additional rules, to check if it’s a Summary and send the info to temps, but in real scenarios, you could have many records after it.

In this example, we want to tell the translator to read anything into the 2nd record EXECPT if it begins with SUM.If the 2nd record doesn’t have a 3 character field that matches up with the positions of the SUM, you’ll need to add one (remember, multiple fields can overlap, so even if there is already a field with say positions 1-10, you can add another for 1-3, just do NOT auto-position the record).

You’ll then want to go to the properties of the 2nd record and choose the Key Field tab.

Here you can select the field that will match up with the positions for the SUM, and add a constant of SUM (or codelist, if there are multiple tags that could come after).The key thing here is, to make sure you check the box that says “Match record when key does not match”.

This will do exactly what we need it to.It will read any line of data into the record except for when the field you specified matches the constant you specified.

That’s it, pretty simple way around it.This will work for all of the current products…

If you’re using Gentran Director, or Gentran Server, there’s an alternative as well.

In those products, under the “Special” tab, for the record properties, there is an option to choose “Wildcard”.This does basically the same thing as the “Match record when key does not match”.

When Wildcard is selected, whatever is specified as the Tag for the record, will be what does NOT get read into the record.You can also fill out a 2nd tag in the “Alternate Tag” box, right next to the wildcard selection.In our example, you’d just put SUM in as the tag.

The only drawback to that, is anyone (or you looking at the map years from now) usually just check out the tag, most people don’t normally switch to the Special tab, to see if it’s a wildcard.

In either scenario, I like to add a quick note in the “description” of the record, so I remember it’s not a normal record.

The next time you see your data showing up in the wrong spot or disappearing, you’ll know right away to see if you’re missing a tag, and it’ll make your mapping life that much easier.

As always, any and all comments are welcome. Even a quick “thanks”, to let us know that you found this somewhat useful, is appreciated!

Most people only use
constants in the standard rule, to write out a set value.

Some people will even
use them, when setting up key fields for inbound processing.

That’s all fine and
dandy, but unlock their true potential and use them in extended rules as well!

Yes, any constant you
have defined in the map, can be referenced in an extended rule.

As a reminder, you can
see and setup your constants by going to Edit > Constants.

Each constant will have
an ID (name), type and value.

You can reference any
constant in an extended rule, simply by using the ID, just as you would a
variable.

Now, you may be
scratching your head, thinking “if something’s a constants, why wouldn’t I just
reference the value in the extended rule?”.

That’s a valid point,
and for many situations, it would be a lot easier to just type the value.

Example:

If
#FIELD = “constant value” then

…

Or

#FIELD
= “constant value”;

Or even

Writeblock(“constant value”);

In those situations,
yes, it’s a lot easier to just type the value in-line and be done with it.

But what if you need to
reference the same value in multiple extended rules? What if the value changes?

Now you need to go dig
thru the rules, and change them all.
Sure, you could create a variable in the Pre-session rules, and
reference it instead. It’ll accomplish the same idea.

But what if you have a
constant value that has special characters in it?

To me, it’s a bit of a
burden to go look up the hex values, then replace them within the quotes with
^##. Especially when there are multiple
values. Plus, if you look at the map a
year from now, you have to “decode” it, to tell what the true value is. Anyone else that looks at your map, won’t
know off hand either.

I personally always
forget the exact syntax for using the release character too. If your constant has quotes in it, is it /”
or \” or //”, it usually takes me a couple tries to get it 100% correct.

Instead, just copy and
paste the value into the constant and be done with it.

Tabs? No problem

Quotes? No problem

Unprintable characters?
No problem

The constant value
takes them all, then you just use the ID in the rule.

I’ve seen some maps
where there is a constant called QUOTE, with a value of “.

Then on certain fields
where the value of the field needs to be within quotes, they have a rule

#FIELD = QUOTE + #FIELD
+ QUOTE;

Simple as that, and
anyone looking at it, has some idea as to what is going on there.

I ran across a problem
recently, where there were certain descriptions being sent in the data.

They were 250+
characters long, and most only differed by a few characters.

Originally, they were
trying to type the entire description within the rule.

This caused a few
problems…

1. You can only have 256 characters within
quotes

2. Those that were under 256, the extended rule
box only shows you about 50 characters, then you have to scroll.

3. It was a pain in the rump, trying to see
exactly which description we were looking at, since they were all very similar.

Once we created a few
constants, and gave them some meaningful names, it was a lot easier to read the
extended rule when it looked like:

IF
#DESC = const_itemA then

...

IF
#DESC = const_itemB then

...

IF
#DESC = const_itemC then

...

Which leads me to my
final bit of advice for constants.

Since they do look and
behave just like variables, I recommend either going with a naming convention
that lets you know it’s a constant, like adding const_ to the beginning of the
ID (name). Or if your variables are
usually lowercase, make constants uppercase.

If nothing else, just
add in a quick comment, to say it’s a constant.

IF
#DESC = itemA then //itemA is a constant

...

Sure it all makes sense
now, but when you or someone else looks at the rule later on, you don’t want to
be searching in the map to see where the “variable” was declared and populated,
only to finally remember it was a constant.
Save your hair from being pulled out, and do the extra work up front.

Hopefully now that you
know you can use constants in extended rules, it can help clean up your map,
and make your mapping life that much easier.

As always, any and all
comments are welcome. Even a quick “thanks”,
to let us know that you found this somewhat useful, is appreciated!

I figured, I'd start the blog off with my favorite extended rule of all time, the messagebox().

This powerful, yet widely overlooked, extended rule, can help you with many different scenarios.

It is often part of my first response, when someone asks me a question.

Why is my field writing out the wrong data? - Did you messagebox the variable, to see where it picked up the bad data?

Why isn't my IF statement working? - Did you messagebox the values to see what they contained?

Why is my temp group not writing out all iterations? - Did you messagebox the indexes?

I could go on and on, but you'd probably hit the back button, if you haven’t already.

For GSW (Sterling Gentran:Server for Windows), the messagebox will pop up an actual box to the screen. While the messagebox is on the screen, the translation is paused. Based on the 2nd parameter you use, different buttons will be available (such as OK, Yes, No, Cancel etc.), and can return different values back to the translator. But for now, we'll focus on just displaying values.

For SI (IBM Sterling B2B Integrator), the messagebox will write to the tx.log, and does not show on the screen.

(Stay tuned to the end of this blog entry, to get the super secret way to see the values easier!)

Using messagebox() is easy, you just pass it a string and the number 0 for the 2nd parameter (again, GSW can take values other than 0).

messagebox(string,0);

The string can either be a string variable or a #field name.

A lot of people get discouraged, when trying to use the messagebox(), because they’ll simply display the value.With SI especially, seeing a bunch of values show up in a log, isn’t that helpful.The secret, is to add literals to a string, and combine values into one line.

Example:

String[100] msg;

msg = "";

msg = "First field: "+#fielda+" Second field: "+#fieldb;

messagebox(msg,0);

(Note the spaces within the quotes, for easier readability)

Assuming you had 2 fields in your map named fielda and fieldb, and they contained the data ABC and 123 respectively, your message would show up as:

First field: ABC Second field: 123

This is a lot more helpful, especially when looking thru logs, and the possibilities are wide open to all that you want to combine into 1 message.They can also help vent frustrations, when a rule is giving you trouble.I’ve been known to add messages like:

msg = "WHY IS VARIABLE store no: "+store_no+" WHEN IT SHOULD BE 1234?!";

messagebox(msg,0);

(Yes, it’s those little things, that entertain me…)

One limitation with messagebox() (hey, pobody’s nerfect!) is that it can only use strings.So, any numeric or date values, that you want to peek into, have to be converted.But we can use the same logic with them.I use this a lot with indexes, when loops are not writing out correctly:

Example:

String[100] msg;

String[5] str_x, str_y;

msg = "";

x = "";

y = "";

…

$Temp[x][y].#temp_field = #fielda;

ntoa(x,str_x);

ntoa(y,str_y);

msg = "Stored: "+#fielda+" into: ["+str_x+"]["+str_y+"]";

messagebox(msg,0);

…

This would give a message such as:

Stored: ABC into: [2][3]

Usually, if you’re storing into a temp with indexes, there would be multiple lines, so this becomes helpful when you see a number get skipped, or the child not resetting correctly.Then you can narrow it down as to what’s causing the index not to have the correct value.

I could go on for days with examples.If there’s something that you would like to see more information on, leave a comment, and we can do an entry specifically for it!

With the addition of SWIFTNet support a few versions back, you can use the cerror() extended rule instead of messagebox(), just switch the order of the parameters.

cerror(1,msg);

Whatever number you put for the first parameter, will be the “error number” that shows up in the translator report.I use this to my advantage, when I add multiple cerrors, I use unique numbers in each, so I know which cerror was executed.

This will display the message right in the translator report.

Report Entry:

Section: INPUTSeverity: ERROR

SyntaxSpecific: falseSyntax: -1Code: 1

Info:

10004: Field Name

Field 1: ABC

10019: Location Index

0

Code:, will show the number that you included, and if it’s a “real” error number, will show the description for that error.I like to use single digits, so I can tell them apart from real errors.

10004: Field Name, will show the string below it.In this case Field 1: ABC is the output from my msg variable.

Cerror() is real handy, but does have the limit of the number of errors that will show up in the translator report.Also, it does cause an actual error to show up in the translator report.This is fine for maptest, but if you’re testing an entire BP flow, it would cause an error.

Overall, learn to embrace the messagebox() and it will make your mapping life that much easier.

I probably should have included all of this, in the first entry, but was too excited to just get something up and going.

As you can tell from the description, this blog is going to be mapping and translation advice, hints, tips, etc. for IBM Sterling B2B Integrator (formally GIS/Gentran Integration Suite) and Sterling Gentran:Server (primarily for Windows, but we may throw some UNIX or Gentran Director in there).You’ll probably see them referred to as SI and GSW for the most part, or just Sterling Integrator and Gentran Server.

The authors are going to be from IBM support, but this by no means should be an alternative to opening a PMR.Think of it more as mappers helping mappers.Also, needless to say, any opinions or comments are those of the author specifically, and not those of IBM.

Most of the entries are going to assume that you're familiar with using the mapper, and can create your own maps.

They are going to be single topic posts to help you get more out of the mapper, and show you some tips you may not have thought of.

We will try to vary the amount of knowledge required from time to time. So if something seems too hard, or to easy, just stay with us, and see if the next one is more up your alley.

We’re still working on what the frequency will be, weekly, maybe bi-weekly.The more demand and feedback, the more we’ll bring you!With that said, we love to hear your feedback and comments, good and bad!If there’s something you’re hoping to see on here, feel free to ask for it.