All Forums Tools
07 Feb 2014
DEFINE SCHEMA target_schema FROM TABLE

Hi i am trying to use DEFINE SCHEMA target_schemaz FROM TABLE in a tbuild script and it is failing with
"Teradata Parallel Transporter Version 14.10.00.01

TPT_INFRA: TPT05004: CLI error 224 in connecting session (function ConnectSession).

MTDP: EM_NOHOST(224): name not in HOSTS file or names database.

 

TPT_INFRA: TPT04032: Error: Schema generation failed for table 'SONARTEST.EOD_USAGE' in DBS '(null)':

"GetTableSchema" status: 48."

 

So I beleive it need connection information but I don't know how to supply it.

 

Any help would be appreciated, thank you.

feinholz 1234 posts Joined 05/08
07 Feb 2014

On what platform are you running?
You will need to add your Teradata Database node id's and IP addresses into the /etc/hosts file (for Unix/Linux).
Please refer to the CLIv2 documentation for more information.
 

--SteveF

07 Feb 2014

I'm on Windows

feinholz 1234 posts Joined 05/08
07 Feb 2014

Have you used any of our load/unload products before?
 

--SteveF

07 Feb 2014

is there a way to supply connection information in the script?
 

07 Feb 2014

I am a newbee but making great progress - I have everything working but I am trying to generalize my scripts thus the desire to use the dynamic schema operator.     I have spent several hours now going through the TPT user and reference manuals, if there is a wex based training courtse that covers this please point me at it.  Thank you, --Don  

feinholz 1234 posts Joined 05/08
07 Feb 2014

The connection information in the script is through the use of the TdpId, Username and UserPassword attributes.
But the TdpId must be in your hosts file.
Or, you can just put the IP address in the TdpId attribute.
By "dynamic schema operator", do you mean the schema mapping operator?
What is it you are really trying to do?
 

--SteveF

07 Feb 2014

 I am moving tables from netezza to teradata , they have the equivalent table definations.  Since I have many to do, I'm trying to develop a script that could just take the table name.
I am testing the use of ODBC to move the data directly from one db to the other.
My thought was to use the  DEFINE SCHEMA XXX  FROM TABLE  syntax so I won't need to code that part.
I  have the script working by manually coding the scema directive and wish to remove that to make it generic.
On the NZ side I can create a view to output the data and timestamps columns formated correctly, although I wish that wasn't necessary.   Apparently the data comes out formatted incorectly by default for loading timestamps and dates.
So I still am not able to get this working,  the real operators all have an attributes section but the schema directive doesn't.
I tried this (below)  as my best guess but it doesn't work, I obscured the tdpid below

ATTRIBUTES

(

VARCHAR TargetTdpId = @jobvar_tdpid,

VARCHAR TargetUserName = @jobvar_username,

VARCHAR TargetUserPassword = @jobvar_password,

VARCHAR SourceTdpId = @jobvar_tdpid,

VARCHAR SourceUserName = @jobvar_username,

VARCHAR SourceUserPassword = @jobvar_password,

VARCHAR TdpId = '10.xx.xxx.xxx',

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password

);

 

DEFINE SCHEMA target_schemaz FROM TABLE 'SONARTEST.EOD_USAGE';  

 

 

So I don't see how to bind TdpId  ,UserName   and  UserPassword   to the schema directive.

 

Thank you.
 

10 Feb 2014

Ok - i see that won't work for me even if I get past the login issue:
This script below  creates aschema file and then uses it.
One issue I need to get past is to use a variable as part of the sql statement.
Below you will see :

from  Dbc.COLUMNS c 

where TableName = ''tabelname''

AND DatabaseName = ''dbname''

 

 

I tried various ways to substute job variables but couldn't seem to get it to work.

 

Is there some special quoting necessary?

 

 

My table name is defined in @jobVar_TargetTable

 

 

Code below:

 

 

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

 

DEFINE SCHEMA SCHEMA_GEN

(

schema_out char(128)

);

 

/*****************************/

DEFINE OPERATOR FILE_WRITER()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

TYPE DATACONNECTOR CONSUMER

