DEFINE SCHEMA target_schema FROM TABLE

Tools

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.

Tags (2)
26 REPLIES
Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

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

Re: DEFINE SCHEMA target_schema FROM TABLE

I'm on Windows

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

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

-- SteveF

Re: DEFINE SCHEMA target_schema FROM TABLE

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

Re: DEFINE SCHEMA target_schema FROM TABLE

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  

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

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

Re: DEFINE SCHEMA target_schema FROM TABLE

 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.

Re: DEFINE SCHEMA target_schema FROM TABLE

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]);

);

);

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

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