Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Red face Unanswered: stored procedure for dropping and rebuild indexes

    I have an ETL tool that let's me configure a pre/post-session action like a shell script. Basically I want to drop an index on the primary key of a facttable. I have lots of information on stored procedures, but not a hint at a stored procedure for dropping or rebuilding an index.
    Is this possible at all this way, can someone give an example? or hint to a good site.....?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Surely we can do this in a stored procedure but in your case the PK should be the one defined by you and not by system. This comes in my mind because your index is on the primary key which is generally created by the system.

    I have done it practically in the procedure.
    Just write a simple procedure and try it.
    If you have problem let us know.

    cheers,
    Prashant

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    I have defined the primary key myself, it is a key over 5 fields, since these are the dimensions in the facttable (datawarehouse in this case).
    Anyway that is not the issue, I want to get examples of HOW to define the stored procedure to drop and rebuild indexes...........
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    Sorry for the delay..Actually i was not in office all these days...

    Here's a sample procedure:

    CREATE PROCEDURE BUILDINDEX()
    SPECIFIC BUILDINDEX
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL STORED PROCEDURE
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE T_OFFICEID INTEGER;
    DECLARE T_TOTAL INTEGER;
    DECLARE T_COUNTER INTEGER;
    DECLARE TEMPCURSOR CURSOR WITH HOLD FOR SELECT OFFICEID FROM OFFICE;
    SELECT COUNT(*) INTO T_TOTAL FROM OFFICE;
    SET T_COUNTER = 0;
    OPEN TEMPCURSOR;
    WHILE T_COUNTER < T_TOTAL DO
    SET T_COUNTER = T_COUNTER + 1;
    FETCH TEMPCURSOR INTO T_OFFICEID;
    create index OFFICE001 on OFFICE ( OFFICEID ASC, OFFICENAME ASC ) ;

    SELECT OFFICENAME, FULLNAME from OFFICE;

    DROP index CDSWEB.OFFICE001;
    END WHILE;
    END P1


    Let me know if this is ok for you.

    cheers,
    Prashant

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    dazzled

    Thanks for taking the time to post the example..............
    I am a bit unsure about the temp cursor and the loop you are using. Sorry to ask , does this have anything to do with the index?
    Sorry for my ignorance , can you give some comment on your example?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi Ties,

    That's just for example..
    You can make your own procedure with single statement or statement to be executed in loop or anything that fits your logic.

    cheers,
    Prashant

Posting Permissions

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