SCHEMA *

RIBUTES

(

VARCHAR PrivateLogName    = 'file_writer_privatelog',

VARCHAR FileName          = 'gen_schema_output.txt',

VARCHAR IndicatorMode     = 'N',

VARCHAR OpenMode          = 'Write',

VARCHAR Format            = 'TEXT',

VARCHAR TrimColumns = 'TRAILING'

);

 

 

/*****************************/

DEFINE OPERATOR EXPORT_OPERATOR()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA SCHEMA_GEN

RIBUTES

(

VARCHAR PrivateLogName = '_load_log_' || @LoadId ,

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

INTEGER MaxSessions       =  32,

INTEGER MinSessions       =  1,

VARCHAR AccountId,

VARCHAR SelectStmt        = 

'SELECT

CAST( 

CASE rn

when 1 then ''DEFINE SCHEMA source_schema (''

else '',''

end || s

||

case

when rn = cnt then  '');''

else ''''

end   

AS CHAR(128)) AS schema_out

from (

select  

ColumnName ||

CASE columnType

WHEN ''CV'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''CF'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''DA'' THEN ''ANSIDATE''

WHEN ''I'' THEN ''INTEGER''

WHEN ''I1'' THEN ''BYTEINT''

WHEN ''I2'' THEN ''SMALLINT''

WHEN ''I8'' THEN ''BIGINT''

WHEN ''TS'' THEN ''VARCHAR(27)''

ELSE  ''50''

END  as s,

row_number() over ( order by ColumnId) as rn,

count(*)  over ()  as cnt,

ColumnId

from  Dbc.COLUMNS c 

where TableName = ''tablename''

AND DatabaseName = ''dbname''

) x

order by ColumnId

;'

 

);

/*****************************/

 

STEP export_to_file

(

APPLY TO OPERATOR (FILE_WRITER() )

SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );

);

 

/*****************************/

 

INCLUDE 'gen_schema_output.txt'

 

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

RIBUTES

(

VARCHAR PrivateLogName = '_ddl_log_' || @LoadId ,

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR ErrorList = '3807'

);

 

DEFINE OPERATOR ODBC_OPERATOR

DESCRIPTION 'ODBC_OPERATOR'

TYPE ODBC

SCHEMA source_schema

RIBUTES

(

VARCHAR UserName = 'stage_adm',

VARCHAR UserPassword   = 'stage_adm',

VARCHAR DSNName = 'stage' ,

VARCHAR SelectStmt = '  select * FROM tablename limit 1000',

VARCHAR PrivateLogName = '_dataconnector_log_' || @LoadId 

);

 

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

RIBUTES

(

VARCHAR PrivateLogName = '_load_log_' || @LoadId ,

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR TargetTable = @jobVar_TargetTable,

VARCHAR LogTable = @jobvar_LogTable ,

VARCHAR ErrorTable1 = @jobvar_ErrorTable1,

VARCHAR ErrorTable2 = @jobvar_ErrorTable2

);

 

STEP Setup_Tables

(

APPLY

('DROP TABLE ' || @jobvar_LogTable || ';'),

('DROP TABLE ' || @jobvar_ErrorTable1 || ';'),

('DROP TABLE ' || @jobvar_ErrorTable2 || ';')

TO OPERATOR (DDL_OPERATOR);

);

 

STEP Load_Trans_Table

(

APPLY $INSERT @jobVar_TargetTable TO OPERATOR (LOAD_OPERATOR[2]) SELECT * FROM OPERATOR(ODBC_OPERATOR[2]);

);

);

 

feinholz 1234 posts Joined 05/08
10 Feb 2014

TPT will not parse/touch anything in single quotes.
 
Thus, if you want to use job variables inside a SQL statement, then you need to break it up into pieces, and use the concatenation operator.
  
But you already did that when you implemented the DROP TABLE statements.
You would do the same thing with the SELECT.
The "@" character denotes the name of a job variable. Thus, you would do:
 
SelectStmt = 'SELECT ..... from  Dbc.COLUMNS c where TableName = ' || @my_tablename || ' AND DatabaseName = ' || @my_dbname || ';'
 

--SteveF

11 Feb 2014

Thank you feinholz.   So I don't confuse anyone I thought I had a single script  working to create a schema file and then use it via a include.   I thought this was working but in reality it was using the existing file (previous run) before I recreated it.
I wish there was a way to differ this include until after I create the schema.  For now I split this into two executions of tbuild.   
I also had some copy and paste issues above so here is the working schema generation script:
 

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

 

DEFINE SCHEMA SCHEMA_GEN

(

schema_out char(128)

);

 

/*****************************/

DEFINE OPERATOR FILE_WRITER()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName    = 'file_writer_privatelog',

VARCHAR FileName          = 'gen_schema_output.txt',

VARCHAR IndicatorMode     = 'N',

VARCHAR OpenMode          = 'Write',

VARCHAR Format            = 'TEXT',

VARCHAR TrimColumns = 'TRAILING'

);

 

 

/*****************************/

DEFINE OPERATOR EXPORT_OPERATOR()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA SCHEMA_GEN

ATTRIBUTES

(

VARCHAR PrivateLogName = '_load_log_' || @LoadId ,

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

INTEGER MaxSessions       =  32,

INTEGER MinSessions       =  1,

VARCHAR AccountId,

VARCHAR SelectStmt        = 

'SELECT

CAST( 

CASE rn

when 1 then ''DEFINE SCHEMA source_schema (''

else '',''

end || s

||

case

when rn = cnt then  '');''

else ''''

end   

AS CHAR(128)) AS schema_out

from (

select  

ColumnName ||

CASE columnType

WHEN ''CV'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''CF'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''DA'' THEN ''ANSIDATE''

WHEN ''I'' THEN ''INTEGER''

WHEN ''I1'' THEN ''BYTEINT''

WHEN ''I2'' THEN ''SMALLINT''

WHEN ''I8'' THEN ''BIGINT''

WHEN ''TS'' THEN ''VARCHAR(27)''

ELSE  ''50''

END  as s,

row_number() over ( order by ColumnId) as rn,

count(*)  over ()  as cnt,

ColumnId

from  Dbc.COLUMNS c 

where TableName = ''' || @TargetTable || '''

AND DatabaseName = '''  || @jobvar_tgt_dbname || '''

) x

order by ColumnId

;'

 

);

