Usage Notes for Loaders

See Usage Notes for Table Loader and Cloudera Impala Tables.

In jobs that include the SCD Type 2 Loader and a Sybase target, or a Netezza target, SQL pass-through is not supported, even when you specify Yes for the Use SQL pass-through option.

In jobs that include the SCD Type 2 Loader and a Sybase target, the job runs only if you include the value UNDOPOLICY=NONE in the SQL Options field.

When the SCD Type 2 Loader Transformation is used to load Netezza or Neoview tables, the Autocommit option should be turned off in the DBMS library. To turn off this option, display the properties window for the library, select OptionsAdvanced Options, and then select the Input/Output tab. Set the Whether to COMMIT immediately after a transaction field to No.

A job using the SCD Type 1 Loader or SCD Type 2 Loader transformations cannot use Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT as valid target tables because the UPDATE statement is not supported.

You can set the MINMAXVARLIST option for either an SPD Server Loader transformation or an SPD Server table. However, the scope of the option depends on the type of object that you select.

When you set the MINMAXVARLIST option for an SPD Server Loader transformation, the option is applied to any table that is loaded with the transformation. To set the option on a loader, open the property window for the SPD Server Loader transformation. Then, click the Options tab and enter minmaxvarlist=xxx in the Advanced Data Table Options field (where xxx denotes the range of variables that you want to specify).

When you set the MINMAXVARLIST option for an SPD Server table, the option is applied to the table in any job when the transformation that uses the table supports table options. To set the option on a table, open the properties window for the SPD Server Table. Then, click the Physical Storage tab and click Table Options. You can then enter minmaxvarlist=xxx in the Additional Options field (where xxx denotes the range of variables that you want to specify).

When you use the Table Loader or SCD Type 2 loader transformations with a Vertica DBMS table target, you must set the AUTOCOMMIT option to No. However, you must set the AUTOCOMMIT option to Yes if the Load Style field on the Load Technique tab is set to Update/Insert and the Matching Rows field is set to either Modify by Column(s) or Skip Matching Rows.

If you receive the error message “ERROR: One or more values have been changed in the record to be updated”, then you must set the AUTOCOMMIT option to No.

PI System software gathers data from multiple systems and serves as a translator that synchronizes data center operations. PI System software does not support SQL INSERT or UPDATE operations, so you cannot connect PI tables as a target of the Update transformation. This is also true for Hadoop (Hive), HAWQ, Impala, LASR, or SASHDAT tables. Accordingly, you cannot specify the UPDATE/INSERT load technique in the Table Loader transformation when the target table is in PI System format.

PI System does not support column descriptions when the target table is in the PI System format. If you add labels in the Load Technique tab for the PI tables, a warning indicates that the variable label is not saved.

A PI table cannot be a target for the SCD Type 2 Loader transformation.

When exporting the PI table, if the Enable case-sensitive DBMS object names option is not checked but the Include/replace physical table option is checked, an error message states that the “Physical file does not exist.” The Enable case-sensitive DBMS object names option must be checked because the PI system is case-sensitive.

PI system does not support column descriptions when the target table is in the PI System format. To avoid warnings in the log, remove any column descriptions defined in the Column tab of the PI target table.

PI tables must have a valid SAS table name of less than 32 bytes to work with Data Integration Studio.

HAWQ is “Hadoop With Query” and provides an SQL interface to store data natively in the Hadoop Distributed File System (HDFS). Currently, the HAWQ architecture allows users to write data once to the HDFS and read it many times. However, you cannot edit the file once it is in the HDFS. Since the DELETE and UPDATE commands edit the file, HAWQ does not support SQL updates or deletes. Specifically, you cannot connect HAWQ tables as a target of the Update transformation. This is also true for Hadoop (Hive), Impala, LASR, PI, or SASHDAT tables.

Specifying the UPDATE/INSERT load technique in the Table Loader transformation results in an error when using a HAWQ target table.