Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Question Unanswered: Typical Upgrade related issue.

    Hello,

    I am an Application Developer. I question is not specific to Oracle or any other vendor, but since I use oracle Jdeveloper Studio, I am asking in oracle section.

    My question is related to 2 typical database upgrade issues that I am aware of:

    1. Is there any s/w that I can use to bundle my Application SQL statements properly for fresh and upgrade type of installation ?

    for e.g.: I created a database table for first release of my application. Later I modify the schema of table.
    For Fresh Application SQL bundle: whole CREATE SQL statement should be kept and for Upgrade application SQL Bundle; Only ALTER Statement should be kept for execution.
    Also that s/w should manage bundling of SQL statements according to different Upgrade versions.


    2. How to handle Conflict records between parent and child table records during upgrade.
    for e.g.: for first release I shipped country and cities table with following records.
    Country
    Code:
    ID | NAME
    1      US
    Cities
    Code:
    ID  | CITY_NAME | COUNTRY_ID
    1      NY         1
    2      LA         1
    I upgrade and shipped following Cities records for 2nd release


    Cities
    Code:
    ID  | CITY_NAME | COUNTRY_ID
    3      Dallas     1
    but before upgrading to 2nd release, customer had already added following record in Cities in their database

    Cities
    Code:
    ID  | CITY_NAME | COUNTRY_ID
    3      SJ     1
    Now while upgrading, user will get INSERT conflict error as record "3" already exists.

    how do we prevent from such conflicts ?

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    INSERT INTO Cities (ID, city_name, country_id)
    VALUES
    (
    (SELECT MAX(ID) + 1 FROM Cities),
    'SJ',
    1
    )
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Aug 2011
    Posts
    10
    Thanks,

    But you think it is a easy job for hundreds of table with 100s of rows in each table ?

    What I mean to ask is - what is the standard ways that you as a DBA follows to tackle such situation ?

  4. #4
    Join Date
    Aug 2011
    Posts
    10
    any one please ?

  5. #5
    Join Date
    Aug 2011
    Posts
    10
    Up! Up!

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    1. Create those scripts manually. There may be some tools, but I never used any. You may some ideas about them (and follow with their investigation) from this thread: http://www.orafaq.com/forum/t/174271/0/

    2. Do not let user enter the ID for new rows. Create sequence which value is assigned to it. If you want to let some IDs reserved for your (future) data, start the sequence with reasonable value (e.g. 100). Then lower values will not collide with user data and you may use them any time.
    Of course, this has to be done when the table is created. When facing the situation you described, you have to use sequence even for your data (so the ID for given row may not be the same for different users).

    Do not use MAX+1 approach. Never. It may assign the same number to different rows, when two users add rows simultaneously.

  7. #7
    Join Date
    Aug 2011
    Posts
    10
    Thank for you valuable suggestion.

    I didn't get following part:
    > "Do not use MAX+1 approach. Never. It may assign the same number to different rows, when two users add rows simultaneously."

    first of all I am not gonna use MAX+1 while user entering data in new record. MAX+1 is useful while user saving new record, thus no 2 users will have conflicts with primary key (and how your above statement is relevant to this post ?)

    [Note: MAX is not max(primaryid). MAX is Last_Generated_ID]

    If I implement this strategy, then I need to make sure that Last_Generated_ID on Production server should pick last generated id for users and for Internal, if should be picked from separate range (for e.g.: 1-100)


    I come up with one more suggestion: instead of using reserved range, use globally unique instance id for reference. Thus no 2 users will have same key generated every.

    for example, instead of referring COUNTRY_ID in cities, use CONTRY_INSTANCE_ID in Cities for reference. Where CONTRY_INSTANCE_ID is randomly generated globally unique String while creating Country record.
    What you think of this idea ?

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    As for MAX+1 approach, I was reacting on Florins post, where he suggested it. I just wanted to emphasize that that approach is not acceptable at all.

    About generation of random ID, uniqueness and randomness do not have anything in common. You may generate the same "random" identification twice. Maybe I did not understand your proposal, but I would definitely use sequence. What is wrong with it?

  9. #9
    Join Date
    Aug 2011
    Posts
    10
    The framework I am using to develop application doesn't support use of SEQUENCE and for the sake of 'support multiple databases', application will generate the primary key value. Also there may be a requirement to generate String primary key value automatically. for e.g.: Primary Key of Country should starts with CTR. (CTR00001,CTR00002,CTR00003). So to achieve all these I am storing LAST_GENERATED_ID (i.e. value "3" for 3 CTR records) in separate table for every other table. Whenever user creates new Country record, LAST_GENERATED_ID value is updated to 4 "while saving the NEW Country record" and new primary key for country record becomes CTR00004.

    I don't see anything wrong in this approach (thus, obviously there is no easy way for database user to create new Country record at database level - which is fine).
    Please point me to Florins post.

    I am sorry for confusion, I just mean Globally Unique String (forget about Randomness). One drawback of using reserved range is - even if you set 1-99999 as reserved range, they may be a case where we (folks who developed the application) want to ship 100000th record with the installer and still doesn't want to get conflict with user data (Record ID 100000 will be the first user record ).
    Last edited by sahanand; 09-09-11 at 08:29.

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Your approach seems to be correct. However it will lead to serialization (for obtaining the new ID, you have to wait till the transaction currently updating it ends). If insertion happens often from multiple sessions, this may lead to its slowness because of this bottleneck.

    > Please point me to Florins post.
    Post #2 (the very first reply) in this thread.

    Of course, you have to choose the range sufficiently. Anyway, you may still generate the ID from sequence/table for non-user rows.

  11. #11
    Join Date
    Aug 2011
    Posts
    10
    Now it make sense.

    Thank you for your time.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •