I have requirement to delete oracle database records from message flow, prior to 180 days and only 100k records. Flow will trigger every night 1 am thru cronjob. Cronjob put message to MQ queue where flow listens on. MQ input connects to compute node on where perform DB operations using ODBC connection.
The query i am is working fine few times and after getting below timeout error.
WMB exception:Id Number:BIP2322E:Child DQL exception:HYT00:0:0[IBM][ODBC Oracle Wire Protocol driver]Timeout expired.
Query:
DECLARE deleterecordsQuery CHAR;
SET deleterecordsQuery = 'DELETE from (SELECT Schema.PRSDB.ACCNTEVENT AE WHERE AE.RETRYAFTER IS NULL OR AE.RETRYAFTER < ?) WHERE ROWNUM <= ?';
PASSTHRU(deleterecordsQuery, 'o1-AUG-23 00.00.00.00000000 AM' 10000);
I have this passthru in while loop, up to 10 iterations. each time 10000, so 10000 x 10 = 100000.
I tried Querytimeout value 60 and 120. both are cases its working few times and after started getting timeouts. DB has 4 million records.
How to achieve this deletion without any timeout/performance issues?