DSXchange

In a system with over 80 active projects the UV_SCHEMA file has become corrupted. Unfortunately, even when logging in as root or dsadm, it can no longer be modifed (to replace the missing CATALOG entry).

I have tried working with the builtin SQL statements in order to get access (both GRANT ALL PRIVILEGES ON UV_SCHEMA TO root; and other command attempts) but nothing seems to work; it might be a catch-22 type situation where the SQL level commands are trying to read the UV_SCHEMA file in order to get access information... but since the CATALOG entry is missing it won't work...

Can anyone think of a way to get the UV_SCHEMA file updateable?

Arnd

Create the projects on another box and copy the file at the OS level. I would just create one or two projects then copy this file to another file name. Fix the VOC to point to this copy. Update it. Copy to the other machine.

Oh dear.

First check whether UV_SCHEMA really is corrupted. Use uvfixfile or fixtool.

If it is, repair the damage.

You may need to follow this with some VERIFY.SQL SCHEMA pathname commands to reinstate schema entries into the SQL Catalog.


Note to Inexperienced Users

Arnd is very experienced with the database, and understands what I have described above. These can be very dangerous commands if used unwisely. Please do not inundate me with questions about them.

The six SQL Catalog tables can NOT be edited by anyone, privileges notwithstanding. They have a special bit set in the file header that means they can only be updated with appropriate DDL statements and the VERIFY.SQL utility.

Don't try it at home, kiddies!

Hello Ray & Kim,

I've tried getting another valid UV_SCHEMA file/table into the same physical location - no avail, it also gives me no update privileges as either root/dsadmin.

Normally I would bite the bullet and do a clean re-install, but this site is quite large and those 80+ projects have thousands of jobs and many active developers, so it cannot be taken offline unless necessary.

I don't have my 'internals' guide anymore, otherwise I would manually modify the UV_SCHEMA header to allow me to do normal UniVerse I/O on the file before resetting it. The UV_SCHEMA was corrupted, lots of blink and other errors - but the main problem is that by rectifying this the CATALOG entry was removed and subsequently the access to the table for updates disappeared.

It seems to be a catch-22 scenario - I can't reinsert the "CATALOG" entry because.... the CATALOG entry is missing...

VERIFY.SQL SCHEMA /usr/ascential/datastage/sql/catalog [ FIX ]

If you use a name as the argument in VERIFY.SQL it fixes the objects to conform to the catalog. If you specify a pathname it fixes the system tables to conform to what's out there.

(Caveat: I've never tried this with the CATALOG schema - never needed to.)

Ray - thanks for the suggestion, we tried that earlier and got an invalid schema error. The issue with that corrupted file is that now all projects are 'protected' and cannot be unprotected!

The restores done won't work either, the UV_SCHEMA file is still inaccessible to both root and dsadm.

I looks like a lengthy re-install is going to be unavoidable.

Can you restore UV_SCHEMA from backup (remembering that it's a dynamic hashed file, so you'll need its DATA.30, OVER.30 and .Type30 as well?

If you can, you sholud be able to use VERIFY.SQL for any project whose schema is not recorded.

Hello Ray,

the restores either evinced the same corruption or were incomplete (i.e. only a DATA.30 or a OVER.30 since they were using incremental backups) so the file needed to be FIXed, whereupon the same problems cropped up.

The VERIFY.SQL SCHEMA {pathname} showed inconsistancies that were not correctable - mainly entries to tables (all the UV_... ones) that were not part of a valid schema "". You couldn't add them to any schema, since no write access could be granted to any of the tables.

In the end a re-install was done, plus the projects were backed up and re-imported. Since some of these projects had been around for quite some time and had been extensively used with thousands of jobs in total, it was a good idea to do this kind of a "cleanup" operation; but the impact to the designer community was quite high.

I think that pretty much any other errors would have been manually recoverable in some fashion in order to get up-and-running; but the loss of the UV_SCHEMA ends up being worse than losing a VOC...

Did none of them have the wit to go back to the previous full backup? D'oh!

Yes, I agree, UV_SCHEMA and UV.ACCOUNT are the worst to lose, closely followed by the VOC file of the UV account.

Tales from the Trenches
Step N in an installation or upgrade, after configuring tunables and connectivity, should be to grab a full backup before letting anyone loose on the system.