Update column in all the jobs

austin_316 · Post by **austin_316** » Tue Sep 07, 2010 9:35 am

Hi,
i have a column name 'source_type' which is present in almost 500 jobs in my project. the data type and length for the column when we created the jobs were varchar2(6). but now due to the requirement we have changed it to varchar2( 8 ). can you please let me know if there is any way that i can update datatype for this column(column name is same in all the jobs) all my jobs without having to open each job. please let me know if there is any possibility as i want to save time in updating this column.

thanks.

arunkumarmm · Post by **arunkumarmm** » Tue Sep 07, 2010 1:11 pm

From whatever I think of, you can export the jobs and make this change in the dsx. For example, you column may be defined in the dsx like

BEGIN DSSUBRECORD
Name "EbcData"
SqlType "1"
Precision "30"
Scale "0"
Nullable "0"

So, if you want to change the column with the name "EbcData" from 30 to 35 you can do a find and replace.

Find:
Name "EbcData"
SqlType "1"
Precision "30"

Replace:
Name "EbcData"
SqlType "1"
Precision "35"

But this is not the best or the safest way to do it.

austin_316 · Post by **austin_316** » Tue Sep 07, 2010 9:19 pm

thanks arun for the reply.
since it is a column it is coming like SOURCE_TYPE:nullable ustring[max=8]=SOURCE_TYPE;
so i tried to change it SOURCE_TYPE:nullable ustring[max=6]=SOURCE_TYPE;
but still it is not working and also considering what you said i think it is not the safest way to do it

.
can anyone please help me in doing this. the only thing i am looking here is to save time in updating that column so that i need not open each job and make changes in the all the stages of the job .

thanks,
ravi

chulett · Post by **chulett** » Tue Sep 07, 2010 9:57 pm

What's "not working" about what you are doing? And isn't that backwards from your original post?

austin_316 · Post by **austin_316** » Tue Sep 07, 2010 10:23 pm

hi craig,
sorry for not being clear in my previous post.

Code: Select all

SOURCE_TYPE:nullable ustring[max=6]=SOURCE_TYPE; 
so i tried to change it SOURCE_TYPE:nullable ustring[max=8]=SOURCE_TYPE

this is the actual one. in the job it was max=6 and i exported the job and made changes in the dsx file as max=8 wherever i could find the source_type and then imported the job. but still in the job it is showing as 6 after i have imported making the changes.

thanks,
ravi.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 08, 2010 1:44 am

Life is not so easy in version 8. Are the jobs linked to regular or shared table definitions? In either case, what does the Locator property of the table definition tell you?

Why not perform a Where Used analysis on the column? - You may find that it's used in a surprisingly small number of jobs, which you could have fixed manually by now.

PhilHibbs · Post by **PhilHibbs** » Thu Sep 09, 2010 9:23 am

ray.wurlod wrote:Why not perform a Where Used analysis on the column? - You may find that it's used in a surprisingly small number of jobs, which you could have fixed manually by now.

He's already stated that it is used in almost 500 jobs

austin_316 · Post by **austin_316** » Fri Sep 10, 2010 2:18 am

yes. the column is used in almost 500 jobs and thats where iam worrying about to open aeach and every job and then change it in all the stages of the job

PhilHibbs · Post by **PhilHibbs** » Fri Sep 10, 2010 4:15 am

austin_316 wrote:yes. the column is used in almost 500 jobs and thats where iam worrying about to open aeach and every job and then change it in all the stages of the job

I'd do this with a Perl script and a DSX file.

austin_316 wrote:since it is a column it is coming like SOURCE_TYPE:nullable ustring[max=8]=SOURCE_TYPE;
so i tried to change it SOURCE_TYPE:nullable ustring[max=6]=SOURCE_TYPE;
but still it is not working and also considering what you said i think it is not the safest way to do it .

That doesn't look like a DSX file to me. You should be seeing something like this:

