Change a datatype

amirse · Post by **amirse** » Thu Jun 17, 2004 3:13 am

I need to change field datatype from integer(9) to decimal(10) .
is there a way to locate all the relevant places in the project that should be changed ? or I need to manually seek for them ?

Thanks,
Amir

denzilsyb · Post by **denzilsyb** » Thu Jun 17, 2004 3:34 am

I am not aware of a cascading function like that, but you could do one of two things (what I would do):

1. save the new table definition and load the new definition where appropriate or

2. when you read from the database, convert the datatype to what you have it as - provided this doesnt interfere with what you need to do with the data.

hope it helps

dnzl

WoMaWil · Post by **WoMaWil** » Thu Jun 17, 2004 4:12 am

Hi amrise,

Doing like denzil proposed all derivations which you might have made are also swift away so manualy changing it everywhere may be the better solution. If you have no changed derivations you could do so.

One not supported way of changing it would be:

Do an export of the relevant jobs
Open the Export-file in an editor (for example MS-Word)
Do an change over the whole file
Save the file as a new Name (for to save the old version)
Import the new *.dsx file

That should it be.
Be very carefully by doing so.

Wolfgang

chucksmith · Post by **chucksmith** » Thu Jun 17, 2004 7:01 am

I have a tool that will search a dsx for all occurrances of a string, and report the category and job name where the string was found. If this data type change is associated with a column name or table name, you could search for it, and get a short list of jobs to review.

With any luck, I will post it on www.anotheritco.com today. It should work at revision 6 and above.

Chuck

kduke · Post by **kduke** » Thu Jun 17, 2004 8:41 am

I posted some queries in the past to look for a table name in Reporting Assistant. You could search for those. You could also use MetaStage. What you are looking for is a specific table in an Oracle, ODBC or some specific stage type. You need to develop some type of methodology which you are comfortable with and use it over and over. The doc_tool.mdb which comes with Reporting Assistant or MetaStage or write your own.

I wrote DwNav to help me do exactly this. It will show you all tables by stage type and all the jobs that updated or read from those tables. Very easy to use and trace from source to target through all your jobs. It will producted html documentation also showing these relationships. I like it. I use it all the time.

Choose a methodology and stick to it. Everyone has their favorites.

chucksmith · Post by **chucksmith** » Wed Jun 23, 2004 7:29 am

I posted two new tools last night. SearchDsx searches a DataStage DSExport file for a sting, and ListFilesAndTablesFromDsx list mosts, if not all, file and table names used by each job and category. These tools are for version 6 and beyond. They work best if you export your job designs and routines to a DSExport file, and then use that file as input to the tools.

Follow the DataStage Tools link at www.anotheritco.com .

Chuck