TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Tools
Enthusiast

TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Hi,

I am trying to create one generic TPT script for data copy purpose and facing some issues with DEFINE SCHEMA syntax. Details are explained below. 

Environmental Details:

Teradata PT Version: 14.10.00.02

OS: Linux

Requirement: Create a generic TPT scripts to copy tables data from one server of teradata to another server. Table name, Server details and all other required information will be passed through parameter file dynamically. Thus single TPT script will serve the purpose of multiple tables copy.

Script Created

DEFINE JOB TABLE_DATA_COPY

(

DEFINE OPERATOR TABLE_DATA_LOAD

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR UserName =@TgtUserName, 

VARCHAR UserPassword =@TgtUserPassword, 

VARCHAR LogTable =@TgtUtilDatabase||'.'|| @TgtTable||'_log', 

VARCHAR TargetTable =@TgtDatabase||'.'|| @TgtTable|| '', 

INTEGER BufferSize, 

INTEGER ErrorLimit, 

INTEGER MaxSessions, 

INTEGER MinSessions, 

INTEGER TenacityHours, 

INTEGER TenacitySleep, 

VARCHAR AccountID, 

VARCHAR DateForm, 

VARCHAR ErrorTable1 =@TgtUtilDatabase||'.'|| @TgtTable||'_ERR1', 

VARCHAR ErrorTable2 =@TgtUtilDatabase||'.'|| @TgtTable||'_ERR2', 

VARCHAR NotifyExit, 

VARCHAR NotifyExitIsDLL, 

VARCHAR NotifyLevel, 

VARCHAR NotifyMethod, 

VARCHAR NotifyString, 

VARCHAR PauseAcq, 

VARCHAR PrivateLogName, 

VARCHAR TdpId =@TgtTptId, 

VARCHAR TraceLevel, 

VARCHAR WorkingDatabase

);

DEFINE SCHEMA TABLE_SCHEMA FROM TABLE @SrcDatabase||'.'|| @SrcTable

;

DEFINE OPERATOR TABLE_DATA_EXTRACT 

TYPE EXPORT

SCHEMA TABLE_SCHEMA

ATTRIBUTES

(

VARCHAR UserName =@SrcUserName, 

VARCHAR UserPassword =@SrcUserPassword, 

VARCHAR SelectStmt ='SELECT * FROM '|| @SrcDatabase||'.'|| @SrcTable||';', 

INTEGER BlockSize, 

INTEGER MaxSessions, 

INTEGER MinSessions, 

INTEGER TenacityHours, 

INTEGER TenacitySleep, 

INTEGER MaxDecimalDigits =38, 

VARCHAR AccountID, 

VARCHAR DateForm, 

VARCHAR NotifyExit, 

VARCHAR NotifyExitIsDLL, 

VARCHAR NotifyLevel, 

VARCHAR NotifyMethod, 

VARCHAR NotifyString, 

VARCHAR PrivateLogName, 

VARCHAR TdpId =@SrcTptId,

VARCHAR TraceLevel, 

VARCHAR WorkingDatabase

);

APPLY

$INSERT

TO OPERATOR

(

TABLE_DATA_LOAD[1]

)

SELECT * FROM OPERATOR

(

TABLE_DATA_EXTRACT[1]

);

);

Parameter File Used for Testing

TgtTptId= 'TDDEV'

TgtUserName= '******'

TgtUserPassword= '******'

SrcTptId= 'TDTEST'

SrcUserName= '******'

SrcUserPassword= '******'

TgtDatabase= 'EDW_D1_WORK'

TgtTable= 'TABLE1_WORK'

TgtUtilDatabase= 'EDW_D1_UTIL'

SrcDatabase= 'EDW_S1_WORK'

SrcTable= 'TABLE1_WORK'

Run Command: tbuild -f <ScriptName> - v <ParmFileName>

Error Details:

Teradata Parallel Transporter Version 14.10.00.02

TPT_INFRA: TPT02932: Error: Invalid token near line 51 (text was '!')

