How to Improve Performance of Setintegrity ?

Zhong Shi

How to Improve Performance of Setintegrity ?
List Experts,

After loading data onto data warehouse, we have to run the setintegrity.
There are only three tables that have foreign key on them but the
setintegrity runs more then one week. What are the possible ways to tune the
system so that the setintegrity run faster? What resources are used by the
setintegrity? Or generally and internally how the setintegrity work?

System: UDB v7.2, AIX4.2, RS/6000.
Temp Tablespace: 147365 4k pages
Temp Tablespace Bufferpool 1000 4k pages (I know, this looks bad :-)
Index Tablespace Bufferpool: 8192 8k page
Data Tablespace Bufferpool: 8192 8k page

When I look at the monitor, here what I see:
Total Sort Time (ms) 767,070,270.0
Sort Overflow (%) 18.85
Average Sort time (ms) 35,081.46
Synchronous I/O time (ms) 766,935,810.0

Your advice are highly appreciated.

Thanks

Zhong Shi
DBA
Incepture, Inc



[login to unmask email]

Re: How to Improve Performance of Setintegrity ?
(in response to Zhong Shi)
Make sure you have indexes supporting your foreign keys.
The following query from Richard Yevich and Susan Lawson will find foreign
keys without indexes

SELECT SR.TBNAME, SR.RELNAME
FROM SYSIBM.SYSRELS SR, SYSIBM.SYSTABLES TB
WHERE SR.TBNAME = TB.NAME
AND TB.DBNAME = 'dbname'
AND TB.CREATOR = 'table_creator'
AND TB.CREATOR = SR.CREATOR
AND NOT EXISTS
(SELECT * FROM
SYSIBM.SYSFOREIGNKEYS FK,
SYSIBM.SYSINDEXES IX,
SYSIBM.SYSKEYS SK
WHERE SR.RELNAME = FK.RELNAME
AND FK.CREATOR = TB.CREATOR
AND IX.TBCREATOR = TB.CREATOR
AND IX.CREATOR = SK.IXCREATOR
AND SR.TBNAME = FK.TBNAME
AND SR.TBNAME = IX.TBNAME
AND IX.NAME = SK.IXNAME
AND FK.COLSEQ = SK.COLSEQ
AND FK.COLNO = SK.COLNO
AND FK.COLNAME = SK.COLNAME)
ORDER BY SR.TBNAME, SR.RELNAME ;

Carol Sutfin
Corporate DBA
AmSouth Bank
(205)326-5214 Fax:(205)326-5613
[login to unmask email]




"Shi, Zhong"
<[login to unmask email] To: [login to unmask email]
URE.COM> cc:
Sent by: DB2 Data Subject: How to Improve Performance of Setintegrity ?
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


01/09/03 10:46 AM
Please respond to
DB2 Data Base
Discussion List





List Experts,

After loading data onto data warehouse, we have to run the setintegrity.
There are only three tables that have foreign key on them but the
setintegrity runs more then one week. What are the possible ways to tune
the
system so that the setintegrity run faster? What resources are used by the
setintegrity? Or generally and internally how the setintegrity work?

System: UDB v7.2, AIX4.2, RS/6000.
Temp Tablespace: 147365 4k pages
Temp Tablespace Bufferpool 1000 4k pages (I know, this looks bad :-)
Index Tablespace Bufferpool: 8192 8k page
Data Tablespace Bufferpool: 8192 8k page

When I look at the monitor, here what I see:
Total Sort Time (ms) 767,070,270.0
Sort Overflow (%) 18.85
Average Sort time (ms) 35,081.46
Synchronous I/O time (ms) 766,935,810.0

Your advice are highly appreciated.

Thanks

Zhong Shi
DBA
Incepture, Inc



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can