Problem with Fastload and teradata 14.0 with a NULLIF

Tools

Problem with Fastload and teradata 14.0 with a NULLIF

We recently upgraded from 12.0 to 14.0.  Today we ran a script to load a database and it failed.  The error log held a 2679 conversion error. The table is defined as:

CREATE SET TABLE %DATABASE%.OXAC007_%PERIOD% ,NO FALLBACK ,

    NO BEFORE JOURNAL,

    NO AFTER JOURNAL,

    CHECKSUM = DEFAULT

    (

        CRT_ID              CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,

        PROCS_TYPE_CD       CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

        APPL_ID             CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

        ECN                 DECIMAL(15,0),

        CUST_AUTH_MTHD      CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

        CHG_TYP_CD          CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

        ACCT_COID           CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

        ACCT_PTYPE          CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

        ACCT_NBR            CHAR(23) CHARACTER SET LATIN NOT CASESPECIFIC,

        ADDR_USE_CD         CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

        PRMRY_ADDR_IND      CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

        PREV_ADDR_EFCT_DATE DATE FORMAT 'yyyymmdd',

        PREV_ADDR_EXPR_DATE DATE FORMAT 'yyyymmdd',

        ADDR_LAST_MNT_DATE  DATE FORMAT 'yyyymmdd'

    )

    PRIMARY INDEX ( ACCT_NBR );

Here's the guts of the fastload script:

SET RECORD VARTEXT "|";

DEFINE

    CRT_ID              (VARCHAR(7)),

    PROCS_TYPE_CD       (VARCHAR(5)),

    APPL_ID             (VARCHAR(3)),

    ECN                 (VARCHAR(15), NULLIF = 'NULL'),

    CUST_AUTH_MTHD      (VARCHAR(5)),

    CHG_TYP_CD          (VARCHAR(3)),

    ACCT_COID           (VARCHAR(5)),

    ACCT_PTYPE          (VARCHAR(3)),

    ACCT_NBR            (VARCHAR(23)),

    ADDR_USE_CD         (VARCHAR(2)),

    PRMRY_ADDR_IND      (VARCHAR(1)),

    PREV_ADDR_EFCT_DATE (VARCHAR(8)),

    PREV_ADDR_EXPR_DATE (VARCHAR(8)),

    ADDR_LAST_MNT_DATE  (VARCHAR(8)),

    FILE=%IN_FILE%

;

SHOW;

/* INITIAL FASTLOAD IMPORT TASK. */

BEGIN LOADING %DATABASE%.OXAC007_%PERIOD%

    ERRORFILES

    %DATABASE%_E.E1_OXAC007_%PERIOD%,

    %DATABASE%_E.E2_OXAC007_%PERIOD%

    CHECKPOINT 1000000;

/* Insert data records into the target table. */

INSERT INTO %DATABASE%.OXAC007_%PERIOD%

(

    CRT_ID,

    PROCS_TYPE_CD,

    APPL_ID,

    ECN,

    CUST_AUTH_MTHD,

    CHG_TYP_CD,

    ACCT_COID,

    ACCT_PTYPE,

    ACCT_NBR,

    ADDR_USE_CD,

    PRMRY_ADDR_IND,

    PREV_ADDR_EFCT_DATE,

    PREV_ADDR_EXPR_DATE,

    ADDR_LAST_MNT_DATE

)

VALUES

(

    :CRT_ID,

    :PROCS_TYPE_CD,

    :APPL_ID,

    :ECN,

    :CUST_AUTH_MTHD,

    :CHG_TYP_CD,

    :ACCT_COID,

    :ACCT_PTYPE,

    :ACCT_NBR,

    :ADDR_USE_CD,

    :PRMRY_ADDR_IND,

    :PREV_ADDR_EFCT_DATE,

    :PREV_ADDR_EXPR_DATE,

    :ADDR_LAST_MNT_DATE

);

/* End the load process. */

.END LOADING;

/* Log off the server. */

.LOGOFF;

.QUIT;

The %VAR% values are variables substituted for at run time.  These scripts have been running unchanged for the last three years and the input data has not substantially changed.  The error is 2679 on the ECN column on entries that have the string 'NULL' in them.

TIA,

Dan Mercer

2 REPLIES
Teradata Employee

Re: Problem with Fastload and teradata 14.0 with a NULLIF

Hi Dan,

Which version of FastLoad 14.0 are you running. FastLoad has a fix for it starting from 14.00.00.005

Thomas

Re: Problem with Fastload and teradata 14.0 with a NULLIF

Thank you.  We are on 14.00.00.003 - we have another problem with TPT that is fixed at 14.00.00.009 so we will be upgrading.'

Dan Mercer