I'm running DB2 v7.2 under Windows 2000 Server with IBM's Tivoli program feeding data to multiple tables. Tivoli feeds approximately 125 million records per month to this db. I don't have much disk space so I pull specific records from last months data and delete everything except this months data. My process:

I originally created three temp_tbls which hold data from last month. Each month I drop and recreate these three tables. I then run three input queries that pull data from multiple tables.

This month, I can't seem to get the queries to complete. They act as if they need to run forever. While any one of these queries is running... I can look at it's temp_tbl and see records in it. The query never ends, preventing the commit to complete the input of records, making them permenant in the temp_tbl. If I force the query to stop.. or lose my connection to this server, stopping my query, the data being input into the temp_tbls is lost. I have not made any changes to the queries or the instance and this process has worked for me since Jan.

I ran a reorg on all tables involved and each reorg came back successful.

Any suggestions?

Hi,
In your input queries, what command are you using: for example(IMPORT/LOAD etc). If you are using IMPORT then you can use the COMMITCOUNT clause to see the latest COMMIT point.
If you are using the LOAD command then you can use the LOAD QUERY to see the number of records loaded till now.

Thanks,
Jayanta Datta
New Delhi, INIDA

I am using:

INSERT INTO LM_CPU (ENDPOINT_LABEL, INSTANCE_KEY, NAME, STAT, METRICVALUE, DTTM_DATE, DTTM_TIME)

SELECT
DB2ADMIN.ENDPOINTS.ENDPOINT_LABEL,
LEFT(DB2ADMIN.INSTANCES.INSTANCE_KEY, LENGTH(DB2ADMIN.INSTANCES.INSTANCE_KEY)-1) AS INSTANCE_KEY,
DB2ADMIN.METRICS.NAME,
DB2ADMIN.METRICS.STAT,
DB2ADMIN.METRICSDATA.METRICVALUE,
DATE(DB2ADMIN.METRICSDATA.TIMEKEY_DTTM) AS DTTM_DATE,
TIME(DB2ADMIN.METRICSDATA.TIMEKEY_DTTM) AS DTTM_TIME
FROM
((DB2ADMIN.INSTANCES
INNER JOIN DB2ADMIN.ENDPOINTS ON DB2ADMIN.INSTANCES.EID = DB2ADMIN.ENDPOINTS.EID)
INNER JOIN DB2ADMIN.METRICS ON DB2ADMIN.ENDPOINTS.EID = DB2ADMIN.METRICS.EID)
INNER JOIN DB2ADMIN.METRICSDATA ON (DB2ADMIN.ENDPOINTS.EID = DB2ADMIN.METRICSDATA.EID)
AND (DB2ADMIN.INSTANCES.IID = DB2ADMIN.METRICSDATA.IID) AND (DB2ADMIN.METRICS.MID = DB2ADMIN.METRICSDATA.MID)
WHERE
(
(
YEARMONTH(DATE(TIMEKEY_DTTM)) = YEARMONTH(CURRENT DATE) -1
)
AND
(
(DB2ADMIN.INSTANCES.INSTANCE_KEY Like 'Processor%') OR (DB2ADMIN.INSTANCES.INSTANCE_KEY Like '%System%')
)
)


Today, I created a VIEW using the above... from the first Select statement down. I then ran an export on that view and it was still running after 6.5hrs. Normally, the entire SQL statement would take less than 1hr to complete. With the Control Center open, I received a memory error msg and the application (my Control Center) was terminated. This forced the export to stop. I'm now trying to IMPORT the IXF File into the LM_CPU table.

I'm beginning to think that one or more of the tables is missing one or more records that is forcing the query to loop. Problem here is I've run the ReOrg on all the data tables which came back successful and don't know how I would prove my thought right/wrong.