TPT_INFRA: TPT04017: Exception "Invalid token" caught during job script file parsing/compilation.

Job script compilation failed.

Job terminated with status 8.


Explanation and Furthur Testing: There is no such text (!) in the script. I tried with hardcoding all the parameterized values just to find out the issue but the script failed because of DEFINE SCHEMA statement. When I am replacing the above DEFINE Schema statement with specific column details like below, the script is running fine.

DEFINE SCHEMA TABLE_SCHEMA FROM TABLE 

(

CALDR_ID INTEGER,

DT_DSC VARCHAR(50),

DT INTDATE

);

In Teradata Parallel Transporter User Guide, Release 14.10 (B035-2445-082K), it is mentioned that there are features to generate schema definition dynamically. There are several options available to achieve this. I referred (Page number 223 onwards) that user guide and tried several options like below but all of them are failing with same king of error.

DEFINE SCHEMA TODAYS_TRANSACTIONS FROM TABLE 'Daily_Trans';

DEFINE SCHEMA PROD_EXT FROM SELECT 'Select a,b,c,sum(d) from Products;';

DEFINE SCHEMA TRANS FROM SELECT OF OPERATOR EXPORT2;

I tried to use all the above options using hardcoded databasename and tablename just to test whether those are working but getting below error - 

Teradata Parallel Transporter Version 14.10.00.02

TPT_INFRA: TPT04143: Error: Line 39 of Job Script File '/home/*****/Table_Data_Copy1.tpt':

Syntax Error: literal string (DBS table name) expected.

Job script preprocessing failed.

Job terminated with status 8.

It seems that those syntax are not working. Then I tried with obsolete  sysnax  DEFINE SCHEMA <SchemaName> <TableName>;

But that syntax is also giving same kind of error. Is this a bug of teradata? How can I fix this issue and create my generic script?

27 REPLIES
Teradata Employee

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

I will look into the issue you are having, but in the meantime, I have a question.

If all you are doing is moving data from one table to another, have you tried using the "EasyLoader" feature?

The command is "tdload" and is a script-less method for moving data.

You can either use a job variable file, or specify the necessary logon credentials and table names on the command line.

-- SteveF
Enthusiast

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Thank you for your response!!!

I didn't use EasyLoader before. I have plan to Delete target before copying data as well. I will try to see if I can use EasyLoader for this requirement. 

Meantime if you can look into this Schema Definatin issue and figure out what is happening, it would be great.

Teradata Employee

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Just to warn you: EasyLoader is a means to load data only. You would have to drop tables and create tables outside of that tool.

If you would rather have a single script to do that, then stick with "tbuild".

-- SteveF
Enthusiast

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Thanks for your warning. I was just checking tdload option and getting one error.

Job variable File:

TargetTdpId = 'TDDEV',

TargetUserName = '******',

TargetUserPassword = '******',

TargetTable = 'DatabaseName.TABLE1_WORK',

SourceTdpId = 'TDTEST',

SourceUserName = '******',

SourceUserPassword = '******',

SourceTable = 'DatabaseName.TABLE1_WORK'

Command Rantdload -j /directory/<jobvariablesfile>

Error Message:

Teradata Load Utility Version 14.10.00.02

Teradata Parallel Transporter Version 14.10.00.02

