Informix

 View Only
Expand all | Collapse all

Changing the owner of a database

  • 1.  Changing the owner of a database

    Posted Fri February 21, 2025 05:02 PM

    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
    ------------------------------


  • 2.  RE: Changing the owner of a database

    Posted Fri February 21, 2025 05:07 PM

    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
    ------------------------------

    Original Message


  • 3.  RE: Changing the owner of a database

    Posted Fri February 21, 2025 05:42 PM
    There has been a RFE for this for decades

    On 2/21/2025 4:07 PM, Mike Walker via IBM TechXchange Community wrote:
    010001952a8ce53b-8ab9755a-1a88-49ad-96ce-99c7d8df86c8-000000@email.amazonses.com">
    I wish there was a quick way, but unfortunately it requires unloading everything, creating the database with the new owner, and importing... -posted to the "Informix" group



    Original Message


  • 4.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 09:42 AM

    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
    ------------------------------

    Original Message


  • 5.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 09:46 AM
    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>;





    Original Message


  • 6.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 10:16 AM

    Ah, if only it was so simple.  Unfortunately, this is not a valid syntax for Informix.  



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message


  • 7.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 10:32 AM

    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

     




    Original Message


  • 8.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 10:46 AM
    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

    On 2/24/2025 8:45 AM, suman suhag via IBM TechXchange Community wrote:
    01000195386ba066-21d5cf45-7000-4199-9a28-72c731a4e852-000000@email.amazonses.com">
    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:... -posted to the "Informix" group



    Original Message


  • 9.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 12:44 PM

    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
    ------------------------------

    Original Message


  • 10.  RE: Changing the owner of a database

    Posted Mon February 24, 2025 01:02 PM
    I did a long shot test

    Grant DBA to new owner, login as new owner and run  the rename, makes no difference.



    On 2/24/2025 11:43 AM, Art Kagel via IBM TechXchange Community wrote:
    01000195390ed069-244a1eb9-fec4-48e1-817f-cdf14eaf8488-000000@email.amazonses.com">
    Yea, Suman's syntax is not valid in v15.0 either. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK...



    Original Message


  • 11.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 03:37 AM

    Hi,

    I wonder if changing a default dbspace is a RFE, too?



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------

    Original Message


  • 12.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 06:22 AM

    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
    ------------------------------

    Original Message


  • 13.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 08:23 AM

    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
    ------------------------------

    Original Message


  • 14.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 09:06 AM

    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
    ------------------------------

    Original Message


  • 15.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 09:12 AM

    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
    ------------------------------

    Original Message


  • 16.  RE: Changing the owner of a database

    Posted Thu February 27, 2025 09:58 AM

    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
    ------------------------------

    Original Message