Volatile Table "Disappears" while BTEQ job is running? - Failure 3807

Tools & Utilities

Volatile Table "Disappears" while BTEQ job is running? - Failure 3807

Hi All.

Long time lurker, First time poster here.

I searched before posting this question but couldn't find an answer.

I have a one-step test job using BTEQ, using a VERY long SQL query (several concatenated cards). The job ran for approximately 3.5 hours, until it abended on a failure 3807.

"Failure 3807 Object ''VT_PROCESS_DATES'' does not exist."

Volatile table VT_PROCESS_DATES was created successfully at the start of the job and was used successfully (insert and update) several times while the job was running.

The spelling was correct. There were no LOGOFF, DROP, DELETE prior to failure.

The SQL control cards are current production cards and runs successfully every month.

Can a Volatile Table "disappear" within the same job step WHILE THE JOB IS RUNNING?

Maybe due to insufficient resources allocated to the job? Kinda like a -904 in DB2, only in this case, a volatile table disappeared.

Could it be that my job did not get enough resources to execute properly (since it was in test) and eventually, the volatile table was dropped while the job was still running? (hence, "Volatile")

Re: Volatile Table "Disappears" while BTEQ job is running? - Failure 3807

As far as I know, the only thing that can make a volatile table disappear like that is a database system reset. In that event the session would be recovered, and BTEQ (by default) would resubmit the request that was active at the time of the reset, but both the definition and contents of any volatile tables would be gone. If your application runs frequently and the table definition doesn't vary, consider creating a global temporary table instead.

Re: Volatile Table "Disappears" while BTEQ job is running? - Failure 3807

That's what everyone I work with said too. Nobody's ever seen this happen. But we're pouring over the SYSPRINT in the JES listing and they see the same thing I described above. The Volatile Table was successfully created and used several times before the abend.

Anyway, thank you for your input. I'll have to continue to search through the listing and hopefully find the cause of the abend.