I am working on a project that uses Oracle as the database. The schema in the development is xxx. In the SQL statements in all the jobs, I have the follwing format:
Select Cols from xxx.Tablename.
Now the problem is we are ready to go to the QA and for whatever reason the schema name cannot be 'xxx' in QA. I am trying to find out if there is a way to change the SQL in all the jobs - like changing the dsx file. I am not sure. Do I have to open each and every job to change?
I will appreciate any help.
Thanks.
Changing database schema name across all jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Job parameters provide insurance against things that can change over time (like passwords and selection criteria) and things that can be different on different environments (like DSNs, passwords, user IDs, schema names, pathnames).
You should have made them into job parameters in the first place. My advice is still to go back and turn them into job parameters. You can do this by editing an export file, but will also need to add the job parameter into the job design.
You should have made them into job parameters in the first place. My advice is still to go back and turn them into job parameters. You can do this by editing an export file, but will also need to add the job parameter into the job design.
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.
Make sure you NEVER use fully qualified embedded schema names. When you import metadata, NEVER use fully qualified names because when you load the table definition it shows up in the DERIVATION column.
If you are not going to work thru public synonyms, then you need to parameterize the schema name as part of the table name AND ALIAS it. You can leave the schema name parameter blank and still have the job work thru a public synonym, but if you supply a schema name you will want to include the trailing ".". Ex:
You can pass in schemaname as blank or as "yourschema."
If you are not going to work thru public synonyms, then you need to parameterize the schema name as part of the table name AND ALIAS it. You can leave the schema name parameter blank and still have the job work thru a public synonym, but if you supply a schema name you will want to include the trailing ".". Ex:
Code: Select all
#schemaname#yourtable AS yourtable
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Ray, thank you very much for the reply. We have around 200 jobs. If I add a job parameter say 'SchemaName' to all of them, then export the project, change 'xxx' (existing schema name) to the job parameter, and then import the project I believe it will work. I am in a critical phase of the project development so please let me know if I will OK doing this. Of course, I will have a back up before doing all these.ray.wurlod wrote:Job parameters provide insurance against things that can change over time (like passwords and selection criteria) and things that can be different on different environments (like DSNs, passwords, user IDs, schema names, pathnames).
You should have made them into job parameters in the first place. My advice is still to go back and turn them into job parameters. You can do this by editing an export file, but will also need to add the job parameter into the job design.
Thanks again.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You will only be partially OK.
As I said, you also have to create a parameter in each of the jobs.
That's more difficult to do in an export file, though not impossible. Exactly how it is done depends on the style of the export file, but each parameter is individually tagged, so all you have to do is to incorporate the correct block of lines for an extra parameter.
For DSX:
For XML:
As I said, you also have to create a parameter in each of the jobs.
That's more difficult to do in an export file, though not impossible. Exactly how it is done depends on the style of the export file, but each parameter is individually tagged, so all you have to do is to incorporate the correct block of lines for an extra parameter.
For DSX:
Code: Select all
BEGIN DSSUBRECORD
Name "SchemaName"
Prompt "Schema name"
Default "xxx"
HelpTxt "Schema (owner) name of objects in database"
ParamType "0"
ParamLength "0"
ParamScale "0"
END DSSUBRECORD
Code: Select all
<SubRecord>
<Property Name="Name">SchemaName</Property>
<Property Name="Description"/>
<Property Name="Prompt">Schema name</Property>
<Property Name="Default">xxx</Property>
<Property Name="HelpTxt">Schema (owner) name of objects in database</Property>
<Property Name="ParamType">0</Property>
<Property Name="ListValues"/>
<Property Name="ParamLength">0</Property>
<Property Name="ParamScale">0</Property>
</SubRecord>
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.
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
i can think of a way, say your current sql statement is something like scott.acct for one of the job.
step1: I would first declare a job parameter "SchemaName" in all my 200 DS jobs, with default value as "scott".
ways of declaring job parameter in all jobs:
1. manually
2. using parameter manager( I would recommend you using parameter manager, as it will reduce effort and chances of makin human error is less.
Step2: export all the jobs, say in xml format.
step3: open the export in a text file, then use "find and replace" utility. where i will find for "scott." and replace with "#SchemaName#." and save the changes.
step4: Import it into a NEW project, so as to avoid screwing your old one, in case of any mistakes made during "Find and Replace".
step5: Test all the jobs, if it works properly. Throw a party :D
step1: I would first declare a job parameter "SchemaName" in all my 200 DS jobs, with default value as "scott".
ways of declaring job parameter in all jobs:
1. manually
2. using parameter manager( I would recommend you using parameter manager, as it will reduce effort and chances of makin human error is less.
Step2: export all the jobs, say in xml format.
step3: open the export in a text file, then use "find and replace" utility. where i will find for "scott." and replace with "#SchemaName#." and save the changes.
step4: Import it into a NEW project, so as to avoid screwing your old one, in case of any mistakes made during "Find and Replace".
step5: Test all the jobs, if it works properly. Throw a party :D
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Another thought.
Code: Select all
create synonym xxx.tablename for yyy.tablename;
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.