Delete step requirement in DB2 LOAD Utility

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

DB2 LOAD utility only allows RESUME and REPLACE options, with REPLACE directing DB2 to replace entire table with loaded rows, wiping out any existing rows. A DELETE process needs to be added that will clean up existing rows being loaded and use the RESUME option on the LOAD utility to avoid wiping out existing data that is not in the extract subset.

Environment

Mainframe and DB2

Cause

That function does not exist in the product.

Resolution

This requires the addition of one or other of 2 new tags in the template XML

[DELETE1] – this deletes from the target schema tables according to the join logic in the extract , so each delete statement has a where clause like

DELETE

FROM TRAVEL_E.HOTEL_BOOKINGS

WHERE  (BOOKING_ID)

IN  (

SELECT L3.BOOKING_ID

from TRAVEL_E.HOTEL_BOOKINGS L3

INNER JOIN TRAVEL_E.ITINERARIES L2 ON L3."ITN_ID" = L2."ID"

INNER JOIN TRAVEL_E.PEOPLE L0 ON L2."PEO_ID" = L0."ID"

AND L0.ID < 500);

These deletes are done in the logical order according to the extract design, i.e. children in the extract tree are deleted before their parents.

It does NOT delete in constraint order.

 

[DELETE2]  - this deletes ALL rows (no where clause) for the extract tables in the target schema according to database constraint order.

It requires the tables are first registered in Datamaker, and during registration , the table order has been calculated.

If any tables in the extract are not registered, or the order has not been calculated, Subset will throw an error and no script is generated.

 

Using either of these tags in a template, should not be included in an ALLEXTRACT or ALL DATA loop, they process all extract and selected data tables automatically.

So

<ALLEXTRACT>

    <TEXT>[DELETE1]</TEXT>

  </ALLEXTRACT>

Is wrong, the correct way they should  be added is

<TEXT>[DELETE1]</TEXT>

Without surrounding ALLEXTRACT or ALLDATA tags

 

An example used to test both is attached.

Note – in both cases, if the target schema is constrained, it is recommended that the constraints are disabled/dropped prior to the delete and re enabled/ re-created afterwards.

This is because if any of the extract tables have FK rules to tables that are not in the extract, the delete will fail for that table.

Additional Information

Please contact Support for the location of the patch for this issue.