All Forums Tools
18 Jun 2014
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?
 
 

Thanks & Regards,

Chandrajit Santra

feinholz 1234 posts Joined 05/08
18 Jun 2014

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

18 Jun 2014

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.

Thanks & Regards,
Chandrajit Santra

feinholz 1234 posts Joined 05/08
18 Jun 2014

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

19 Jun 2014

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 Ran: tdload -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?

 

Thanks & Regards,
Chandrajit Santra

19 Jun 2014

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?

Thanks & Regards,
Chandrajit Santra

feinholz 1234 posts Joined 05/08
19 Jun 2014

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

19 Jun 2014

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.

Thanks & Regards,
Chandrajit Santra

JimHudd 12 posts Joined 11/11
22 Apr 2015

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.

feinholz 1234 posts Joined 05/08
22 Apr 2015

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

snelluri 8 posts Joined 01/10
25 Feb 2016

I am trying to copy a table from production to our Development. It is similar to the case what Chandrajit is trying to do. However i have a need that i need to restrict the data in the select statement (I don't have it in the code yet). But the current code looks like a simple table copy from prod to Dev. I am encountering weird issue. 

Teradata Parallel Transporter Version 15.00.00.02

TPT_INFRA: Syntax error at or near line 38 of Job Script File 'move_schema_on_demand_tpt.tpt':

TPT_INFRA: At ";" missing { DBMS_ LIBRARY_ LOG_ LOGGER_ OPERATOR_ SCHEMA_ SYSTEM_ TABLE_ WORKING_ } in Rule: Non-Executable Statement

Compilation failed due to errors. Execution Plan was not generated.

Job script compilation failed.

Job terminated with status 8.

 

Here is my code:

 

DEFINE JOB move_schema_on_demand

(

DEFINE OPERATOR W_1_o_move_schema_on_demand

TYPE UPDATE

SCHEMA *

ATTRIBUTES

(

VARCHAR UserName = 'XXXXXX', 

VARCHAR UserPassword = 'xxx', 

VARCHAR LogTable =  'TRGTDBNAME.targettable_log', 

VARCHAR TargetTable = 'TRGTDBNAME.targettable', 

INTEGER BufferSize, 

INTEGER ErrorLimit, 

INTEGER MaxSessions, 

INTEGER MinSessions, 

INTEGER TenacityHours, 

INTEGER TenacitySleep, 

VARCHAR AccountID, 

VARCHAR AmpCheck, 

VARCHAR DeleteTask, 

VARCHAR ErrorTable1, 

VARCHAR ErrorTable2, 

VARCHAR NotifyExit, 

VARCHAR NotifyExitIsDLL, 

VARCHAR NotifyLevel, 

VARCHAR NotifyMethod, 

VARCHAR NotifyString, 

VARCHAR PauseAcq, 

VARCHAR PrivateLogName, 

VARCHAR TdpId = 'TESTSYSTEM', 

VARCHAR TraceLevel, 

VARCHAR WorkingDatabase, 

VARCHAR WorkTable

);

 

DEFINE SCHEMA W_0_s_move_schema_on_demand FROM TABLE 'SRCDBNAME.Sourcetable' 

    ;

 

DEFINE OPERATOR W_0_o_move_schema_on_demand

TYPE EXPORT

SCHEMA W_0_s_move_schema_on_demand

ATTRIBUTES

(

VARCHAR UserName = 'xxxxx',

VARCHAR UserPassword = 'xxx', 

VARCHAR SelectStmt = 'SELECT * FROM SRCDBNAME.Sourcetable;', 

INTEGER BlockSize, 

INTEGER MaxSessions, 

INTEGER MinSessions, 

INTEGER TenacityHours, 

INTEGER TenacitySleep, 

INTEGER MaxDecimalDigits, 

VARCHAR AccountID, 

VARCHAR DateForm, 

VARCHAR NotifyExit, 

VARCHAR NotifyExitIsDLL, 

VARCHAR NotifyLevel, 

VARCHAR NotifyMethod, 

VARCHAR NotifyString, 

VARCHAR PrivateLogName, 

VARCHAR TdpId = 'PRODSYSTEM', 

VARCHAR TraceLevel, 

VARCHAR WorkingDatabase

);

 

APPLY

(

$INSERT 

)

TO OPERATOR

(

W_1_o_move_schema_on_demand[1]

)

SELECT * FROM OPERATOR

(

W_0_o_move_schema_on_demand[1]

);

);

 

I ran the script as follows: 

tbuild -f move_schema_on_demand_tpt.tpt -j move_schema_on_demand

 

User ids have sufficient access. Any input would be greatly appreciated. It seems to be a TPT issue. 

 

feinholz 1234 posts Joined 05/08
25 Feb 2016

Please try removing the parentheses from before and after the $INSERT.
Also, if you use templates, you can shorten your script quite a lot.
All you would need is this:
DEFINE JOB . . . .
(
DEFINE SCHEMA W_0_s_move_schema_on_demand FROM TABLE 'SRCDBNAME.Sourcetable' ;
 
APPLY $INSERT 
TO OPERATOR ($UPDATE)
SELECT * FROM OPERATOR ($EXPORT) ;
);
 
and provide the logon attribute on the command line or in the job variable file.
(I think we expand the $INSERT macro to include the parentheses and we do not support embedded parentheses on the APPLY.)
 

--SteveF

snelluri 8 posts Joined 01/10
25 Feb 2016

SOLVED: 
I used TPT templates and my problem is solved. Now i can move complete tables or partial set from the production to non-production using the following scripts:
TPT Script (by the way i used load operator, if you want to run it for multiple tables let the source and target table names be changing and your custom SQL as well, throush a shell or some other program and fire it. you should be good to go):

DEFINE JOB move_schema_on_demand

(

APPLY $INSERT 

TO OPERATOR

(

$LOAD()

        ATTRIBUTES

        (

        TargetTable = @LoadTargetTable

        )

    )

SELECT * FROM OPERATOR

(

$EXPORT()

        ATTRIBUTES

        (

          SelectStmt = 'Your customselect statement ;'

         )

);

);

 

Parameter file:

 

SourceTdpId = 'SRCSYSTEM',

SourceUserName = 'xxxxx',

SourceUserPassword = 'xxx',

SourceWorkingDatabase = 'SRCDB',

LoadTargetTable = 'TargetTable'

TargetTdpId = 'Target system',

TargetUserName = 'xxxxx',

TargetUserPassword = 'xxxxx',

TargetWorkingDatabase = 'TargetDB',

LoadLogTable = 'Target log table',

LoadErrorTable1 = 'Target error table 1',

LoadErrorTable2 = 'Target error table 2'

Thanks to tdload for giving me this thought. 

feinholz 1234 posts Joined 05/08
25 Feb 2016

Actually, sorry, I can further cimplify the script:
 
DEFINE JOB . . . .
(
APPLY $INSERT 
TO OPERATOR ($UPDATE)
SELECT * FROM OPERATOR ($EXPORT) ;
);
 
With this syntax, we will generate the schema from the SELECT statement provided to the Export operator (which references the source table).
Provide the SELECT statement on the command line, in the job variable file, or in the script (like the following):
 
DEFINE JOB . . . .
(
APPLY $INSERT 
TO OPERATOR ($UPDATE)
SELECT * FROM OPERATOR ($EXPORT
ATTRIBUTES(
SelectStmt = 'SELECT * FROM . . . .;')
)
) ;

);
 
 

