Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Multiphase Data Pump (Field Notes)
By Allan Mitchell
Version 2000
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4 | Page 5

For the purpose of demonstration we decided to move the authors table from pubs to the authors2 table in pubs. They have the exact same structure. In this article we will document three types of possible problem in a data pump and show you how the Multiphase data pump handles them. The types of problem we will deal with are:

1. Null Data into a non-nullable column.

2. Incorrect datatype conversions i.e. trying to enter character data into an integer field.

3. Primary Key Violations

Problem 1 Dealing with NULL data.

To test for this we're going to have to make a change to the data in the authors table and a change to the nullability of the city field in the authors2 table.

--Set a city in the source to NULL
UPDATE authors SET city = NULL WHERE city = 'Lawrence'
--Change the destination city column to not allow NULLS (Source allows)
ALTER TABLE authors2 ALTER COLUMN city VARCHAR(20) NOT NULL

If we were to use a datapump with no phases then we'd get the error returned in Figure 1.4.

Figure 1.4 Error from NULL Data.

NullNormal

Let's handle the error using phases and see what happens when we encounter that row

Figure 1.5 Insert Failure.

NullFailHandled

Here we can see that we encountered an On Insert Error and in our function we can then handle it. The step as a whole returns success.

Here are some notes that you may find useful.

  • All Phases are available to you using both FAST LOAD and not.
  • Source Data is not available to you in the Pre Source Phase or in the Batch Complete, Post Source data and Pump Complete phases.
  • If you try to access the data in the Post Source data phase you will fail the step.
  • Data which does not violate the NULL constraint is entered into the destination.
Page 1 | Page 2 | Page 3 | Page 4 | Page 5