TPUMP is a flexible multi-session Load utility. The only characteristic of Tpump that limits its use is its transaction oriented nature. If the situation allows execution of Fastload or Multiload, these utilities will always outperform TPUMP in a large batch operation when measuring Teradata resource consumption and response time. Consideration of TPUMP in a load scenario should occur before that of Fastload or Multiload. Basically TPUMP has none of the disadvantages of Multiload (table locking, Load Slot Usage, single amp operations), Fastload (empty table only, load slot usage), or BTEQ (no checkpointing or restartability).
Best practices for the development of BTEQ Applications:
- Logtable and error tables are job specific.
- The BTEQ clean up step drops the logtable and prepares the target table.
- The file layout section should completely define the input file. .FILLER should be replaced with .FIELD and any null if logic should be completed in the DML section of the TPUMP.
- If the script is doing complex updates, ROBUST ON is in the "BEGIN LOAD" statement.
- If the script is doing a high volume (due to high pack and/or sessions) of simple inserts and the checkpoint is not 1, ROBUST ON is in the "BEGIN LOAD" statement.
- The PACK factor is lower than the maximum (32K block). You will receive a warning if it is too high.
- SERIALIZE is on for UPSERT scripts.
- If maintaining a table without a unique primary index (a NUPI), you probably need to use the SERIALIZE ON feature to prevent blocking.
- When using SERIALIZE ON, be sure to type ‘KEY’ next to each of the primary index fields in the INFILE_LAYOUT section.
- All dates are formatted.
- All 6 digit dates in non characters fields are converted to char(06) so sliding date rule can be applied properly.
- The # of sessions are appropriate.
