Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Post Unanswered: Defered Index Creation

    Currently working with a database with roughly 120 million rows and need to create multiple indexes.

    Drive space is plenty, and would prefer to make one scan across the data to build these new indexes.

    How does one defer the commit of index creation till the commit has been issued. Option of unloading and reloading the data is not an option since the data is roughly 45 gig and already contains roughly 20 indexes on it as it (DW, massive drive space, and no transactions, so I can get away with being greedy)

    I have tried "db2 +c -tvf so_script_file" with no success.

    System: Linux
    DB2: v8.1 WGE

    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Defered Index Creation

    in order to explicity commit use the following flag:

    db2set db2options=+c

    then issue your create index statement.


    dollar

    Originally posted by Viffer
    Currently working with a database with roughly 120 million rows and need to create multiple indexes.

    Drive space is plenty, and would prefer to make one scan across the data to build these new indexes.

    How does one defer the commit of index creation till the commit has been issued. Option of unloading and reloading the data is not an option since the data is roughly 45 gig and already contains roughly 20 indexes on it as it (DW, massive drive space, and no transactions, so I can get away with being greedy)

    I have tried "db2 +c -tvf so_script_file" with no success.

    System: Linux
    DB2: v8.1 WGE

    Thanks

  3. #3
    Join Date
    Jan 2003
    Posts
    2

    Re: Defered Index Creation

    Alas, same result. It will not defer the creation of the indexes, but rather start building them.

    Originally posted by dollar489
    in order to explicity commit use the following flag:

    db2set db2options=+c

    then issue your create index statement.


    dollar

  4. #4
    Join Date
    Sep 2002
    Posts
    456

    Re: Defered Index Creation

    To my understanding you don't want to create index right away as table is large and on the DB2 side I don't think there is any option of deferring the index creation (I think Oracle does).

    One solution though; why not schedule a job to execute at night? This way it won't affect your users.

    dollar
    Originally posted by Viffer
    Alas, same result. It will not defer the creation of the indexes, but rather start building them.

Posting Permissions

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