All Forums Tools
nitindixit 6 posts Joined 05/12
12 Oct 2012
Using templates for operators provided in TPT

Hi,
How can we use the templates for the operators provided in TPT?
I am having problem in providing schema for producer operators...
Here is the script
DEFINE JOB jobname
DESCRIPTION '---'
(
DEFINE SCHEMA countries_SCHEMA @LoadTargetTable;
APPLY $INSERT @LoadTargetTable TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($EXPORT('countries_SCHEMA'));
);
All variables are in seperate job variable file.
Error is - Unmatched function parenthesis -- '(' -- for operator EXPORT
I went through the manuals but counldn't come up with solutions.
I am using Teradata version - 13.10.03.08
Iam not sure what iam doing wrong ... please help me out

feinholz 1234 posts Joined 05/08
12 Oct 2012

I am not sure this will totally solve the problem, but you should not be putting a table name after the $INSERT.

--SteveF

nitindixit 6 posts Joined 05/12
15 Oct 2012

Hi
Iam pasting a tpt script snippet from TPT User Guide 14.0,  (page - 240)
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]);
);
Is this snippet wrong? Tablename is present after $INSERT.
 
Could you tell me about any other way to make TPT script reusable, upto a certain limit..
Thanks
Nitin
 

feinholz 1234 posts Joined 05/08
15 Oct 2012

Snippet is not wrong. I was unaware of that new syntax enhancement.
Please provide the entire (real) script (not the snippet from the manual).
 

--SteveF

feinholz 1234 posts Joined 05/08
15 Oct 2012

Based on the script in the very first post, if that is indeed the script you submitted, please remove the single quotes from around the countries_SCHEMA after the $EXPORT.
 

--SteveF

feinholz 1234 posts Joined 05/08
15 Oct 2012

Please provide me with the contents of the job variable file. When we take the script sample from the very first post (and provide our own values for the job variables), we do not get the same issue as you.
 

--SteveF

nitindixit 6 posts Joined 05/12
17 Oct 2012

Hi
Here is the script - sample_country.tpt
 
DEFINE JOB load_sample_country
(

DEFINE SCHEMA countries_schema @LoadTargetTable;
APPLY $INSERT @LoadTargetTable
TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($EXPORT(countries_schema));
);

 
Here is the job variable file - jobvar.txt
LoadPrivateLogName = 'loadoper_private_log',
LoadTargetTable = 'sample_country',
LoadWorkingDatabase = 'dbname',
LoadErrorTable1 = 'e1__sample_country',
LoadErrorTable2 = 'e2_sample_country',
LoadLogTable = 'log_sample_country',
LoadMaxSessions = 5,
LoadMinSessions = 1,

ExportPrivateLogName = 'exportoper_private_log',
ExportMaxSessions = 5,
ExportMinSessions = 1,
ExportSelectStmt = 'select * from dbname.countries;',

TargetUserName = 'username',
TargetUserPassword = 'password',
TargetTdpId = 'ipaddress',

SourceUserName = 'username',
SourceUserPassword = 'password',
SourceTdpId = 'ipaddress'

 
Here sample_country and countries table have same structure.
"sample_country" is my target table while iam extracting data from "countries" table.
TPT Version - 13.10.00.02
 
Iam getting the same error -
Unmatched function parenthesis -- '(' -- for operator EXPORT.
Please help me out. I really can't understand what  iam doing wrong.
 
 
 
 

feinholz 1234 posts Joined 05/08
17 Oct 2012

Ok, spoke with the engineer:
Explicit schema association via the statement
                SELECT * FROM OPERATOR ($EXPORT(countries_schema));
is not supported in 13.10; they need to remove the schema reference:
                SELECT * FROM OPERATOR ($EXPORT());
You can refer them to the  'Inferred Schemas' sub-section of the section 'Simplifying Scripts with Operator Templates and Inferred Schemas' in Chapter 27 of the 13.10 TPT User Guide for specifics on how template operators get associated with schemas.

--SteveF

nitindixit 6 posts Joined 05/12
18 Oct 2012

Hi Feinholz
Thanks for the reply.
I searched the Teradata website but could not find user guide for tpt version 13.1.
I could find version 13 is present , and it does not contain 'Simplifying Scripts with Operator Templates and Inferred Schemas' in Chapter 27. Could you send me a link from where i can find the user guide 13.1.
Thanks again
Nitin
 