/*****************************/

 

STEP export_to_file

(

APPLY TO OPERATOR (FILE_WRITER() )

SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );

);

);

 

11 Feb 2014

Looking back at my intial approach:
DEFINE SCHEMA EOD_USAGE FROM TABLE 'SONARTEST.EOD_USAGE'; 
 
i got it sort of working by suplying the source login information on the command line.
 

T:\td>tbuild   -u 'sourceTdpid='10.xx.xx.xx2' sourcerName='sonartest' sourceUserPassword='sonartest' "  -o
my current error is:

FILE_READER: TPT19003 NotifyMethod: 'None (default)'

FILE_READER: TPT19008 DataConnector Producer operator Instances: 2

FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR/VARDATE' schema.

FILE_READER: TPT19003 TPT Exit code set to 12.

FILE_READER: TPT19221 Total files processed: 0.

FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR/VARDATE' schema.

FILE_READER: TPT19003 TPT Exit code set to 12.

LOAD_OPERATOR: connecting sessions

 

The manual talkes about a special variable sourceFormat when set to 'Delimited' changes the generated schema output.

 

"SourceFormat

Before generating the DEFINE SCHEMA statement, Teradata PT queries the special job

variable SourceFormat. If has the value Delimited, then the generated schema will be in

delimited-file format; otherwise, it will be in the normal format in which the schema column

definitions closely match the Teradata Database table's column descriptions."

 

I tried supplting that on the command line as part of the -u" sourceFormat='Formated'.

 

IS there something wrong with my syntax?

 

-u" ...  sourceFormat='Delimited' "

 

 

I saw no change in the generated schema output.

 

feinholz 1234 posts Joined 05/08
11 Feb 2014

