Update column in all the jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
Update column in all the jobs
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.
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.
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
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.
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.
Arun
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
Update column in all the jobs
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
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
![Sad :(](./images/smilies/icon_sad.gif)
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
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
Update column in all the jobs
hi craig,
sorry for not being clear in my previous post. 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.
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
thanks,
ravi.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
I'd do this with a Perl script and a DSX file.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
That doesn't look like a DSX file to me. You should be seeing something like this: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.
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
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
Update column in all the jobs
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
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
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: Update column in all the jobs
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.austin_316 wrote:Hi Phil,
Thanks alot for the reply. This is the field definition for SOURCE_TYPE...
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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 ![Wink ;)](./images/smilies/icon_wink.gif)
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"
![Wink ;)](./images/smilies/icon_wink.gif)
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"
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: Update column in all the jobs
1. Export your DSXaustin_316 wrote:Please suggest a way to do this.
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
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)
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant