Data Services 4.2 SP8 Patch 2 update install

Sharing my experience of applying the 4.2 SP8 Patch2 update for Data Services and Information Steward.

We were on 4.2 SP2 Patch2 for almost 3 years. There were only few niggles that prompted us to consider the upgrade. They were related FROMDBLOVER and slowness of central repository.

The DBLOVER issue happens randomly and few times in a month. Since our ETL framework can handle restarts better, it wasn’t a show stopper. The central repository slowness is just a frustration for the developers and administrators.

As part of the upgrade, we had to pick the compatible versions of IPS and DS/IS components. We have decided to go with the latest support pack available at that time. The components that we chose were,

IPS 4.2 SP3 PL6 + DS 4.2 SP8 PL2 + IS 4.2 SP8 PL2

The installation was very straightforward and much faster compared to earlier versions. We had couple of issues during the installation,

• New license key needed for IPS 4.2 (this was clearly indicated during the installation of IPS component)
• Known issue related to registering SQL server repository. The workaround provided in the note worked like a charm

We learned these during our development server upgrade and were well prepared for test and production server updates. So we had no struggles during the upgrade of Test and Production servers.

During testing of our existing stuff, we found few minor issues that needed change,

• We have lookup() functions that were used in R3 dataflows. In earlier versions when CACHE_SPEC is missed completely, the validation didn’t pick them up. With the new version, this syntax error is picked up. We had to find all the validation errors and fix them as part of the upgrade process
• We use Python to consume internal and external web services data as it is much simpler than setting up adapters. After the upgrade, the internal web services worked just fine while the external web services have started failing. We identified that this was caused by the new python version 2.7.9 (earlier it was 2.6.2). Some related info can be found here

And we found a bug with the product that was confirmed by SAP as a defect. We did not plan for regression testing, our plan was to make sure that all the jobs run in development and test environment without fail. Fortunately I had to make some fixes to few of the data flows as an emergency fix that led to the discovery of this defect.

The defect happens when we have a group by transform that does ratio calculation with a check for divide for zero error. For example our mapping for one of the column looked like

ifthenelse(sum(denominator)<>0,sum(numerator)/sum(denominator),cast(null,'decimal(19,8)'))

The data flow never fail to run, it runs successfully but it loses all the rows except for the last row. And after much struggle we realized that this doesn’t happen if the group by transform is connected immediately to either a target table or a data transfer transform. By the time we realized this issue, we had completed our test server upgrade. We were left with two options, 1) to roll back the updates in development and test environments or 2) find all the data flows which used group by with the potential problem and fix it with the work around of adding a data transfer transform,

We haven’t heard back from SAP. We decided to go ahead with the 2nd option. We had over 1000 data flows in our production environment. I had managed work through the repository table AL_LANGXMLTEXT and narrow down to 120+ data flows that have used group by transform. Using manual checks, we narrowed it further to 30 data flows that may need a fix. The whole process took a week but we were pretty confident with our approach.

It is certainly worth the effort.