TPT without schema for STREAM Operator

Tools & Utilities
Enthusiast

TPT without schema for STREAM Operator

Hi All,

With TTU 14, we could create templates for TPT without the need to specify SCHEMA definition. I was able to successfully implement it for EXPORT, SELECTOR and LOAD operator. However, I am unable to do it for STREAM. I get the following erorr when I try it on the same lines of LOAD. Please find the script and the exact error.

Script

USING CHARACTER SET @v_utf 

DEFINE JOB load_template 

(

  APPLY ('INSERT INTO MyTable;') TO OPERATOR ($STREAM() ATTR (TdpId=@v_tdpid, UserName=@v_userid, UserPassword=@v_password, MaxSessions=@v_sessions, DateForm = 'ANSIDATE', INTEGER Pack = @v_pack))

  SELECT * FROM OPERATOR($FILE_READER(DELIMITED @v_tablename) ATTR (FileName=@v_file_name, Format=@v_format, TextDelimiter=@v_delimiter_value, OpenMode = 'Read', IndicatorMode = @v_mode));

);

Error

TPT_INFRA: Syntax error at or near line 7 of Job Script File 'stream_template_delimited.out.ctl':

TPT_INFRA: At "INTEGER" missing { REGULAR_IDENTIFIER_ EXTENDED_IDENTIFIER_ EXTENDED_IDENTIFIER_NO_N_ } in Rule: Regular Identifier

TPT_INFRA: Syntax error at or near line 7 of Job Script File 'stream_template_delimited.out.ctl':

TPT_INFRA: TPT03247: Rule: Attribute Value Specification

TPT_INFRA: Semantic error at or near line 9 of Job Script File 'stream_template_delimited.out.ctl':

TPT_INFRA: TPT03111: Rule: Restricted APPLY Statement

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

Job script compilation failed.

Please let me know if you need any additional information.

Thanks,

Naveen K

12 REPLIES
Teradata Employee

Re: TPT without schema for STREAM Operator

Try removing the word INTEGER from in front of Pack.

-- SteveF
Enthusiast

Re: TPT without schema for STREAM Operator

Yes, thanks. That error is gone now. However, I am facing following error after I have made that change to the script. 

$STREAM: TPT16102: An error occurred while submitting the following DML statement: 

   USING "col1"(VARCHAR(60)),"col2"(VARCHAR(12000)),"col3"(VARCHAR(19)),"col4"(VARCHAR(2)),"col5"(VARCHAR(12)),"col6"(VARCHAR(40)),"col7"(VARCHAR(60)),"col8"(VARCHAR(33)),"col9"(VARCHAR(1500)),"col10"(VARCHAR(900)),"col11"(VARCHAR(18))INSERT INTO MyTable;

$STREAM: TPT10508: RDBMS error 3707: Syntax error, expected something like a 'SELECT' keyword or a 'VALIDTIME' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword between the word 'its_content_ref_thin' and ';'.

Teradata Employee

Re: TPT without schema for STREAM Operator

Please provide the entire script (and job variable file, if you use one), and the complete output from the console.

Thanks!

-- SteveF
Enthusiast

Re: TPT without schema for STREAM Operator

I don't use the job variable file. Complete script is same as the original post except that I have removed INTEGER keyword. 

I am still pasting the complete script and the console output.

Script

USING CHARACTER SET @v_utf 

DEFINE JOB load_template 

(

  APPLY ('INSERT INTO MyTable;') TO OPERATOR ($STREAM() ATTR (TdpId=@v_tdpid, UserName=@v_userid, UserPassword=@v_password, MaxSessions=@v_sessions, DateForm = 'ANSIDATE', Pack = @v_pack))

  SELECT * FROM OPERATOR($FILE_READER(DELIMITED @v_tablename) ATTR (FileName=@v_file_name, Format=@v_format, TextDelimiter=@v_delimiter_value, OpenMode = 'Read', IndicatorMode = @v_mode));

);