--SteveF

snelluri 8 posts Joined 01/10
25 Feb 2016

Thanks Steve. I figured out the issue. Appreciate your help though. 

Shiv_Giri 7 posts Joined 11/11
13 May 2016

Really good script Nelluri. Will surely come in handy.

Ranjit1208 7 posts Joined 05/16
25 May 2016

Hello Nelluri/Steve,

 

 

We are facing the same issue when run below script. I could see script look good. May be a third eye can help here.

 

Please check below query and help us where we are going away.

 

Error Message:

 
TPT_INFRA: Syntax error at or near line 35 of Job Script File 'tpt.txt':
TPT_INFRA: At ";" missing { DBMS_ LIBRARY_ LOG_ LOGGER_ OPERATOR_ SCHEMA_ SYSTEM_ TABLE_ WORKING_ } in Rule: Non-Executable Statement

Compilation failed due to errors. Execution Plan was not generated.

 

##################################################

 

Script

 

DEFINE JOB TABLE_DATA_COPY

DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'

(

DEFINE OPERATOR FILE_WRITER ()

DESCRIPTION 'TERADATA FAST LOAD UTILITY'

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

VARCHAR TraceLevel             = 'NONE',

VARCHAR PauseAcq               = 'N',

INTEGER ErrorLimit             = 1,

INTEGER BufferSize             = 64,

INTEGER TenacityHours          = 2,

INTEGER TenacitySleep          = 1,

INTEGER MaxSessions            = 4,

INTEGER MinSessions            = 1,

VARCHAR FileName               = '@SrcTable.log',

VARCHAR Format                 = 'Delimited',

VARCHAR OpenMode               = 'Write',

VARCHAR IndicatorMode          = 'N',

VARCHAR TextDelimiter          = '|',

VARCHAR PrivateLogName         = 'LOD002C1',

VARCHAR TDPID                  = @env,

VARCHAR USERName               = @userName,

VARCHAR userpassword           = @userPwd,

VARCHAR AccountID,

VARCHAR ErrorTable1            = 'LOD002_e1',

VARCHAR ErrorTable2            = 'LOD002_e2',

VARCHAR LogTable               = 'PUBLISHLOGDB_TS2.LOD002_log',

VARCHAR WorkingDatabase        = 'PUBLISHLOGDB_TS2',

VARCHAR TargetWorkingDatabase  = 'PUBLISHLOGDB_TS2'

);

  

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

