Toad Automation - Job Runs ok as TEST, but not When A Scheduled Task

I’m using Toad For Data Analysists I’m connecting to an iSeries DB2 database.
I have a script that runs 20 SQL’s. If I run the script from the TOAD Automation Window by clicking TEST, it works properly. If I schedule the job, the sixth SQL (it is an INSERT SQL) keeps inserting records forever. I should see about 156,000 records in the file, but the SQL goes past the 156,000 and just keeps running.

There are other insert SQL’s prior to the sixth SQL, and they run ok.

So…it works ok by clicking TEST from the TOAD Automation Window, but runs into problems when it’s run as a scheduled job.

THe SQL causing the issue is attached.

Copy of 01B_INSERT_MC2SMARTA.sql (4.14 KB)

I would suggest getting some debugging info so we can see what is different about the two ways of running it.

First I would go to the automation settings in the script (top activity of each script) and change the logging level to ‘Verbose’. This will write more info to the log.

Then take the SQL statement that is part of your insert, and put it in an execute script activity. Set the result variable to a name of your choice. We want to see how many rows the select statement gets and how this may different when running Test vs scheduled.