VARDATE value ignoring TextDelimiter

Tools & Utilities

VARDATE value ignoring TextDelimiter

Good morning!  I looked through the forums and the web and didn't find anyone else with this problem so here we go.  I'm running tbuild 14 to a 13.10 database.

1. Data file:

cat bub.txt

REPORT_DATE|REPORT_TYPE|CALC_HBW_LBW

01-JUN-2011 00:00:00|M|LBW

01-JUN-2011 00:00:00|D|LBW

01-JUN-2011 00:00:00|N|LBW

2. Table DDL:

CREATE SET TABLE xxxxxxx.BUB ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      REPORT_DATE TIMESTAMP(0),

      REPORT_TYPE VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      CALC_HBW_LBW VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( REPORT_DATE ); 

3. Load producer script:

DEFINE JOB BUB

(

        DEFINE SCHEMA BUB

        (

                REPORT_DATE VARDATE(20) FORMATIN('DD-MMM-YYYYBHH:MI:SS') FORMATOUT('YYYY-MM-DDBHH:MI:SS'),

                REPORT_TYPE VARCHAR(100),

                CALC_HBW_LBW VARCHAR(100)

        );

        DEFINE OPERATOR BUB_GETFILE

        TYPE DATACONNECTOR PRODUCER

        SCHEMA BUB

        ATTRIBUTES

        (

                PrivateLogName = 'BUB.log',

                DirectoryPath = '/oraopr/teradata',

                FileName = 'bub.txt',

                Format = 'Delimited',

                OpenMode = 'Read',

                SkipRows = 1,

                TrimColumns = 'BOTH',

                TruncateColumnData = 'YesWithoutLog',

                TextDelimiter = '|'

        );

        DEFINE OPERATOR BUB_LOADFILE

        TYPE LOAD

        SCHEMA *

        ATTRIBUTES

        (

                TdpId = 'xxxxx',

                UserName = 'xxxxx',

                UserPassword = 'xxxxxx',

                TargetTable = 'BUB',

                WorkingDatabase = 'xxxxxx',

                LogTable = 'BUB_log'

        );

        APPLY

        (

                'INSERT INTO BUB (

                REPORT_DATE,

                REPORT_TYPE,

                CALC_HBW_LBW

                ) VALUES (

                :REPORT_DATE,

                :REPORT_TYPE,

                :CALC_HBW_LBW

                );'

        )

        TO OPERATOR (BUB_LOADFILE)

        SELECT * FROM OPERATOR (BUB_GETFILE);

); 

4. Output from tbuild:

Teradata Parallel Transporter Version 14.00.00.08

Job log: /opt/teradata/client/14.00/tbuild/logs/sdlopr-53.out

Job id is sdlopr-53, running on sdlompa2

Teradata Parallel Transporter Load Operator Version 14.00.00.08

BUB_LOADFILE: private log not specified

Teradata Parallel Transporter BUB_GETFILE: TPT19006 Version 14.00.00.08

BUB_GETFILE Instance 1 directing private log report to 'BI_MAINT_SUMMARY_FULL.log-1'.

BUB_GETFILE: TPT19008 DataConnector Producer operator Instances: 1

BUB_GETFILE: TPT19003 ECI operator ID: BUB_GETFILE-12550

BUB_GETFILE: TPT19222 Operator instance 1 processing file '/oraopr/teradata/bub.txt'.

BUB_LOADFILE: connecting sessions

BUB_LOADFILE: preparing target table

BUB_LOADFILE: entering Acquisition Phase

BUB_LOADFILE: entering Application Phase

BUB_LOADFILE: Statistics for Target Table:  'BUB'

BUB_LOADFILE: Total Rows Sent To RDBMS:      3

BUB_LOADFILE: Total Rows Applied:            1

BUB_LOADFILE: Total Rows in Error Table 1:   0

BUB_LOADFILE: Total Rows in Error Table 2:   0

BUB_LOADFILE: Total Duplicate Rows:          2

BUB_GETFILE: TPT19221 Total files processed: 1.

BUB_LOADFILE: disconnecting sessions

BUB_LOADFILE: Total processor time used = '0.52 Second(s)'

BUB_LOADFILE: Start : Mon Mar 25 13:07:52 2013

BUB_LOADFILE: End   : Mon Mar 25 13:08:57 2013

Job step MAIN_STEP completed successfully

Job sdlopr completed successfully

5. What loaded into the table:

 select * from bub;

REPORT_DATEREPORT_TYPECALC_HBW_LBW

2011-06-01 00:00:00 L WBW

From what I'm seeing it the table, it looks like the VARDATE works perfectly but seems to be causing tbuild to ignore my | delimiter and dump characters out of the rows prior to inserting.  I've read the TPT User Guide and Reference and I'm not seeing where what I'm trying to do wouldn't work.

The only thing I can think of is it's not working because VARDATE is a 14.00 feature and I'm running to a 13.10 database?  If that's the case, then what should I use to format my DATE and numeric columns with?