some records are missing when runstats running

Pushpa Herath

some records are missing when runstats running

Hi

 I am using DB2 LUW v9.8 for the application purpose data purge script is running to delete old records. at the same time for the same table records are getting insert by trigger. as I have monitored, after delete the record on the table in the time of runstats ran some record are missing but all the other time there are no any record missing issue and trigger is also working fine. this script is deleting around 150,000 record per day i'm not sure whether this trigger issue or runstats issue. does any one have a idea about this and what would be the issue and what can i do to resolved this issue

delete query on script as below:

sResp=`db2 +c "DELETE FROM $CtrlTableName WHERE SHCLOG_ID IN (SELECT SHCLOG_ID FROM $CtrlTableName A LEFT JOIN $LogTableName B ON A.SHCLOG_ID = B.CHIP_INDEX WHERE B.CHIP_INDEX IS NULL AND SRCID <> 'EOD' FETCH FIRST $Records ROWS ONLY)"`

runstats script query:

sResp=`db2 "runstats on table $CtrlTableName with distribution on key columns and detailed indexes all allow write access"`

Thanks

 

Colin Clayton

some records are missing when runstats running
(in response to Pushpa Herath)
You'll need to be clearer in your question.
Are you saying records you know to have been inserted are missing from the table, or have been deleted by the purge process by mistake?
Why is runstats relevant ? Are you really saying that the stats are incorrect rather than any data is missing?
How do you know rows are missing, can you identify them ?




Sent from my Samsung Galaxy smartphone.
-------- Original message --------
From: Pushpa Herath <[login to unmask email]>
Date: 02/12/2017 08:20 (GMT+00:00)
To: [login to unmask email]
Subject: [DB2-L] - some records are missing when runstats running


Hi

I am using DB2 LUW v9.8 for the application purpose data purge script is running to delete old records. at the same time for the same table records are getting insert by trigger. as I have monitored, after delete the record on the table in the time of runstats ran some record are missing but all the other time there are no any record missing issue and trigger is also working fine. this script is deleting around 150,000 record per day i'm not sure whether this trigger issue or runstats issue. does any one have a idea about this and what would be the issue and what can i do to resolved this issue

delete query on script as below:

sResp=`db2 +c "DELETE FROM $CtrlTableName WHERE SHCLOG_ID IN (SELECT SHCLOG_ID FROM $CtrlTableName A LEFT JOIN $LogTableName B ON A.SHCLOG_ID = B.CHIP_INDEX WHERE B.CHIP_INDEX IS NULL AND SRCID <> 'EOD' FETCH FIRST $Records ROWS ONLY)"`

runstats script query:

sResp=`db2 "runstats on table $CtrlTableName with distribution on key columns and detailed indexes all allow write access"`

Thanks



-----End Original Message-----

Pushpa Herath

RE: some records are missing when runstats running
(in response to Colin Clayton)

Hi,

thanks for the reply,

not sure, because after the purge script complete then it's run db2runstats by script. some record are missing only in the point of runstats running. exactly same time. we checked with logs

this process is application insert records to one table (table "X" after that trigger is fired data to other table (table "Y")

only this table "Y" missing data.

Thanks