Question reg Oracle to DB2 migration of ETL code

dataservices job design
1

Hi,
We are migrating our databases from Oracle to DB2.

And we are in the process of reviewing the challenges in migration.

We have migrated the data structures. Now, I need to migrate the ETL code and make it DB2 compliant, especially wrt scripts and SQL transforms used.

The jobs that I am migrating involve a large no of workflows /dataflows.

I would like to know if there is any way in which I can count the no of scripts and SQL transforms in the jobs (which need to changed to DB2 format) without having to manually dig into the details.

This will save me a lot of time.

Thanks for your help.

Regards
Prem

biexplorer :india: (BOB member since 2007-06-21)

2

Wish I knew how to be of more help, but I will offer this bit of advice. Since you are having to rewrite anyway, get rid of the SQL transforms. And as many of the script SQL() calls as you can. It will save you alot of trouble in the long run and generally improve performance.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

3

Well, I still dont have a way to find out the no of SQL transforms. But I have one to find out all the transforms used in a job and where.


Select * from (
SELECT LEVEL,parent_obj, descen_OBJ, descen_OBJ_type FROM 
(select * from ALVW_PARENT_CHILD where descen_obj_type not in ('Datastore', 'Table')) 
START WITH descen_OBJ = 'WF_EDW_EXPENSE_MODULE' CONNECT BY PRIOR   DESCEN_OBJ= PARENT_OBJ 
order by level)
where descen_obj_type = 'Transform'

The above query not only gives the transforms used, but if you remove the outer query, it also gives a list of all objects used (except datastore and tables… which you can change in the where clause).

Let me know if anyone has anything better.

biexplorer :india: (BOB member since 2007-06-21)

4

Try this for counting the number of SQL transforms:

select distinct src_tab_name
from al_colmap
where SRC_TYPE = 'Transform' and SRC_DS is not null

eepjr24 :us: (BOB member since 2005-09-16)

5

Well, am not sure why.

al_colmap is empty. (We are using 11.7.3.2 version)

Regards
Prem

biexplorer :india: (BOB member since 2007-06-21)

6

Werner, can you assist on this one?

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

7

AL_COLMAP is one fo the two tables that get’s populated via an Calculate Usage Dependencies.

Either you

  • Go to webAdmin -> Impact Lineage -> …
  • Designer -> Object Lib -> rightclick -> Repository ->…
  • or in the Designer options say you want to calculate that during save (what I would do if the table is used frequently)

Is that the answer to your question?

Werner Daehn :de: (BOB member since 2004-12-17)

8

Super!!! :cheers:

Werner,
Designer → Object Lib → rightclick → Repository ->…
That loaded the tables !! Thx.

Ernie,
The query works!!
And the logic behind this is awesome…

  1. Source is a transform. (so it could be SQL transform or Row generation or date generation… )
  2. But we say source_ds is not null. (only SQL transform has a SRC_DS)

Super Ernie and Werner!!

Regards
Prem

biexplorer :india: (BOB member since 2007-06-21)