TPT operator to load data into a target table with referential integrity and foreign keys

Tools & Utilities
Highlighted
Enthusiast

TPT operator to load data into a target table with referential integrity and foreign keys

Hi everyone,

 

I am performing a .tpt script to load data from a source table to a target table endowed with referential integrity and foreign keys. At first, I used the SQL_Inserter operator to apply the load step, but I am dealing with a source table with hundreds of thousands of rows and this operator seems to be very slow to carry out this process.

 

Then, I am trying the DDL operator but I get the following error:

 

"Operator 'DDL_OPERATOR' is not of type 'Consumer'. Operator is rejected as a target of APPLY operation"

 

My .tpt script shows like this:

 

DEFINE JOB  LOAD_JOB
(
   DEFINE SCHEMA LOAD_SCHEMA
   (
	"FIELD_1"		  VARCHAR(2),
	"FIELD_2"		  VARCHAR(2)
   );
   

   DEFINE OPERATOR DDL_OPERATOR
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR WorkingDatabase = @BBDD,     
      VARCHAR TdpId           = @IP,
      VARCHAR UserName        = @USER,
      VARCHAR UserPassword    = @PASSWD,
      VARCHAR PrivateLogName  = 'log_1',
      VARCHAR ARRAY ErrorList = ['3807','3803']
   );   


   DEFINE OPERATOR EXPORT_OPERATOR
   TYPE EXPORT
   SCHEMA LOAD_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'log_2',
      INTEGER MaxSessions    =  8,
      INTEGER MinSessions    =  2,
      VARCHAR TdpId          = @IP,
      VARCHAR UserName       = @USER,
      VARCHAR UserPassword   = @PASSWD,
      VARCHAR SelectStmt     = 'SELECT ''F1'' || FIELD_1, ''F2'' || FIELD_2 FROM ' || @SOURCE_TABLE || ';'
   );
   
   STEP DELETE
   (
      APPLY
        ('DELETE FROM ' || @TARGET_TABLE || ';')
		  TO OPERATOR (DDL_OPERATOR );
   );

   STEP LOAD
   (
      APPLY
         ('INSERT INTO ' || @TARGET_TABLE || ' ( 	
		                		:FIELD_1,
		                		:FIELD_2);')
         TO OPERATOR ( DDL_OPERATOR [1] )
      SELECT * FROM OPERATOR ( EXPORT_OPERATOR [1] );
   );
);

 

Any idea?

 

PS: I have also used the Stream operator but also seems to be very slow and it does not work appropriately within my .tpt job (perhaps, because this script contains errors?).


Accepted Solutions
Ambassador

Re: TPT operator to load data into a target table with referential integrity and foreign keys

The DDL Operator doesn't support INSERT, only INSERT SELECT.

You can try UPDATE, which supports the extended MulttiLoad protocol.

 

But if both source and target are actually on the same system, why don't you use plain SQL instead of TPT?

1 ACCEPTED SOLUTION
4 REPLIES 4
Ambassador

Re: TPT operator to load data into a target table with referential integrity and foreign keys

The DDL Operator doesn't support INSERT, only INSERT SELECT.

You can try UPDATE, which supports the extended MulttiLoad protocol.

 

But if both source and target are actually on the same system, why don't you use plain SQL instead of TPT?

Teradata Employee

Re: TPT operator to load data into a target table with referential integrity and foreign keys

Why EXPORT the data if it's already in the database? That will be slower.

You can use the DDL operator with an INSERT/SELECT or MERGE statement, just as you do with the DELETE.

 

If you have database-enforced RI/FK constraints, UPDATE will work only if both client and database versions support MLOADX.

Enthusiast

Re: TPT operator to load data into a target table with referential integrity and foreign keys

Thank you for the responses.

 

I have created a table without referential integrity and foreign keys to load data with the Update operator and it works fine. However, when I repeat the process with the same type of table but taking into account referential integrity and foreign keys I get the following error:

 

UPDATE_OPERATOR: TPT10508: RDBMS error 2644: No more room in database DB.

 

On the other hand, I use the Export operator to apply the SelectStmt as above. Is there any other way to carry out this process appropriately within TPT? I need to use TPT because of its use and expansion of the functionality of the traditional Teradata extract and load utilities.

 

I am using the Teradata version 15.10.

Enthusiast

Re: TPT operator to load data into a target table with referential integrity and foreign keys

I have just applied the DDL operator with a single INSERT/SELECT sentence and it works perfectly. This is the final result:

 

DEFINE JOB  LOAD_JOB
(
   DEFINE OPERATOR DDL_OPERATOR
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR WorkingDatabase = @BBDD,     
      VARCHAR TdpId           = @IP,
      VARCHAR UserName        = @USER,
      VARCHAR UserPassword    = @PASSWD,
      VARCHAR PrivateLogName  = 'log_1',
      VARCHAR ARRAY ErrorList = ['3807','3803']
   );   

   STEP DELETE
   (
      APPLY
        ('DELETE FROM ' || @TARGET_TABLE || ';')
		  TO OPERATOR (DDL_OPERATOR );
   );

   STEP LOAD
   (
      APPLY
         ('INSERT INTO ' || @TARGET_TABLE || ' ( 	
		                		FIELD_1,
		                		FIELD_2)
	SELECT ''F1'' || FIELD_1, ''F2'' || FIELD_2 FROM ' || @SOURCE_TABLE || ';')
         TO OPERATOR ( DDL_OPERATOR [1] );
   );
)

 

With this option, I think the issue is completely solved.

 

Thank you very much for your help.