DEFINE OPERATOR TABLE_DATA_EXTRACT 

TYPE EXPORT

SCHEMA TABLE_SCHEMA

ATTRIBUTES

(

VARCHAR UserName =@userName, 

VARCHAR UserPassword =@userPwd, 

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 =@env,

VARCHAR TraceLevel, 

VARCHAR WorkingDatabase

);

 

 

APPLY TO OPERATOR (FILE_WRITER[1])

SELECT * FROM OPERATOR (TABLE_DATA_EXTRACT[2]);

);

ranjit

Ranjit1208 7 posts Joined 05/16
25 May 2016

Now getting a different issue.
TPT_INFRA: TPT04032: Error: Schema generation failed for table 'DBNAME.TABLE_NAME;' in DBS 'DBSTES':
  "GetTableSchema" status: 48.

ranjit

Ranjit1208 7 posts Joined 05/16
25 May 2016

We debugged the flow and got below and we are running in Teradata Parallel Transporter Version 14.10.00.02 
Entering FetchResponse: expected flavor: 169

Got flavor: 170

Leaving FetchResponse with error -996.

Entering FetchResponse: expected flavor: 11

Got flavor: 11

Leaving FetchResponse without error.

Entering FetchResponse: expected flavor: 12

Got flavor: 8

Leaving FetchResponse with error -996.

Entering DisconnectSession.

Leaving DisconnectSession with result: 0

TPT_INFRA: TPT04032: Error: Schema generation failed for table 'DB.TABLE_NAME;' in DBS 'td14':

  "GetTableSchema" status: 48.

 

Job script preprocessing failed.

Job terminated with status 12.

ranjit

feinholz 1234 posts Joined 05/08
25 May 2016

Which issue do you want me to address:
This one:
TPT_INFRA: At ";" missing { DBMS_ LIBRARY_ LOG_ LOGGER_ OPERATOR_ SCHEMA_ SYSTEM_ TABLE_ WORKING_ } in Rule: Non-Executable Statement
 
Or this one:
TPT_INFRA: TPT04032: Error: Schema generation failed for table 'DB.TABLE_NAME;' in DBS 'td14':
  "GetTableSchema" status: 48.

--SteveF

Ranjit1208 7 posts Joined 05/16
25 May 2016

second one please...
 
TPT_INFRA: TPT04032: Error: Schema generation failed for table 'DB.TABLE_NAME;' in DBS 'td14':
  "GetTableSchema" status: 48.

ranjit

feinholz 1234 posts Joined 05/08
25 May 2016

I will need more information.
Same script?
Is there a job variable file?
Still 14.10.00.02?

--SteveF

Ranjit1208 7 posts Joined 05/16
25 May 2016

yes, with the same script and we are passing db name, password, table from job variable file .
 
Yes, version is 14.10.00.02 

ranjit

feinholz 1234 posts Joined 05/08
26 May 2016

