This section contains short tips that I found while working with Sterling Integrator. I also got some from my friends. If you find something interesting and valuable to share, please add your comment down and we can include it in this list.

 

*******************************************

1  FSA – “File lock cannot be obtained” error – FSA in bootstrap mode should not use single file collect, as the whole folder is locked, and other files cannot be collected.

 Way to use FSA:

          o   Bootstrap Mode & collect multiple files

o   Non-Bootstrap Mode & collect single file or multiple files

*******************************************

2  Update Process Data in a map used by EDIFACTEnvelope service

"Invoke BP for every interchange" parameter in UNB is responsible for not propagating info to a child process run by the envelope!

• when the Invoke Business Process for Each Interchange = Yes then this shorter version of process data is passed to the BP

Process Data

• when the Invoke Business Process for Each Interchange = NO then this version of process data is passed to the BP

*******************************************

3  Option for select box in Custom Sevice - MUST have a unique name.

*******************************************

4  Check Consistency in SQL map

If we add a new column in database, that is referenced through the map, Check Consistency option will add a new column in the map.

But, if the column is added in the middle of columns, all columns from table, that appears before a new one, will be recognized in the map when Check Consistency is done, but all columns after a new one will throw an error.

So, good practice or even recommendation is to add a new column in table, always in the end, and we will avoid error when Check Consistency.

*******************************************

5  [format]DeenvelopeUnified not called

<assign to="Mode">document</assign>

If we add this assign in EDIDeenvelope process, it will not call [format]DeenvelopeUnified BP, but just update Process Data with something similar to:

*******************************************

6  Pass a ProcessData or just a part further to [format](De)EnvelopeUnified or to a BP defined in envelope

If we want to pass Process Data from the BP that contains EDIDeenvelope/EDIEnvelope further, to the last BP defined in envelope and called by …Unified BP, then just a step before EDIEnvelope/EDIDeenvelope we must add message_to_child/message_to_child statement with part or the whole Process Data into assign element.

To pass Process Data or a part of it from … Unified BP, before Invoke Service we have to add assign element with message_to_child

*******************************************

7  SFTP user identity key

User identity key and user cross reference is in AUTH_XREF_SSH table.

SSH_USER_KEY contains NAME_OBJECT_ID.

*******************************************

8  SetUserToken service

To add user to the process, if it is not done implicitly, we can do it explicitly:

<assign to="USER_TOKEN" from="string('admin')"></assign>

*******************************************

9  Select query in Oracle, with parameters, for LWJDBC does not work

If we have a query e.g. select * from tableName where column1 = ? and column2 = ?, and sometimes it does not work. We might check data types for the columns, and if it is CHAR type, maybe rpad (right-padded to the total number of characters that length specifies) function can help.

 Usage:

 RPAD (text-exp , length [, pad-exp])

 Example:

 rpad(?,24,’ ’)

*******************************************

10  Why Prepared Statements are faster?

It could be a good practice in SI to use SQL dynamic prepared SQL statements instead of static once.

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, DB will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times.

*******************************************

 

11  UPDATE Process Data from a translation map

• If we update Process Data from a translation map in this way:

update ProcessData set xpathresult="" where xpath="ROOT/ROW/";

update ProcessData set xpathresult=#AAA where xpath="ROOT/ROW/AAA";

 … result is:

•  … but if we write it in this way:

update ProcessData set xpathresult=#AAA where xpath="ROOT/ROW/AAA";

… result is:

 

*******************************************

12  Extended rule - string as HEX value ...

Examples for adding string as HEX values:

#addSpace = "^20";

#newLineFeed = "^0A";

#newCarriageReturn = "^0D";

stringVar = “#string1” + “^20” + “#string2”

 *******************************************

13 Where to put db2jcc_license_cisuz.jar in SI

db2jcc_license_cisuz. jar should NOT be placed in  \IBM\SQLLIB\...

It goes into SI_install_dir\jdk\jre\lib\exe in SI.

*******************************************

14 Uppercase and Lowercase in mapping

#stringField = new("java.lang.String",#stringField).toUpperCase();

#stringField = new("java.lang.String",#stringField).toLowerCase();

 *******************************************

15 SFG Custom Delivery protocol

The business process, that implements the custom protocol, name must be unique for each custom protocol. Do not use an underscore character (_) in the name of the business process.
You should avoid using a BP name with "_" and especially a BP name with several underscores.

