RedPrairie Integrator Tips

RedPrairie Integrator tool (also called SeamLES) allows RP toolset to communicate with any external system. Following are some tips that may come in handy:

• Some common tips - including some performance tips
• Integrator query shows as a bad query in Oracle
• About Integrator Caching
• IFD Field Class: EO Expression on child segment
• Do not store hard-coded values in integrator settings
• Restrictions on MOCA based Retrieve Method Implementations
• Integration by reading foreign databases
• Create files from communication methods that do not use files

---

• Some common tips - including some performance tips
• DO NOT use IFD Field algorithms to fetch event data.
Please Please Please do not do it. It gives me a heart-burn every time I see a result IFD algorithm pulling event data, e.g. If EO has a field like "stoloc" and you need arecod of it, people will add arecod as a field in result IFD with a field level algorithm to pull the arecod by a single select. In worst cases I have seen 10s of such algorithms with single selects. It creates a performance nightmare. In such cases you should add fields to the corresponding EO.
So general rule should be that you must not select event related data in an IFD Field algorithm. It is ok to fetch some constants like policies, code descriptions, convert UOMs etc. but not this.
Also when looking for performance look at these IFD field algorithms and even if you are doing it right - e.g. policies etc. sheer number of these calls can have an adverse impact.
Imagine a full truck-load in SHIP_LOAD. You may have 1000s of segments - what if you had these selects in some of these fields; you can easily imagine the stress on the database. By employing a caching strategy or pushing them to a parent IFD segment or EO segment you could really improve performance.
• Use "Synchronous except send" rather than "Asynchronous" for result IFDs going to external systems.
Typical flow of an event that creates asynchronous result IFD is:

Event Status	Description
EC	Event Output Created
IC	Result IFD Created
SC	Result IFD Sent

When result IFD is ASYNC, user gets the prompt back when event is in EC. Then slIFDGen (A) task runs on an interval to work on these events one at a time to create result IFDs from them. Then another task takes events in IC to send the transactions to external systems.
The slIFDGen (A) can me multi-threaded but an ever better option is to simply use "Synchronous except send" rather than Asynchronous. This implies that when event is logged it will create EO and also create result IFDs. When user gets the prompt back, event will be in IC not EC. It may take slightly more time but that overhead will not be perceivable by the user. But the advantage will be that there will not be a single task responsible for moving so many events from EC to IC.
• Use "Synchronous" for result IFDs going to DCS.
You should never use synchronous communication for transactions going to external systems because that tightly couples the two systems and a transaction will fail if the communication link is down.
But for transactions going to DCS, the work is done by processing algorithms and there is no communication link. For these result IFDs:
1. Always set result IFD as synchronous
2. Set "Always persist Event output" and "Always persist interface document" checkboxes to off
3. Set "Persist event data" on event to on. You may turn it off if performance gain is significant. Keeping it on provides event argument data in event query
This will greatly improve the performance because integrator will not write event output data and result IFD data to the database. That is a significant chunk of I/O that is saved resulting in significant improvement. Cost is that in event query you will see the event and its arguments but no EO or result IFD data.
• Separate identification and processing for inbound data
When we get downloads sent to integrator, we can configure the slInAdapter task to either process all the way (download goes from NEW to EDONE or EERR) or we can break it up such that slInAdapter only identifies the download and slIFDEvt will log the events from it. In this case download will go from NEW to ID. slIFDEvt will take it to EDONE or EERR.
This can help in performance. Furthermore we can start multiple instances of slIFDEvt which further improves the performance.
• A separate sender slIFDGen for each external system
It is possible to have a single sender slIFDGen to send data to all systems. But it is better to have a separate sender for each external system.
• Use CSV data layout for result IFDs if using XML, processing algorithm, or table based
The data layout on result IFD defines how data is stored in sl_ifd_data_dtl table. As you can see there is no option there for XML - the reason is that XML is a communication detail. This implies that the result IFD layout could be whatever you want in such cases.
So in such cases I recommend always using CSV rather than FIXED BYTE because it provides you the flexibility to change the lengths of the fields more easily.

---

• IFD Field Class: EO Expression on child segment
This is a less known (documented) feature which comes in handy. Lets say that you have Event Output data that looks like


ORDER: ordnum=A
   ORDER_LINE: ordlin=001, prtnum=A1, qty = 10
   ORDER_LINE: ordlin=002, prtnum=A2, qty = 20
   ORDER_LINE: ordlin=003, prtnum=A3, qty = 30
   

Now lets say at the result IFD level your result IFD Segment which is at the ORDER level needs to access some data from the ORDER_LINE level; that is when this feature comes in. Lets say your result IFD Segment at the order level needs the first part number, or total ordered quantity. In these cases you can add a field to the result IFD Segment with class of "EO Expression on Child Segment". Then you can put something like:
<<EO_SEGMENT>> [SQL Group expression]
So to get first part number, you will say:
<<ORDER_LINE>> min(prtnum)
to get total ordered quantity, you will say:
<<ORDER_LINE>> sum(qty)
to get number of lines, you will say:
<<ORDER_LINE>> count(*)
The EO Segment can be the immediate child, or grand child and so on. It is not restricted to the immediate child. There are other classes like "EO Expression Look up first" and "EO Expression Look down first" that can work for a subset of the use cases but those classes do not respect the parent- child relationship. And those classes cannot apply group functions either.
Often people do not use this option and end up changing the event output to provide additional data elements. Changing event output should be the last resort.

---

• Integration by reading foreign databases
Often when we are dealing with a custom system to integrate with, the integration discussion rapidly moves towards the other system creating files in a certain format and RP consuming them. Or using a queuing mechanism. There is another option.
RP interfaces to Oracle Apps, JDE, and Great Plains use the strategy where no download is sent to RP, instead RP peaks into the host system's database. This strategy should always be considered when interfacing with a custom system. For the overall project, it saves a lot of time because the work of creating intermediate files is taken off the table. The project then becomes:
Infrastructure Needed If RP is running on SQLServer, use linked servers to connect to host system database (it could be any major database). If RP is running on oracle, you will use Oracle heterogeneous connectivity option to create database link to a foreign database. You could also employ any other mechanism offered by the host system such as web services.
Host to RP
1. Create a new system in integrator
This new system represents the host system.
2. Create a notification table on host system
Host will populate that to indicate an event occurred. It will create a single row per event and put in some key data that allows you to pull more information. This table will also indicate which event it maps to in RP, e.g. ORDER_MANAGEMENT or PART_MANAGEMENT etc.
3. Create alternate event expressions in RP
You will map the event argument to the column name in this queue table
4. Create Retrieve Method implementation from the new system.
You will use the standard event output but change the retrieve method implementation for the new system to pull extra data, such as order header, address, and line data.
5. Standard result IFD to DCS should work
Since you mapped to standard EO, result IFD should just work.
6. Configure the standard table-to-event adapter
RP to host
1. No new system to create, same system as above would work
2. Configure the ODBC communication method for this system
3. Create new tables on host where you will populate all of the data
4. Create custom result IFD in RP and send to this new system
The result IFD should be based on standard EO if possible. Custom EO is ok as well. The result IFD will have a special field in each segment called "TABLE_NAME". This tells where this data goes. Fields within the segment correspond to columns in the table.
Using this strategy shaves off weeks from the overall project. I have used this successfully in several projects; not to mention that the design for JDE, Oracle Apps, and great plains also use this.
So be creative when discussing integration possibilities.

---

• Restrictions on MOCA based Retrieve Method Implementations
When creating a retrieve method implementation, you can use SQL or MOCA. With SQL you use from and where clause and with MOCA you can put in a MOCA command snippet.
With MOCA there are some restrictions.
1. You cannot use expressions when putting in EO Field column data. With SQL, for example, you could say prtnum||'X' but something like that will not work for MOCA based retrieve methods. Similarly in SQL you have some bind variables available like :i_c_absent_ind_char etc. These cannot be used in MOCA based RMs This is important to note because the client applications automatically add these for missing fields and then you get a "No rows affected" error and it is hard to find out why. There is no net loss of functionality because you can put all of these expressions in the MOCA snippet quite easily with a "publish data"
2. Order by and group by is not respected for MOCA based RM. For "order by" there is no loss of functionality because you can use sort moca command. Grouping is more difficult - you could use groovy.
MOCA based RMs are a good idea though. They improve code reuse. It also allows for creating solutions like calling external web services or a RM that can update data as well (Voice interfaces use the latter).

---

• About Integrator Caching
Integrator has a lot of meta-data to support the integration flow. The events, event outputs, Retrieve Methods, Algorithms, Result IFDs etc. are all in the database.
If you are analyzing performance, you need to realize that regardless of how much data is there, All of it is cached after the first access. So it is not worth your while to worry about these SQLs. Just make sure that sl_cache_parm table has large numbers as cache_siz. We expect all of the data to be cached so increasing the numbers is ok.
Often people trace a slow transaction and then go off on a tangent about these 100s of SQLs that are pulling data from integrator meta-data tables. If folks who are not familiar with integrator get involved (e.g. DBAs) then the discussion goes further off track because they keep coming back to these SQLs. You need to understand that these SQLs make no difference over the long run. For example if you are downloading an order - after first download, every other download will not read this meta-data.
Integration performance issues are always in one of the following places:
• Bad SQLs in retrieve method implementations
• Expensive SQLs in blocking algorithms, processing algorithms, field level algorithms etc.
So focus on these places when troubleshooting for performance. Regarding integration meta-data only issue could be that you have to increase the cache_siz.

---

• Create files from communication methods that do not use files
Lets say you are communicating to another system over sockets or over JMS. In such cases what if the message is corrupted over the queue or the socket; how would you troubleshoot? Similarly if data is sent to you how do you see in raw format what was sent before it is processed.
People generally consider communication method as a sacred entity that no one should touch - in fact creating custom communication methods is quite easy and I always create a custom one to support the following ideas:
• Create environment variable UC_SEND_OVER_QUEUE and UC_CREATE_FILE_FOR_MSG
Idea is that if UC_SEND_OVER_QUEUE is F, I would not call the components that push message to the queue. This implies that if the development environment is not connected to the queue my events will still go to SC.
The UC_CREATE_FILE_FOR_MSG, if set to T, will write a file in a pre-defined location with the full message that the system was about to push to the queue.
These two help a lot because I can always see on my system what the message will look like. It helps in troubleshooting where the exact issue is.
To implement this you just have to modify the post algorithm of the communication method (and then create a custom communication method).
• Use this for inbound data as well
For data coming into integrator, you cannot make a change in the communication method; instead override the "sl_handle inbound_data" command. In this case use the UC_CREATE_FILE_FOR_MSG environment variable to see if the functionality is enabled (you can also use the communication method attributes). At this time you have "dwnld_info" available along with "data". dwnld_info may have interesting information to create a file name (e.g. for JMS it has a signature of the message). data is the raw data.
So you can create the file first and then call the "^sl_handle inbound_data" to call core functionality.

---

• More to come later!