Changing the owner of a database

What is the quickest way to change the owner of an Informix database to a different user? I tried searching on the internet for the answer to this question, and I didn't find one. ------------------------------ John Dargan ------------------------------

I wish there was a quick way, but unfortunately it requires unloading everything, creating the database with the new owner, and importing everything back. I think that there is an RFE asking for this functionality. Mike ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

There has been a RFE for this for decades

Here is the reorganized text, focusing on the depth analysis of the consistent hashing algorithm: Consistent hashing maps server IDs to a ring using a hash function. Incoming requests are hashed to the same ring and assigned to the server next to it in the clockwise direction. Challenges and Limitations 1. Skew: A single server may manage a disproportionate number of requests, leading to overload and decreased performance. 2. Connection Churn: Changes in server topology can lead to a large number of requests being reassigned, causing connection churn. Amazon's Improvement: Multiple IDs per Server Assigning multiple IDs to each server reduces skew and leads to a more balanced distribution of requests. Google's Innovation: Bandicorporate Sequence Google's innovation introduces a bandicorporate sequence, which constrains the load variance amongst servers. This ensures requests are distributed evenly, minimizing the risk of overload. Benefits and Results 1. 90% Reduction in Connection Churn: Minimizes connection churn, leading to faster processing and improved user experience. 2. Improved Load Balancing: Requests are distributed more evenly across servers, reducing the risk of overload. 3. Enhanced Scalability: Handles changes in server topology efficiently, making it ideal for large-scale distributed systems. Technical Details 1. Hash Function: Consistent hashing uses a hash function to map server IDs to a ring. 2. Ring Structure: The ring structure allows for efficient addition and removal of servers. 3. Request Assignment: Incoming requests are hashed to the same ring and assigned to the server next to it in the clockwise direction. References - Karger, D., Lehman, E., Leighton, T., Panigrahy, R., Levine, M., & Lewin, D. (1997). Consistent hashing and random trees: Distributed caching protocols for relieving hot spots on the World Wide Web. Proceedings of the 29th Annual ACM Symposium on Theory of Computing, 654-663. - Amazon. (2014). Consistent Hashing. - Google. (2019). Bandicorporate Sequence for Consistent Hashing. ------------------------------ suman suhag ------------------------------

Dear Sir, Use the following SQL command to change the owner of the database: you can use the dbaccess utility to change the owner of the database: bash dbaccess <database_name> - <<EOF rename database <database_name> to <new_owner>.<new_database_name>; EOF Or RENAME DATABASE <current_database_name> TO <new_owner>.<new_database_name>;

Ah, if only it was so simple. Unfortunately, this is not a valid syntax for Informix. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

The easiest supported method I know of is: Dbexport or Art's myexport the database Edit the .sql file to change the owner of any needed objects Dbimport or myimport the database as the new user. --EEM

That is undoc'd - if it works. Doesn't work on my 14.10.FC11W1 install Syntax Read syntax diagramSkip visual syntax diagram RENAME DATABASE owner.old_database TO new_database

Yea, Suman's syntax is not valid in v15.0 either. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I did a long shot test Grant DBA to new owner, login as new owner and run the rename, makes no difference.

Hi, I wonder if changing a default dbspace is a RFE, too? ------------------------------ Sincerely, Dennis ------------------------------

Dennis: I do not thing that this is an existing RFE, no. Feel free to put it in, though this may be a tougher one to implement. The "default" dbspace for a database is not explicitly recorded anywhere, it is just the dbspace where the table's TABLESPACE/header page and the database catalog tables reside. But that's an issue for JC and his team, so, go for it. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

What would a database's default dbspace be other than the dbspace the database got created in? I don't understand the desire here, or what new behavior is being suggested. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Andreas: I think that the impetus here is "Where are new tables, indexes, and other database objects that have no storage specification going to be created?" The answer to that is "In the default dbspace!" which, as you note, is currently the dbspace in which the database and its catalog tables were all created. The OP wants to be able to specify a different "default" dbspace for new objects. AUTOLOCATE is not a solution because it only affects where new databases are created by default and where the partitions of fragmented tables will reside, and it actually does not allow the user to specify a dbspace (though the SQL API does). Non-fragmented tables and indexes are not affected by AUTOLOCATE. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Alright, so one more item for a possible "database properties" project. Btw., with "tenant databases" you already got to specify db-/blob-/sblobspaces assigned to and exclusively to be used by a specific database. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Andreas: Yup, tenant databases have several advantages. But, they are not much used in the field. I have a client that is a service bureau. They run two primary servers containing databases for over 700 customers. Each has a table dbspace (where the database lives) and an index dbspace and several also have a blobspace or smart blobspace, but none of them have been defined as tenant databases. That despite my having recommended doing that before their last host upgrade when they could have reconfigured everything. For some, that's just one more added complexity. <sigh> For what it's worth, a suggestion to simplfy implementation: You could just expand the sysautolocate table in sysmaster and in each database to process some flag value (say for argument 0x8000000) to indicate that that that, or those, dbspaces should be used as the default dbspace for new objects, or maybe separate flag values for tables versus indexes. Such a record in sysmaster:sysautolocate would be the superdefault overridden by the local database's sysautolocate records. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------