TCL WHO command points to different Project

[marbar]

Hello,

I wanted to delete an obsolete project (MB_WHRCS89_90_DEV)
but as I started gathering some info
it looks like that project is somehow pointing (see WHO command below) to
my current development project (WHRCS89_90_DEV).

I ran the following TCL commands for project MB_WHRCS89_90_DEV:

Code: Select all

>SELECT * FROM UV.ACCOUNT WHERE @ID = 'MB_WHRCS89_90_DEV';
Account...   UID   GID  Owner name......  UNIX pathname...........  Permissions

MB_WHRCS89     0     0  DataStage\7.5\ap  /appl/xxxxxx/Ascential
_90_DEV                 ux19\30328        /DataStage/DSEngine/proj
                                          ects/MB_WHRCS89_90_DEV

1 records listed.

Code: Select all

>SELECT * FROM UV_SCHEMA WHERE @ID = 'MB_WHRCS89_90_DEV';


Schema. MB_WHRCS89_90_DEV
Owner.. 4967
Path... /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV


1 records listed.

Code: Select all

>LOGTO MB_WHRCS89_90_DEV
>

Code: Select all

>WHO
16 WHRCS89_90_DEV From xxxxxx

Code: Select all

>VERIFY.SQL SCHEMA MB_WHRCS89_90_DEV
Checking permission.
* Cannot find schema 'MB_WHRCS89_90_DEV'.
* Data for the schema 'MB_WHRCS89_90_DEV' should be deleted.

2 fixable errors found.

Items marked with a '!' are information messages only.
Items marked with a '*' can be fixed by using the FIX option to VERIFY.SQL.
Items marked with a '**' are situations where VERIFY.SQL could not continue.

Then I ran the same TCL commands for project WHRCS89_90_DEV:

Code: Select all

>SELECT * FROM UV.ACCOUNT WHERE @ID = 'WHRCS89_90_DEV';
Account...   UID   GID  Owner name......  UNIX pathname...........  Permissions

WHRCS89_90     0     0  DataStage\7.5\ap  /appl/xxxxxx/Ascential
_DEV                    ux19\63912        /DataStage/DSEngine/proj
                                          ects/WHRCS89_90_DEV

1 records listed.

Code: Select all

>SELECT * FROM UV_SCHEMA WHERE @ID = 'WHRCS89_90_DEV';

Schema. WHRCS89_90_DEV
Owner.. 4967
Path... /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS89_90_DEV


1 records listed.

Code: Select all

>LOGTO WHRCS89_90_DEV
>

Code: Select all

>WHO
16 WHRCS89_90_DEV From xxxxxx
>

Code: Select all

>VERIFY.SQL SCHEMA WHRCS89_90_DEV
Checking permission.
Building table list...........
.
.
.
....Done.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/J_Fact_PS_DIRECT_SRVC_FOUND_2'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/DS.GETINFO.DEFAULTS'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/DSLink299'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.
.
.
.
.

Here the list of messages outputed by the VERIFY.SQL seems to be infinite...
It just goes on and on, probably for every object in the project.


I am afraid that if I delete project MB_WHRCS89_90_DEV, my development project will
be deleted too. I have backups of the development project so I could recreate it from scratch
but it is something I would like to avoid if there is a way to correct this situation.

Any comments, suggestions are greatly appreciated.
Thanks

[ray.wurlod]

Before you go any farther, try VERIFY.SQL specifying the pathname of the schema, rather than its name. For example

Code: Select all

VERIFY.SQL SCHEMA /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV 

Code: Select all

VERIFY.SQL SCHEMA /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS89_90_DEV 

[marbar]

Ray,
Here it is for project MB_WHRCS89_90_DEV:

Code: Select all

>VERIFY.SQL SCHEMA /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV
Checking permission.
* Possible moved or duplicate schema.
** /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV is a
   duplicate schema. It cannot have data in the SQL catalog.

1 fixable error found.
1 verify operation discontinued.

Items marked with a '!' are information messages only.
Items marked with a '*' can be fixed by using the FIX option to VERIFY.SQL.
Items marked with a '**' are situations where VERIFY.SQL could not continue.

For project WHRCS89_90_DEV (again, lots of messages...I just inserted some below):

Code: Select all

>VERIFY.SQL SCHEMA /appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS89_90_DEV
Checking permission.
Building table list..................................................................................
.....................................................................................................
.
.
.
.
..................Done.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/J_Fact_PS_DIRECT_SRVC_FOUND_2'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/DS.GETINFO.DEFAULTS'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/DSLink299'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.
.
.
.
.
Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/DS_TEMP1130'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.
.
.
.
.
Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/VOC'.
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

Verifying table '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/WHRCS
89_90_DEV/VOCLIB'.
Press any key to continue...
Checking file permissions.
** No SQL catalog data for schema '' in UV_SCHEMA.

