时间戳

首页 > 解决方案 > 如何进行频繁提交，以便我的 sql 删除查询不会超时

sql - 如何进行频繁提交，以便我的 sql 删除查询不会超时

问题描述

我有几个更新/删除查询作为我的 Spring Batch 项目中各个任务的一部分，这些查询正在超时，因为它们正在删除大量记录。由于这些只是更新和删除查询，我将它们实现为不是面向块的 Tasklet 步骤。

我怎样才能频繁提交以避免超时。

public class DeleteTask implements Tasklet {

    private static final String DELETE_QUERY = "DELETE FROM MYTABLE WHERE COLUMN = 'TEST'";
    private JdbcTemplate jdbcTemplate;


    public DeleteTask(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


    @Override
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) {
        log.info("Purging IMCO records from MYTABLE table...");
        try {
            jdbcTemplate.update(DELETE_QUERY);
        } catch (Exception e) {
            log.info("Failed to purge  records from MYTABLE table due to:");
            log.info(e.getMessage());
        }
        log.info("Records purged");
        return RepeatStatus.FINISHED;
    }
}

编辑：异常状态：

 StatementCallback; SQL [DELETE FROM MYTABLE WHERE LOB_CD = 'TEST'[jcc][t4][2055][11259][4.14.113] The database manager is not able to accept new requests, has terminated all requests in progress, 
ERRORCODE=-4499, SQLSTATE=58009; nested exception is com.ibm.db2.jcc.am.DisconnectNonTransientException: [jcc][t4][2055][11259][4.14.113] The database manager is not able to accept new requests, has terminated all requests in progress,

标签： sqldb2spring-batch

解决方案

一个简单的 SQL 复合语句可能是最好的方法

BEGIN
    DECLARE DONE BOOLEAN DEFAULT FALSE;  
    --
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN SET DONE = TRUE; END;
    --
    WHILE NOT DONE
    DO
        DELETE FROM (SELECT * FROM MY_TABLE WHERE COL1 = 'A' FETCH FIRST 20000 ROWS ONLY)
        COMMIT;
    END WHILE;
END

推荐阅读