I am working on parameterizing the TPTLOAD job script that loads a target table in teradata.I have a schema file where the target table schema has been defined. I now want to pass the schema file name to the TPTLOAD job script dynamically i.e as a parameter as a part of the tbuild command.
For instance we have the TPTLOAD job script as
USING CHARACTER SET UTF16
DEFINE JOB EXTRACT_FILE_LOAD
DESCRIPTION 'Export rows from a Teradata table to a file'
USING CHARACTER SET UTF16
DEFINE JOB FILE_LOAD
DESCRIPTION 'Load a Teradata table from a file'
DEFINE OPERATOR DDL_OPERATOR
VARCHAR TdpId = @tdpid,
VARCHAR UserName = @userid,
VARCHAR UserPassword = @password,
In the above example we have the INCLUDE 'filename.schema'; where we neeed to pass the filename as a parameter just as we pass VARCHAR UserName = @userid through the tbuild command using the -u option.
Please help me with this......
Thanks in advance :)
Have you experimented?
Job variables can be used anywhere. So, just like how you can use it for UserName, you can use it for the INCLUDE statement.
tbuild -f <script> -u "my_file_name='some-file.txt'"
Try it and see if it works.
I have the following TPT export script to use the field report mode feature to export data from the Teradata Database and transform it into delimited VARTEXT format using the DataConnector operator :
If you look in the TPT 13.10 User Guide, page 446, there is an example script for what you want to do.
When using the SQL Selector operator, you need to specify:
When you use "TYPE PRODUCER", TPT is expecting you to provide the "external name" for the operator library file. We do not use that anymore.
By specifying "TYPE SELECTOR", TPT knows which operator file to load dynamically.
I need to know if the WildcardInsert = 'Y' can be used in a stream_operator.
There is a TPT User Guide which refers to using WildcardInsert and refers to using it as :
VARCHAR WildcardInsert /*'Y' builds the INSERT statement from the target table definition, similar to a HELP TABLE statement in a FastLoad script. Use when input file schema exactly matches table schema. Permits the INSERT portion of the APPLY statement to simply specify INSERT INTO tablename, which reduce the amount of user coding.
I have a TPT Stream job script where I want to use the WildcardInsert as Y but the loading fails everytime as :
STREAM_OPERATOR: TPT10508: RDBMS error 3707: Syntax error, expected something like a 'SELECT' keyword or a 'VALIDTIME' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword be
I know doubt whether WildcardInsert can be used in a stream_operator at all ! If not what could be the alternate solution.
No, the WildcardInsert feature is only for the Load operator (which only uses a single DML statement).
The documentation (13.10 version I just looked at) seemed pretty clear to me that it is not supported for the Stream operator or Update operator but is supported for the Load operator.
Re: the post about the 2 columns and loading all of the data into a single column:
TPT has a feature whereby you indicate in the schema a column that is a metadata column specifically for the file name, and TPT will insert the file name into that column for the user.
Check the documentation for the "metadata" keyword in the schema object. I think it is something like:
Column_name VARCHAR(100) METADATA(FILENAME),
TPT does not support a newline character as a field delimiter.
But pick a delimiter as a character (or series of characters whose sequence) you know will not be part of the data and that should be sufficient.