2 replies Latest Post - ‏2013-01-25T11:59:34Z by SystemAdmin
7754 Posts

Pinned topic Connector migration - from Oracle to Netezza

‏2013-01-14T12:13:15Z |
Hello everyone,

I am aware of the existence of Connector Migration Tool for DataStage. As far as I know it helps to migrate jobs from using stages to use data connectors.

In my case, I need to migrate hundreds of jobs that are currently using Oracle connectors to use Netezza connectors instead. Is there any efficient/automate way to do that?

Updated on 2013-01-25T11:59:34Z at 2013-01-25T11:59:34Z by SystemAdmin
  • vmcburney
    34 Posts

    Re: Connector migration - from Oracle to Netezza

    ‏2013-01-17T01:58:06Z  in response to SystemAdmin
    I don't think this exists yet but it would be a very good idea for IBM to build one. You would have to write your own Connector migrator - one way is to modify an export file. The entire DataStage project could be exported to an XML or DSX file and then converted so that the Oracle connectors become Netezza connectors using a scripting language or a DataStage job. It should be easy to do a basic migration where the stage is replaced and the database, schema, login, table and column details are retained. Any automated Select, Insert and Update commands should be easy to retain. Those functions that may be tricky and may require manual conversion are:
    • Converting Oracle Connector bulk load requests across to Netezza nzload.
    • Converting Oracle user-defined SQL over to nzsql. There may be user-defined Oracle insert or update statements or sql select statements.
    • Replacing before and after SQL statements such as jobs that disable and enable indexes during loads.

    The high risk jobs are those that have Oracle as a source (such as reading from an enterprise layer and writing to a dimensional layer) as there may be complex user-defined SQL and SQL functions and even PL/SQL to replace.

    You could write a Metadata Workbench queries to see what you have got. You can write a query that shows the properties of every Oracle Connector and export that directly into Excel. Seperate it into categories - source or target, insert upsert load delete, generated SQL versus user defined SQL. For user defined SQL you can add a weighting depending on the complexity of the SQL. This would give you an accurate job count, connector count, complexity weighting and help estimate the size of the overall task.

    Vincent McBurney
    Certus Solutions
    ITToolbox Blog: Tooling Around in the IBM InfoSphere
    Twitter: @vmcburney
    • SystemAdmin
      7754 Posts

      Re: Connector migration - from Oracle to Netezza

      ‏2013-01-25T11:59:34Z  in response to vmcburney
      Wow... seems like a lot of work, but yet a good solution!
      Thank you very much! :)