Download (direct link):
operations that do not require statement SET QUIET ON.
accounting to be returned to your output
large exports over a network update the resp_buf_len entry in the /usr/lib/clispb.dat file from 8k to 31k.
Restarts and Aborts on BTEQ Jobs with Identity Column
If a restart occurs during a BTEQ import, BTEQ will re-submit the last uncompleted row insert after the system recovers from the restart. Identity column numbering will continue from there. It is possible, however, for a restart to cause duplicates because BTEQ may do a re-insert even if the previous insert has completed. Duplicates will not be detected if the target table is not defined with a UPI.
If a session abort occurs during a channel-attached BTEQ import, the last uncompleted row insert will not be re-submitted and associated data may be lost. Associated data may also be lost if a network-attached BTEQ import session is aborted and there is no other session through which to re-submit the insert.
In both cases, manually restarting the import can result in duplicate rows if rows newly inserted before the session abort are not deleted unless the target table is defined with a UPI.
FastLoad allows you to load data from the host to your Teradata System. It allows fast loading of singe un-populated (empty) tables. For more information on this utility, see Teradata FastLoad Reference.
FastLoad loads large amounts of data into an empty table on the Teradata RDBMS. Row size and the number of columns, more than any other factors, affects FastLoad performance.
Because it is not usually an option to reduce the rows and columns, consider the following when you load large tables:
B - 2 Teradata RDBMS Database Administration
Appendix B: Import/Export Utilities
IF you are loading . . . THEN . . .
a large table without fallback • Initially, set session to the number of AMPs in your system. Then, experiment by reducing the number of sessions. • Avoid checkpointing too frequently. • Avoid NULLIF clauses, VAR fields, and/or indicator bits whenever possible. • Run concurrent FastLoad jobs instead of just one (default MAX = 5 can be changed in DBSCONTROL to a maximum of 15).
a large table with fallback 1 Observe the suggestions for loading the large table without fallback. 2 Create the table without fallback protection. 3 Load the table. 4 Use BTEQ to alter the table to have fallback protection.
FastExport quickly exports data from Teradata RDBMS databases to the client platform. This utility can format and export very large amounts of data very quickly. (For more information on this utility, see Teradata FastExport Reference.)
To improve performance:
• Do not use too many sessions.
• Avoid using any option that causes the evaluation of individual fields within a layout, including:
• NULLIF clauses
• Concatenated fields
• APPLY WHERE conditions
MultiLoad allows you to upload data from the client to your Teradata RDBMS. It operates on multiple tables simultaneously and can also insert, update, and delete data. (For more information, see Teradata MultiLoad Reference.)
To improve MultiLoad performance:
• Target data tables without triggers or join indexes.
• Minimize concatenation and redefinition of input data.
• Restrict the number of NUSIs.
• Make the PI of each data table unique or nearly unique.
• Minimize the use of error tables.
• Do not checkpoint too often.
• Avoid using too many sessions.
B - 2
Teradata RDBMS Database AdministrationAppendix B: Import/Export Utilities
Archive and Recovery (ARC)
The ARC utility is used to:
• Dump (archive) tables, databases, and/or certain dictionary tables
• Reload data onto the same or a different Teradata RDBMS
• Checkpoint, rollforward and rollback permanent journals (data recovery)
Although you can perform ARC functions with other utilities, ARC is often a convenient way to unload and reload databases and tables on the same Teradata RDBMS, or to move your databases from one Teradata RDBMS version or platform to another. (For more detail, see Chapter 8: "Archiving, Restoring and Recovering Data".)
To improve ARC performance, you can:
• Run multiple cluster jobs in parallel for large tables and databases.
• Use RESTORE rather than COPY. Use COPY only when RESTORE does not perform the required function, such as restoring to a different Teradata configuration. (Compare the RESTORE and COPY command in Teradata Archive/Recovery Utility Reference.) |
• Use the appropriate number of sessions for the job:
All-AMPs archive Specify no more than one session/AMP.
Cluster or specific archive Specified sessions should be an even multiple of the total AMPs, for example, 4 AMPs, use 4, 8, 12, and so forth.
Restore/copy This job uses all of the sessions specified in the SESSIONS parameter.
Note: When working with RI constraints or PPIs, RESTORE invalidates table headers. For an explanation of how to find the error rows and validate the state of the table, see "Solving PPI and RI Validation Errors" on page 12-27.