feinholz 1234 posts Joined 05/08
18 Oct 2012

All of our docs for all releases are in the same place.
Thus, if you can see the TPT 13.00 docs, you should be able to see the TPT 13.10 docs.
http://www.info.teradata.com/
 

--SteveF

sahmed448 8 posts Joined 02/10
18 Apr 2013

Hello,
Can someone please help me with a sample TPT script using update operator to load multiple tables based on the condition: if the value of the first column of the row in the input data file is 'abc' then load table 1
if the value is 'xyz' then load table 2.
Input file looks something like this:
abc 100 mike   IT  programer
abc 101 james  sales    Salesmen
abc 200 frank  DBA      administration
xyz 10  Marketing
xyz 11  administration
abc 600 John   Marketing executive
abc 900 Sam   Hardware  workshop
I checked the manual but i coudnt find any direct sample script close to what i am trying to achieve.
 
Here is the code which i have written and doesnt work:
Erro: UPDATE_OPERATOR: TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match "IN_Cd".
 
DEFINE JOB FILE_LOAD
DESCRIPTION 'Load 2 TD tables from a file'
(
DEFINE SCHEMA Load_Test_Schema
(
     IN_Table_Name  VARCHAR(30)
    ,IN_Cd     VARCHAR(255)
    ,IN_Field_1     VARCHAR(1000)
    ,IN_Field_2     VARCHAR(1000)
    ,IN_Field_3     VARCHAR(1000)
    ,IN_Field_4     VARCHAR(1000)
    ,IN_Field_5     VARCHAR(1000)
);
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log1',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA Load_Test_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log1',
VARCHAR DirectoryPath = '%DDDD%',
VARCHAR FileName = 'testfile.dat',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter ='|'
);
DEFINE OPERATOR UPDATE_OPERATOR
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'update_log',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR LogTable = 'ERRORSDB.LG_Load2',
VARCHAR ARRAY TargetTable = ['devDB.Stagingtble1','devDB.Stagingtble2'],
VARCHAR ARRAY ErrorTable1 = ['ERRDB.ET_Stagingtble1','ERRDB.ET_Stagingtble2'],
VARCHAR ARRAY ErrorTable2 = ['ERRDB.UV_Stagingtble1','ERRDB.UV_Stagingtble2']
);

APPLY CASE WHEN  (IN_Table_Name = 'abc')
THEN
'INSERT INTO devDB.Stagingtble1
        (col1, col2, col3)
  VALUES(:IN_Cd
 ,:IN_Field_1
 ,:IN_Field_2)
;'
WHEN  (IN_Table_Name = 'xyz')
THEN
'INSERT INTO devDB.Stagingtble2
            (col1 
 ,col2 
         )
VALUES ( :IN_Field_4
 ,:IN_Field_5)
;'
END
TO OPERATOR (UPDATE_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);
);
 
Thanks
sahmed

TonyL 20 posts Joined 12/09
23 Apr 2013

The Load_Test_Schema schema definition has 7 fields.
The input file should have 7 fields.

sahmed448 8 posts Joined 02/10
27 Apr 2013