I guess I should have been more specific.
Please provide the job variable file.
And just to confirm, the script is exactly the same as you posted above, but you are now not getting the first error?

--SteveF

Ranjit1208 7 posts Joined 05/16
27 May 2016

Steve, sorry for confusion. Please find the script, job variable file and debugging output below.
 
1: Command we executed at unix
-----------------------------------------

/opt/teradata/client/14.10/tbuild/bin/tbuild -t -f tpt.txt -v jobvars2.txt

2: tpt.txt
-----------

DEFINE JOB TABLE_DATA_COPY
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(
        DEFINE OPERATOR FILE_WRITER
        DESCRIPTION 'TERADATA FAST LOAD UTILITY'
        TYPE DATACONNECTOR CONSUMER
        SCHEMA *
        ATTRIBUTES
        (
                VARCHAR TraceLevel             = 'all',
                VARCHAR PauseAcq               = 'N',
                INTEGER ErrorLimit             = 1,
                INTEGER BufferSize             = 64,
                INTEGER TenacityHours          = 2,
                INTEGER TenacitySleep          = 1,
                INTEGER MaxSessions            = 4,
                INTEGER MinSessions            = 1,
                VARCHAR FileName               = 'JOB_FAMILY.log',
                VARCHAR Format                 = 'Delimited',
                VARCHAR OpenMode               = 'Write',
                VARCHAR IndicatorMode          = 'N',
                VARCHAR TextDelimiter          = '|',
                VARCHAR PrivateLogName         = 'LOD002C1',
                VARCHAR TDPID                  = @env,
                VARCHAR USERName               = @userName,
                VARCHAR userpassword           = @userPwd,
                VARCHAR AccountID,
                VARCHAR ErrorTable1            = 'LOD002_e1',
                VARCHAR ErrorTable2            = 'LOD002_e2',
                VARCHAR LogTable               = 'STGDB_TS2.LOD002_log',
                VARCHAR WorkingDatabase        = 'STGDB_TS2',
                VARCHAR TargetWorkingDatabase  = 'STGDB_TS2',
                VARCHAR TargetTdpId = @env,
                VARCHAR TargetUserName = @userName,
                VARCHAR TargetUserPassword = @userPwd,
                VARCHAR SourceTdpId = @env,
                VARCHAR SourceUserName = @userName,
                VARCHAR SourceUserPassword = @userPwd
        );
  
DEFINE SCHEMA TABLE_SCHEMA FROM TABLE 'DB_TS2.JOB_FAMILY;';

        DEFINE OPERATOR TABLE_DATA_EXTRACT
        TYPE EXPORT
        SCHEMA TABLE_SCHEMA
        ATTRIBUTES
        (

                VARCHAR PrivateLogName         = 'LOD002P1',
        VARCHAR TraceLevel             = 'all',
        INTEGER Blocksize              = 64260,
        INTEGER TenacityHours          = 1,
        INTEGER TenacitySleep          = 1,
        INTEGER MaxSessions            = 4,
        INTEGER MinSessions            = 1,
        VARCHAR DateForm               = 'ANSIDATE',
        VARCHAR TDPID                  = @env,
        VARCHAR USERName               = @userName,
        VARCHAR userpassword           = @userPwd,
        VARCHAR AccountID,
        INTEGER Outlimit,
        VARCHAR SelectStmt             = 'SELECT * FROM DB_TS2.JOB_FAMILY;',
        VARCHAR WorkingDatabase        = 'STGDB_TS2',
        VARCHAR TargetWorkingDatabase = 'STGDB_TS2',
                VARCHAR TargetTdpId = @env,
                VARCHAR TargetUserName = @userName,
                VARCHAR TargetUserPassword = @userPwd,
                VARCHAR SourceTdpId = @env,
                VARCHAR SourceUserName = @userName,
                VARCHAR SourceUserPassword = @userPwd
        );
 
APPLY
        TO OPERATOR
        (
                FILE_WRITER[1]
        )
        SELECT * FROM OPERATOR
        (
                TABLE_DATA_EXTRACT[2]
        );

 
3: jobvars2.txt

env='testdb',
userName='EDW_RO',
userPwd='test_123'

4:Debugg log

Teradata Parallel Transporter Version 14.10.00.02
Entering GetTableSchema.
   tdpid:        'testdb'
   tdpid_len:    6
   user_name:    'EDW_RO'
   name_len:     10
   user_passwd:  <not displayable>
   passwd_len: 
   dbase_name:   'STGDB_TS2'
   dbase_len:  9
   table_name:   'DB_TS2.JOB_FAMILY;'
   tblname_len:  25
   schema_name:  'TABLE_SCHEMA'
   schema_fname: '/opt/teradata/client/14.10/tbuild/logs/T13332.C12'
   schema_fname: '/opt/teradata/client/14.10/tbuild/logs/T13332.C11'
Entering InitWorkArea.
   Uppercase charset name: 'ASCII'
Leaving InitWorkArea with result: 0
DBCHINI returns charset string: 'ASCII                         '
DBCHINI returns charset type:   ' '
set automaticRedrive to: 'E'
   allocated buffer for larger CLI msg at: 0x96339f8
   dbciMsgM set to: 4096
Entering SetSessionCharSet: char_set: 'ASCII'
   dbcptr->inter_ptr: 'ASCII                         '
   charset_type:      'N'
Leaving SetSessionCharSet.
dbcptr->inter_ptr: 'ASCII                         '
dbcptr->charset_type:   'N'
Entering ConnectSession.
   dbcptr->logon_len: 32
   dbcptr->logon_ptr: 'testdb/EDW_RO,'
Entering FetchResponse: expected flavor: 8
Got flavor: 8
Leaving FetchResponse without error.
Entering FetchResponse: expected flavor: 12
Got flavor: 12
Leaving FetchResponse without error.
Entering GetDBSLimits.
GetDBSLimit: dblimits.MaxDecimal: 38
Leaving GetDBSLimits: result: 0.
Entering GetDBSVersion.
         result: 0,
         supported: 1
        dbs_rel: '14.10.07.04                   '
        dbs_ver: '14.10.07.04                     '
Leaving GetDBSVersion.
Entering DoDatabaseSql.
   dbasename: 'STGDB_TS2'
   dbcptr->req_len:  19
   dbcptr->req_ptr: 'DATABASE STGDB_TS2;'
Entering FetchResponse: expected flavor: 8
Got flavor: 8
Leaving FetchResponse without error.
Entering FetchResponse: expected flavor: 11
Got flavor: 11
Leaving FetchResponse without error.
Entering FetchResponse: expected flavor: 12
Got flavor: 12
Leaving FetchResponse without error.
Exiting DoDatabaseSql.
Entering GetTableDefinition.
   tblname_len: 25 - tablename: 'DB_TS2.JOB_FAMILY;'
   dbcptr->max_decimal_returned: 38
   dbcptr->req_len:  40
   dbcptr->req_ptr: 'SELECT * FROM DB_TS2.JOB_FAMILY;;'
Entering FetchResponse: expected flavor: 8
Got flavor: 8
Leaving FetchResponse without error.
Entering FetchResponse: expected flavor: 169
Got flavor: 169
Leaving FetchResponse without error.
   StatementInfo parcel body length: 614
   ptr starts at 6992
   PBTILOUT: 3, PBTIID: 7, PBTILEN: 8
   14 bytes processed in statementinfo parcel
   ptr ends at 69a0
   ptr starts at 69a0
   PBTILOUT: 4, PBTIID: 7, PBTILEN: 0
   20 bytes processed in statementinfo parcel
   ptr ends at 69a6
   ptr starts at 69a6
   PBTILOUT: 1, PBTIID: 2, PBTILEN: 127
   From PclStatementInfo parcel, got the followings:
   layout len:    127
   column name:   'JOB_FAMILY_CODE'
   col name len:  15
   format:        'X(18)'
   format len:    5
   title:         'JOB_FAMILY_CODE'
   title len:     15
   column size:   18
   data type:     448
   UDT indicator: 0
   UDT name:     ''
   Precision:     0
   Scale:         0
   CharType:      1
   interval digits: 0
Entering AdjustColInfo
   DBS column type: 448
      use_stmtinfo: 1
   Precision: 0
       Scale: 0
   leaving with internal coltype: 'CV'
   leaving with tpt_datatype:'     7
   leaving with Precision:         0
   leaving with     Scale:         0
Leaving AdjustColInfo with result 0
   column name: 'JOB_FAMILY_CODE'
   column type:  7
   colsize:      18
   lobsize:      0
   column cs:    1
   precision:    0
   scale:        0
   interval:     0
   153 bytes processed in statementinfo parcel
   ptr ends at 6a2b
   ptr starts at 6a2b
   PBTILOUT: 1, PBTIID: 2, PBTILEN: 141
   From PclStatementInfo parcel, got the followings:
   layout len:    141
   column name:   'JOB_FAMILY_DESCRIPTION'
   col name len:  22
   format:        'X(90)'
   format len:    5
   title:         'JOB_FAMILY_DESCRIPTION'
   title len:     22
   column size:   90
   data type:     449
   UDT indicator: 0
   UDT name:     ''
   Precision:     0
   Scale:         0
   CharType:      1
   interval digits: 0
Entering AdjustColInfo
   DBS column type: 449
      use_stmtinfo: 1
   Precision: 0
       Scale: 0
   leaving with internal coltype: 'CV'
   leaving with tpt_datatype:'     7
   leaving with Precision:         0
   leaving with     Scale:         0
Leaving AdjustColInfo with result 0
   column name: 'JOB_FAMILY_DESCRIPTION'
   column type:  7
   colsize:      90
   lobsize:      0
   column cs:    1
   precision:    0
   scale:        0
   interval:     0
   300 bytes processed in statementinfo parcel
   ptr ends at 6abe
   ptr starts at 6abe
   PBTILOUT: 1, PBTIID: 2, PBTILEN: 131
   From PclStatementInfo parcel, got the followings:
   layout len:    131
   column name:   'EDW_CREATE_USER'
   col name len:  15
   format:        'X(30)'
   format len:    5
   title:         'EDW CREATE USER'
   title len:     15
   column size:   30
   data type:     452
   UDT indicator: 0
   UDT name:     ''
   Precision:     0
   Scale:         0
   CharType:      1
   interval digits: 0
Entering AdjustColInfo
   DBS column type: 452
      use_stmtinfo: 1
   Precision: 0
       Scale: 0
   leaving with internal coltype: 'CF'
   leaving with tpt_datatype:'     5
   leaving with Precision:         0
   leaving with     Scale:         0
Leaving AdjustColInfo with result 0
   column name: 'EDW_CREATE_USER'
   column type:  5
   colsize:      30
   lobsize:      0
   column cs:    1
   precision:    0
   scale:        0
   interval:     0
   437 bytes processed in statementinfo parcel
   ptr ends at 6b47
   ptr starts at 6b47
   PBTILOUT: 1, PBTIID: 2, PBTILEN: 165
   From PclStatementInfo parcel, got the followings:
   layout len:    165
   column name:   'EDW_CREATE_DATETIME'
   col name len:  19
   format:        'YYYY-MM-DDBHH:MI:SS'
   format len:    19
   title:         'EDW CREATE DATE'
   title len:     15
   column size:   19
   data type:     764
   UDT indicator: 0
   UDT name:     ''
   Precision:     0
   Scale:         0
   CharType:      0
   interval digits: 0
Entering AdjustColInfo
   DBS column type: 764
      use_stmtinfo: 1
   Precision: 0
       Scale: 0
   leaving with internal coltype: 'TS'
   leaving with tpt_datatype:'     43
   leaving with Precision:         0
   leaving with     Scale:         0
Leaving AdjustColInfo with result 0
   column name: 'EDW_CREATE_DATETIME'
   column type:  43
   colsize:      19
   lobsize:      0
   column cs:    0
   precision:    0
   scale:        0
   interval:     0
   608 bytes processed in statementinfo parcel
   ptr ends at 6bf2
   ptr starts at 6bf2
   PBTILOUT: 4, PBTIID: 2, PBTILEN: 0
   614 bytes processed in statementinfo parcel
   ptr ends at 6bf8
Entering FetchResponse: expected flavor: 169
Got flavor: 170
Leaving FetchResponse with error -996.
Entering FetchResponse: expected flavor: 11
Got flavor: 11
Leaving FetchResponse without error.
Entering FetchResponse: expected flavor: 12
Got flavor: 8
Leaving FetchResponse with error -996.
Entering DisconnectSession.
Leaving DisconnectSession with result: 0
TPT_INFRA: TPT04032: Error: Schema generation failed for table 'DB_TS2.JOB_FAMILY;' in DBS 'testdb':
  "GetTableSchema" status: 48.
 
Job script preprocessing failed.
Job terminated with status 12.

EDW_RO user has create access onto STGDB_TS2.
 
Please review the code and help us to identify the issue.

ranjit

Ranjit1208 7 posts Joined 05/16
01 Jun 2016

Hi Steve,
Could you please help to review the script and correct if we are going away.
Thanks in advance.
Regards,
Ranjit

ranjit

feinholz 1234 posts Joined 05/08
02 Jun 2016

Before I can provide the final answers, can you please share the table DDL with me?
I have a suspicion about what is happening, but would like to look at the table definition anyway.
 
I believe that the issue you are facing is due to a bug that we fixed in 14.10.00.15.
It is recommended that you upgrade to that efix at a minimum.
 
As to the script, you have some incorrect information.
For the File Writer operator, you have a lot of attributes defined that do not belong to that operator.
Possibly a cut and paste issue, and they will be ignored by the DataConnector operator, but it should be cleaned up.
So go one step further, it would be best if you started using operator templates.
These templates will remove the need for DEFINE OPERATOR objects in the script.
Your script would/should look like this:
 

DEFINE JOB TABLE_DATA_COPY
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(

DEFINE SCHEMA TABLE_SCHEMA FROM TABLE 'DB_TS2.JOB_FAMILY;';

APPLY TO OPERATOR ($FILE_WRITER ATTRIBUTES (
TraceLevel = 'none',
FileName = 'JOB_FAMILY.log',
Format = 'Delimited',
OpenMode = 'Write',
IndicatorMode = 'N',
TextDelimiter = '|',
PrivateLogName = 'LOD002C1' )
)
SELECT * FROM OPERATOR ($EXPORT(TABLE_SCHEMA) ATTRIBUTES (
PrivateLogName = 'LOD002P1',
TraceLevel = 'none',
TenacityHours = 1,
TenacitySleep = 1,
MaxSessions = 4,
MinSessions = 1,
DateForm = 'ANSIDATE',
TdpId = @env,
UserName = @userName,
UserPassword = @userPwd,
SelectStmt = 'SELECT * FROM DB_TS2.JOB_FAMILY;',
WorkingDatabase = 'STGDB_TS2' )
);
);
I removed BlockSize from the Export operator because you always want us to use the default.
Basically, always allow us to use defaults for all attributes unless there are specific reasons why you want to override them.
Also, do not set TraceLevel to 'all'. You only want to do that if Teradata Support (GSC) asks you to in order to get a trace to debug a problem.
Setting TraceLevel will slow down your job and your logs will be much larger than you will want.
 

--SteveF

feinholz 1234 posts Joined 05/08
02 Jun 2016

BTW, I did not want to throw too much at you at one time. The script I provided can be made even shorter, by moving all of the attribute settings into the job variable file, and having you use pre-defined job variables to assign the values.
 
The script would look something like this:
 

DEFINE JOB TABLE_DATA_COPY
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(
    
   APPLY TO OPERATOR ($FILE_WRITER)
   SELECT * FROM OPERATOR ($EXPORT));
);

and the job variable file would look like this:

SourceTdpId            = 'testdb',
SourceUserName         = 'EDW_RO',
SourceUserPassword     = 'test_123',
ExportPrivateLogName   = 'LOD002P1',
ExportTraceLevel       = 'none',
ExportTenacityHours    = 1,
ExportTenacitySleep    = 1,
ExportMaxSessions      = 4,
ExportMinSessions      = 1,
ExportDateForm         = 'ANSIDATE',
ExportSelectStmt       = 'SELECT * FROM DB_TS2.JOB_FAMILY;',
ExportWorkingDatabase  = 'STGDB_TS2',

FileWriterFormat           = 'Delimited',
FileWriterTraceLevel       = 'none',
FileWriterFileName         = 'JOB_FAMILY.log',
FileWriterOpenMode         = 'Write',
FileWriterIndicatorMode    = 'N',
FileWriterTextDelimiter    = '|',
FileWriterPrivateLogName   = 'LOD002C1'

As you can see, your script now becomes a little more general purpose, no hardcoding. The job is controlled more by the metadata in the job variable file.
 

--SteveF

You must sign in to leave a comment.