I think you may be confusing a few things.
"SourceFormat" describes what the data will look like as it arrives to the Data Connector operator as a file reader. Not the file writer.
If you are trying to export from Teradata and write the data in delimited format to a flat file, then your schema must match what it looks like in the Teradata table (and you do that with your DEFINE SCHEMA . . . FROM TABLE command.
If you want your data in delimited format, then just define the format in the DC operator (as a file writer) definition. If you would rather specify that on the command line (not sure why you would want to do this), then you use TargetFormat.
 

--SteveF

11 Feb 2014

Hi, No actually I reading from a file and writing to a teradata table.   It just happens that the file layout will match the Teradata table schema (althought all delimited character) since I am porting to Teradata from an identical table schema.  So I was hoping that I could just use the Teradata table layout to generate the schema for the file layout.   When I saw the description of sourceFormat I thought that might do the trick . 

feinholz 1234 posts Joined 05/08
11 Feb 2014

Then use this:
 
DEFINE SCHEMA EOD_USAGE DELIMITED FROM TABLE 'SONARTEST.EOD_USAGE';
 
That will convert the data types of the columns in the table schema to their equivalent VARCHAR data types (with appropriate sizes).
 
No need to use SourceFormat.

--SteveF

12 Feb 2014

Thank you so much that is what I needed!
The correct syntax is:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE';  
 

12 Feb 2014

I'm sorry one more road block.  Can I have the table name as a variable?
I tried a few ways to no avail.
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED @jobVar_TargetTable 
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED ''' || @jobVar_TargetTable || '''
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '' || @jobVar_TargetTable || ''
 

12 Feb 2014

I see this exact example in the manual:

Example 1: Job Script with Simplified Syntax

DEFINE JOB PLOAD_JOB

DESCRIPTION 'PLOAD JOB'

(

/* Use the schema of the TargetTable for TPT_SCHEMA */

DEFINE SCHEMA TPT_SCHEMA DELIMITED @LoadTargetTable;

APPLY $INSERT @LoadTargetTable TO OPERATOR ($LOAD [@LoadInstances])

SELECT * FROM OPERATOR ($FILE_READER(TPT_SCHEMA) [@ReaderInstances]);

);

12 Feb 2014

I guess I should add it doesn't work.

feinholz 1234 posts Joined 05/08
12 Feb 2014

You can put a job variable anywhere in the script you want.
We even have customers switching between the Update operator and the Stream operator and parameterizing the operator "type".
As in:
   DEFINE OPERATOR <some-name>
   TYPE @my_protocol
   . . . . .
So, yes you can use a job variable for the table name in the DEFINE SCHEMA.
In fact, we recommend it.
We recommend you use job variables for anything in the script that can change.
When you say "it does not work", please be more specific and include the job output, and version of TPT.
 

--SteveF

12 Feb 2014

Thank you for your help:
This worked:
T:\td>tbuild  -f eod_usage_tpt_pattern  -v "local.jobvars" -j donjob2123 -u  "LoadId='212345'  TargetTable='eod_usage' LoadFileName='*eod_usage*.gz' "
script snippet:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE'; 
=========================
this failed:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED @X;  

T:\td>tbuild  -f eod_usage_tpt_pattern  -v "local.jobvars" -j donjob2123 -u  "LoadId='212345'  TargetTable='eod_usage' LoadFileName='*eod_usage*.gz' X='SONARTEST.EOD_USAGE'"

Teradata Parallel Transporter Version 14.10.00.01

TPT_INFRA: TPT04143: Error: Line 5 of Job Script File 'eod_usage_tpt_pattern':

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

Job script preprocessing failed.

Job terminated with status 8.

 

=====================================

AND this failed:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '@X';

T:\td>tbuild  -f eod_usage_tpt_pattern  -v "local.jobvars" -j donjob2123 -u  "LoadId='212345'  TargetTable='eod_usage' LoadFileName='*eod_usage*.gz' X='SONARTEST.EOD_USAGE'"

Teradata Parallel Transporter Version 14.10.00.01

TPT_INFRA: TPT05014: RDBMS error 3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'FROM' keyword and '@'.

TPT_INFRA: TPT04032: Error: Schema generation failed for table '@X' in DBS 'dc40-teradata.us.teo.earth':

"GetDelimitedFileSchema" status: 48.

 

Job script preprocessing failed.

Job terminated with status 12.

 

=====================

And this failed
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '''  || @X || ''';

T:\td>tbuild  -f eod_usage_tpt_pattern  -v "local.jobvars" -j donjob2123 -u  "LoadId='212345'  TargetTable='eod_usage' LoadFileName='*eod_usage*.gz' X='SONARTEST.EOD_USAGE'"

Teradata Parallel Transporter Version 14.10.00.01

TPT_INFRA: TPT05014: RDBMS error 3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'FROM' keyword and '"||"'.

TPT_INFRA: TPT04032: Error: Schema generation failed for table '  || @X || ' in DBS 'dc40-teradata.us.teo.earth':

"GetDelimitedFileSchema" status: 48.

 

Job script preprocessing failed.

Job terminated with status 12.

12 Feb 2014

copied your response to the correct thread:
 

4 min ago

In Appendix B of the TPT Reference Manual we list:
DEFINE SCHEMA schema-name 'DBS table name'
DEFINE SCHEMA schema-name DELIMITED 'DBS table name'
as being obsolete, and not to use.
 
The syntax should be:
DEFINE SCHEMA schema-name FROM TABLE 'DBS table name'
DEFINE SCHEMA schema-name FROM TABLE DELIMITED 'DBS table name'
 
When you tried this:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '@X';
it failed because you put the job variable in quotes.
We do not parse anything in quotes. The error is expected.
 
As you saw, when using job variables, this is the correct syntax:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED @X;
 
As for this:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '''  || @X || ''';
That is supposed to fail because there is nothing to concatenate. You just put the string "||@X||" as the table name.
 
But if you say this does not work:
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE';
then that is a problem.
Please re-run just this example and send me the output.

 

12 Feb 2014

Thanks for sticking with me on this!
Results:
This form did work:
DEFINE SCHEMA EOD_USAGE FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE';
=============================
but
DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED @X;
Which should be the correct syntax fails.
 
produces error;

T:\td>tbuild  -f eod_usage_tpt_pattern  -v "local.jobvars" -j donjob2123 -u  "LoadId='212345'  TargetTable='eod_usage' LoadFileName='*eod_usage*.gz' X='SONARTEST.EOD_USAGE'"

Teradata Parallel Transporter Version 14.10.00.01

TPT_INFRA: TPT04143: Error: Line 7 of Job Script File 'eod_usage_tpt_pattern':

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

Job script preprocessing failed.

Job terminated with status 8.

 

======================

By The way:
The latest manual (I think) Chapter 15: Best Practices Example 1: -pg 248

Teradata Parallel Transporter

User Guide

Release 14.10

B035-2445-082K

March 2013
Has the syntax this way: DEFINE SCHEMA TPT_SCHEMA DELIMITED @LoadTargetTable;
In any event I can't get any form of it to work with a variable.

feinholz 1234 posts Joined 05/08
12 Feb 2014

Thanks for pointing out the doc issue. We will get it fixed.
As for this:
In any event I can't get any form of it to work with a variable.
Sorry, I was reading it wrong.
I thought this did not work for you:
DEFINE SCHEMA EOD_USAGE FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE';
but this did:
DEFINE SCHEMA EOD_USAGE FROM TABLE DELIMITED @X;
 

--SteveF

feinholz 1234 posts Joined 05/08
12 Feb 2014

Ok, well you found a bug in our code and we will get it fixed.
The workaround is to use that obsolete syntax. We never deprecate the syntax, even when we make it obsolete.
So, it will work for you.
Try this (for now):
DEFINE SCHEMA EOD_USAGE DELIMITED @X;
Also, I am told that job variable name and references are case-sensitive.
I would like to get that changed as well.

--SteveF

13 Feb 2014

Thank you!
  I thought I tried every combination but must have missed this one because it did work!

rajesh_loke 1 post Joined 03/14
16 Mar 2014

thank you

You must sign in to leave a comment.