SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Tools
Enthusiast

SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

I have a proble with TPT, and never pass through this error.

Script:

DEFINE JOB EXPORT_TABLE_TO_FILE
(
DEFINE SCHEMA TABLE_SCHEMA
(
COL_MSISDN VARCHAR(40)
);

DEFINE OPERATOR SQL_SELECTOR
TYPE SELECTOR
SCHEMA TABLE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'LP_selector_log',
VARCHAR TdpId = @hostname,
VARCHAR LogonMech = 'LDAP',
VARCHAR UserName = @username,
VARCHAR UserPassword = @password,
VARCHAR SelectStmt = 'SELECT MSISDN FROM SANDBOX.CTF_REF_CHURN',
VARCHAR ReportModeOn
);

DEFINE OPERATOR FILE_WRITER
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'LP_dataconnector_log',
VARCHAR FileName = 'CTF_REF_CHURN.dat',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Delimited',
VARCHAR DirectoryPath = 'D:\Workspace\',
VARCHAR TextDelimiter = '|'
);

DEFINE OPERATOR EXPORT_OPERATOR
TYPE EXPORT
SCHEMA TABLE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'LP_export_log',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @hostname,
VARCHAR LogonMech = 'LDAP',
VARCHAR UserName = @username,
VARCHAR UserPassword = @password,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT MSISDN FROM SANDBOX.CTF_REF_CHURN'
);

STEP export_to_file
(
APPLY TO OPERATOR (FILE_WRITER)
SELECT * FROM OPERATOR (SQL_SELECTOR);
);
);

DDL:

CREATE MULTISET TABLE SANDBOX.CTF_REF_CHURN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
PERIOD_MONTH INTEGER,
MSISDN VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
LAST_USAGE_CHARGE DATE FORMAT 'yyyy-mm-dd',
LAST_USAGE_NON_CHARGE DATE FORMAT 'yyyy-mm-dd',
LAST_USAGE_CHARGE_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
LAST_USAGE_NON_CHARGE_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
CHURN_EVENT DATE FORMAT 'yyyy-mm-dd',
CHURN_EVENT_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
ACTIVATION_EVENT DATE FORMAT 'yyyy-mm-dd' DEFAULT DATE '1970-01-01',
BRAND VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'SP',
AREA VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'AREA 1',
REGIONAL VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '01.Sumbagut',
LAST_USAGE_EVENT DATE FORMAT 'yyyy-mm-dd' DEFAULT DATE '1970-01-01',
GRACE_EVENT DATE FORMAT 'yyyy-mm-dd' DEFAULT DATE '1970-01-01',
LACCI_HOME VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '51010',
ACTIVATION_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '1',
LAST_USAGE_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '1',
GRACE_DAY VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '1',
LACCI_LAST VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '51010',
DELTA_PERIOD FLOAT)
PRIMARY INDEX ( MSISDN )
PARTITION BY RANGE_N(PERIOD_MONTH BETWEEN 201201 AND 201212 EACH 1 );

Log:

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize
Found CheckPoint file: C:\Program Files\Teradata\client\13.10\Teradata Parallel
Transporter/checkpoint\lhutfhypriLVCP
This is a restart job; it restarts at step export_to_file.
Teradata Parallel Transporter Executor Version 13.10.00.02
Teradata Parallel Transporter Coordinator Version 13.10.00.02
Teradata Parallel Transporter Executor Version 13.10.00.02
Teradata Parallel Transporter DataConnector Version 13.10.00.02
FILE_WRITER: TPT19206 Attribute 'TraceLevel' value reset to 'MILESTONES'.
FILE_WRITER Instance 1 directing private log report to 'LP_dataconnector_log-1'.

FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter SQL Selector Operator Version 13.10.00.02
SQL_SELECTOR: private log specified: LP_selector_log
FILE_WRITER: TPT19003 ECI operator ID: FILE_WRITER-2180
FILE_WRITER: TPT19222 Operator instance 1 processing file 'D:\Workspace\CTF_REF_
CHURN.dat'.
SQL_SELECTOR: connecting sessions
SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0,
Total Rows Sent = 0
SQL_SELECTOR: disconnecting sessions
SQL_SELECTOR: Total processor time used = '0.249602 Second(s)'
SQL_SELECTOR: Start : Thu Aug 16 10:22:50 2012
SQL_SELECTOR: End : Thu Aug 16 10:22:51 2012
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0,
Total Rows Sent = 0
FILE_WRITER: TPT19221 Total files processed: 0.
Job step export_to_file terminated (status 12)
Job lhutfhypri terminated (status 12)
Total available memory: 10000000
Largest allocable area: 10000000
Memory use high water mark: 44672
Free map size: 1024
Free map use high water mark: 15
Free list use high water mark: 0

