7 Replies Latest reply on Jan 28, 2019 7:49 AM by Rahul Yadav

    MDM 10.2 - ExecuteBatchDelete physically deletes records

    Rahul Yadav Guru

      We would like to use ExecuteBatchDelete request to soft delete record (hub_state_ind=-1). However, when we execute ExecuteBatchDelete API, it physically deletes record instead of soft deleting it. How can we soft delete the record using ExecuteBatchDelete  API?

        • 1. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
          Patrick Piper Seasoned Veteran

          The ExecuteBatchDelete is only for hard deletes, the Delete request is for soft deletion. The best practice would be to create a temp table from the XREF, loop over each record, and execute the request for each.

          • 2. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
            Rahul Yadav Guru

            Hello Patrick,

             

            We have around 1 million records which come once every month. Looping through XREF table and run it in parallel requires custom code. Do you have any sample code run delete request in parallel?

            • 3. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
              Patrick Piper Seasoned Veteran

              I would consult the 2nd chapter of MDM_102HFX_ServicesIntegrationFrameworkGuide_en.pdf, it'll point you to <installdir>\hub\resourcekit\sdk\sifsdk sample project. Your class's main method could look something like this (none of this has been tested, I've just cut and pasted snippets from working code and removed several try/catch blocks).

               

              import java.io.FileInputStream;

              import java.io.InputStream;

              import java.sql.Connection;

              import java.sql.DriverManager;

              import java.sql.ResultSet;

              import java.sql.SQLException;

              import java.sql.Statement;

              import java.util.ArrayList;

              import java.util.Properties;

               

               

              import com.siperian.sif.client.SiperianClient;

              import com.siperian.sif.message.Field;

              import com.siperian.sif.message.Password;

              import com.siperian.sif.message.Record;

              import com.siperian.sif.message.RecordKey;

              import com.siperian.sif.message.mrm.DeleteRequest;

              import com.siperian.sif.message.mrm.DeleteResponse;

               

              public class test {

               

              public static void main(String[] args) throws Exception {

              Properties prop = new Properties();

              InputStream input = new FileInputStream("<siperian-client.properties location>");

              prop.load(input);

              input.close();

              SiperianClient sipClient = SiperianClient.newSiperianClient(prop);

              // The code below should also work, but personally haven't tried it

              // SiperianClient sipClient = SiperianClient.newSiperianClient(new File("properties/siperian_client.properties"));

               

              String dbURL ="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<port>)))(CONNECT_DATA=(SERVICE_NAME=<serviceName>)))";

              Connection connection = DriverManager.getConnection(dbURL, "<DB user>", "<DB password>");

              String sql = "SELECT PKY_SRC_OBJECT, ROWID_SYSTEM FROM <TEMP_TABLE> ";

              Statement stmt = connection.createStatement();

              ResultSet rs = stmt.executeQuery(sql);

               

              while(rs.next()) {

              DeleteRequest request = new DeleteRequest();

               

              Password password = new Password();

              password.setPassword("<Hub password>");

              password.setEncrypted(false);

               

              request.setOrsId("<ORS ID>");

              request.setPassword(password);

              request.setUsername("<Hub user>");

               

              RecordKey recordKey = new RecordKey();

              recordKey.setSourceKey(rs.getString(1));

              recordKey.setSystemName(rs.getString(2));

              ArrayList recordKeys = new ArrayList();

              recordKeys.add(recordKey);

              request.setRecordKeys(recordKeys);

              request.setSiperianObjectUid("<BO>");

              DeleteResponse response = (DeleteResponse) sipClient.process(request);

              }

               

              }

               

              }

              • 4. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
                Rahul Yadav Guru

                Hello Patrick,

                 

                I am aware of this code. I am particularly looking for source code which can be used to achieve parallel execution if we have the high volume of data to process.

                • 5. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
                  Patrick Piper Seasoned Veteran

                  Not sure what you mean by parallel execution, I don't believe I can help then.

                  • 6. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
                    Vijay Samptoor Guru

                    Rahul Tadav,

                     

                    Why don't you

                     

                    1. Create a temporary table and dump all the records that you want to soft delete periodically in that table table.

                    2. Prepare a MUTEX (Mutual Exclusion) code that starts parallelly and takes XREFS from the temp table in a loop and starts executing the Soft deletion.

                    3. Commit the status in batches in the Temporary table once the soft deletion is done & then housekeep the temp table (purge all soft deleted Xrefs from the temp table)

                    • 7. Re: MDM 10.2 - ExecuteBatchDelete physically deletes records
                      Rahul Yadav Guru

                      Hello Everyone,

                       

                      Thank you for your inputs.

                      @Vijay - With the approach you mentioned, it will still not process millions of records with faster speed.

                       

                       

                      We implemented below solution:

                       

                      1. Created input table with ROWID_OBJECT and TABLE_NAME (from which records to be deleted)

                       

                      2. Wrote Java multithreading to delete records in parallel

                       

                      3. Used JDBC and Siperian connections for better performance.