0

I am writing my first tpt script. My requirement is basic. I want to load a table in exadata ( which has clob and blob datatypes as well) into teradata. I have come across few posts saying i can only use sql inserter operator to load clob and blob types. I have found below script:

SET TdpId=
SET UserName=
SET UserPassword=
DEFINE JOB LOADING_LOB_DATA
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(

DEFINE SCHEMA SOURCE_SCHEMA
DESCRIPTION 'PRODUCT INFORMATION SCHEMA'
(
     COL1_ID VARCHAR(38),
     COL2_CLOB CLOB(50000) AS DEFERRED BY NAME,
     COL3_BLOB BLOB(100000) AS DEFERRED BY NAME
);

DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'DDL Operator'
TYPE DDL
ATTRIBUTES
(
    VARCHAR ARRAY ErrorList = ['3706','3803','3807'],
    VARCHAR DateForm,
    VARCHAR TraceLevel = ‘None’,
    VARCHAR PrivateLogName = 'ddllog',
    VARCHAR TdpId = @TdpId,
    VARCHAR UserName = @UserName,
    VARCHAR UserPassword = @UserPassword,
    VARCHAR AccountID,
    VARCHAR WorkingDatabase
);

DEFINE OPERATOR FILE_READER ()
DESCRIPTION 'DataConnector READER Operator'
TYPE DATACONNECTOR PRODUCER
SCHEMA SOURCE_SCHEMA
ATTRIBUTES
(
    VARCHAR TraceLevel = ‘None’,
    VARCHAR PrivateLogName = 'dtac_r_log',
    VARCHAR DirectoryPath = @SourceDirectoryPath,
    VARCHAR FileName = @SourceFileName,
    VARCHAR OpenMode = 'Read',
    VARCHAR IndicatorMode,
    VARCHAR Format = @SourceFileFormat
);

DEFINE OPERATOR SQL_INSERTER ()
DESCRIPTION 'TERADATA INSERTER UTILITY'
TYPE INSERTER 
INPUT SCHEMA *
ATTRIBUTES
(
    VARCHAR TraceLevel = 'None',
    VARCHAR PrivateLogName = 'ins_log',
    VARCHAR Tdpid = @TdpId,
    VARCHAR UserName = @UserName,
    VARCHAR UserPassword = @UserPassword
);

STEP CREATE_SOURCE_TABLE
(
    APPLY
    ('drop table ' || @SourceTable || ' ;'),
    ('create table ' || @SourceTable || ' , FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
   (
        col1_id VARCHAR(38),
        col2_clob clob(50000),
        col3_blob blob(100000)
    ) primary index (col1_id);')

   TO OPERATOR ( DDL_OPERATOR () );
);

STEP LOADING_DATA_TO_SOURCE_TABLE
(
    APPLY

     ('INSERT INTO ' || @SourceTable || ' values ( 
        :COL1_ID, :COL2_CLOB, :COL3_BLOB);')

   TO OPERATOR (SQL_INSERTER [1])

   SELECT * FROM OPERATOR (FILE_READER ());
);

);

Can anyone tell me what's the use of file_reader() operator and ddl_operator here? Won't i require a ODBC_OPERATOR first here and then a sql opertaor? Please help me with this.

user3901666
  • 399
  • 11
  • 29
  • This script (re)creates the target table first (using the DDL operator), then reads the data from a flat file (using the FILE_READER operator) and finally insert into the target table (using the SQL_INSERTER operator). If there were no LOBs you could easily use the ODBC operator, but afaik it can't export LOBs. The script seems to be from an old article on Teradata's Developer Exchange, you better read the TPT User Guide, which includes lots of example scripts: http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/Load_and_Unload_Utilities/B035_2445_035K/2445ch05.099.09.html – dnoeth Feb 17 '17 at 10:49
  • Sure Dnoeth, Thanks for your comments!! – user3901666 Feb 17 '17 at 13:06
  • @dnoeth...is the TPT able to read the LOB table directly from exadata or from file exported from oracle exadata as in above scripts and in examples i can see only File Reader is used , there is no varaible for source details , etc. – anwaar_hell Nov 06 '17 at 20:07

0 Answers0