Hi Tony, Below is the sample rows from the data file and my actual TPT script:
Stg_Imd_Production_Company|tt1790885|Columbia Pictures||||||||
Stg_Imd_Production_Company|tt1790885|Annapurna Pictures||||||||
Stg_Imd_Country_Rating|tt1790885|us||R||||||
Stg_Imd_Production_Company|tt1790885|First Light Production||||||||
Stg_Imd_Country_Gross_BO|tt1790885|us|USD|95314281||||||
Stg_Imd_Award|tt1790885|Academy Awards, USA|2013|Oscar|Won|||1|Best Achievement in Sound Editing||
Stg_Imd_Award|tt1790885|Academy Awards, USA|2013|Oscar|Nominated|||2|Best Motion Picture of the Year||
Stg_Imd_Country_Rating|tt1790885|au||M||||||
Stg_Imd_Keyword|tt1790885|navy-seal|2|||||||
DEFINE JOB FILE_LOAD
DESCRIPTION 'Load 2 TD tables from a file'
(
DEFINE SCHEMA ImxLoad2_Schema
(
     IN_Table_Name     VARCHAR(30)
    ,IN_Imdb_Cd   VARCHAR(255)
    ,IN_Imdb_Field_1   VARCHAR(1000)
    ,IN_Imdb_Field_2   VARCHAR(1000)
    ,IN_Imdb_Field_3   VARCHAR(1000)
    ,IN_Imdb_Field_4   VARCHAR(1000)
    ,IN_Imdb_Field_5   VARCHAR(1000)
    ,IN_Imdb_Field_6   VARCHAR(1000)
    ,IN_Imdb_Field_7   VARCHAR(1000)
    ,IN_Imdb_Field_8   VARCHAR(1000)
    ,IN_Imdb_Field_9   VARCHAR(3500)
);
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log1',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA ImxLoad2_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log1',
VARCHAR DirectoryPath = '%DDATADIR%',
VARCHAR FileName = 'imdb.dat',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter ='|'
);
DEFINE OPERATOR UPDATE_OPERATOR
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR ARRAY TargetTable = ['staging_dev.Stg_Imd_Award','staging_dev.Stg_Imd_Production_Company'],
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR  LogTable = 'ERRORSDB_DEV.LG_ImxLoad2',
VARCHAR PrivateLogName = 'update_log'
);

APPLY
CASE
WHEN  IN_Table_Name = 'Stg_Imd_Award'
THEN  'INSERT INTO staging_dev.Stg_Imd_Award
        (Imdb_Title_Cd  
 ,Awarding_Entity 
 ,Award_Year 
 ,Award  
 ,Results  
 ,Year_Order 
 ,Year_Order_Name
 ,Award_Sequence
 ,Category
 ,Comments)
  VALUES(:Imdb_Cd
 ,:IN_Imdb_Field_1
 ,:IN_Imdb_Field_2
 ,:IN_Imdb_Field_3
 ,:IN_Imdb_Field_4
 ,:IN_Imdb_Field_5
 ,:IN_Imdb_Field_6
 ,:IN_Imdb_Field_7
 ,:IN_Imdb_Field_8
 ,:IN_Imdb_Field_9)
;'
WHEN  IN_Table_Name = 'Stg_Imd_Production_Company'
THEN
'INSERT INTO staging_dev.Stg_Imd_Production_Company
            (Imdb_Title_Cd 
 ,Production_Company 
 ,Notes
         )
VALUES (:IN_Imdb_Cd
 ,:IN_Imdb_Field_1
 ,:IN_Imdb_Field_2)
;'
END
IGNORE DUPLICATE INSERT ROWS
TO OPERATOR (UPDATE_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);
);
 
 
Error: TPT_INFRA: Syntax error at or near line 113 of Job Script File imxLoad1.tpt.run:
TPT_INFRA: At "APPLY"
Compilation failed due to errors. Execution Plan was not generated.
 
 
This is how i defined the record layout in my old Multi-old script:
.LAYOUT Imd_Record_Layout;
.FILLER Table_Name 1 VARCHAR(30);
.FIELD Imdb_Cd * VARCHAR(255);
.FIELD Imdb_Field_1 * VARCHAR(1000);
.FIELD Imdb_Field_2 * VARCHAR(1000);
.FIELD Imdb_Field_3 * VARCHAR(1000);
.FIELD Imdb_Field_4 * VARCHAR(1000);
.FIELD Imdb_Field_5 * VARCHAR(1000);
.FIELD Imdb_Field_6 * VARCHAR(1000);
.FIELD Imdb_Field_7 * VARCHAR(1000);
.FIELD Imdb_Field_8 * VARCHAR(1000);
.FIELD Imdb_Field_9 * VARCHAR(3500);
 
 

TonyL 20 posts Joined 12/09
29 Apr 2013

The "IGNORE DUPLICATE INSERT ROWS" DML option is in the wrong place.
The "IGNORE DUPLICATE INSERT ROWS" DML option is after the DML expression.
For example:
APPLY
CASE
WHEN <search condition>
THEN <DML expression>
IGNORE DUPLICATE INSERT ROWS
WHEN <search condition>
THEN <DML expression>
IGNORE DUPLICATE INSERT ROWS
END

sahmed448 8 posts Joined 02/10
29 Apr 2013

Thanks Tony, i am now getting the following error after applying the fixes:I have checked the input file and also the schema defination and they look good and number of columns etcc. matches.I cant get pass this step now.
Any help will be appreciated.
 
