Inside multi-platform integrations with Integration Manager
I have been working on a very challenging systems conversion project, from JD Edwards on AS/400 and DB2 to Microsoft Dynamics GP v9. Part of the challenge stems from the fact that certain processes, mainly in-house applications developed for the AS/400 will continue to support existing business processes pass the "go-live" date.
I am tasked with developing multiplatform integrations to support application processes in place and by doing so, I've had to dust off my DB2 - AS/400 skills. Per the client's request, the integrations need to be easy to use, extremely flexible and adaptable, and low maintenance (as in little to no programming required). Many of you may think that due to the nature of the systems involved, this could very well be an eConnect implementation. However, Integration Manager was chosen because of the characteristics previously described.
As I work through implementing the first integration to manage expense reimbursements from Lotus Notes, my integration needs to read data from some interface tables in a DB2 catalog. As such, we deployed the IBM Client Access ODBC driver. Working through the configuration was fairly simple, but due to the driver features I was afraid Integration Manager was not going to be able to store the DSN configuration.
One thing I discovered while working with IM v9 SP4 (9.00.0054) is that due to it's underlaying implementation of the almost outdated use of ADO technology, it cannot perform a preview of the source queries, with the following error message:
Upon further research, it seems this error is generated because server-side cursors are not supported with ISAM (Indexed Sequential Access Method) files. As it turns out, ISAM was originally developed by IBM and later replaced with a storage file system methodology called VSAM (Virtual Storage Access Method). VSAM is the physical access method used in DB2.
What does this have to do with IM? Well, IM -- version 9 specifically -- uses ADO to connect and retrieve data. The problem with ADO and VSAM file systems is that the cursor location needs to be set to AdUseClient and the cursor type needs to be checked for bookmark support:
oRecordSet.Supports(adBookmark)
Apparently, this is not being done in IM v9's internal ADO implementation. The good news is, I checked this against IM v10 and I have to say it works just fine, since, IM v10 was developed from the ground up with ADO.NET.
Given there was no preview available, testing began with a small dataset.
Integration Manager began importing expense vouchers and in the process neglected randomly to import the distributions. I executed the same integration a few times and obtained random results: transactions that previously imported fine along with their distributions did not import and failed the second time around. I verified the RecordSource Rule and Source properties to make sure they were set to Default Non-Imported and the source query for the distributions, respectively.
Upon adding some VBScript to write the records that were being read and attempted during the integration, I noticed that the header records were being read in order while the distribution records were shifting randomly. In some cases IM would match the header record with the distribution record, but in some cases it would skip a valid set of distributions for an expense voucher. By changing the UseOptimizedFiltering flag in IM to False the imports began to work as expected.
Integration Manager is a very useful tool and should not be overlooked when considering multiplatform systems integrations, especially if the customer insists on easy of use. All the integrations will be scheduled on a server and will run unattended, which only validates IM's flexibility.
Until next post!
MG.-
Mariano Gomez, MVP
I am tasked with developing multiplatform integrations to support application processes in place and by doing so, I've had to dust off my DB2 - AS/400 skills. Per the client's request, the integrations need to be easy to use, extremely flexible and adaptable, and low maintenance (as in little to no programming required). Many of you may think that due to the nature of the systems involved, this could very well be an eConnect implementation. However, Integration Manager was chosen because of the characteristics previously described.
As I work through implementing the first integration to manage expense reimbursements from Lotus Notes, my integration needs to read data from some interface tables in a DB2 catalog. As such, we deployed the IBM Client Access ODBC driver. Working through the configuration was fairly simple, but due to the driver features I was afraid Integration Manager was not going to be able to store the DSN configuration.
One thing I discovered while working with IM v9 SP4 (9.00.0054) is that due to it's underlaying implementation of the almost outdated use of ADO technology, it cannot perform a preview of the source queries, with the following error message:
Upon further research, it seems this error is generated because server-side cursors are not supported with ISAM (Indexed Sequential Access Method) files. As it turns out, ISAM was originally developed by IBM and later replaced with a storage file system methodology called VSAM (Virtual Storage Access Method). VSAM is the physical access method used in DB2.
What does this have to do with IM? Well, IM -- version 9 specifically -- uses ADO to connect and retrieve data. The problem with ADO and VSAM file systems is that the cursor location needs to be set to AdUseClient and the cursor type needs to be checked for bookmark support:
oRecordSet.Supports(adBookmark)
Apparently, this is not being done in IM v9's internal ADO implementation. The good news is, I checked this against IM v10 and I have to say it works just fine, since, IM v10 was developed from the ground up with ADO.NET.
Given there was no preview available, testing began with a small dataset.
Integration Manager began importing expense vouchers and in the process neglected randomly to import the distributions. I executed the same integration a few times and obtained random results: transactions that previously imported fine along with their distributions did not import and failed the second time around. I verified the RecordSource Rule and Source properties to make sure they were set to Default Non-Imported and the source query for the distributions, respectively.
Upon adding some VBScript to write the records that were being read and attempted during the integration, I noticed that the header records were being read in order while the distribution records were shifting randomly. In some cases IM would match the header record with the distribution record, but in some cases it would skip a valid set of distributions for an expense voucher. By changing the UseOptimizedFiltering flag in IM to False the imports began to work as expected.
Integration Manager is a very useful tool and should not be overlooked when considering multiplatform systems integrations, especially if the customer insists on easy of use. All the integrations will be scheduled on a server and will run unattended, which only validates IM's flexibility.
Until next post!
MG.-
Mariano Gomez, MVP
Comments