Constraint Owner
A DBA inherited 11.50.FC5 instances on AIX where objects had been created under a personal user rather than informix, and asked whether he could just UPDATE the owner column in sysconstraints. The consensus answer was no: changing object ownership isn't supported, and hand-editing system catalogs risks corruption and cache problems. The recommended route was to drop and recreate the objects, e.g. dbexport/dbimport using myschema (utils2_ak) with options to strip owner clauses. The poster said he lacked downtime for an export/import and would recreate the constraints instead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Folks, I have adopted some online instances (11.50.FC5 on AIX 5.3) in which the previous DBA had a bad habit of creating database entities as himself rather than informix. My question is... Can I simply update sysconstraints (owner column) to informix rather than dropping and recreating them? TIA, Dan
On Thu, Nov 18, 2010 at 7:01 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > Hi Folks, > > I have adopted some online instances (11.50.FC5 on AIX 5.3) in which the > previous DBA had a bad habit of creating database entities as himself > rather > than informix. My question is... Can I simply update sysconstraints (owner > column) to informix rather than dropping and recreating them? > > TIA, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Altering object owner is not supported. There is no oficial way of doing it and what you suggest is not supported (and can probably get you into trouble due to several system caches etc. As a side note, creating the objects as a user different from Informix is a good idea. But not with a "personal" user. A specific user (application user) should be used. This opinion may not be consensual... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf30433fa815b69c0495589b27
Most catalog tables should not be tampered with. Dire things can happen.
Bad idea.
You will have to drop the offending objects and recreate them with the
desired owner. The easiest way is to export the database then replace the
schema file with one generated by myschema using -l for dbimport
compatibility and -O to eliminate all owner clauses. You can add other
options to speed the reload such as the -a option to generate more
appropriate extent sizing, etc. Then just drop the database (temporarily
rename it) and dbimport as Informix or whomever to recreate everything with
the appropriate user.
Myschema is part of the package utils2_ak which you can download free from
the IIUG software repository (www.iiug.org/software).
Art
On Nov 18, 2010 2:02 PM, "DAN MUELLER" <dan.mueller@trnswrks.com> wrote:
> Hi Folks,
>
> I have adopted some online instances (11.50.FC5 on AIX 5.3) in which the
> previous DBA had a bad habit of creating database entities as himself
rather
> than informix. My question is... Can I simply update sysconstraints (owner
> column) to informix rather than dropping and recreating them?
>
> TIA,
> Dan
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--20cf30433eb27f3bf6049558d179
Well aren;t you guys just the spoil sports of the day. (-; Thanx for the input. I will recreate them. BTW Art, I don;t have the downtime to export/import the db. Thanx, Dan
At least with dbexport you can remove the owner part of the entities
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN
MUELLER
Sent: Thursday, November 18, 2010 1:43 PM
To: ids@iiug.org
Subject: Re: Constraint Owner [21993]
Well aren;t you guys just the spoil sports of the day. (-;
Thanx for the input. I will recreate them.
BTW Art, I don;t have the downtime to export/import the db.
Thanx,
Dan
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
_____
avast! Antivirus <http://www.avast.com>; : Outbound message clean.
Virus Database (VPS): 101118-1, 11/18/2010
Tested on: 11/18/2010 1:47:18 PM
avast! - copyright (c) 1988-2010 ALWIL Software.
I said that dbexport/dbimport was the 'easiest' solution. I guess it's not
the the most convenient for you.
Have fun out there!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Nov 18, 2010 at 2:43 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote:
> Well aren;t you guys just the spoil sports of the day. (-;
>
> Thanx for the input. I will recreate them.
>
> BTW Art, I don;t have the downtime to export/import the db.
>
> Thanx,
> Dan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3054a7199d4bb50495594e65