Teradata Table Loader Transformation

Teradata Table Loader

The Teradata Table Loader transformation can be added to a process flow when a Teradata table is used as a target. The Teradata Table Loader also has a unique Load Technique tab that provides different load options depending on whether the source table is in the same Teradata database as the target table.
All loader transformations write to a table that is registered to a library. For more information about registering tables and libraries, see the "Overview of SAS/ACCESS Connections to RDBMS" section in the "Connecting to Common Data Sources" chapter of the SAS Intelligence Platform: Data Administration Guide.
You can specify a variety of table options unique to Teradata tables on the Table Options tab. Other loader options can be set on the Options tab.
The Teradata Table Loader transformation also supports the pushdown feature that enables you to process relational database tables directly on the appropriate relational database server. For more information, see Pushing ELT Job Code Down to a Database.

Teradata Indexes

Teradata indexes differ from other database indexes and require special handling. These differences apply to all uses of the Teradata tables, not just when using the Teradata Table Loader. Specifically, primary indexes cannot be dropped or removed for existing tables. They have to be created when the table is created. You can query for the Teradata Primary Index (PI) and give it a name if it does not have one. You can register this PI using the Register Tables function on the File menu. Once the PI is registered, go to the Index tab on the Teradata table's properties. A check box will show which index is the PI. All Teradata tables have a single primary index that cannot be changed once the table is registered unless it is dropped or recreated.

Teradata Custom Restart

Teradata custom restart allows a step to be restarted where the load stopped rather than being started from the beginning of the step. Teradata custom restart is available when loading from a SAS or other DBMS source that is not on the same server as the Teradata target. Custom restart is not available when Upsert is selected.
When custom restart is supported, the step determines the last good checkpoint, and the row number is saved as the restart number. After the error condition is fixed by an administrator (for example, the database size has been extended), the next run of that job will start loading the target table where it stopped.
The load styles that are available on the Teradata loader for SAS to Teradata loads are:
  • Append (Multiload)
  • Determine load technique at runtime (Multiload/Fastload)
  • Replace (Fastload)
  • Replace (Multiload)
  • Trickle Feed Append (TPUMP)
  • Upsert (Multiload/Upsert)
When restart is used, and when the Determine load technique at runtime option is selected, the same technique that was used in the first run is used during the restart.
Using the Use TPT Utilities option with the Determine load technique at runtime option provides a more seamless restart because Fastload without TPT does not support restart through the access engine, which calls the TPUMP functionality multistatement. Other TPT and load style combinations result in the following manner:
  • When TPT is set, Multiload will generate CHECKPOINT=xxx.
  • When TPT is not set, Multiload will generate ML_CHECKPOINT=xxx.
  • Regardless of the TPT setting, CHECKPOINT=xxx will be generated when Fastload is used.
The Use TPT Utilities check box is located on the Load Technique tab of the Teradata Table Loader's properties window. This check box is available if the source table is not in the same Teradata database as the target table and if one of the load styles for the Teradata loader, except Upsert (Multiload/Upsert), is selected. When this check box is selected, SAS Data Integration Studio uses the Teradata parallel transporter (TPT) API for loading data. You can select additional TPT options in the TPT window located on the Teradata Options tab on the Table Options tab in the Teradata Table Loader properties window.
For more information about restarting jobs, see About Restarting Jobs.