Can we pass schema and sql statement as variables to tpt script

Tools
Enthusiast

Can we pass schema and sql statement as variables to tpt script

Hi,

Is it possible to pass schema definition and sql statement in job variables file or command line parameters? %INCLUDE needs exact filename to be mentioned for schema definition which makes the tpt script for one particular extract. We are working on a generic script for 100+ tables so that we can pass the schema and sql in jv file along with instances.

DEFINE SCHEMA SOCPARC_SUBSET_PP

DESCRIPTION 'DW_ADDRESS'

(

@SCHEMADEF

);

VARCHAR SelectStmt        = @SQL

Job Variable script:

SCHEMADEF=`cat xyz.txt`

SQL=`cat abc.txt`

2 REPLIES
Teradata Employee

Re: Can we pass schema and sql statement as variables to tpt script

Symbol substitution in TPT is exactly that. A "substitution".

It is not "substitution and execute".

Thus, having "cat" in the string will not actually execute the "cat" command.

You can do:

   %INCLUDE @schema_file_name

and then in the job variable file, define schema_file_name as xyz.txt.

For the SelectStmt, no the job variable file must have the actual SELECT statement assigned to the job variable named SQL.

Or, on the command line, you can use the -u command line option and assign SQL=SELECT . . . .;

-- SteveF
Enthusiast

Re: Can we pass schema and sql statement as variables to tpt script

Hi Steve,

Thanks for the inputs on this. We were able to pass the values having a shell wrapper around the actual tbuild command and also schema file as you mentioned. We can use similar approach for INSERT as well.

In Shell:

sql=`cat sql.txt`

ins=`cat insert.txt`

schema="schema.txt"

tbuild -f tpt_script -u "sql='$sql' schema='$schema'"

tpt script:

INCLUDE @schema

sql=@sql