the table of WORKFLOW_CONTEXT is use to store metadata at 'each' step of a process (a good process will limit the persistance of step to the essential).
So inserting row in this table is done by the product and should be one of the most intensive table to receive insert.
a few million insert by day in this table could be expected for your database.
the software could using a lot the database.
It is essential to avoid any buffering, it will impact the engine on
Sterling B2B application for all "business process" execution.
this table should not be more impacted by the processing some "big" file.
It work only on metadata, independantly to the size of file (except if your business process load the file in metadata "processdata").
The file are stored in another table (table Trans_data) or on the file system.
your corporation should look at :
document storage option :
you should check if the "document" are stored by the software in the database (DB) or the filesytem (FS).
for big file it is recommended to use if possible the filesystem storage option.
The database will only store a pointer to the filesystem (less stressfull to the database).
-> very important for big file (gigabytes?)
Persistance :
reduce the persistance level of business process.
-> less insert
It should not be related to big file.
lifespan :
if you have lots of process, your database will grow for processing's table (workflow_context, trans_data by example).
you can reduce the size by using reducing the lifespan of the data in these table.
-> no change of log file sync but for the size of your tablespace
check database configuration :
check the configuration of your database against the prerequisite/option of the software :
by example : the official document for SBI 6.1 installed by IIM : https://www.ibm.com/support/knowledgecenter/SS3JSW_6.1.0/installing/installing/integrator/SI_OracleDB.html
-> to look the configuration's modification to be done
best regard------------------------------
Vincent Lahousse
Satisco
------------------------------
Original Message:
Sent: Thu November 12, 2020 04:19 PM
From: William Fogarty
Subject: Inserts into Table WORKFLOW_CONTEXT Causing "log file sync" Waits in Oracle Database
We are in the process of migrating our handling of EDI to the Sterling B2B application. I am the Oracle DBA that created the database that supports the application. We are using the services of a third party to help our EDI analyst configure the settings of the Sterling B2B application. Recent tests using some large files produced significant Oracle "log file sync" waits in the database during execution of thousands of inserts into table SI_USER.WORKFLOW_CONTEXT. (A log file sync wait occurs when the log buffer is flushed to the online redo log.) A SQL commit command forces the flushing of the log buffer to the online redo log. One of Oracle's recommendation to reduce log file sync waits is to commit the inserts in groups instead of individually. I asked our consultant if the inserts into WORKFLOW_CONTEXT are committed individually or in groups. He replied, "The inserts are not grouped by design - it's a checkpoint restart feature of the software to allow recovery points in the event of system failure. There is no changing this." I am asking for your opinion on whether the consultant's reply is correct. I am finding it hard to believe that the Sterling B2B application would force a commit after each insert into WORKFLOW_CONTEXT when it appears that contributes significantly to the length of the log file sync.
Thank you,
Bill
#IBMSterlingB2BIntegratorandIBMSterlingFileGatewayDevelopers
#DataExchange