3 Replies Latest reply on Feb 1, 2018 2:00 AM by Nico Heinze

    Strategy for keeping Informatica platform up to date when source system cannot be upgraded

    Daniel Machet New Member

      Hi,

       

      I have a very complex source system application which is running on a version of Siebel which only supports a maximum of Oracle 10g R2, due to a mix of risk aversion and challenges in providing full test assurance for this application, upgrading will be a challenge for some time to come. My concern is that I want to keep my Informatica platform within support by using only supported versions but Informatica platform versions are only compatible with very recent Oracle Source, Target and Repository databases (per the Product Availability Matrixes and support requests). Is there a suitable strategy or convenient connector for dealing with this scenario which can handle a source system database as 10g R2 and a target database as whatever the later Oracle version (up to 12) is that is supported by the in support Informatica Platform?

        • 1. Re: Strategy for keeping Informatica platform up to date when source system cannot be upgraded
          Nico Heinze Guru

          This is not really easy to answer.

          As far as I know, an Oracle 11g client is supported for access to Oracle 10g and 12c database instances. From this point of view it would suffice to access the 10g database using a 11g client.

          So, as long as you use PowerCenter versions which are supported to use a 11g client, you MIGHT be able to safely access 10g databases.

           

          Now the big caveat: As long as all the PowerCenter mappings only work with SQL statements which are 100% compatible with 10g, sourcing and/or targeting a 10g instance should work without hassle.

          And here is the big catch:

          As we all know, there are many places in which SQL statements can be set up "manually" or will be created by PowerCenter. SQL Transformations, Stored Procedures, Source Qualifiers, and targets with e.g. Target Update Overrides. Not to forget relational Lookup transformations.

          And maybe there are Java Transformations which use JDBC to connect to some 10g database.

          Or there may be shell scripts / batch files / whatever which establish some access to a 10g database.

           

          All these entities must be examined, with a few exceptions.

          Relational Source Qualifiers without a SQL Override and with just standard constructs in User-Defined Source Filters and User-Defined Joins should be harmless.

          Relational Lookup transformations without a SQL Override and with just standard constructs in the Source Filter should be harmless, too.

          Target Update Overrides with just standard constructs should be harmless.

           

          Everything else must be thoroughly checked whether there are any PL/SQL constructs which MAY have adverse effects in such a situation.

           

          You see, there's much to do. Good luck that you don't find too many severe obstacles.

           

          Regards,

          Nico

          • 2. Re: Strategy for keeping Informatica platform up to date when source system cannot be upgraded
            Daniel Machet New Member

            Thanks Nico,

             

            So...

             

            1) Change my Oracle connector (for my relational connections) to Oracle 11

            2) Run all my batches and extract the sql from each of the logfiles

            3) Try running each of those queries in an Oracle 10g database

            4) Amend any overrides I can amend and troubleshoot otherwise... my concern lies mostly in troubleshooting as when I hit a snag I'm not sure at what point I'll hit a wall with support (although you're always incredibly responsive)

            • 3. Re: Strategy for keeping Informatica platform up to date when source system cannot be upgraded
              Nico Heinze Guru

              Queries created by PowerCenter (please let's forget about Push-Down Optimisation, this would be a completely different story) are harmless. By default (without PDO) PowerCenter uses very simple ANSI SQL statements, they won't cause you any trouble.

               

              You have to cater for all sorts of SQL Overrides; be it source filters, user-defined joins, complete SELECT statements, and SQL transformations.

              You don't have to cater for the queries created by PowerCenter itself.

               

              BTW yet another reason why I always try to avoid SQL Overrides with complete SELECT statements; it's easier (in your case) to check for e.g. source filters than to analyse complete SELECT statements with e.g. 8 embedded WITH clauses.

               

              As to your step #2 this is more work than necessary. You can extract all relevant parts from the Metadata Exchange views (part of the PowerCenter repository database / schema), you don't have to parse log files.

               

              As to step #3, no, don't run them, analyse them manually. Running a query which is optimised for 12c on a 10g database may cause all sorts of adverse effects, so I don't consider this a good approach.

               

              As to step #4, GCS will assist you as long as all repository databases are stored on a supported DBMS version, and I am confident they will try to help you with any issues when addressing 10g as a source / target DB as good as they can. At least that's my personal experience with GCS.

               

              Regards,

              Nico