TPT without schema for STREAM Operator

Tools
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