Informix

 View Only

  • 1.  Changing the owner of a database

    Posted 4 days ago

    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 4 days ago

    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 4 days ago
    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 2 days ago

    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 2 days ago
    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 2 days ago

    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 2 days ago

    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 2 days ago
    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 2 days ago

    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 2 days ago
    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