I need some help from the wM Experts as I am stuck on this!!!

I got the following:

-ListFileinDirectory
–LOOP /filenameList (go over every file in dir)
—getFile
—converToValues
—CloseStream
—LOOP /FileRecord/Record1
----MAP (loads Document List from Record1)
—SQLBatchInsert (adapter services inserts data into Oracle)

Somehow I need to create a document list of 5,000 records at a time so the BatchInsert will do SQL insert of only 5,000 records at a time. So for example. If Record1 LOOP will have 10,000 I need to split that into 2. So the loop will load the document list with first 5,000 records then do the insert then go back and load the document list with remaining 5,000 records out of the initial 10,000.

Thanks!!
SMS

I recommend using modulo to do the split.

Create a few variables in your pipeline:
maxPerBatch = 5000
currentCount = 0

When in your loop increment currentCount by one.

Do a branch that checks for currentCount % maxPerBatch == 0
(that is, currentCount modulo maxPerBatch equals 0)

If true branch out and do your batch insert.

Oh, also you need to handle the situation where the last batch is less than 5000 but still needs to be inserted. Another branch or a branch after your loop should do the trick which activates only if the last batch size is > 0.

Make sense?

P.S. Modulo is easy in java: n % m is about all it takes.

Kind of lost here… Could you kindly please walk me step by step in the flow?

S

Sure. I am including a sample SW_Samples.zip that shows a simple example of this type of functionality without using any java services. Instead i added a few extra variables:

currentBatchCount and nextBatchCount to allow me to use the divideints builtin service to simulate modulo.

If you step through the code (put some values into the array - particularly put more than three values in) so that you can see it execute the branch only when currentCount % maxBatchCount == 0 (sic - actually when currentBatchCount != nextBatchCount).

!!!
-greg
SW_Samples.zip (6.12 KB)

Depending on your situation, you may want to look at similar solution but using java Svcs. You can pass your documentList with the maxPerBatch variable and it will output the broken up documentList.

Greg, This is exacly what I needed!!! … Thank You for the Help!!

Yay! So glad it was helpful!
-greg

Greg,

I added two things to the flow where XX are

–MAP
–LOOP
—MAP
—MAP
—pub.list:appendToStringList (create inputLoad string list) XX
—BRANCH
----SEQ %currentBatchCount% != %nextBatchCount
----MAP (Batch achieved do BatchInsertSQL from inputLoad)
----MAP (drop inputLoad) XX
—SEQ $default
----MAP (do nothing no batch yet)
—MAP
–SEQ (do partial batch here)

Everytime it LOOPs a single value is passed in “inputs” and I need to take that value and store in the stringList (inputLoad) so when the Batch is acchived I can take that inputLoad and the the BatchInsertSQL. For some reason when I use the appeneToStringList trans the list is not being build. I am only getting single value in the inputLoad and when the LOOP loops again it passes the new value overwritting the previouse one instead of appending. Would you have any idea why?

Thanks,
SMS

Ok, I got it. I linked items backwords.