This is my first TPT, and I can get through the this SCHEMA error

Tags (2)
12 REPLIES
Teradata Employee

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Normally, we enforce that the schema definition must match the data coming from the database (exactly).

However, we did make a change to TPT in 13.10 (in efix #3) to allow the schema to have a mismatch if the column definition (for VARCHAR columns only I believe) was smaller in size than the schema definition.

So, you can do one of 2 things:

1. Change the column definition in the schema from VARCHAR(40) to VARCHAR(20).

2. Upgrade your version of TPT 13.10 to the latest and greatest patch level.

-- SteveF
Enthusiast

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

if we change the query too 'SELECT cast(MSISDN as varchar(40)) FROM SANDBOX.CTF_REF_CHURN', will that help get rid of this error ?

Teradata Employee

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Did you try it?  :)

The best option is to upgrade to the latest and greatest efix.

That is always preferable.

Aside from that, the CASE should work (it might have been better to just try it out to see if it works instead of posting the question).

-- SteveF
Enthusiast

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

yes i did try this, i had 30-40 odd columns in the sql so wanted to check before maknig the change.

Anyways i made the change and it works.. its strange thogh there's no implicit conversion. it has to be exact match

Teradata Employee

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

In general, TPT expects an exact match.
There is no "conversion" for TPT to perform.
For VARCHAR we will allow the actual data to be smaller in size than the schematic.
-- SteveF
Enthusiast

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Hi feinholz@,

For a table with 3 columns

NET_ID DECIMAL(38,0) NOT NULL,

NET_NAME VARCHAR(300),

NET_KEY DECIMAL(9,0) NOT NULL

What will be the FILE_SCHEMA in TPT 13.10 scipt? I tried the following but got " TPT15105: Error 13 in finalizing the table schema definition"

DEFINE SCHEMA OUTPUT_FILE_SCHEMA

(

"NET_ID"  Varchar(18),

"NET_NAME" VARCHAR(300),

"NET_KEY"  Varchar(9)

);

DEFINE OPERATOR SQL_SELECTOR

TYPE SELECTOR

SCHEMA OUTPUT_FILE_SCHEMA

ATTRIBUTES

(

  INTEGER MaxDecimalDigits = 18,

  VARCHAR DateForm = 'ANSIDATE',

  VARCHAR PrivateLogName = 'selector_log',

Enthusiast

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Does UTF8 change the byte size in SCHEMA definition?

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a delimited file'
(
DEFINE SCHEMA OUTPUT_FILE_SCHEMA
(
"NET_ID" Varchar(39),
"NET_NAME" VARCHAR(900),
"NET_KEY" Varchar(39)
);
DEFINE OPERATOR SQL_SELECTOR
TYPE SELECTOR
SCHEMA OUTPUT_FILE_SCHEMA
ATTRIBUTES
Teradata Employee

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

@ericsun2: the schema must match the data. Thus the Selector is retrieving 2 DECIMALs and a VARCHAR. Your schema does not match that.

And yes, if you are using a client session character set of UTF8, you must adjust the sizes of the VARCHAR fields in the schema definition to account for the extra data. You must multiply by 3.

-- SteveF
Enthusiast

Re: SQL_SELECTOR: TPT15105: Error 13 in finalizing the table schema definition

Hi Steven,

Thanks for the reply. Let me clarify my question:

  1. NET_ID defined as Decimal(38,0) in DDL, but what should I define in TPT file schema?
    • when MaxDecimalDigits = 18MaxDecimalDigits = 18MaxDecimalDigits = 18: shall I use VARCHAR(18), VARCHAR(19), VARCHAR(20) or VARCHAR(38)?
    • when MaxDecimalDigits = 38: shall I use VARCHAR(38), VARCHAR(39) or VARCHAR(40)?
  2. NET_NAME is defined as VARCHAR(300) CHAR SET UNICODE in DDL
    • when in UTF-8: I should use VARCHAR(900)
    • when in UTF-16: I should use VARCHAR(600)
    • when in ASCII: I should use VARCHAR(300), right?
  3. NET_KEY is defined as Decimal(9,0) in DDL, shall I use VARCHAR(9), VARCHAR(10) or VARCHAR(11)?
  4. If I have NET_TRAFFIC_BYTES defined as BIGINT in DDL, shall I use VARCHAR(20), VARCHAR(21) or VARCHAR(22)?