Need advise: DB2 Alter table command rolling back

[rmrama]

Hello,

The production environment I'm supporting has this job that does certain things, and re-populates a table on a daily basis. Let's call this table Table_A.

The job is designed to select and process records from a source table and write into Table_A. We have inserted an "ALTER TABLE" statement into the Before section of the source DB2 stage. The target DB2 stage (for Table_A) is configured to only insert records.

My current problem is that the "ALTER TABLE" command times out because of a deadlock caused by a select query being executed by my users unto Table_A. Because the ALTER statement fails, the subsequent writes into Table_A throws up loads of PK/FK contraint violations and kills the job entirely.

Is there any way for the job to be able to remove all data in Table_A and start re-populating without worrying about user queries? Will MQTs or views help elevate locking problems?

Advance thanks for your time.

[ogmios]

This is a "known" problem with DB2... besides forcing off all users accessing the table (and hoping they don't restart their query) not much can be done about it.

What we do in some places is to start early (window for ETL) and send an automatic e-mail after the ETL finishes for every project.

Ogmios

[rmrama]

Thanks Ogmios.

In fact, the ETL batch in our current environment is designed to complete this job before office hours start. We get hit with locking whenever:

- The source files from the mainframe system coming into the warehouse gets delayed.
- We go into a catchup mode, forcing the batch to be executed 24x7 until we're back on schedule.

Hope to hear other ideas if available. Thanks again.

Regards.