15.00 - Space Calculation Example - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Space Calculation Example

The following example of how Teradata TPump log table space is derived takes a simple load that consists of the following script:

LOGTABLE CME.TLddNT14H;
.LOGON OPNACC1/CME,CME;
DROP TABLE TBL14TA;
DROP TABLE TBL14TB;
DROP TABLE tlnt14err;
CREATE TABLE TBL14TA,FALLBACK
(ABYTEINT BYTEINT,
ASMALLINT SMALLINT,
AINTEGER  INTEGER,
ADECIMAL  DECIMAL (5,2),
ACHAR     CHAR (5),
ABYTE     BYTE(1),
AFLOAT     FLOAT,
ADATE      DATE)
UNIQUE PRIMARY INDEX (ASMALLINT);
CREATE TABLE TBL14TB,FALLBACK
(ABYTEINT BYTEINT,
ASMALLINT SMALLINT,
AINTEGER  INTEGER,
ADECIMAL  DECIMAL (5,2), 
ACHAR     CHAR (5),
ABYTE     BYTE(1),
AFLOAT     FLOAT,
ADATE      DATE)
UNIQUE PRIMARY INDEX (ASMALLINT);
/*****************************************************************/
/* BEGIN TLOAD WITH ALL THE OPTIONS SPECIFIED SUCH AS ERRLIMIT, **/
/* CHECKPOINT, SESSIONS,TENACITY                                **/
/*****************************************************************/
.BEGIN LOAD ERRLIMIT 5 CHECKPOINT 15 SESSIONS 4 1 TENACITY 2
ERRORTABLE tlnt14err ROBUST ON PACK 20;
.LAYOUT LAY1A;
.FILLER ATEST    *  BYTEINT;
.FIELD ABYTEINT  *  BYTEINT;
.FIELD ASMALLINT *  SMALLINT;
.FIELD AINTEGER  *  INTEGER;
.FIELD ADECIMAL  *  DECIMAL (5,2);
.FIELD ACHAR     *  CHAR (5);
.FIELD ABYTE     *  BYTE(1);
.FIELD AFLOAT    *  FLOAT;
.FIELD ADATE     *  DATE;
.DML LABEL LABELA IGNORE DUPLICATE ROWS IGNORE MISSING ROWS
                  IGNORE EXTRA ROWS;
INSERT INTO TBL14TA VALUES (:ABYTEINT,:ASMALLINT,:AINTEGER,:ADECIMAL,
                         :ACHAR,:ABYTE,:AFLOAT,:ADATE);
.DML LABEL LABELB IGNORE DUPLICATE ROWS IGNORE MISSING ROWS
                  IGNORE EXTRA ROWS;
INSERT INTO TBL14TB VALUES (:ABYTEINT,:ASMALLINT,:AINTEGER,:ADECIMAL,
                         :ACHAR,:ABYTE,:AFLOAT,:ADATE);
.IMPORT INFILE ./tlnt014.dat
               LAYOUT LAY1A FROM 1 FOR 400
               APPLY LABELA WHERE ATEST = 1
               APPLY LABELB WHERE ATEST = 2;
.END LOAD;
.LOGOFF;

From this script the space requirements can be calculated to be:

  • 200 bytes for initialization +
  • 200 bytes * 6 for support environment statements +
  • 200 bytes * 2 for DML SQL statements +
  • 400 bytes for the BEGIN/END load pair +
  • 200 bytes for the IMPORT
  • which is a starting total of 2400 bytes.

    Further, assume that Teradata Database can accept about 32 statements per second and that the load takes a little more than an hour to complete.

    The space for partial and complete checkpoints is calculated with the following steps:

    Calculating the Space for Checkpoints

    1 32 statements per second translates to 1920 statements per minute.

    2 1920 / 20 (the packing factor) = 93 partial checkpoints/minute

    3 Multiply by 15 (15 minute CP frequency) = 1395 partial checkpoint rows maximum.

    4 Each checkpoint row is 117 + (12 * 20) = 357 bytes so 498,015 bytes are in partial checkpoint rows.

    5 Given that the load takes just more than an hour, assume 5 checkpoints are written at 300 bytes each.

    Now the grand total of space in the log table is: 2,400 + 498,015 + 1,500 = 517,980 bytes.