Hi all.
I am trying to setup the workflow below. The purpose of this workflow is to select a subset of data from a very large DB2 table and incrementally append it to an output Teradata table. The source table is 250 million rows so I cannot run this all at once due to memory/hard disk/timeout issues. The top line of the workflow selects a start date and will pass it as a variable to the Database Reader’s SQL code’s Where statement like this: Where E139_WK_END_DT = ‘$${SE139_WK_END_DT}$$’. This recordset will then be passed to the Database writer and appended to the output table in Teradata. The intention is that the Loop End will restart and increment the date by 7 days and run the database reader and writer nodes again, then repeat until the date is equal to today (this would probably repeat about 100 times to append all 250 million rows). I have numerous similar tables I want to perform this same action on so, getting this to work would help a lot.
The workflow works just fine except for the looping. Knime gives me this error when i try to run the Variable Loop End node:
ERROR Variable Loop End 2:29 Execute failed: Loop End is not connected to matching/corresponding Loop Start node. You are trying to create an infinite loop!
I can see that I do not have a black arrow line from the Loop Start to through the reader and writer nodes, but I cannot figure out how to do so since these nodes do not allow that. I am somewhat new to Knime, so I don’t have a lot of experience with the various Knime nodes outside of standard ETL type data transfers.
The “Generic Loop Start” is not what you think it is as the name is quiet misleading. Read the nodes description:
In combination with a “Variable Condition Loop (End)” node, you can build a loop while body is executed until a certain condition on one of the flow variables is met. The condition must be set in the loop’s end node.
What you actually want is the “Chunk Loop Start” Node.
Thanks for your suggestions @beginner
I changed my end loop node to the ‘Variable Condition Loop End’ like you mentioned and realized that I could use its input node to connect to my Loop Start node’s flow path (This seems kind of obvious is retrospect). This stopped Knime from giving me the infinite loop error. It also seems that I needed to connect my ‘Table Row to Variable’ to the end loop node as opposed to the connecting from the database writer. I did change the workflow to a Chunk Loop Start as you suggested, but i moved the workflow around a few times before I got it to work and ended up putting back the Generic one. I will do some research on the Chunk start vs Generic.
It is working now and on it’s 5th or 6th loop!!!
Thanks a lot!
Glad to see you found a way. Anyways have two suggestions:
If possible use new Database Integration nodes. Guess you are on an older version of KNIME where new DB nodes are still in Labs but if you upgrade your KNIME you’ll see they came out of Labs and are production ready.
