Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Angry Unanswered: Create index slow in DB2 UDB 7.2

    I have a partition table that contain about 1.6 million rows and i run the following sql :

    CREATE INDEX IDX_ST04_D_TRADE ON SIMS.SECURITYTRADING2004 (D_TRADE)

    but it takes more than 1 hour and still running.


    any idea why its taking so long?
    Hello

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you checked if the index creation is in progress or has not started yet(eg. lockwait) ..

    What's your hardware ?

    If this is a onetime activity(i suppose, it is ) don't worry about the performance ...
    Of course, this does indicate that your system is not tuned ...

    Parallelism
    sortheap
    sortheapthres
    logbufsz
    bufferpool size
    numioservers
    changepagethreshold
    prefetchsize
    numiocleaners
    physical placement of the data and index tablespaces
    physical placement of the temp tablespace etc
    influence the index creation performance ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    24
    If i not mistaken, the hardware is Intel 2.5G CPU with 2G RAM run on windows 2k server, i am using a query tool and simple execute the cmmand:

    CREATE INDEX IDX_ST04_D_TRADE ON SIMS.SECURITYTRADING2004 (D_TRADE)

    but its showing running..... even i had wait for 1 hour, when i open the DB2 Control center and check the following table whether the index is already created, but it seems doesn't turn up. So i have cancle the query, another question is, if i have the following SQL:

    (SELECT
    SIMS.CURR_YEAR_SECTRAD.D_TRADE,
    SIMS.CURR_YEAR_SECTRAD.I_SECURITY,
    SIMS.CURR_YEAR_SECTRAD.I_BOARD,
    SIMS.CURR_YEAR_SECTRAD.I_TRADING_TYPE,
    SIMS.CURR_YEAR_SECTRAD.I_CUSTOMER,
    SIMS.CURR_YEAR_SECTRAD.I_BROKER,
    SIMS.CURR_YEAR_SECTRAD.I_SUBBROKER,
    SIMS.CURR_YEAR_SECTRAD.I_TRADING_METHOD,
    SIMS.CURR_YEAR_SECTRAD.I_BUY_SELL ,
    SIMS.CURR_YEAR_SECTRAD.Q_VOLUME
    FROM
    SIMS.CURR_YEAR_SECTRAD
    WHERE
    SIMS.CURR_YEAR_SECTRAD.I_BUY_SELL = 'B'
    ORDER BY SIMS.CURR_YEAR_SECTRAD.D_TRADE)

    UNION ALL

    (SELECT
    SIMS.PREV_YEAR_SECTRAD.D_TRADE,
    SIMS.PREV_YEAR_SECTRAD.I_SECURITY,
    SIMS.PREV_YEAR_SECTRAD.I_BOARD,
    SIMS.PREV_YEAR_SECTRAD.I_TRADING_TYPE,
    SIMS.PREV_YEAR_SECTRAD.I_CUSTOMER,
    SIMS.PREV_YEAR_SECTRAD.I_BROKER,
    SIMS.PREV_YEAR_SECTRAD.I_SUBBROKER,
    SIMS.PREV_YEAR_SECTRAD.I_TRADING_METHOD,
    SIMS.PREV_YEAR_SECTRAD.I_BUY_SELL ,
    SIMS.PREV_YEAR_SECTRAD.Q_VOLUME
    FROM
    SIMS.PREV_YEAR_SECTRAD
    WHERE
    SIMS.PREV_YEAR_SECTRAD.I_BUY_SELL = 'B'
    ORDER BY SIMS.PREV_YEAR_SECTRAD.D_TRADE)


    Tis SQL took me 15min to finish, any idea should i create the index on the where clause key which is I_BUY_SELL only? i only given less than 3min to finish tis, please help ....
    Hello

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What percent of the rows in the table have REV_YEAR_SECTRAD.I_BUY_SELL = 'Y' ?

    Regarding index creation performance, I would check your tablespaces size allocation for tablespace used for the index and also your system temporary tablespaces. You system temporary tablespace(s) should be at least as large as your largest table. If you use SMS, the space must exist on the directory path since DB2 allocates the tablespace size as needed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you need
    ORDER BY SIMS.CURR_YEAR_SECTRAD.D_TRADE
    and
    ORDER BY SIMS.PREV_YEAR_SECTRAD.D_TRADE
    in your queries.
    In this case, these order bys don't help at all .. When doing UNION ALL this ordering may be lost ...


    If you want in a the order of d_trade, then use CTE
    WITH TEMP AS
    (select * from tab1 union all select * from tab2)
    select * from temp order by 1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2004
    Posts
    24
    the ratio is around 50% I_BUY_SELL = 'B' and I_BUY_SELL = 'S', in this case how i can check the table space whether is sufficient to fulfill this matter?
    Hello

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are going to retrieve 50% of the rows in a table, or even more than 10%, in most cases it is more efficient for DB2 to perform a tablespace scan, so an index may not help for that particular query.

    You should ask your DB2 DBA for information about your tablespaces and containers for the table, indexes, and system temporary tablespaces. If you don't have a DBA, get one.

    You query perfomance can probably be improved with normal DB2 tablespace/container optimization and proper configuration of the extents, prefetch size, I/O servers, etc. This is the responsibility of a DBA.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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