Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Disable & enable of Index

    Hi,

    is it possible to temporarily disable & again enable of an index on a table in db2luw.
    If so can some one tell me the syntax or process, to achieve this.

    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What do you mean by enable/disable index?
    LUW db2 lets you create and drop an index, reorg-it, runstats it, use in hints etc.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    My requirement is in a procedure , need to remove the index on a table & some operations on the table & again need to ADD the same index as to the previous stage.
    I am having some lakhs of tables on which I need to do the same.
    can't manually drop , create on all the tables individually.

    This can be directly done on sql server as
    ALTER INDEX I9 ON tableName DISABLE
    ALTER INDEX I9 ON tableName REBUILD

    Looking for same functionality in db2luw
    Last edited by HABBIE; 12-10-14 at 06:35.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    There is no such feature in present versions of db2 on luw. (Not sure if there is a genuine need either...it might be said that an index is either good or evil, and evil ones should be dropped ).

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply.
    But there has to be an alternative way to do these kind of things.
    As similar to disabling triggers on a table in db2luw, we can't do this directly, we have to follow some mechanism.
    So I am looking for this kind of alternative.
    Please excuse me if I am wrong any where.
    Last edited by HABBIE; 12-10-14 at 07:23.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Why would you want to disable an index before running a SP?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The only disable is called DROP.
    What are you attempting to do with all of these procedures against tables and indexes? I have been supporting database systems for 19 years now and have never seen a need for a lot of the things you have been asking us about lately.
    Dave

  8. #8
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the information.
    I have a basic question , as I never worked on indexes I am asking this. I have a scenario where I need to drop all indexes on a particular table & need to recreate it.
    1)First I will find all the indexes using sysibm.sysindexes catalog table.
    2)Again i want to recreate them with the same constraints & on same columns, as if they appear previous.

    Can some one tel me the syntax for this.

    Thanks

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2look do all that for you.. see options in infocenter
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Why would you want to drop all the indexes of a table and recreate them later?

    I am guessing right, you are loading some data so you are trying to do it EXACTLY as you accomplish the same tasks in SQL Server. WelL, DB2 is not SQL Server, so you are on the wrong path. You need to forget some of the things you know about Microsoft databases and read the IBM docs.
    Anyway, I am hoping you are NOT doing this on PROD, or your SYSCAT tablespace will suffer.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  11. #11
    Join Date
    Jul 2014
    Posts
    294
    aflorin
    Thanks for the reply. This is not on production.
    I am updating tons of records . Before that I have to disable all the indexes on all the tables & after that have to create only one user created index (I9) on all the tables. My update query has to be run only with one index(I9).
    When once my update query got completed. again I have to reset all the tables to their original position(previous indexes).

    That is what I am trying to find.
    If you have a better solution for this, can you please let me know.It l be a very good favour.
    Last edited by HABBIE; 12-11-14 at 05:35.

  12. #12
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    A better solution might be to do the bulk update in an unlogged DGTT (which contains the PK and only the columns needing to be updated), which is indexed and runstatted, and then MERGE from there into the target table.

    Be wise: don't fixate on an SQL-server approach. There lies pain and failure.

  13. #13
    Join Date
    Jul 2014
    Posts
    294
    may be this DGTT will work for one table with large number of records. I have to do this on an heterogenous database. I have to implement this on multiple tables each having more than a lakh records.

  14. #14
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I would try with LOAD - from a csv file or from a CURSOR
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  15. #15
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your earlier postings mentioned using an sproc. Often this means processing tables in series. Slow.

    You mention a 'lakh records' which is not western terminology for numbers, but I understand it to mean 100,000.
    For db2 this is a trivial number of rows, even for small hardware systems.

    Another approach would be do to it in parallel (i.e process several tables concurrently via different connections, each connection working on a different table)) - ideal for scripting, if your hardware is multi-core and you have the relevant skills.

    Depending on the nature of the bulk updates (whether joins to other tables are needed etc) multiple different approaches are possible - but a key detail is to simply avoid dropping and recreating the indexes - simply by choose a more effective design that uses DB2 features properly.

    Another approach is to scrutinize the exact update(s) and the number of columns to be updated per table, and then determine which indexes would need to be maintained by the update. It may be that only a couple of indexes need to be touched by the update(s). In which case, consider allowing db2 to do what it does best: scale.

    Many local factors may influence the design choice: skills, resources, experience, competence , concurrency requirements, online requirements, maintenance-window, hardware available etc. Only you know the weight of these factors at your site.

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
  •