Modify the length of a field in all jobs in a project

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
sangi1981
Participant
Posts: 99
Joined: Fri Jun 13, 2008 8:10 am

Modify the length of a field in all jobs in a project

Post by sangi1981 »

Hi all,
I write to ask for an advice about a massive upgrade of a Datastage Project.

There is a field called COD_X, that is used everywhere, in every job parallel, server, and sequence.
Number of object is about 5000.

On this field are applied some business transformation rules.

This field has type varchar(2).

DS Project insists on DB Oracle, where field COD_X is used in every primary key, but this is a problem out of scope for that forum.


Our objective is to upgrade this field from varchar(2) to varchar(5).

We can't modify every job.

So I ask for your help, to find a way to obtain the same result with little effort, but highly reliable.

Thanks in advance

Sandro
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: MODIFY LENGTH OF A FIELD IN ALL JOB OF A PROJECT (5000 J

Post by chulett »

sangi1981 wrote:We can't modify every job.
I assume you meant manually or one by one. Obviously you'll need to modify every job.

There will be effort and risk so I would make sure you have good backups before proceeding with any effort of this nature. First thing that pops into my head is a dsx export of the entire project, then your favorite editor to make the appropriate size changes and then re-import the affected (or all) jobs.

How will you know what to change where? I would suggest taking an export of a job that will need to be changed. Then manually make the appropriate changes and create a new export. Use a comparison tool (I like UltraCompare) to list out everything that is different between the two and use that as your roadmap. First test before you go all crazy is to take another job, export it and make the changes you think you need to it, saving a copy of the original, unedited export as well so worst case you have something to fall back to. Then import the changes and verify the correctness of it. Lather, rinse, repeat until it comes out all shiny and clean... then proceed with the mass updates.

Good luck. Be curious what other advice people may have.
-craig

"You can never have too many knives" -- Logan Nine Fingers
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

I vote for DSX file export/edit as well.

You practice on one test job first of course.
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

Craig's very detailed explanation is exactly how I would approach it.

Also be aware that you will miss cases where the column gets mapped to a different name and that other column also needs its size adjusted.

Lots of risk. You'll have to regression test it all and fix anything that your mass update misses.

Mike
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yeah, even after all these years I didn't think there was another approach for a massive update like this so put that out there...
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply