DSXchange

Is it possible to automatically change the column derivations and table names for all the jobs using the ODBC stage e.g.
mydatabase.me.mytable.mycolumn mydatabase.you.mytable.mycolumn

I would say it is possible. My way of doing it is:

Export all the jobs u want to modify as XML files and use any text editor to find and replace the string you want and again import those jobs and just test couple of them.

IHTW

One nasty little side-effect of column derivations in the ODBC/OCI stage is that when you load metadata in that stage, the column derivation is filled out with linkname.column name. This is frustrating.

So, if you want blank column derivations, load your metadata in the transformer after the stage. When you open the ODBC/OCI stage, you'll see that the column derivations are blank. Now, the auto-generated SQL won't fully qualify every column. The only thing you need to worry about is parameterizing the table name. You can do a partial parameter, like #Owner#.yourtablename.

Otherwise, you're going to manually have to do this change over. There's no automated way without hacking into the job design. A .dsx file text find/replace is one hack option.

The best way to do this is with an edit macro. Unless you are very comfortable with the Universe editor then do not even think about this. But a friend of mine Peter Lai did this and saved a lot of time. It could work on any string which is clearly defined without any worry about changing the wrong thing.

SEARCH DS_JOBOBJECTS
String: mydatabase.me.mytable

Will create a SELECTLIST of all the records you want to change. If you know what en edit macro is then you will know about &ED&. If you are in the editor then do .s Kim and this will create &ED&. In this file you need to create a record like this:

ED &ED& FIX.MY.SCHEMA
EP
1
R/mydatabase.me.mytable/mydatabase.you.mytable/g99999
FI
.X FIX.MY.SCHEMA

FI


The macro works like this. "1" goes to line 1. The "R" does the change or replace. "FI" files your change. ".X" executes the macro on the next item in the SELECTLIST. The macro should look like this:


EP
1
R/mydatabase.me.mytable/mydatabase.you.mytable/g99999
FI
.X FIX.MY.SCHEMA


I would do the changes on a DSX export file and not an XML file if I was going the other way. Either way back up all your changes with a full export. You will need DcsCompileAll to compile your jobs after.

What I showed you is very dangerous. Do not blame me if it destroys your DataStage job or even your project. This is a very effective tool for us uniVerse geeks.

Thanks Kim.

I make it a habit to remove the qualifications on table names immediately after importing table definitions. This means that they can be loaded into column collections unqualified.
The downside is that, where I want user-defined SQL to extract from multiple tables, I have to add table aliases/qualifiers manually. This is a small cost to pay for the convenience.
Very few databases actually demand tablename and owner qualifiers, provided the request is unambiguous. (Alas, a couple of databases do demand these qualifiers.)

kduke wrote:The best way to do this is with an edit macro. Unless you are very comfortable with the Universe editor then do not even think about this. But a friend of mine Peter Lai did this and saved a lot of time. It could work on any string which is clearly defined without any worry about changing the wrong thing.

This could be extended by using the LOOP construct in the editor macro so that all changes to all records in the Select List can be executed with a single command.
With some knowledge of the metadata (which is not - as noted elsewhere - in the public domain), the change to DS_JOBOBJECTS could also be wrought with SQL.