Console Output

Teradata Parallel Transporter Version 14.10.00.10 

Job log: /opt/teradata/client/14.10/tbuild/logs/MyTable_10052016004841_mytdpid-313291.out

Job id is MyTable_10052016004841_mytdpid-313291, running on myhost

Teradata Parallel Transporter Stream Operator Version 14.10.00.10

Teradata Parallel Transporter DataConnector Operator Version 14.10.00.10

$STREAM: private log not specified

$FILE_READER: Instance 1 directing private log report to 'dtacop-etlt-20478-1'.

$FILE_READER: DataConnector Producer operator Instances: 1

$FILE_READER: ECI operator ID: '$FILE_READER-20478'

$FILE_READER: Operator instance 1 processing file '/mypath/MyTable_selector.out'.

$STREAM: Start-up Rate: UNLIMITED statements per Minute

$STREAM: Operator Command ID for External Command Interface: $STREAM20477

$STREAM: connecting sessions

$STREAM: TPT16102: An error occurred while submitting the following DML statement: 

   USING "col1"(VARCHAR(60)),"col2"(VARCHAR(12000)),"col3"(VARCHAR(19)),"col4"(VARCHAR(2)),"col5"(VARCHAR(12)),"col6"(VARCHAR(40)),"col7"(VARCHAR(60)),"col8"(VARCHAR(33)),"col9"(VARCHAR(1500)),"col10"(VARCHAR(900)),"col11"(VARCHAR(18))INSERT INTO MyTable;

$STREAM: TPT10508: RDBMS error 3707: Syntax error, expected something like a 'SELECT' keyword or a 'VALIDTIME' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword between the word 'MyTable' and ';'.

$STREAM: disconnecting sessions

$STREAM: Total processor time used = '0.06 Second(s)'

$STREAM: Start : Tue May 10 00:48:47 2016

$STREAM: End   : Tue May 10 00:48:49 2016

$FILE_READER: Total files processed: 0.

Job step MAIN_STEP terminated (status 12)

Job MyTable_10052016004841_mytdpid terminated (status 12)

Job start: Tue May 10 00:48:42 2016

Job end:   Tue May 10 00:48:49 2016

Teradata Employee

Re: TPT without schema for STREAM Operator

I believe your INSERT statement in the APPLY clause is not complete.

Where is the VALUES clause?

-- SteveF
Enthusiast

Re: TPT without schema for STREAM Operator

I don't want to provide values because I want to template the control file and be able to use it for any table. For example, I used following LOAD script that I can use for any table by passing table name as input parameter. I am hopind same thing to happen with STREAM also.

LOAD script example

USING CHARACTER SET @v_utf 

DEFINE JOB load_template 

(

  APPLY $INSERT TO OPERATOR ($LOAD() ATTR (TdpId=@v_tdpid, UserName=@v_userid, UserPassword=@v_password, TargetTable=@v_tablename, MaxSessions=@v_sessions, DateForm = 'ANSIDATE'))

  SELECT * FROM OPERATOR($FILE_READER(DELIMITED @v_tablename) ATTR (FileName=@v_file_name, Format=@v_format, TextDelimiter=@v_delimiter_value, OpenMode = 'Read', IndicatorMode = @v_mode));

);

Teradata Employee

Re: TPT without schema for STREAM Operator

The two examples are not equivalent.

In your Load operator example, you are using the $INSERT macro.

TPT takes that macro and generates the entire INSERT statement (with the VALUES clause).

In your Stream script example, you provided an incomplete INSERT DML statement.

-- SteveF
Enthusiast

Re: TPT without schema for STREAM Operator

I had tried both ways and neither of them worked. Coming to the main question, is it possible to templatize STREAM operator based loading?

Teradata Employee

Re: TPT without schema for STREAM Operator

Not sure what you mean by "neither of them worked". The incomplete INSERT statement will never work.

If $INSERT did not work, please provide the information showing the failure.

Thanks!

-- SteveF