Managed File Transfer

Sterling Managed File Transfer

Come for answers, stay for best practices. All we're missing is you.


#Sterling
#Supplychain
 View Only

  • 1.  Concurrency Issues - DATABASE B2Bi

    Posted 10/02/20 08:09 AM
    Hi all,

    I create a BP that received a request HTTP and get parameter from request and execute a simple INNER JOIN between MBX_MESSAGE and MBX_PATH in B2Bi database, but sometimes this is very slow, analysing the database, the B2Bi execute many query like similar example bellow, causing concurrency, someone already see something like this? how solve this?



    SELECT D.NAME AS WFD_NAME ,

    SESS.SESSION_ID AS SESSION_ID ,

    SESS.SESSION_ARCHIVE_ID ,

    SESS.CHILD_SESSIONID ,

    SESS.CON_START_TIME ,

    SESS.CON_END_TIME ,

    SESS.DIS_START_TIME ,

    SESS.DIS_END_TIME ,

    SESS.IS_LOCAL_INIT ,

    SESS.ADAPTER_NAME ,

    SESS.ADAPTER_TYPE ,

    SESS.PS_INSTANCE ,

    SESS.CON_IS_SUCCESS ,

    SESS.PROTOCOL ,

    SESS.SECURE_MODE ,

    SESS.ENDPOINT1 ,

    SESS.ENDPORT1 ,

    SESS.ENDPOINT2 ,

    SESS.ENDPORT2 ,

    SESS.DIS_IS_SUCCESS ,

    SESS.STATUS_CODE ,

    SESS.ERROR_MSG ,

    SESS.START_WFID ,

    SESS.START_WFSTEP ,

    SESS.END_WFID ,

    SESS.END_WFSTEP ,

    SESS.PRINCIPAL ,

    XFER.XFER_ID ,

    XFER.SESSION_ARCHIVE_ID AS XFER_SESSION_ARCHIVE_ID,

    XFER.IS_PUT ,

    XFER.DOC_ID ,

    XFER.IS_SUCCESS ,

    XFER.FILE_SIZE ,

    XFER.START_TIME ,

    XFER.END_TIME ,

    XFER.MBOX_PATH ,

    XFER.MESSAGE_ID ,

    XFER.MESSAGE_NAME ,

    XFER.REMOTE_FILENAME ,

    XFER.DOC_NAME ,

    XFER.STATUS_CODE AS XFER_STATUS_CODE ,

    XFER.ERROR_MSG AS XFER_ERROR_MSG ,

    XFER.IS_BIN_XFER ,

    XFER.KBYTES_XFER ,

    XFER.WFID ,

    XFER.WFSTEP ,

    XFER.IS_SECURE

    FROM ACT_SESSION SESS

    LEFT OUTER JOIN ACT_XFER XFER

    ON SESS.SESSION_ID = XFER.SESSION_ID

    LEFT OUTER JOIN WORKFLOW_CONTEXT WFC

    ON SESS.START_WFID = WFC.WORKFLOW_ID

    AND WFC.ACTIVITYINFO_ID = 0

    LEFT OUTER JOIN WFD D

    ON D.WFD_ID = WFC.WFD_ID

    WHERE SESS.CON_END_TIME >= TO_DATE(:1 , 'mm/dd/yyyy hh24:mi:ss')

    AND SESS.DIS_END_TIME IS NULL

    AND SESS.PROTOCOL IN ('SFTP','MailboxService','HTTP','FTP','Connect:Direct')

    AND SESS.ADAPTER_NAME NOT IN ('MESAHttpServerAdapter')

    UNION

    SELECT D.NAME AS WFD_NAME ,

    SESS.SESSION_ID AS SESSION_ID ,

    SESS.SESSION_ARCHIVE_ID ,

    SESS.CHILD_SESSIONID ,

    SESS.CON_START_TIME ,

    SESS.CON_END_TIME ,

    SESS.DIS_START_TIME ,

    SESS.DIS_END_TIME ,

    SESS.IS_LOCAL_INIT ,

    SESS.ADAPTER_NAME ,

    SESS.ADAPTER_TYPE ,

    SESS.PS_INSTANCE ,

    SESS.CON_IS_SUCCESS ,

    SESS.PROTOCOL ,

    SESS.SECURE_MODE ,

    SESS.ENDPOINT1 ,

    SESS.ENDPORT1 ,

    SESS.ENDPOINT2 ,

    SESS.ENDPORT2 ,

    SESS.DIS_IS_SUCCESS ,

    SESS.STATUS_CODE ,

    SESS.ERROR_MSG ,

    SESS.START_WFID ,

    SESS.START_WFSTEP ,

    SESS.END_WFID ,

    SESS.END_WFSTEP ,

    SESS.PRINCIPAL ,

    XFER.XFER_ID ,

    XFER.SESSION_ARCHIVE_ID AS XFER_SESSION_ARCHIVE_ID,

    XFER.IS_PUT ,

    XFER.DOC_ID ,

    XFER.IS_SUCCESS ,

    XFER.FILE_SIZE ,

    XFER.START_TIME ,

    XFER.END_TIME ,

    XFER.MBOX_PATH ,

    XFER.MESSAGE_ID ,

    XFER.MESSAGE_NAME ,

    XFER.REMOTE_FILENAME ,

    XFER.DOC_NAME ,

    XFER.STATUS_CODE AS XFER_STATUS_CODE ,

    XFER.ERROR_MSG AS XFER_ERROR_MSG ,

    XFER.IS_BIN_XFER ,

    XFER.KBYTES_XFER ,

    XFER.WFID ,

    XFER.WFSTEP ,

    XFER.IS_SECURE

    FROM ACT_XFER XFER,

    ACT_SESSION SESS

    LEFT OUTER JOIN WORKFLOW_CONTEXT WFC

    ON SESS.START_WFID = WFC.WORKFLOW_ID

    AND WFC.ACTIVITYINFO_ID = 0

    LEFT OUTER JOIN WFD D

    ON D.WFD_ID = WFC.WFD_ID

    WHERE XFER.START_TIME >= TO_DATE(:2 , 'mm/dd/yyyy hh24:mi:ss')

    AND XFER.END_TIME IS NULL

    AND XFER.SESSION_ID = SESS.SESSION_ID

    AND SESS.PROTOCOL IN ('SFTP','MailboxService','HTTP','FTP','Connect:Direct')

    AND SESS.ADAPTER_NAME NOT IN ('MESAHttpServerAdapter')

    UNION

    SELECT D.NAME AS WFD_NAME ,

    SESS.SESSION_ID AS SESSION_ID ,

    SESS.SESSION_ARCHIVE_ID ,

    SESS.CHILD_SESSIONID ,

    SESS.CON_START_TIME ,

    SESS.CON_END_TIME ,

    SESS.DIS_START_TIME ,

    SESS.DIS_END_TIME ,

    SESS.IS_LOCAL_INIT ,

    SESS.ADAPTER_NAME ,

    SESS.ADAPTER_TYPE ,

    SESS.PS_INSTANCE ,

    SESS.CON_IS_SUCCESS ,

    SESS.PROTOCOL ,

    SESS.SECURE_MODE ,

    SESS.ENDPOINT1 ,

    SESS.ENDPORT1 ,

    SESS.ENDPOINT2 ,

    SESS.ENDPORT2 ,

    SESS.DIS_IS_SUCCESS ,

    SESS.STATUS_CODE ,

    SESS.ERROR_MSG ,

    SESS.START_WFID ,

    SESS.START_WFSTEP ,

    SESS.END_WFID ,

    SESS.END_WFSTEP ,

    SESS.PRINCIPAL ,

    XFER.XFER_ID ,

    XFER.SESSION_ARCHIVE_ID AS XFER_SESSION_ARCHIVE_ID,

    XFER.IS_PUT ,

    XFER.DOC_ID ,

    XFER.IS_SUCCESS ,

    XFER.FILE_SIZE ,

    XFER.START_TIME ,

    XFER.END_TIME ,

    XFER.MBOX_PATH ,

    XFER.MESSAGE_ID ,

    XFER.MESSAGE_NAME ,

    XFER.REMOTE_FILENAME ,

    XFER.DOC_NAME ,

    XFER.STATUS_CODE AS XFER_STATUS_CODE ,

    XFER.ERROR_MSG AS XFER_ERROR_MSG ,

    XFER.IS_BIN_XFER ,

    XFER.KBYTES_XFER ,

    XFER.WFID ,

    XFER.WFSTEP ,

    XFER.IS_SECURE

    FROM ACT_SESSION SESS

    LEFT OUTER JOIN ACT_XFER XFER

    ON SESS.SESSION_ID = XFER.SESSION_ID

    LEFT OUTER JOIN WORKFLOW_CONTEXT WFC

    ON SESS.START_WFID = WFC.WORKFLOW_ID

    AND WFC.ACTIVITYINFO_ID = 0

    LEFT OUTER JOIN WFD D

    ON D.WFD_ID = WFC.WFD_ID

    WHERE SESS.DIS_END_TIME >= TO_DATE(:3 , 'mm/dd/yyyy hh24:mi:ss')

    AND SESS.PROTOCOL IN ('SFTP','MailboxService','HTTP','FTP','Connect:Direct')

    AND SESS.ADAPTER_NAME NOT IN ('MESAHttpServerAdapter')

    UNION

    SELECT D.NAME AS WFD_NAME ,

    SESS.SESSION_ID AS SESSION_ID ,

    SESS.SESSION_ARCHIVE_ID ,

    SESS.CHILD_SESSIONID ,

    SESS.CON_START_TIME ,

    SESS.CON_END_TIME ,

    SESS.DIS_START_TIME ,

    SESS.DIS_END_TIME ,

    SESS.IS_LOCAL_INIT ,

    SESS.ADAPTER_NAME ,

    SESS.ADAPTER_TYPE ,

    SESS.PS_INSTANCE ,

    SESS.CON_IS_SUCCESS ,

    SESS.PROTOCOL ,

    SESS.SECURE_MODE ,

    SESS.ENDPOINT1 ,

    SESS.ENDPORT1 ,

    SESS.ENDPOINT2 ,

    SESS.ENDPORT2 ,

    SESS.DIS_IS_SUCCESS ,

    SESS.STATUS_CODE ,

    SESS.ERROR_MSG ,

    SESS.START_WFID ,

    SESS.START_WFSTEP ,

    SESS.END_WFID ,

    SESS.END_WFSTEP ,

    SESS.PRINCIPAL ,

    XFER.XFER_ID ,

    XFER.SESSION_ARCHIVE_ID AS XFER_SESSION_ARCHIVE_ID,

    XFER.IS_PUT ,

    XFER.DOC_ID ,

    XFER.IS_SUCCESS ,

    XFER.FILE_SIZE ,

    XFER.START_TIME ,

    XFER.END_TIME ,

    XFER.MBOX_PATH ,

    XFER.MESSAGE_ID ,

    XFER.MESSAGE_NAME ,

    XFER.REMOTE_FILENAME ,

    XFER.DOC_NAME ,

    XFER.STATUS_CODE AS XFER_STATUS_CODE ,

    XFER.ERROR_MSG AS XFER_ERROR_MSG ,

    XFER.IS_BIN_XFER ,

    XFER.KBYTES_XFER ,

    XFER.WFID ,

    XFER.WFSTEP ,

    XFER.IS_SECURE

    FROM ACT_XFER XFER,

    ACT_SESSION SESS

    LEFT OUTER JOIN WORKFLOW_CONTEXT WFC

    ON SESS.START_WFID = WFC.WORKFLOW_ID

    AND WFC.ACTIVITYINFO_ID = 0

    LEFT OUTER JOIN WFD D

    ON D.WFD_ID = WFC.WFD_ID

    WHERE XFER.END_TIME >= TO_DATE(:4 , 'mm/dd/yyyy hh24:mi:ss')

    AND XFER.SESSION_ID = SESS.SESSION_ID

    AND SESS.PROTOCOL IN ('SFTP','MailboxService','HTTP','FTP','Connect:Direct')

    AND SESS.ADAPTER_NAME NOT IN ('MESAHttpServerAdapter')

    ORDER BY SESSION_ID,

    XFER_ID

    )

    WHERE ROWNUM <5000

    ------------------------------
    Douglas Rezende
    ------------------------------

    #IBMSterlingB2BIntegratorandIBMSterlingFileGatewayDevelopers
    #DataExchange


  • 2.  RE: Concurrency Issues - DATABASE B2Bi

    Posted 10/06/20 01:22 PM
    My first question is what are you trying to achieve with this query? The overhead of collecting 47 elements is extremely heavy. There are various services that might provide what you need for minimal resources consumption. Hitting the WFC table is never a good thing to do, as it is the bottleneck for workflow engine.

    ------------------------------
    Mark Murnighan
    Solution Architect
    ------------------------------



Related Content