RENAME TABLE ERROR
A user on IDS 10 tried to change a table's owner with "RENAME TABLE dbo.tab TO informix.tab" and got error 201 (syntax error at the dot before the new name). Respondents explained that RENAME TABLE can only change the table name, not the owner; the owner-change syntax in the manual applies only to Extended Parallel Server (8.xx), not IDS. The only workaround is to unload the data, drop and recreate the table under the new owner (checking views, procedures and other dependencies first). The poster accepted this and did the unload/drop/recreate; several also advised against using 'informix' as the owner of user tables.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi. Recently I discovered in the production DB of the company some tables with OWNER different from INFORMIX. I decided to make a RENAME TABLE of the tables to change the such owner which indicates the documentation of IBM When executing "RENAME TABLE dbo.ct_rhhlaboralr5 TO informix.ct_rhhlaboralr5" I receive "201: A syntax error has occurred." The position of the error is the dot after informix. That I am making bad? I'm use informix user form this process, in IDS10FC6 Thanks
I just tried this on 9X and it looks like it changes the table name, not the ownership rename table 'informix'.bin_file to bin_files
On 12/12/2007, NESTOR RODRIGUEZ <ronga@adinet.com.uy> wrote: > Hi. > Recently I discovered in the production DB of the company some tables with > OWNER different from INFORMIX. > I decided to make a RENAME TABLE of the tables to change the such owner which > indicates the documentation of IBM > When executing "RENAME TABLE dbo.ct_rhhlaboralr5 TO informix.ct_rhhlaboralr5" > I receive "201: A syntax error has occurred." > The position of the error is the dot after informix. > That I am making bad? > > I'm use informix user form this process, in IDS10FC6 > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > RENAME TABLE only allows the changing of the table name not the table owner. The owner of a table need not be informix (and the purests here would argue it should never be :-) ). From memory the only way to change the owner of a table is to drop and recreate it, however I would be very careful when doing this as you may braek something important !! Especially in this case dbo is a default owner in $QL $erver (wash my mouth out !). Are you linking to such a beast? Is your application runnable against both databases? A bit more careful checking needed before your size 11s end up between you mandibles. Keith
You can't change the owner of a table with the rename command ... In order to do this you would need to drop and recreate the table with the new owner. Of course you would need to unload the data .. or move it to a different table first ... and also make sure that the table isn't reference anywhere else .. like views or procedures ... This is on the feature request for the next version of 11 .... or whatever they call it ... hopefully it makes it ... "NESTOR RODRIGUEZ" <ronga@adinet.com.uy> Sent by: ids-bounces@iiug.org 12/12/2007 06:22 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject RENAME TABLE ERROR [10713] Hi. Recently I discovered in the production DB of the company some tables with OWNER different from INFORMIX. I decided to make a RENAME TABLE of the tables to change the such owner which indicates the documentation of IBM When executing "RENAME TABLE dbo.ct_rhhlaboralr5 TO informix.ct_rhhlaboralr5" I receive "201: A syntax error has occurred." The position of the error is the dot after informix. That I am making bad? I'm use informix user form this process, in IDS10FC6 Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
In http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq ls.doc/sqls107.htm Syntax >>-RENAME TABLE--+--------+--old_table--TO----------------------> '-owner.-' >--+-------------------+--new_table---------------------------->< | (1) | '--------new_owner.-' and .... :) Usage To rename a table, you must be the owner of the table, or have the ALTER privilege on the table, or have the DBA privilege on the database. An error occurs if old_table is a synonym, rather than the name of a table. The renamed table remains in the current database. You cannot use the RENAME TABLE statement to move a table from the current database to another database, nor to rename a table that resides in another database. In Dynamic Server, you cannot change the table owner by renaming the table. An error occurs if you try to specify an owner. qualifier for the new name of the table. In Extended Parallel Server, a user with DBA privilege on the database can change the owner of a table, if the table is local. Both the table name and owner can be changed by a single statement. The following example uses the RENAME TABLE statement to change the owner of a table: RENAME TABLE tro.customer TO mike.customer When the table owner is changed, you must specify both the old owner and new owner. Important: When the owner of a table is changed, the existing privileges granted by the original owner are retained. In Extended Parallel Server, you cannot rename a table that contains a dependent GK index. In an ANSI-compliant database, if you are not the owner of old_table, you must specify owner.old_table as the old name of the table. If old_table is referenced by a view in the current database, the view definition is updated in the sysviews system catalog table to reflect the new table name. For further information on the sysviews system catalog table, see the IBM Informix Guide to SQL: Reference. If old_table is a triggering table, the database server takes these actions: * Replaces the name of the table in the trigger definition but does not replace the table name where it appears inside any triggered actions * Returns an error if the new table name is the same as a correlation name in the REFERENCING clause of the trigger definition When the trigger executes, the database server returns an error if it encounters a table name for which no table exists. ......................................... I prefer not create new table, load data into new table, drop old table and rename new table to old name.
NESTOR RODRIGUEZ wrote: > <SNIP> > In Dynamic Server, you cannot change the table owner by renaming the table. An > error occurs if you try to specify an owner. qualifier for the new name of the > table. > > In Extended Parallel Server, a user with DBA privilege on the database can > change the owner of a table, if the table is local. Both the table name and > owner can be changed by a single statement. The following example uses the > RENAME TABLE statement to change the owner of a table: > > RENAME TABLE tro.customer TO mike.customer > <SNIP> > I prefer not create new table, load data into new table, drop old table and > rename new table to old name. > You have no choice. As it states above: "In Dynamic Server, you cannot change the table owner by renaming the table. An error occurs if you try to specify an owner. qualifier for the new name of the table." Changing the owner of a table is ONLY permitted in Extended Parallel Server (Informix 8.xx versions) NOT in IDS (versions 7.xx, 9.xx, 10.xx, 11.xx) Art S. Kagel > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
I would personally not use "informix" owner for user tables. Cheers NESTOR RODRIGUEZ <ronga@adinet.com.uy> wrote: Hi. Recently I discovered in the production DB of the company some tables with OWNER different from INFORMIX. I decided to make a RENAME TABLE of the tables to change the such owner which indicates the documentation of IBM When executing "RENAME TABLE dbo.ct_rhhlaboralr5 TO informix.ct_rhhlaboralr5" I receive "201: A syntax error has occurred." The position of the error is the dot after informix. That I am making bad? I'm use informix user form this process, in IDS10FC6 Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Im preffer not use informix owner, but in this case, the migrate siystemo from SQL $erver, the programmers, create some tables with owner DBO. DBO not exist in the Linux user list (And in the future, neither it will exist :D ). Is a long war, to migrate de M$SQL to Linux/Informix :) , and I'm win ;). To avoid future problems, since the usurious dbo doesn't exist, I'n need change the owner. In the IBM documentation of IDS10, RENAME TABLE, permit change the owner, but not :( :'( I'm unload, drop and re-create la table now.
NESTOR RODRIGUEZ wrote: > Im preffer not use informix owner, but in this case, the migrate siystemo from > SQL $erver, the programmers, create some tables with owner DBO. > DBO not exist in the Linux user list (And in the future, neither it will exist > :D ). > Is a long war, to migrate de M$SQL to Linux/Informix :) , and I'm win ;). > To avoid future problems, since the usurious dbo doesn't exist, I'n need > change the owner. > In the IBM documentation of IDS10, RENAME TABLE, permit change the owner, but > not :( :'( > Read the manual more carefully, it states, as I posted yesterday, that changing the owner is ONLY permitted in Extended Parallel Server (Informix 8.xx) not in IDS! Art S. Kagel > I'm unload, drop and re-create la table now. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >