Using name of Flat File in TPT Load

Tools & Utilities
Fan

Using name of Flat File in TPT Load

Hi All, I'm new here..

I would like to know if you can help me.

I need to do a script TPT for load Flat File in a Teradata Table.

My problem is:

I have to store the name of flat file that I load, in a column of target table.

How i can do this ??

In this case:
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA Trans_n_Accts_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = '/teradata_dev2/',
VARCHAR FileName = 'CG1_0780_20110505121803_5201000_7000.DAT',
VARCHAR Format = 'Delimited',
VARCHAR TextDelimiter = 'TAB',
VARCHAR OpenMode = 'Read'
);

I would like to load the value of field (VARCHAR FileName) in a column value of a target table.

I tried to do this, but did not work:

STEP Load_Trans_Table
(
APPLY
('INSERT INTO ' || 'tb3' || '(col1,col2) VALUES(:col1, :col2);')
TO OPERATOR (LOAD_OPERATOR[1])
SELECT FileName AS COL2, COL1 FROM OPERATOR(FILE_READER[1]);
);

That this value changes dynamically, and I need to store this value in a table to know what was the origin of the line.

If you can help me I thank.
Tags (1)
12 REPLIES
Teradata Employee

Re: Using name of Flat File in TPT Load

Yes, we do support this. In the schema object in the script, we support this syntax:

DEFINE SCHEMA T2_SOURCE_SCHEMA
DESCRIPTION 'T2 SCHEMA'
(
METACOL1 VARCHAR(40) METADATA(FileName),
A_IN_ID1 VARCHAR(4),
A_IN_C1 VARCHAR(10),
A_IN_C2 VARCHAR(10),
A_IN_C3 VARCHAR(10)
);

where you introduce a column that will hold the file name. The DataConnector operator (file reader) will insert the file name into that column placeholder.

-- SteveF
Fan

Re: Using name of Flat File in TPT Load

Hi,

When I tried to do this:

DEFINE SCHEMA Auxiliar
(
COL2 VARCHAR(7),
COL3 VARCHAR(15),
METACOL1 VARCHAR(40) METADATA(FileName)
);

this is the error:

Teradata Parallel Transporter Version 12.00.00.10
line 31: syntax error at "METADATA" missing RPAREN_ in Rule: Explicit Schema Element List
Syntax error occurred in parse rule Rule: DEFINE SCHEMA
Compilation failed due to errors. Execution Plan was not generated.
Job script compilation failed

What I did wrong ??

Thanks
Teradata Employee

Re: Using name of Flat File in TPT Load

The feature is not available in TPT12.0.
It was added to TPT13.0.
-- SteveF
N/A

Re: Using name of Flat File in TPT Load

Hi all,

I am sorry for reopening an old topic, but I am, I think, in the same situation, in TPT13, and I have the very same error :

DEFINE SCHEMA BCR_SCHEMA_PROFIL
(
ID_PROFIL varchar(10)
,LIBELLE varchar(512)
,FLAG_SUPPRESSION varchar(1)
,DATE_CREATION varchar(23)
,DATE_MAJ varchar(23)
,DATE_SUPPRESSION varchar(23)
,FIC_NAME VARCHAR(100) METADATA(FileName)
);

And the error:

$ tbuild -f tpt_PROFIL.tpt -v ../cfg/tpt_SPO.var
Teradata Parallel Transporter Version 13.00.00.08
line 55: syntax error at "METADATA" missing RPAREN_ in Rule: Explicit Schema Element List
TPT_INFRA: TPT03020: Error: Syntax error occurred in parse rule Rule: DEFINE SCHEMA
TPT_INFRA: TPT03050: Error: Semantic error at or near job script line 62:
Schema 'BCR_SCHEMA_PROFIL' is undefined.

Do you have any idea, please?

Thank you in advance.

Teradata Employee

Re: Using name of Flat File in TPT Load

My typo. It was added in TPT13.10, not TPT13.0.

-- SteveF

Re: Using name of Flat File in TPT Load

Hi All,

I am also in the same situation where I need to load the file name into the table. we are using TPT Version 13.10.00.04.

my source file is a fixed width file. So when define

METACOL1 VARCHAR(40) METADATA(FileName)

in the schema definition the script is failing because for File Type "TEXT" every column has to be CHAR or DATE. but when I define the METADATA column as CHAR the script is failing again saying "Metadata type 'FileName' (1) must be data type VARCHAR".

I am not sure how I can solve this problem. Please help.

Thanks

Teradata Employee

Re: Using name of Flat File in TPT Load

Right now, the METADATA feature is only supported with a record format of DELIMITED.

-- SteveF
Enthusiast

Re: Using name of Flat File in TPT Load

Error while loading data from Oracle table to teradata table

Hi Experts,

I am trying to run a TPT script which aims to load data from oracle table to teradata table.On Executing the script am getting the following error:

gilderoy:/ngs/app/slsaudedwt >tbuild -f ora_tera.ctl -v para_var.sql

Teradata Parallel Transporter Version 13.10.00.12

Job log: /opt/teradata/client/13.10/tbuild/logs/slsaudt- 28962.out

Job id is slsaudt-28962, running on gilderoy

Found CheckPoint file: /opt/teradata/client/13.10/tbuild/checkpoint/ slsaudtLVCP

This is a restart job; it restarts at step MAIN_STEP.

TPT_INFRA: TPT02192: Error: Cannot load shared library    

           dlopen error:        0509-022 Cannot load module /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.

        0509-150   Dependent module libodbc.a(odbc.so) could not be loaded.

        0509-022 Cannot load module libodbc.a(odbc.so).

        0509-026 System error: A file or directory in the path name does not exist.

        0509-022 Cannot load module /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.

        0509-150   Dependent module /opt/teradata/client/13.10/tbuild/lib/libodbcop.so could not be loaded.

TPT_INFRA: TPT02109: Cannot open shared library libodbcop.so, status = Open Error

TPT_INFRA: TPT02240: Error: Cannot create operator object, status = Operator Error

TPT_INFRA: TPT02103: Executor unable to set up its table object

Job step MAIN_STEP terminated (status 8)

Job slsaudt terminated (status 8)

My TPT Script:

DEFINE JOB ora_tera

DESCRIPTION 'Loading data from oracle environment to teradata'

(

            DEFINE SCHEMA Oracle_DataSource_Schema

            DESCRIPTION 'Schema of Oracle Data Source'

            (

                        Emp_Id                 VARCHAR(10),

                        Emp_frst_Name          VARCHAR(50),

                        Emp_Last_Name          VARCHAR(50),

                        Emp_Age                INTEGER,

                        Emp_Salary             DECIMAL(18,3)

             );

            DEFINE OPERATOR ODBC_Operator

DESCRIPTION 'TPT ODBC Operator'

TYPE ODBC

SCHEMA Oracle_DataSource_Schema

            ATTRIBUTES

            (

                        VARCHAR  PrivateLogName      = @ODBCPrivateLogName,

                        VARCHAR  DSNName             = @DSNName,

                        VARCHAR  UserName            = @ODBCUserName,

                        VARCHAR  UserPassword        = @ODBCPassword,

                        VARCHAR  SelectStmt          = @SelectStmt

            );

 DEFINE OPERATOR LOAD_OPERATOR

    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

    TYPE LOAD

    SCHEMA *

    ATTRIBUTES

    (

       VARCHAR PrivateLogName    = @LoadPrivateLogName,

       VARCHAR TargetTable       = @TargetTable,

       VARCHAR TdpId             = @TargetTdpId,

       VARCHAR UserName          = @TargetUserName,

       VARCHAR UserPassword      = @TargetUserPassword,

       VARCHAR ErrorTable1       = @ErrorTable1,

       VARCHAR ErrorTable2       = @ErrorTable2,

       VARCHAR LogTable          = @LogTable

    );

            APPLY

                ( 'INSERT INTO ' || @TargetTable || '

                                 (

                                 :Emp_id,

                                 :Emp_frst_Name,

                                 :Emp_Last_Name,

                                 :Emp_Age,

                                 :Emp_Salary

                                 );')

           TO OPERATOR (  $LOAD [@LoadInstances] )

           SELECT * FROM OPERATOR (  ODBC_Operator [@ODBCInstances]);

        );             

);

My TBuild Library details:

 >ls  -l /opt/teradata/client/13.10/tbuild/lib

total 18744

-r--r--r--    1 root     system       963207 Nov 16 2012  libdataconop.so

-r--r--r--    1 root     system        52495 Nov 16 2012  libddlop.so

-r--r--r--    1 root     system       145692 Jul 11 2012  libexportop.so

-r--r--r--    1 root     system       612006 Nov 16 2012  libfeomop.so

-r--r--r--    1 root     system       608506 Nov 16 2012  libflimop.so

-r--r--r--    1 root     system       111938 Nov 16 2012  libinsertop.so

-r--r--r--    1 root     system       124897 Mar 01 2011  libloadop.so

-r--r--r--    1 root     system        66415 Nov 16 2012  liblogview.so

-r--r--r--    1 root     system       617062 Nov 16 2012  libmlimop.so

-r--r--r--    1 root     system       237610 Nov 16 2012  libodbcop.so

-r--r--r--    1 root     system       567705 Nov 16 2012  libopcommon.so

-r--r--r--    1 root     system        13048 Nov 16 2012  liboscmdop.so

-r--r--r--    1 root     system       175324 Nov 16 2012  libptmin.so

-r--r--r--    1 root     system        38972 Nov 16 2012  libptmos.so

-r--r--r--    1 root     system       206895 Nov 16 2012  libptmpi.so

-r--r--r--    1 root     system        87969 Nov 16 2012  libptmpo.so

-r--r--r--    1 root     system      2893172 Nov 16 2012  libpxcommon.so

-r--r--r--    1 root     system       207750 Nov 16 2012  libpxextrct.so

-r--r--r--    1 root     system        15128 Nov 16 2012  libpxicu.so

-r--r--r--    1 root     system       126221 Nov 16 2012  libselectop.so

-r--r--r--    1 root     system       349594 Apr 30 2012  libstreamop.so

-r--r--r--    1 root     system        84054 Nov 16 2012  libtableqry.so

-r--r--r--    1 root     system      1120659 Jan 28 2011  libtelapi.so

-r--r--r--    1 root     system       125710 Feb 24 2011  libupdateop.so

Please reply...

Enthusiast

Re: Using name of Flat File in TPT Load

Hi,

Remove all the checkpoint file from  /opt/teradata/client/13.10/tbuild/checkpoint/ and restart the Job.