E9.2 Database connection issues processing large dataset in orchestrator

[craig005]

I have created an orchestration attempting to release credit holds on orders if the order had previously been released from a hold. I am getting errors around database connections and I'm curious what experience anyone else may have.

Generally, here's what happens:
- Data Request to get a list of orders on hold (normally would be 100 or less, could be up to 1000 records)
- For each record
- Data request to fetch the first date it was ever released from a hold
- If record found
- Form request to release the hold

What I'm seeing is that this will fail trying to execute data requests, based on running out of database connections. It seems directly tied to the JDBj Maximum Connections setting on the AIS server (basically if this is set to 50 it fails after 50 records are processed; increase it to 150 and we can process 150 records).

I know a key use of orchestrations is to process datasets, so it seems odd to me that we'd get hung up on individual database connections when processing a relatively small number of records. What experience do you have with this? Is there something I can do to get the orchestration to better utilize the database connections?

Thanks in advance for any insight you have.

 

[DaveWagoner]

This is a weird one, can you take a screenshot of the orchestration for me? I've done data request loops of thousands of recs (but what might be unique to your situation is that you're doing a DR in the for each, but i'd hope that each DR within the loop would "release" each time). My understanding is that the data connector "downloads" the data one time rather than pages through (taking up a cursor or similar).

I would like to see what your orch looks like?

 

[craig005]

Thanks for your response. Screenshot here...

I default in a list of Company/Order Type/Hold Codes to the main orchestration.

SO Sales Orders on Hold data request gets a list of all orders on hold for a given Co/Type/Code.
SO Credit Hold Prior Release data request finds if the order had previously been released from the same hold.
SO_Get_Order_Hold_Release_Password form request looks up the password for the Hold Code/MCU combo for the order (this previously was a data request but I switched it to a form request as I've been trying to work around the issue -- this change didn't help)
SO Release Order Hold is a form request that actually releases the hold on the order.

 

[DaveWagoner]

The outer orch, what is that iterating over? SO_BATCH_HOLD_RE...

What if you remove that iteration and hardcode the keys for the first data request?

 

[craig005]

I have an orchestration input to the main orchestration that is an array of Company/OrderType/HoldCode sets. This allows me to control which companies release which holds automatically.

From a testing standpoint, I have right now in PY over 1000 C1 holds for SO orders in company 00001. So it is continuing to fail on the very first iteration of the orchestration.

As a test, I ran that SO_BATCH_HOLD... orchestration manually with just that one company/hold/type combination. It failed at 150 just like it would on the first iteration call from the main orchestration.

 

[DaveWagoner]

Yeah It really does sound like it's more a cnc/server/db driver config issue than an orch issue. wish we could page the cnc's into this thread