InfoSphere Optim

InfoSphere Optim

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  The job archive for MySQL source via DB2 federation

    Posted Mon June 23, 2025 09:33 AM

    Hello everyone,
    We are performing data archiving from a MySQL source via DB2 Federated access.
    When we archive the table with a limit of 1,000,000 rows, the job completes successfully in about 10 minutes.
    However, when we archive the entire dataset (3,664,153 rows), the job runs for approximately 1 hour and 17 minutes and ends with the following error:

    [IBM][CLI Driver][DB2/LINUXX8664] SQL20357N
    One or more federated one-phase commit data source sites have failed commit or rollback processing.
    As a result, the transaction outcome might not be consistent across all sites.
    Subsequent SQL statements can be processed.
    Reason code "1".
    SQLSTATE=40003

    According to the report, the job has successfully archived all rows that we counted from the source

    DB2 Version: 11.5.8
    MySQL Version: 5.7.44 (Community Edition)

    Please help me resolve this issue.

    Thanks in advance.



    ------------------------------
    Trieu Huu
    ------------------------------


  • 2.  RE: The job archive for MySQL source via DB2 federation

    Posted Tue August 05, 2025 04:22 PM

    Hi Trieu Huu,

    I work on Optim L3 and was testing your scenario with case TS019421082. I have not yet been able to reproduce the issue that you are seeing. In my sandbox I have a MySQL table defined with over 5 million rows using the DDL that you have provided. In all cases up until this point I have been able to successfully process all of the rows.I would like to continue to work thru the case with you to figure out what may be going on in your system. Please update the case (TS019421082), so that it does not get auto-closed due to inactivity. I will be glad to continue look for a solution for you.

    Best regards,

    Kev



    ------------------------------
    Kevin Hampson
    ------------------------------

    Original Message


  • 3.  RE: The job archive for MySQL source via DB2 federation

    Posted Tue August 05, 2025 04:22 PM

    Hi Trieu Huu,

    I have been working on this case for some time now with you. The case number is TS019421082. In my sandbox I have set up this test and have not seen any issues with Federating a MySQL table with over 4 million rows. I would be glad to continue to test this for you to see if we can reproduce the messages. Please update the case so that it is not auto closed and I will continue to pursue this for you. I am inteested as to where this error maybe coming from.

    Thank you, Kevin



    ------------------------------
    Kevin Hampson
    Optim L3 Support
    ------------------------------

    Original Message


  • 4.  RE: The job archive for MySQL source via DB2 federation

    Posted Tue August 05, 2025 04:22 PM

    Hello Trieu Huu,

    I work for Optim L3 and I have been working on this case. I provided an update in the case that you opened that refers to an APAR that has been opened in the Federated queue. The APAR contains the same error message that you are receiving but there has been no action on it.

    Please look at the previously opened case and look at the current updates.

    Best regards,

    Kevin



    ------------------------------
    Kevin Hampson
    Optim L3 Support
    ------------------------------

    Original Message


Related Content