Automated analysis/migration of Cognos Data Manager jobs

This forum is the beginning of our inclusion of Cognos users to have their own place to go within our community. It is said the ETL and BI don't intersect, but as IBM integrates their platforms, there are less lines of demarcation between the two.

Moderators: chulett, rschirm

Post Reply
anamika
Participant
Posts: 16
Joined: Sat Feb 27, 2016 9:43 am
Location: Ottawa

Automated analysis/migration of Cognos Data Manager jobs

Post by anamika »

Hi All,
Disclaimer: I am aware of Cognos Data Manager migration assistant that helps in the migration of Data Manager jobs to Datastage. For certain reasons we do not have access to this tool at our site. Also, the IBM Data Manager Migration assistant will only migrate jobs to Datastage and not to other tools (understandably) like Talend, SSIS, Informatica etc., Plus it has limitations for automation, job analysis etc.,

I have been entrusted with the task of automating the migration of Cognos Data Manager catalogs/jobs to Datastage. I have started the automated migration in small steps by analyzing Cognos Data Manager jobs using parsing tools and pre-generated Datastage job templates. Had some good success for simple Data Manager jobs and now moving on to automated analysis of complex Cognos Data Manager jobs.
I would be interested in hearing from this community about their experiences in analyzing/migrating/automation of Cognos Data Manager jobs, tools & techniques, strategies, pitfalls etc., to enhance my understanding of migration and automation.

Any thoughts, comments, pointers appreciated.

Thanks
ETL, DW, BI Consultant
chandu123
Premium Member
Premium Member
Posts: 40
Joined: Mon Sep 12, 2016 1:26 pm

Post by chandu123 »

I am in the same boat. In my case, most of the data manager jobs have transformation logic in source SQL itself. As per my research, there is no way to convert the SQL into datastage jobs using stages. Hence I am developing datastage jobs by copying the SQLs to ODBC connector stage with User-defined SQL option as the write mode.

I am interested to know if there are any better practices in the space.
HB
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check out Metadex, from Compact BI. One of their metadata bridges can process SQL into metadata structures suitable for Information Server.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
anamika
Participant
Posts: 16
Joined: Sat Feb 27, 2016 9:43 am
Location: Ottawa

Post by anamika »

The sql inside the DM job can be extracted by querying the Cognos DM catalog metadata database. And it can be automated as well.
Once the Cognos DM job sql is extracted, you can insert that sql into a Datastage job template to create a Datastage job.
The above will work for jobs that follow a format/sequence that is known ahead of time. Dynamically generating a DS job is more complicated, but can be done.
Feel free to ask more questions if you need details.

Thx
ETL, DW, BI Consultant
Post Reply