If you use underscore(s) in BP name, then obscured password value must be in the element which name is composed of BP name portion after the first underscore + name of password parameter taken from consumer.

 *******************************************

16 Date Difference in map extended rule

 

 *******************************************

17 Convert scientific into non scientific notation of real number

Note: Data manipulation is done on Strings only. #field is String type.

  *******************************************

18 Keep Trailing Zeros for real numbers in a map

•  Change can be done in translator.properties:

• Change can be done in customer_overrides.properties:

• Setting can be chosen in the map itself, that you can find in Edit menu -> Details -> Always Keep Trailing Zeros (this option is available just for SI newer than 5.1)

  *******************************************

19 UPDATE and SELECT Process Data in map extended rules

  *******************************************

20 Change time zone in translation

Set up SYSTEM TIME into specific time zone

 

string[25] sysDateString;

datetime sysDateDate;

object sysDate;

object sysDateFormat;

#START_DT:23 = "";

 

sysDate = new("java.util.Date");

sysDateFormat = new("java.text.SimpleDateFormat","yyyy-MM-dd 'T' HH:mm:ss");

sysDateFormat.setTimeZone(sysDateFormat.getTimeZone().getTimeZone("Australia/Perth"));

sysDateString = sysDateFormat.format(sysDate);

 

#START_DT:23 = sysDateString;

 Change any TIME from one time zone to another (GMT -> Australia/Melbourne)

string[50] formattedDateD, formattedDateT, originalDate;
object dateB, originalFormatB, targetFormatD, targetFormatT;
integer createdDateInt;
integer createdTimeInt;

dateB = new("java.util.Date");

originalFormatB = new("java.text.SimpleDateFormat","yyyy-MM-dd'T'HH:mm:ss'Z'");
originalFormatB.setTimeZone(originalFormatB.getTimeZone().getTimeZone("GMT"));
targetFormatD = new("java.text.SimpleDateFormat","yyyyMMdd");
targetFormatD.setTimeZone(targetFormatD.getTimeZone().getTimeZone("Australia/Melbourne"));
targetFormatT = new("java.text.SimpleDateFormat","HHmm");
targetFormatT.setTimeZone(targetFormatT.getTimeZone().getTimeZone("Australia/Melbourne"));

originalDate = #CreatedTime;
dateB = originalFormatB.parse(originalDate);

formattedDateD = targetFormatD.format(dateB);
formattedDateT = targetFormatT.format(dateB);

createdDateInt = atoi(formattedDateD);
createdTimeInt = atoi(formattedDateT);

#TEMP_CREATED_DATE = createdDateInt;
#TEMP_CREATED_TIME = createdTimeInt;

Calculate number of DAYS, MONTHS and YEARS between 2 dates

//real numberOfDays;
//real numberOfMonths;
//real numberOfYears;

integer numberOfDays;
integer numberOfMonths;
integer numberOfYears;

string[5] numberOfDaysString;
string[5] numberOfMonthsString;
string[5] numberOfYearsString;

string[50] startDateString;
string[50] endDateString;

object startDate, endDate, startDateFormat,endDateFormat;
integer startDateMillis, endDateMillis;

string[50] startDateMillisString;
string[50] endDateMillisString;

//////////////////////////////////////////////

startDateString = left(#StartDate:2,10);
endDateString = left(#EndDate:2,10);

startDateFormat  = new("java.text.SimpleDateFormat","yyyy-MM-dd");
endDateFormat = new("java.text.SimpleDateFormat","yyyy-MM-dd");

startDate = startDateFormat.parse(startDateString);
endDate = endDateFormat.parse(endDateString);

startDateMillis = startDate.getTime();
endDateMillis = endDate.getTime();

numberOfDays = (endDateMillis - startDateMillis) / 86400000;
numberOfMonths = (endDateMillis - startDateMillis) / 2592000000;
numberOfYears = (endDateMillis - startDateMillis) / 31536000000;

ntoa(numberOfDays,numberOfDaysString);
ntoa(numberOfMonths,numberOfMonthsString);
ntoa(numberOfYears,numberOfYearsString);

#TEMP_DURATION_MONTHS = numberOfMonthsString;
#TEMP_DURATION_YEARS = numberOfYearsString;

*******************************************

21 Modify self closing XML element