I have sometimes found the Do actions in an Application Engine to be pretty confusing – especially the nature of iterations that happen with each of these actions. I thought it would be informative to share some insights into the working of these actions in an AE program.
Consider the following scenario:
If we have a select statement as below:
Select emplid from PS_JOB where action_dt = sysdate order by emplid.
Suppose executing this query on your DB returns 10 rows. To understand the working of the Do actions, if I embed this query in an AE and print the emplid in a file, how many EMPLIDs will be printed in case of each Do actions?
The Do When in the above diagram can be replaced by Do While and Do Select, while checking for the number of iterations. The Do Actions will contain the SQL select statement as mentioned before and the Peoplecode will write the Emplid value to a File. In case of Do Until, Peoplecode will precede Do Until (as Do Until is the last action).
Coming back to our original question.
How many times will the EMPLID be written in the file in each of these cases??
Do When: Do When is a loop entry criteria. This will always be executed once and only once as long as the SQL statement fetches a row. Thus, with Do Until, one row will be printed. This is pretty straight forward.
Do While: All those of you who thought that the Do While loop will iterate 10 times will need to rethink the working of this loop. Do While will continue executing until at least one row is fetched. The background execution logic of a Do While loop is as follows: Every time, the AE executes the SQL select statement in the Do While Action, if it returns a row it will proceed to further actions, if not the step will be terminated. What is to be understood here is that, in the absence of a loop termination criteria (or in other words logic to discard already selected rows) the Select statement inside a Do While will always fetch the same row. This will mean that a query like the one we have written (select emplid from ps_job) will go into an infinite loop, as there is no termination criterion for this SQL. Thus, this will go into an infinite loop and the same EMPLID (the first Id returned by the query) will be printed in the file. Always keep in mind that a loop termination criterion is absolutely necessary in case of a Do While.
Do Until: Do Until is uncomplicated. It will execute until a row is returned by the query and this ensures that the step is executed atleast once. As the query returns a row, the loop gets terminated after the first iteration itself.
Do Select: Do Select is the most commonly used Do action. But I left Do Select for the last because even this is kind of tricky like Do While. I do not know how many of you have noticed the various types of a Do Select action:
What concerns us out here is the difference between Select/Fetch and Reselect. Will there be any difference if I run the Do Select in these two different modes?
Yes, the working of the Do Select is completely different in these two cases. In case of a Select/Fetch the loop will iterate 10 times, while for Reselect an infinite loop is triggered (exactly similar to a Do While). The reason lies in the difference in working of these two Do Select types. Select/Fetch hits the Db just once, fetches all the values of the Select statement and puts it in a cursor and moves through the cursor during each iteration. This allows a sequential processing which is so integral to row by row processing in an Application Engine. While Reselect queries the DB for each iteration and picks up the first row returned by the select statement and puts it in a cursor. Thus, at any given point of time there will be only one row in the cursor of a Reselect type Do Select action. Moreover, Reselect will be slower than a Select/Fetch due to the DB trips involved in the former. Why does Reselect go into an infinite loop? This happens because for each iteration, the same SQL select statement is fired and the row returned first is buffered into the cursor (unlike a Select/Fetch where the SQL is executed against the DB once and then the AE moves successively through the cursor and terminates once it reaches the end of the cursor). Thus, in case of a Do Select of Reselect type, we will get a result similar to Do While – an infinite loop with the same EMPLID printed.
So what is the difference between a Do Select of Reselect type and a Do While? Remember that Reselect is used when the Restart function of an AE is turned on. A Select/Fetch Do Select does not execute commits for the entire action and thus will not be useful in case of a restart. This deficiency is overcome by the Reselect type, where commits are executed during the Step execution. At hindsight, I would always recommend to use the Restartable function (which is similar to Select/Fetch but with commits turned on) over the Reselect option. Coming to the difference between a Do Select (Reselect) and a Do While boils down the elementary question – why do we have two separate actions if they perform the same function? Do While and Do Select are fundamentally different, the first is similar to a FOR loop (executes for a definite number of times), while the latter is similar to a WHILE loop (executes till a condition is true). That is exactly the reason why a Do While comes before a Do Select.
HAPPY LEARNING :)
