Changing schema name on UV tables

[admin]

Table names that are unqualified by a schema name assume the local schema. Each DataStage project is a schema, so the UniVerse table will be found in the project if it is not qualified by a schema name.

Similarly, you can remove the table name qualifier from a column name if the result is unambiguous. I usually do this in the table definition in the Repository, after importing it, so that the generated SQL statements will be shorter.

This is not just UniVerse; it is fairly common in SQL-using databases.

HTH

-----Original Message-----
From: Moderator [mailto:moderator@oliver.com]
Sent: Thursday, 22 February 2001 09:03
To: informix-datastage@oliver.com
Subject: Changing schema name on UV tables


DataStage 4.0

I have a single server with two projects--Development and Production. We have a set of jobs that use localuv tables. When we create our uv stages for these jobs, the table names and column derivations are prepended with the project name. Eg. Development.CUST.NAME

When the job is exported from Development and imported into Production this, of course, causes the problem of the production job still referencing the Development tables and data.

Now, page 5-5 of the Server Job Tutorial says, "As previously noted, UniVerse and some other RDMS products require you to qualify these names with the account name." (Schema name supposedly required, in UniVerse SQL terms.)

Experimentation has shown, however, this does not seem to be the case. If I manually remove all schema names from the column derivations, WHERE clauses, etc., I can export from Development and import into Production and the jobs access the local tables (via the VOC file
pointers) as would be expected.

Two questions:

1) Is there anything "bad" waiting to happen to me if I do things this way? (I am gun-shy after my flub with the CLEAR.FILE on the logs yesterday, Ray

2) Alternatively, is there a way to automatically change the schema names in the components during/after importing into the project?

Regards,

Clif

[admin]

DataStage 4.0

I have a single server with two projects--Development and Production. We have a set of jobs that use localuv tables. When we create our uv stages for these jobs, the table names and column derivations are prepended with the project name. Eg. Development.CUST.NAME

When the job is exported from Development and imported into Production this, of course, causes the problem of the production job still referencing the Development tables and data.

Now, page 5-5 of the Server Job Tutorial says, "As previously noted, UniVerse and some other RDMS products require you to qualify these names with the account name." (Schema name supposedly required, in UniVerse SQL terms.)

Experimentation has shown, however, this does not seem to be the case. If I manually remove all schema names from the column derivations, WHERE clauses, etc., I can export from Development and import into Production and the jobs access the local tables (via the VOC file
pointers) as would be expected.

Two questions:

1) Is there anything "bad" waiting to happen to me if I do things this way? (I am gun-shy after my flub with the CLEAR.FILE on the logs yesterday, Ray

2) Alternatively, is there a way to automatically change the schema names in the components during/after importing into the project?

Regards,

Clif

[admin]

Use a job parameter to specify the account name.
>
> From: Moderator
> Date: Wed, 21 Feb 2001 14:03:18 -0800
> To: informix-datastage@oliver.com
> Subject: Changing schema name on UV tables
>
> DataStage 4.0
>
> I have a single server with two projects--Development and Production.
> We have a set of jobs that use localuv tables. When we create our uv
> stages for these jobs, the table names and column derivations are
> prepended with the project name. Eg. Development.CUST.NAME
>
> When the job is exported from Development and imported into Production
> this, of course, causes the problem of the production job still
> referencing the Development tables and data.
>
> Now, page 5-5 of the Server Job Tutorial says, "As previously noted,
> UniVerse and some other RDMS products require you to qualify these
> names with the account name." (Schema name supposedly required, in
> UniVerse SQL terms.)
>
> Experimentation has shown, however, this does not seem to be the case.
> If I manually remove all schema names from the column derivations,
> WHERE clauses, etc., I can export from Development and import into
> Production and the jobs access the local tables (via the VOC file
> pointers) as would be expected.
>
> Two questions:
>
> 1) Is there anything "bad" waiting to happen to me if I do things this
> way? (I am gun-shy after my flub with the CLEAR.FILE on the logs
> yesterday, Ray
>
> 2) Alternatively, is there a way to automatically change the schema
> names in the components during/after importing into the project?
>
> Regards,
>
> Clif
>
>

[admin]

Exactly what I wanted to do. But it is nice to have some confirmation from someone who has used it that way that I wasnt getting ready to shoot off my own foot.

Thanks, Ray.

Regards,

Clif


Wednesday, February 21, 2001, 1:47:41 PM, you wrote:

> Similarly, you can remove the table name qualifier from a column name
> if the result is unambiguous. I usually do this in the table
> definition in the Repository, after importing it, so that the
> generated SQL statements will be shorter.