TPT_INFRA: TPT04101: Warning: TMSM failed to initialize
Teradata Parallel Transporter Coordinator Version 13.10.00.04
Teradata Parallel Transporter Executor Version 13.10.00.04
Teradata Parallel Transporter Executor Version 13.10.00.04
Teradata Parallel Transporter Executor Version 13.10.00.04
Teradata Parallel Transporter Executor Version 13.10.00.04
Teradata Parallel Transporter DataConnector Version 13.10.00.04
FILE_READER: TPT19206 Attribute 'TraceLevel' value reset to 'MILESTONES'.
FILE_READER: TPT19206 Attribute 'TraceLevel' value reset to 'MILESTONES'.
Teradata Parallel Transporter Update Operator Version 13.10.00.03
FILE_READER Instance 1 directing private log report to 'dataconnector_log1-1'.
FILE_READER Instance 2 directing private log report to 'dataconnector_log1-2'.
UPDATE_OPERATOR: private log specified: update_log
FILE_READER: TPT19008 DataConnector Producer operator Instances: 2
FILE_READER: TPT19003 ECI operator ID: FILE_READER-4325460
FILE_READER: TPT19222 Operator instance 1 processing file '/whvm2etlhome/ddata/imx/imdb.dat'.
FILE_READER: TPT19012 No files assigned to instance 2.  This instance will be inactive.
UPDATE_OPERATOR: connecting sessions
UPDATE_OPERATOR: preparing target table(s)
UPDATE_OPERATOR: entering DML Phase
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
UPDATE_OPERATOR: TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match ''Imdb_Cd''.
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
UPDATE_OPERATOR: disconnecting sessions
FILE_READER: TPT19221 Total files processed: 0.
UPDATE_OPERATOR: Total processor time used = '0.626918 Second(s)'
UPDATE_OPERATOR: Start : Mon Apr 29 15:57:57 2013
UPDATE_OPERATOR: End   : Mon Apr 29 15:58:17 2013
Job step MAIN_STEP terminated (status 8)
Job imxLoad1.tpt.run.out terminated (status 8)
Total available memory:          10002340
Largest allocable area:          10002340
Memory use high water mark:         61216
Free map size:                       1024
Free map use high water mark:          26
Free list use high water mark:          0
 

TonyL 20 posts Joined 12/09
30 Apr 2013

The value name ":Imdb_Cd" is incorrect in the first INSERT statement.
The correct name is ":IN_Imdb_Cd".

sahmed448 8 posts Joined 02/10
07 May 2013

Thanks a lot Tony, you are right it should be :IN_IMDB_CD.It fixed the problem and the script works like a charm.
 
Thanks Again..

vijaydf 16 posts Joined 06/12
26 Jun 2013

i just noticed I am getting the below message in TPT.but the job is not aborting....
File_Reader: TPT19012 No files assigned to instance 6.  This instance will be inactive.
File_Reader: TPT19012 No files assigned to instance 5.  This instance will be inactive.
File_Reader: TPT19012 No files assigned to instance 3.  This instance will be inactive.
File_Reader: TPT19012 No files assigned to instance 2.  This instance will be inactive.
File_Reader: TPT19012 No files assigned to instance 4.  This instance will be inactive.
 
Why is that the 5 instance are inactive.  i have defined 6 Reader insttance. but why only one instance is active.
TO OPERATOR (File_Loader[3])
SELECT * FROM OPERATOR(File_Reader[6]);
 
Can you someone please help me in understanding. does is make any difference if i give File_Reader[1] & File_Reader[6]?

 
 

Vijay Mani

Fred 1096 posts Joined 08/04
26 Jun 2013

Normally each data connector producer instance reads a separate file, e.g. when you are using FileList or DirectoryPath options.
 
There is a MultipleReaders='Y' option if you really want multiple instances to do "interleaved" reads from the same file.

vijaydf 16 posts Joined 06/12
26 Jun 2013

Is MultipleReaders='Y' is applicable to named pipe input file? we are using tpt 13.10

Vijay Mani

feinholz 1234 posts Joined 05/08
26 Jun 2013

MultipleReaders only applies to flat files.
There is no way for multiple processes to read from a single pipe.
 

--SteveF

You must sign in to leave a comment.