End of Record marker with TPT

Tools
Teradata Employee

End of Record marker with TPT

I have a CSV file that I'm attempting to load via TPT.  I have created the CSV file in Excel.  When I try to load the file with the appropriate number of delimiters, I am getting a BUFFERMAXSIZE error.  When I add another delimiter to the end of each record, the file loads just fine.  This isn't a huge issue, but I'm just confused and would like to understand more about end of record markers.  My schema definition is below as well as my Dataconnector properties

DEFINE SCHEMA MVT_INPUT
Description 'MOVEMENTS INPUT DEFINITION'
(
tk_num VARCHAR(5),
tk_descr VARCHAR(30),
rule VARCHAR(2),
TagName1 VARCHAR(255),
TagName2 VARCHAR(255)
);
ATTRIBUTES
(
FileName = 'GravLoad.csv',
Format = 'DELIMITED',
OpenMode = 'Read',
DirectoryPath = 'D:\Tony\csv',
IndicatorMode = 'N',
TextDelimiter = ','
)
9 REPLIES
Teradata Employee

Re: End of Record marker with TPT

A buffer is created based on the schema, Now, record is scanned till the next delimiter and copied to the buffer.

Now, considering your case where last column does not have a delimiter. When the value (no. of character) is more/less than the buffer size, it will error out. It need to be exactly same number of character defined in the schema if you are not using delimiter in the last column. 

If delimiter is used for the last column, then TPT knows how many characters the column is having.

Teradata Employee

Re: End of Record marker with TPT

Please explain this answer:

"When the value (no. of character) is more/less than the buffer size, it will error out. It need to be exactly same number of character defined in the schema if you are not using delimiter in the last column"

I am unaware of such a rule (that the size of the field must be exactly the same number of characters defined in the schema).

The use of a terminating delimiter is OPTIONAL.

-- SteveF
Teradata Employee

Re: End of Record marker with TPT

Thank you Feinholz!  I've traced the issue back to some NULL values in the rows I'm trying to load.  How can I load NULL values using TPT?  I am getting the MAXBUFFERSIZE error when i have rows with nulls.  I'm creating the CSV file with Excel, and the nulls are just represented by repeating delimiters.  For example:

Column1_value, Column2_value,,Column4_value

where Column3_value is null.  There is no space between the delimiters.  When I add a space, the space gets loaded and the column is not null. What am i missing here?

Teradata Employee

Re: End of Record marker with TPT

You have the correct info.

With: Column1_value, Column2_value,,Column4_value

column 3 will be NULL. No space can be between the delimiters (otherwise the space character will be interpreted as the data for column 3).

(And BTW, NULL is not a value. It is a characteristic of a column.)

-- SteveF
Teradata Employee

Re: End of Record marker with TPT

Thanks, that's what I was thinking.  But I still can't get this to work.  I tried with a test csv that i made in notepad with just a single record and one null column (no space between delimiters) and I'm still getting this buffer max size error.  I couldn't find anything in the manuals about this.  My TPT script, target DDL and sample CSV are below:

SET MyUsername = '*****';
SET MyPassword = '*****';
SET MyTDPID = '*****';

DEFINE JOB MONROE_STRP_LOAD
(
DEFINE SCHEMA GRAV_TAG_SCHEMA
Description 'GRAVITY INPUT DEFINITION'
(
tk_num VARCHAR(5),
tk_descr VARCHAR(30),
rule VARCHAR(2),
TagName1 VARCHAR(255),
TagName2 VARCHAR(255)
);

DEFINE OPERATOR GRAV_TAG_LOAD_OP
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR UserName = @MyUsername,
VARCHAR UserPassword = @MyPassword,
VARCHAR LogTable = 'MONROE_DEV_STG.GRAV_TAG_LKP_LOG',
VARCHAR TargetTable = 'MONROE_DEV_STG.GRAV_TAG_LKP',
VARCHAR TdpId = @MyTDPID,
VARCHAR PrivateLogName= 'GRAV_TAG_LKP_LOG'
);

Define OPERATOR PI_INPUT_DDL
Description 'DDL OPERATOR'
Type DDL
Attributes
(
VARCHAR DateForm,
VARCHAR TdpId = @MyTDPID,
VARCHAR UserName = @MyUsername,
VARCHAR UserPassword = @MyPassword,
VARCHAR PrivateLogName= 'PI_INPUT_DDL_LOG',
VARCHAR WorkingDatabase = 'MONROE_DEV_STG',
VARCHAR TraceLevel = 'None'
);

DEFINE OPERATOR GRAV_TAG_PROD_OP
TYPE DATACONNECTOR PRODUCER
SCHEMA GRAV_TAG_SCHEMA
ATTRIBUTES
(
VARCHAR FileName = 'GravLoad.csv',
VARCHAR Format = 'DELIMITED',
VARCHAR OpenMode = 'Read',
VARCHAR DirectoryPath = 'D:\Tony\csv',
VARCHAR IndicatorMode = 'N',
VARCHAR TextDelimiter = ','
);

STEP setup_pi_input_stg
(
APPLY
(' DELETE MONROE_DEV_STG.GRAV_TAG_LKP;')
TO OPERATOR (PI_INPUT_DDL);
);

STEP load_strp_tbl
(
APPLY
(
'INSERT INTO MONROE_DEV_STG.GRAV_TAG_LKP (tk_num, tk_descr, rule, tagname1, tagname2)
VALUES (:tk_num, :tk_descr, :rule, :tagname1, :tagname2);'
)
TO OPERATOR
(
GRAV_TAG_LOAD_OP[1]

)
SELECT * FROM OPERATOR
(
GRAV_TAG_PROD_OP[1]

);
);

);

CREATE SET TABLE MONROE_DEV_STG.GRAV_TAG_LKP ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
TK_NUM VARCHAR(5),
TK_DESCR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
RULE VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
TagName1 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
TagName2 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( TK_NUM );

My input CSV looks like this:

Tony,,Stricker,loves,tpt

Teradata Employee

Re: End of Record marker with TPT

Please provide version of TPT and entire screenshot of console output.

-- SteveF
Teradata Employee

Re: End of Record marker with TPT

I'm on TPT 14.00.00.06.  log output is below:

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize

Teradata Parallel Transporter Coordinator Version 14.00.00.06

Teradata Parallel Transporter Executor Version 14.00.00.06

Teradata Parallel Transporter SQL DDL Operator Version 14.00.00.06

PI_INPUT_DDL: private log specified: PI_INPUT_DDL_LOG

PI_INPUT_DDL: connecting sessions

PI_INPUT_DDL: sending SQL requests

PI_INPUT_DDL: Rows Deleted:  0

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

PI_INPUT_DDL: disconnecting sessions

PI_INPUT_DDL: Total processor time used = '0.171601 Second(s)'

PI_INPUT_DDL: Start : Thu Mar 27 15:33:18 2014

PI_INPUT_DDL: End   : Thu Mar 27 15:33:20 2014

Job step setup_pi_input_stg completed successfully

Teradata Parallel Transporter Coordinator Version 14.00.00.06

Teradata Parallel Transporter Executor Version 14.00.00.06

Teradata Parallel Transporter Executor Version 14.00.00.06

Teradata Parallel Transporter GRAV_TAG_PROD_OP: TPT19006 Version 14.00.00.06

GRAV_TAG_PROD_OP Instance 1 is not generating a private log report.

GRAV_TAG_PROD_OP: TPT19008 DataConnector Producer operator Instances: 1

Teradata Parallel Transporter Load Operator Version 14.00.00.06

GRAV_TAG_LOAD_OP: private log specified: GRAV_TAG_LKP_LOG

GRAV_TAG_PROD_OP: TPT19003 ECI operator ID: GRAV_TAG_PROD_OP-1788

GRAV_TAG_PROD_OP: TPT19222 Operator instance 1 processing file 'D:\Tony\csv\GravLoad.csv'.

GRAV_TAG_LOAD_OP: connecting sessions

GRAV_TAG_LOAD_OP: preparing target table

GRAV_TAG_LOAD_OP: entering Acquisition Phase

Job is running in Buffer Mode

Task(APPLY_1[0001]): checkpoint completed, status = Success

Task(SELECT_2[0001]): checkpoint completed, status = Success

GRAV_TAG_PROD_OP: TPT19350 I/O error on file 'D:\Tony\csv\GravLoad.csv'.

GRAV_TAG_PROD_OP: TPT19003 BUFFERMAXSIZE: 64260

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

GRAV_TAG_LOAD_OP: disconnecting sessions

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

GRAV_TAG_PROD_OP: TPT19221 Total files processed: 0.

GRAV_TAG_LOAD_OP: Total processor time used = '1.34161 Second(s)'

GRAV_TAG_LOAD_OP: Start : Thu Mar 27 15:33:22 2014

GRAV_TAG_LOAD_OP: End   : Thu Mar 27 15:33:31 2014

Job step load_strp_tbl terminated (status 12)

Job GRAV_TAG_TEST terminated (status 12)

Total available memory:          10000000

Largest allocable area:          10000000

Memory use high water mark:         80400

Free map size:                       1024

Free map use high water mark:          20

Free list use high water mark:          0

Teradata Employee

Re: End of Record marker with TPT

Ok, a couple of things.

First, please upgrade to the latest patch (14.00.00.11).

Next, if the problem still persists, I need you to enable tracing for the Data Connector operator by placing:

VARCHAR TraceLevel = 'all'

into the DC operator definition, and re-run the job and then one of 2 things:

1. run tlogview to extract out the log for the DC operator (tlogview -l <log file name> -f "*" -g) and post the contents here, or

2. just send the entire .out file (the binary log file) to steven.feinholz@teradata.com

-- SteveF
Teradata Employee

Re: End of Record marker with TPT

Thank you, I will perform the upgrade and test again.  If I still have issues I'll send you the log data this weekend.