Waiting for SQL script to finish

[Martina]

This should be really simple, yet I am struggling with this:
During a sequence, I want to run an SQL Script with statements to perform runstats on various tables. This is no problem, the SQL script containing these statements can be called from an Execute command stage or as a After-job subroutine. The problem is that I want DS to wait untill all statements in this script have been executed before going to the next stage in the sequence.

I have already experimented with batch files and copying the logfile to another name and use a wait for file stage for that last file to appear, but it looks like DS is satisfied after firing off the commands in the batch job. It does not wait before going on.

Did anyone have this same problem and worked out a solution to this?

Thanks in advance

Martina.

[dzdiver]

Are you using unconditional triggers?

The conditional triggers can be used to determine if an activity succeeds (or fails etc) which implies waiting for a result.

[Martina]

I am using the conditional trigger, the wait for file stage is set to wait for a file to appear before continuing. However, what I want to accomplish is that this file appears only if a script with statements has finished running. What happens now is this:

In the command stage I have this command:

D:\Ascential\DataStage\Runstats\Main_Interface.cmd

Main_Interface.cmd has the following contents:
call D:\Ascential\DataStage\Runstats\Runstats_interface.bat
call D:\Ascential\DataStage\Runstats\runstats_interface_Rename.bat
exit

Runstats_Interface.bat has the following contents:
db2cmd /c db2 -tvf d:\ascential\datastage\runstats\interface_tables.sql -z d:\ascential\datastage\runstats\interface_tables.log

The second line .bat file has this content:
Copy d:\ascential\datastage\runstats\interface_tables.log d:\ascential\datastage\runstats\succes_interface_tables.log

In the wait for file activity stage, it is specified that succes_interface_tables.log should appear.

Now my problem:
Immediately after launching the .cmd, the first .bat file is started and then immediately the second. The programm doesn't wait untill all statements in the file interface_tables.sql have been executed, however this is exactly what I am trying to do.

Sorry for the lenghty response but it is a bit hard to explain otherwise.

[ogmios]

Then it's not a problem in DataStage but in "DOS"... what about trying to executing in the style as:

Code: Select all

    D:\Ascential\DataStage\Runstats\Runstats_interface.bat  & D:\Ascential\DataStage\Runstats\runstats_interface_Rename.bat

The & should make for unconditional sequential execution (1 & not 2 &&) and both commands are on a single line, if I recall correctly from my early DOS days.

Also you could start the 2 commands using the start syntax (with option wait):

Code: Select all

   start /wait D:\Ascential\DataStage\Runstats\Runstats_interface.bat  
   start /wait D:\Ascential\DataStage\Runstats\runstats_interface_Rename.bat

Ogmios

[Martina]

Thanks for your help. I have solved this issue and it was indeed simple: In the Execute Command stage I call a batchfile. This batchfile contains 1 line:
db2cmd /c /w /i db2 -tvf d:\ascential\datastage\runstats\interface_tables.sql

Because of the use of /c /w /i it now waits before the statements in the script have finished before going on to the next stage.