Over the years of working with DataStage I have come across some tips that I find can be huge time savers when working with the DataStage Transformer stage. Here are two that I find really useful in real world projects – Propagate Values and Derivation Substitution.
Propagate Values
Propagate Values allows us to mass change column level properties with just a few mouse clicks instead of a lot of typing. Let’s work through an example.
We have the columns defined below for the link we are working with. We have just found out that our source system has changed the three columns that begin with SLS to be defined as VarChar 1000 not Char. The easy way to change all of these in one shot is to use Propagate Values.
To do this we need to highlight a row that has the attribute(s) we want to copy and all rows that we want to modify. This can be a range of Column Names selected while holding the SHIFT key or individual Column Names selected holding the CTRL key. Next we right click on the Column Name for the row that has the attributes we are copying from (The STATUS row in this example) and select Propagate Values from the Menu. We would then put a check mark in the Property boxes for SQL type and Length and then Click OK, as shown below.
You will need to click OK on the next confirmation box to complete the propagation.
Our final result is shown below.
This is a real time saver when you have links with a large number of rows that need to be updated. Keep in mind that you can select one Property to propagate or you may select three or more Properties to propagate – it really depends on your particular situation.
Derivation Substitution
Derivation Substitution allows us to modify multiple derivations using a single command instead of either manually typing each derivation or selecting the desired function for each individual derivation. Let’s work through an example.
We have the following derivations setup for our source link. We would like to add the TRIM function to each of the fields that have a Source column in the derivation. The quickest way to do this is to use Derivation Substitution.
To do this we need to highlight the columns we want to work with by selecting while holding either the SHIFT or CTRL key. Then right click the mouse on the selected area and select Derivation Substitution from the menu. This will bring up the Expression Substitution dialog box.
I then type the command highlighted in yellow to complete the substitution. The important thing to note here is that $1 represents the derivation before any substitution occurs. After typing Trim($1) click OK and then click the Yes To All button on the Confirm Action box and OK on the next dialog box.
Here is our final result.
This can be a very powerful tool when you need to make mass changes to your transformer derivations. I urge you to give it a try to see how it works for yourself. You can always cancel out of the transformer to undo if things did not work out the first time.
Leave me a comment if you find these tips useful or if you have any questions.