Job log: /opt/teradata/client/14.10/tbuild/logs/******-73255.out

Job id is ******-73255, running on ******

Teradata Parallel Transporter Export Operator Version 14.10.00.02

Teradata Parallel Transporter Load Operator Version 14.10.00.02

$EXPORT: private log specified: ExportLog

$LOAD: private log specified: LoadLog

$LOAD: connecting sessions

$EXPORT: connecting sessions

TPT_INFRA: TPT02639: Error: Conflicting data type for column(3) - "DT". Source column's data type (CHAR) Target column's data type (INTDATE).

$EXPORT: TPT12108: Output Schema does not match data from SELECT statement

$EXPORT: disconnecting sessions

$LOAD: TPT10508: RDBMS error 3524: The user does not have CREATE TABLE access to database <TargetUserId>.

$LOAD: disconnecting sessions

$EXPORT: Total processor time used = '0.16 Second(s)'

$EXPORT: Start : Thu Jun 19 11:11:24 2014

$EXPORT: End   : Thu Jun 19 11:11:25 2014

$LOAD: Total processor time used = '0.16 Second(s)'

$LOAD: Start : Thu Jun 19 11:11:24 2014

$LOAD: End   : Thu Jun 19 11:11:25 2014

Job step MAIN_STEP terminated (status 12)

Job flwbatch terminated (status 12)

Job start: Thu Jun 19 11:11:21 2014

Job end:   Thu Jun 19 11:11:25 2014

Details: Source and target table structure are same as below 

CREATE MULTISET TABLE <DatabaseName>.TABLE1_WORK ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COL1 INTEGER NOT NULL,

      COL2 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      COL3 DATE FORMAT 'YYYY-MM-DD')

UNIQUE PRIMARY INDEX ( COL1,COL2);


Questions:

1. Why I am getting data type missmatch error when data type are same in both source and target?

2. Why tool is trying to create table in target user database?

Enthusiast

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

I can possibly understand why it is trying to create table in target user database – to create error tables may be. So, I changed my job variable file as below. Also I took a different table which is having all integer columns.

TargetTdpId = 'TDDEV',

TargetUserName = '******',

TargetUserPassword = '******',

TargetWorkingDatabase = <TargetTableDatabaseName>,

TargetTable =’TABLE1’,

SourceTdpId = 'TDTEST',

SourceUserName = '******',

SourceUserPassword = '******',

SourceWorkingDatabase = <SourceTableDatabaseName>,

SourceTable = ’TABLE1’,

ErrorTable1= ‘UTIL Database Name.TABLE1_ERR1',

ErrorTable2 = ‘UTIL Database Name.TABLE1_ERR2'

Now, my job got succeeded. So questions are –

1.    Why Date data type is not working here?

2.    Also, can I change the work table (which is created as part of loading process) database to a different database than target table database?

3.    Can the error table database name be mentioned in a different variable?

Teradata Employee

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

The reason for the DATE not working is because the data for that column is being exported in ANSI format (equivalent to a CHAR(10)) whereas the target table is being interpreted as the standard Teradata integer format.

Yes, you can provide a  fully qualified name for the work table.

Your job variables can be any name (as long as it is not a TPT reserved word).

-- SteveF
Enthusiast

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Thanks for those clarifications.

What is the way out for DATE columns then (or any other type of columns which may encounter similar problem) ?

In Which Variable I shall assign Fully qualified work table? I couldn't understand when you said - "Your job variables can be any name (as long as it is not a TPT reserved word)."  How tdload will interpret which variable is for what purpose? I cannot give target database name in variable SourceWorkingDatabase and assume that TPT will consider it as target right? There must be some naming standards of those variables.

Teradata Employee

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

Is there any follow up to this around the use of defining schema's based on table structures in a database?  I too have had the problems described above and would really like to get be able to use a single TPT script to load a table just by giving it a target table and a filename.

Regards,

Jim.

Teradata Employee

Re: TPT Statement Not Working: DEFINE SCHEMA <SchemaName> FROM TABLE '<TableName>';

TPT supports obtaining the schema from a table in multiple ways.

The user can specify this in a script:

DEFINE SCHEMA <name> FROM TABLE '<tablename>';

and even this:

DEFINE SCHEMA <name> FROM TABLE DELIMITED '<tablename>';

if the user wants the schema to be converted to all VARCHAR (due to their incoming data being provided as delimited data).

In many cases, not even providing the schema at all, and using operator templates, TPT will "infer" the schema based on either the content of the SELECT statement (if using the Export or Selector operator) or from the DDL of the target table (if the producer operator is the file reader). The latter requires that the layout of the data matches the layout of the target table.

-- SteveF