12728 verify operations discontinued.

Items marked with a '!' are information messages only.
Items marked with a '*' can be fixed by using the FIX option to VERIFY.SQL.
Items marked with a '**' are situations where VERIFY.SQL could not continue.

[ray.wurlod]

From that, I deduce that "someone" made an operating system copy of the project, hence the "duplicate schema" reference.

I presume that, when you try to delete the obsolete project, that you get an error. Is that the case? If so, what is that error?

I think that this is a recoverable situation, though a crowbar approach (deleting the obsolete project from the operating system level then repairing the SQL system tables with respect to the project that you want to keep). But that can be done.

[marbar]

There had been a previous attempt by a consultant to delete, among other projects, also project MB_WHRCS89_90_DEV and during that activity the development project was wiped out.
I thought that he had made a mistake and deleted the wrong project but now I am more inclined to think that the delete of project MB_WHRCS89_90_DEV also triggers the delete of the development project.

I do not feel brave enough to go down the same path again, just to confirm this scenario.
I would just like to fix the problem and go on with my work.

The OS delete of project MB_WHRCS89_90_DEV is simple enough.
I would imagine that to eliminate all references to that project from the Repository tables I would have to clean up UV_SCHEMA and UV.ACCOUNT (and I can check existing posts for that).

Is there anything else that you would suggest I should do?

Thanks.

[ray.wurlod]

Do the two VERIFY.SQL using the pathname and the FIX option and, if necessary, add entries to UV.ACCOUNT to describe any project that is not described there. After that you should have two projects. Then you can use both, or delete one from Administrator client. As usual, take a backup (a UNIX copy) of the projects and of the Engine before you begin.

[marbar]

Thanks Ray, I will proceed with the repairs and post the results.

[marbar]

Ray,
Instead of fixing the project I did not need and then deleting it through Administrator I decided to do an OS rename of the project's directory, fix the SQL Catalog tables and fix the UV.ACCOUNT table.

Before going ahead I verified the method by recreating the same conditions with another set of projects and by going through the same set of actions.

1) OS rename of project MB_WHRCS89_90_DEV's directory to MB_WHRCS89_90_DEV1.

2) Cleanup of references in the SQL catalog.

Code: Select all

devserv:DEVENV:/appl/xxxxxx/Ascential/DataStage/DSEngine> . ./dsenv
devserv:DEVENV:/appl/xxxxxx/Ascential/DataStage/DSEngine> bin/dssh
DataStage Command Language 7.5
Copyright (c) 1997 - 2004 Ascential Software Corporation. All Rights Reserved
DSEngine logged on: Sat Jul 26 06:23:20 2008

>VERIFY.SQL SCHEMA MB_WHRCS89_90_DEV
Checking permission.
* '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV' is
   not a DataStage account.
* Data for the schema 'MB_WHRCS89_90_DEV' should be deleted.

2 fixable errors found.

Items marked with a '!' are information messages only.
Items marked with a '*' can be fixed by using the FIX option to VERIFY.SQL.
Items marked with a '**' are situations where VERIFY.SQL could not continue.

>VERIFY.SQL SCHEMA MB_WHRCS89_90_DEV FIX
Checking permission.
* '/appl/xxxxxx/Ascential/DataStage/DSEngine/projects/MB_WHRCS89_90_DEV' is
   not a DataStage account.
* Deleting catalog data for schema 'MB_WHRCS89_90_DEV'.
* Deleting table data for 'EXAMPLE1'.
* Deleting columns data for 'DATE'.
* Deleting columns data for 'QTY'.
* Deleting columns data for 'CODE'.
* Deleting columns data for 'PRODUCT'.
* Deleting ownership record for user 'xxxxxx' on table 'EXAMPLE1'.

2 errors fixed.

Items marked with a '!' are information messages only.
Items marked with a '*' have been fixed.
Items marked with a '**' are situations where VERIFY.SQL could not continue.

3) Delete of project MB_WHRCS89_90_DEV's entry from table UV.ACCOUNT.

Code: Select all

devserv:DEVENV:/appl/xxxxxx/Ascential/DataStage/DSEngine> . ./dsenv
devserv:DEVENV:/appl/xxxxxx/Ascential/DataStage/DSEngine> bin/dssh
DataStage Command Language 7.5
Copyright (c) 1997 - 2004 Ascential Software Corporation. All Rights Reserved
DSEngine logged on: Sat Jul 26 06:23:20 2008

>DELETE FROM UV.ACCOUNT WHERE @ID = 'MB_WHRCS89_90_DEV'

4) Test of the development project to make sure it still works.
5) Delete of MB_WHRCS89_90_DEV project's directory from the UNIX server.

Thanks again for you help.

[ray.wurlod]

You're welcome. Well done on a cautious, and ultimately successful, result.