VARCHAR ARRAY Tables - Parallel Transporter

VARCHAR ARRAY Tables

Target, error, and work table names can be specified in terms of VARCHAR ARRAY types if specifying more than one table, using the following syntax:

   VARCHAR TargetTable = ['table1', 'table2', ..., 'tableN']

Note: Using the ARRAY keyword in assigning an array value is optional.

You cannot specify more error or work tables than there are target tables defined, but you may specify fewer. If fewer error/work table names are defined than target tables, the Update operator creates a name for the error/work table:

The first error table is ttnameN_ET

The second error table is ttnameN_UV

The work table is ttnameN_WT, where ttnameN is the name of the corresponding target table

Note: Target table names are truncated to the maximum number of characters for table names on the Teradata Database minus 3 characters before the suffixes "_ET", "_UV", or "_WT" are appended to target table names.

For example, if the following is specified when no other error/work table specifications exist,

  VARCHAR TargetTable = ['targtable1', 'targtable2', 'thirdtable']

the Update operator creates the following error tables and work tables:

   targtable1_ET

   targtable1_UV

   targtable1_WT

   targtable2_ET

   targtable2_UV

   targtable2_WT

   thirdtable_ET

   thirdtable_UV

   thirdtable_WT

Note that each set of two error tables and one work table belong to a particular target table; the naming convention preserves the uniqueness of the associated target table.

If you specify the following, the Update operator creates the necessary missing table names:

   VARCHAR TargetTable = ['ttname1','ttname2','ttname3']

   VARCHAR ErrorTable1 = ['error_1']

   VARCHAR ErrorTable2 = ['error_2']

   VARCHAR WorkTable = ['work_1','work_2']

If you specify more error table names or work table names than there are target table names, the Update operator issues an error message and terminates the job.

Dropped Tables

Some tables are created during the execution of a job, and others must be created by the user before the job begins. With the Update operator, target tables must exist on the Teradata Database when the Update operator job is executed. The log table is created automatically when you run the Update job script. Error tables and work tables are created by the Teradata Database.

Error tables are dropped by the Update operator during the cleanup phase if no errors are detected during the acquisition phase or the application phase. The work table is dropped by the Update operator during the cleanup phase if no errors are detected during the acquisition phase or the application phase. The log table is dropped by the Update operator after the job completes successfully.

If a job terminates abnormally, then the log, error, and work tables may not be dropped, depending on where in the job the termination occurs. If you want to restart the job from scratch, you must manually drop these tables by running a BTEQ script.

Caution:

Care must be taken if the target tables are manually dropped using a BTEQ script. If something goes wrong with an Update operator job, and you drop the target table manually and then try to rerun the job, you may lose the original data. This is because all rows are actually placed into worktables and they remain there until the Update operator job reaches the application phase, at which time the rows are copied to the real Target tables.