in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 184 185 186 187 188 189 < 190 > 191 192 193 194 195 196 .. 218 >> Next

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

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 Administration Appendix B: Import/Export Utilities

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:

Job Comment
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.
Previous << 1 .. 184 185 186 187 188 189 < 190 > 191 192 193 194 195 196 .. 218 >> Next