derviation logic with derviation substituion $1 logic

marpadga18 · Post by **marpadga18** » Fri Oct 12, 2012 7:49 am

hi all

I have requirement I have 600 coulmns in xfm were I need to put same derviation for all 600 columns except numbering which is highlighted below.

Trim(Field(svREC, svnewdel,1),svquote,'B')
Trim(Field(svREC, svnewdel,2),svquote,'B')
Trim(Field(svREC, svnewdel,3),svquote,'B').
.............................................
.................................................
Trim(Field(svREC, svnewdel,600),svquote,'B')

what I did is I used $1 logic from derivation substitution which set Trim(Field(svREC, svnewdel,1),svquote,'B') for all 600 columns
but again I am doing manually changeing the numbers in the dervaiton like this we have 100 jobs to do so this takeing long time. Is this is possible?? if it can done really help me
Thanks M

ray.wurlod · Post by **ray.wurlod** » Fri Oct 12, 2012 5:19 pm

Not possible. Derivation substitution makes exactly the same substitution on every selected expression (or part thereof).

ArndW · Post by **ArndW** » Sat Oct 13, 2012 3:01 am

Do you have an editor capable of macros (emacs, notepad++ or the like)? If so, you could create a .dsx export and then apply the macro to make these replacements and then re-import the .dsx file with the changes applied. As Ray has noted, it can't be done directly within DataStage.

marpadga18 · Post by **marpadga18** » Sun Oct 14, 2012 9:07 am

Hi ray/arndw,

I will get back to you on this.. any way thanks for valuable suggestions..

marpadga18 · Post by **marpadga18** » Wed Oct 17, 2012 6:36 am

Hi Arndw/Ray,

As per your suggestion we exported the job and ran small java program on that dsx file then again reimported it it worked like charm. Thank you all once again.

robjones · Post by **robjones** » Wed Oct 17, 2012 9:20 am

Just a short cautionary tale. If anyone else does this, use some common sense and make your changes to a copy of your .dsx file.

Naming no names, but I once worked with a guy who ignored the development standards and instead of naming one of his job parameters 'db2_password_str_parm' like the rest of us, he decided to use 'password'.

After he spent weeks developing dozens of jobs, somone finally noticed his mistake. To fix this, he exported all his jobs to dsx, did a global replace of 'password', overwrote his only copy of the dsx file, then imported all the 'corrected' jobs back to the dev project.

When he started looking at the jobs he'd imported, he noticed all kinds of peculiar things like the fact that the password field had disappeared from his Oracle stage properties.

With no backup, he had to redevelop everything from scratch. Bleedin' rocket scientist.