Just when you thought there wasn’t anything more to learn about Integration Manager something else comes along to demistify that theory. I have been involved in a JD Edwards on DB2 and AS/400 systems migration for almost 4 months now and last month I blogged about how Integration Manager can be a powerful tool in multiplatform systems integrations.
Part of disengaging JD Edwards is to write a series of integrations to existing systems running on the AS/400 platform. In the process, the client needed a simple to use tool which required limited programming and maintenance, hence the choice of IM. After unit testing the integrations, everything was A-Ok to begin with the week long systems testing, which would exhause the integrations while allowing the customer to get the overall “feel” for GP and how it would address the existing business processes.
One particular business process — Expense Reimbursements — required an integration to Payables Management. How difficult could this be, right? I have done over 100 PM integrations in the past so this could not be any different. During the system testing, the end-user reported incomplete distributions throughout the integrations of hundreds of expense reports into GP and provided the following log file:
At first glance this would seem typical of transactions that are actually missing distributions, but I checked against the source staging table in DB2 and all distribution records for each transaction reported as missing were actually present. The only distributions in the source were those for the expenses. In addition, this integration had been setup to default any missing distributions in the source recordset, which would automatically create the payables side.
I then checked each vendor (employee) record in GP for a missing payables account. After all, this would explain the case of the missing distributions. All vendor records were properly setup. Now comes the actual troubleshooting process after checking the obvious. My source queries were very simple too:
I proceeded to limit the source queries to just a specific employee and expense document number reported in the log as failing and re-ran the integration. Voila! The record went through to GP without any issues. Now the question was, why is IM kicking back valid records for the integration without any apparent reason?
I also checked to make sure the UseOptimizedFiltering switch was set to False and that we were running IM service pack 4 — more on IM switches here. After a while of looking at the source records in the staging table, I realized that the first records that IM imported were in the same physical order in the table were data was being retrieved from. This was very simple as DB2 tables are VSAM tables, making them very different from SQL Server tables in the way the store data physically. However, for the records that failed, IM was attempting to locate a distribution record in the same order specified by the records being read from the header query.
To correct the problem, I decided to enforce the order of the records in both source queries by adding an ORDER BY clause to the queries, by VendorID and InvoiceNum. This way, as IM advanced through the header source query reading the records, it would consecutively find these in the distributions source query tables as specified by the query relationships (1..M). Now my source queries looked like this:
Upon executing the integration once more, all expense transactions were properly integrated without any missing distributions.
Now, I cannot say I experienced this before with SQL Server sources, but this happens to be a DB2 source and just maybe the ODBC engine works differently, but I can say that by reordering the source queries, I was able to resolve these missing distributions issues and improve performance two-fold.
Until next post!
Mariano Gomez, MVP
Maximum Global Business, LLC.