Code: Select all

      Columns "COutputColumn"
      BEGIN DSSUBRECORD
         Name "SOURCE_TYPE"
         Description "Source Type"
         SqlType "1"
         Precision "6"
         Scale "0"
         Nullable "0"
         KeyPosition "1"
         DisplaySize "6"
         Group "0"
         ParsedDerivation "inlink.SOURCE_TYPE"
         SourceColumn "inlink.SOURCE_TYPE"
         Transform "\(1B)"
         KeyExpression "test_row.TABLE"
         SortKey "0"
         SortType "0"
         AllowCRLF "0"
         LevelNo "0"
         Occurs "0"
         PadNulls "0"
         SignOption "0"
         SortingOrder "0"
         SyncIndicator "0"
         PadChar ""
         ExtendedPrecision "0"
         PKeyParsedDerivation "inlink.SOURCE_TYPE"
         PKeySourceColumn "inlink.SOURCE_TYPE"
         PKeyTransform "\(1B)"
      END DSSUBRECORD

If I get a few minutes I'll write you a Perl script to change them all, if you can post me a clip of your DSX file containing the field definition like the above example that I have made up.

austin_316 · Post by **austin_316** » Wed Sep 29, 2010 9:21 pm

Hi Phil,
Thanks alot for the reply. This is the field definition for SOURCE_TYPE

BEGIN DSSUBRECORD
Name "SOURCE_TYPE"
Description "SOURCE_TYPE: nullable string[max=240]"
SqlType "12"
Precision "8"
Scale "0"
Nullable "1"
KeyPosition "0"
DisplaySize "0"
Group "0"
SortKey "0"
SortType "0"
TableDef "Oracle\\ODSTGTDB\\ODS_10302_TGT2_ACCT_TRANSACTION"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ColumnReference "SOURCE_TYPE"
ExtendedPrecision "1"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD

Please suggest a way to do this.

thanks,
ravi

PhilHibbs · Post by **PhilHibbs** » Thu Sep 30, 2010 4:46 am

austin_316 wrote:Hi Phil,
Thanks alot for the reply. This is the field definition for SOURCE_TYPE...

OK I'll see if I can knock something out later on, bit busy right now. In the mean time, can you install ActivePerl on your PC? If not, I'll make sure the perl script can run under Unix.

kandyshandy · Post by **kandyshandy** » Thu Sep 30, 2010 7:56 am

Austin, If you are familiar with textpad, you can record a macro and get this change done in few mins. Textpad is free to be downloaded

Whatever you tried to change earlier is not the right place. Changing this SOURCE_TYPE:nullable ustring[max=6]=SOURCE_TYPE will not help you.

You need to change the bold shown below..

BEGIN DSSUBRECORD
Name "SOURCE_TYPE"
Description "Source Type"
SqlType "1"
Precision "6"

PhilHibbs · Post by **PhilHibbs** » Thu Sep 30, 2010 8:08 am

Just a quick clarification - are you changing it from 6 to 8 or from 8 to 6?

PhilHibbs · Post by **PhilHibbs** » Thu Sep 30, 2010 8:31 am

austin_316 wrote:Please suggest a way to do this.

1. Export your DSX
2. Run this command, either in Command Prompt or Unix shell:

Code: Select all

perl -ne "$n=$1 if /^         Name .(.+)./;s/6/8/ if /^         Precision / and $n eq 'SOURCE_TYPE' and $p =~ /^         SqlType .12./;$p=$_;print"<export.dsx>new.dsx

Substitute your file names instead of export.dsx and new.dsx.

Note: for ksh, and maybe other Unix shells, you will need to replace all occurrences of $ with \$.

Note that this is not bullet proof - use fc to compare the two files, load the results into a decent editor, and browse through looking for anything unusual, e.g. a line in an Annotation that has 9 spaces at the start, followed by the word "Precision".

3. Take a deep breath, and import the new dsx into your project (or, into a scratch project first just to make sure)

DSXchange