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?

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

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 ....

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.

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

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?

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.