Changing database schema name across all jobs

[SonShe]

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.

[ray.wurlod]

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.

[kcbland]

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:

Code: Select all

#schemaname#yourtable AS yourtable

You can pass in schemaname as blank or as "yourschema."

[SonShe]

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.

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.

Thanks again.

[ray.wurlod]

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:

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

For XML:

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>

[talk2shaanc]

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

[ray.wurlod]

Another thought.

Code: Select all

create synonym xxx.tablename for yyy.tablename;