TPT Table Load

Database
Enthusiast

TPT Table Load

We are loading large amounts of data into a table using TPT and it works fine.  I have my attr file setup to load to a certain table, but the only issue I have is that when the TPT load is successful, I cannot execute anything against the actual load table.  I have to do an Insert into an exact duplicate table and then I can execute against that one.  Is this the way others have set this up?  Due to the large amounts of data we are having a spool issue and I'm wondering if it is due to the fact that we have 2 tables which have the exact same data?  Any thoughts on this?  Thanks.

6 REPLIES
Enthusiast

Re: TPT Table Load

Hi,

Is your TPT load table defined as a NoPI table? If not, is the primary index on TPT loaded table same as the primary index on final table?

  1. Make sure that the values you are trying to store in the primary index column(s) of your final table are unique or nearly unique.
  2. If your TPT load table is not a NoPI table, make sure the Primary index on TPT load table is same as the primary index column of the final table, and are of the same data type.
Enthusiast

Re: TPT Table Load

Hi, I'm not sure about the NoPI Table.  Basically I took the example load table file included in the tpt install and updated it as follows:

/**************************************************************/

/*                                                            */

/* Teradata Parallel Transporter                              */

/* Script will take Rum Files                                 */

/* and will load them via TPT                                 */

/*                                                            */

/**************************************************************/

/*                                                            */

/* Description:                                               */

/*                                                            */

/* This example script uses two job steps.                    */

/*                                                            */

/* The first job step, called "Setup_Tables", uses the DDL    */

/* Operator to setup the target table.                        */

/*                                                            */

/* The second job step, called "Load_RUM_Table", uses         */

/* multiple instances of the DataConnector Operator to read   */

/* rows from a file and uses multiple instances of the Load   */

/* Operator to write the rows into an empty Teradata target   */

/* table.                                                     */

/*                                                            */

/**************************************************************/

DEFINE JOB FILE_LOAD_EDW

DESCRIPTION 'Load a Teradata table from RUM Files'

(

  DEFINE SCHEMA Input_RUM_Table_Schema

  (

    "#TIMESTAMP"                   VARCHAR(20),

    X_IS_BACKEND_TIER              VARCHAR(5),

    X_IS_TRANSACTION_AVAILABLE     VARCHAR(5),

    X_IS_TRANSACTION_COMPLETE      VARCHAR(5),

    X_TRANSACTION_NAME             VARCHAR(70),

    X_SESSION_ID                   VARCHAR(40),

    C_SESSION_START                VARCHAR(20),

    X_APPLICATION_NAME             VARCHAR(40),

    X_END_USER_USER_NAME           VARCHAR(6),

    X_GEO_NET_START_NUM            VARCHAR(40),

    X_GEO_NET_END_NUM              VARCHAR(40),

    C_HOST_NAME                    VARCHAR(40),

    S_HOST_NAME                    VARCHAR(40),

    S_SW_ELEMENT_NAME              VARCHAR(60),

    X_GEO_IP_NUM                   VARCHAR(40),

    X_LOCATION_NAME                VARCHAR(40),

    X_LOCATION_PARENT_NAME1        VARCHAR(40),

    X_LOCATION_PARENT_NAME2        VARCHAR(40),

    X_LOCATION_PARENT_NAME3        VARCHAR(40),

    X_LOCATION_PARENT_NAME4        VARCHAR(40),

    X_LOCATION_PARENT_NAME5        VARCHAR(40),

    X_RUM_PROBE_ID                 VARCHAR(40),

    C_TRANS_GROSS_DOWNLOAD_MS      VARCHAR(36),

    C_TRANS_NET_DOWNLOAD_MS        VARCHAR(36),

    X_TRANS_SSL_TIME_MS            VARCHAR(36),

    X_TRANS_CONNECT_TIME_MS        VARCHAR(36),

    X_TRANS_NETWORK_TIME_MS        VARCHAR(36),

    S_TRANS_SERVER_FIRSTBUF_MS     VARCHAR(36),

    S_TRANS_SERVER_TIME_MS         VARCHAR(36),

    C_TRANS_CLIENT_TIME_MS         VARCHAR(36),

    X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

    X_TRANS_BYTES                  VARCHAR(36),

    X_TRANS_COMPONENTS             VARCHAR(36),

    X_TRANS_ERRORS_EVENTS_NUM      VARCHAR(36),

    X_TRANS_INFO_EVENTS_NUM        VARCHAR(36),

    X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

    X_THRESHOLD_OFFSET_PERCENT     VARCHAR(36),

    C_BROWSER_NAME                 VARCHAR(40),

    C_OS_NAME                      VARCHAR(40),

    ALL_LOGIN_NAMES                VARCHAR(6)

  );

  DEFINE OPERATOR DDL_OPERATOR

  TYPE DDL

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'TPTLoad_DDL_log',

    VARCHAR TdpId          = @jobvar_tdpid,

    VARCHAR UserName       = @jobvar_username,

    VARCHAR UserPassword   = @jobvar_password,

    VARCHAR ErrorList      = '3807'

  );

  DEFINE OPERATOR FILE_READER

  TYPE DATACONNECTOR PRODUCER

  SCHEMA Input_RUM_Table_Schema

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'TPTLoad_Reader_log',

    VARCHAR DirectoryPath  = '/opt/app/d1car1m6/opt/RumReports/Working',

    VARCHAR FileName       = 'TTU19867.TRANSACT.LOG',

    VARCHAR Format         = 'Delimited',

    VARCHAR OpenMode       = 'Read',

    VARCHAR TextDelimiter  = ','

  );

  DEFINE OPERATOR LOAD_OPERATOR

  TYPE LOAD

  SCHEMA *

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'TPTLoad_Load_log',

    VARCHAR TdpId          = @jobvar_tdpid,

    VARCHAR UserName       = @jobvar_username,

    VARCHAR UserPassword   = @jobvar_password,

    VARCHAR TargetTable    = @jobvar_tgt_tblname,

    VARCHAR LogTable       = @jobvar_tgt_tblname || '_LOG',

    VARCHAR ErrorTable1    = @jobvar_tgt_tblname || '_ET',

    VARCHAR ErrorTable2    = @jobvar_tgt_tblname || '_UV'

  );

  STEP Setup_Tables

  (

    APPLY

      ('DROP TABLE   ' || @jobvar_tgt_tblname || '_ET;'),

      ('DROP TABLE   ' || @jobvar_tgt_tblname || '_UV;'),

      ('DROP TABLE   ' || @jobvar_tgt_tblname || ';'),

      ('CREATE TABLE ' || @jobvar_tgt_tblname

                       || '("#TIMESTAMP"                   VARCHAR(20),

                            X_IS_BACKEND_TIER              VARCHAR(5),

                            X_IS_TRANSACTION_AVAILABLE     VARCHAR(5),

                            X_IS_TRANSACTION_COMPLETE      VARCHAR(5),

                            X_TRANSACTION_NAME             VARCHAR(70),

                            X_SESSION_ID                   VARCHAR(40),

                            C_SESSION_START                VARCHAR(20),

                            X_APPLICATION_NAME             VARCHAR(40),

                            X_END_USER_USER_NAME           VARCHAR(6),

                            X_GEO_NET_START_NUM            VARCHAR(40),

                            X_GEO_NET_END_NUM              VARCHAR(40),

                            C_HOST_NAME                    VARCHAR(40),

                            S_HOST_NAME                    VARCHAR(40),

                            S_SW_ELEMENT_NAME              VARCHAR(60),

                            X_GEO_IP_NUM                   VARCHAR(40),

                            X_LOCATION_NAME                VARCHAR(40),

                            X_LOCATION_PARENT_NAME1        VARCHAR(40),

                            X_LOCATION_PARENT_NAME2        VARCHAR(40),

                            X_LOCATION_PARENT_NAME3        VARCHAR(40),

                            X_LOCATION_PARENT_NAME4        VARCHAR(40),

                            X_LOCATION_PARENT_NAME5        VARCHAR(40),

                            X_RUM_PROBE_ID                 VARCHAR(40),

                            C_TRANS_GROSS_DOWNLOAD_MS      VARCHAR(36),

                            C_TRANS_NET_DOWNLOAD_MS        VARCHAR(36),

                            X_TRANS_SSL_TIME_MS            VARCHAR(36),

                            X_TRANS_CONNECT_TIME_MS        VARCHAR(36),

                            X_TRANS_NETWORK_TIME_MS        VARCHAR(36),

                            S_TRANS_SERVER_FIRSTBUF_MS     VARCHAR(36),

                            S_TRANS_SERVER_TIME_MS         VARCHAR(36),

                            C_TRANS_CLIENT_TIME_MS         VARCHAR(36),

                            X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

                            X_TRANS_BYTES                  VARCHAR(36),

                            X_TRANS_COMPONENTS             VARCHAR(36),

                            X_TRANS_ERRORS_EVENTS_NUM      VARCHAR(36),

                            X_TRANS_INFO_EVENTS_NUM        VARCHAR(36),

                            X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

                            X_THRESHOLD_OFFSET_PERCENT     VARCHAR(36),

                            C_BROWSER_NAME                 VARCHAR(40),

                            C_OS_NAME                      VARCHAR(40),

                            ALL_LOGIN_NAMES                VARCHAR(6));')

    TO OPERATOR (DDL_OPERATOR);

  );

  STEP Load_RUM_Table

  (

    APPLY

      ('INSERT INTO ' || @jobvar_tgt_tblname || '("#TIMESTAMP",

                                                 X_IS_BACKEND_TIER,

                                                 X_IS_TRANSACTION_AVAILABLE,

                                                 X_IS_TRANSACTION_COMPLETE,

                                                 X_TRANSACTION_NAME,

                                                 X_SESSION_ID,

                                                 C_SESSION_START,

                                                 X_APPLICATION_NAME,

                                                 X_END_USER_USER_NAME,

                                                 X_GEO_NET_START_NUM,

                                                 X_GEO_NET_END_NUM,

                                                 C_HOST_NAME,

                                                 S_HOST_NAME,

                                                 S_SW_ELEMENT_NAME,

                                                 X_GEO_IP_NUM,

                                                 X_LOCATION_NAME,

                                                 X_LOCATION_PARENT_NAME1,

                                                 X_LOCATION_PARENT_NAME2,

                                                 X_LOCATION_PARENT_NAME3,

                                                 X_LOCATION_PARENT_NAME4,

                                                 X_LOCATION_PARENT_NAME5,

                                                 X_RUM_PROBE_ID,

                                                 C_TRANS_GROSS_DOWNLOAD_MS,

                                                 C_TRANS_NET_DOWNLOAD_MS,

                                                 X_TRANS_SSL_TIME_MS,

                                                 X_TRANS_CONNECT_TIME_MS,

                                                 X_TRANS_NETWORK_TIME_MS,

                                                 S_TRANS_SERVER_FIRSTBUF_MS,

                                                 S_TRANS_SERVER_TIME_MS,

                                                 C_TRANS_CLIENT_TIME_MS,

                                                 X_TRANS_RETRANSMISSION_TIME_MS,

                                                 X_TRANS_BYTES,

                                                 X_TRANS_COMPONENTS,

                                                 X_TRANS_ERRORS_EVENTS_NUM,

                                                 X_TRANS_INFO_EVENTS_NUM,

                                                 X_TRANS_PERFORMANCE_EVENTS_NUM,

                                                 X_THRESHOLD_OFFSET_PERCENT,

                                                 C_BROWSER_NAME,

                                                 C_OS_NAME,

                                                 ALL_LOGIN_NAMES)

                                               VALUES(:#TIMESTAMP,

                                                      :X_IS_BACKEND_TIER,

                                                      :X_IS_TRANSACTION_AVAILABLE,

                                                      :X_IS_TRANSACTION_COMPLETE,

                                                      :X_TRANSACTION_NAME,

                                                      :X_SESSION_ID,

                                                      :C_SESSION_START,

                                                      :X_APPLICATION_NAME,

                                                      :X_END_USER_USER_NAME,

                                                      :X_GEO_NET_START_NUM,

                                                      :X_GEO_NET_END_NUM,

                                                      :C_HOST_NAME,

                                                      :S_HOST_NAME,

                                                      :S_SW_ELEMENT_NAME,

                                                      :X_GEO_IP_NUM,

                                                      :X_LOCATION_NAME,

                                                      :X_LOCATION_PARENT_NAME1,

                                                      :X_LOCATION_PARENT_NAME2,

                                                      :X_LOCATION_PARENT_NAME3,

                                                      :X_LOCATION_PARENT_NAME4,

                                                      :X_LOCATION_PARENT_NAME5,

                                                      :X_RUM_PROBE_ID,

                                                      :C_TRANS_GROSS_DOWNLOAD_MS,

                                                      :C_TRANS_NET_DOWNLOAD_MS,

                                                      :X_TRANS_SSL_TIME_MS,

                                                      :X_TRANS_CONNECT_TIME_MS,

                                                      :X_TRANS_NETWORK_TIME_MS,

                                                      :S_TRANS_SERVER_FIRSTBUF_MS,

                                                      :S_TRANS_SERVER_TIME_MS,

                                                      :C_TRANS_CLIENT_TIME_MS,

                                                      :X_TRANS_RETRANSMISSION_TIME_MS,

                                                      :X_TRANS_BYTES,

                                                      :X_TRANS_COMPONENTS,

                                                      :X_TRANS_ERRORS_EVENTS_NUM,

                                                      :X_TRANS_INFO_EVENTS_NUM,

                                                      :X_TRANS_PERFORMANCE_EVENTS_NUM,

                                                      :X_THRESHOLD_OFFSET_PERCENT,

                                                      :C_BROWSER_NAME,

                                                      :C_OS_NAME,

                                                      :ALL_LOGIN_NAMES);')

    TO OPERATOR (LOAD_OPERATOR[2])

    SELECT * FROM OPERATOR(FILE_READER[2]);

  );

);





This is the '$loadfile' for the tbuild argument.  I have an attribute file that contains the userid, pw and table, and then I kick off the inserts using

   open(TPT,"$builddir/tbuild -f $loadfile -v $attrfile |");

Once this is complete, then I do the Insert to the duplicate table:  The other table is created like this:

"CREATE TABLE $intable

,FALLBACK

,NO BEFORE JOURNAL

,NO AFTER JOURNAL

(

#timestamp TIMESTAMP(0) NOT NULL

,x_is_backend_tier char(5) Not Null

,x_is_transaction_available char(5) Not Null

,x_is_transaction_complete char(5) Not Null

,x_transaction_name varchar(70) Not Null

,x_session_id varchar(40) Not Null

,c_session_start TIMESTAMP(0) NOT NULL

,x_application_name varchar(40) Not Null

,x_end_user_user_name char(6) Not Null

,x_geo_net_start_num varchar(40) Not Null

,x_geo_net_end_num varchar(40) Not Null

,c_host_name varchar(40) Not Null

,s_host_name varchar(40) Not Null

,s_sw_element_name varchar(60) Not Null

,x_geo_ip_num varchar(40) Not Null

,x_location_name varchar(40) Not Null

,x_location_parent_name1 varchar(40) Not Null

,x_location_parent_name2 varchar(40) Not Null

,x_location_parent_name3 varchar(40) Not Null

,x_location_parent_name4 varchar(40) Not Null

,x_location_parent_name5 varchar(40) Not Null

,x_rum_probe_id varchar(40) Not Null

,c_trans_gross_download_ms varchar(36) Not Null

,c_trans_net_download_ms varchar(36) Not Null

,x_trans_ssl_time_ms varchar(36) Not Null

,x_trans_connect_time_ms varchar(36) Not Null

,x_trans_network_time_ms varchar(36) Not Null

,s_trans_server_firstbuf_ms varchar(36) Not Null

,s_trans_server_time_ms varchar(36) Not Null

,c_trans_client_time_ms varchar(36) Not Null

,x_trans_retransmission_time_ms varchar(36) Not Null

,x_trans_bytes varchar(36) Not Null

,x_trans_components varchar(36) Not Null

,x_trans_errors_events_num varchar(36) Not Null

,x_trans_info_events_num varchar(36) Not Null

,x_trans_performance_events_num varchar(36) Not Null

,x_threshold_offset_percent varchar(36) Not Null

,c_browser_name varchar(40) Not Null

,c_os_name varchar(40) Not Null

,all_login_names char(6) Not Null

)

PRIMARY INDEX (x_session_id, #timestamp, c_session_start, x_transaction_name)

;";

I know this is a lot of data, but I'm still not sure why the original TPT load table doesn't allow me to query it.  Am I not creating the load table file correctly?  

Thanks for the help.

Enthusiast

Re: TPT Table Load

Hi,

While creating your TPT load table, you have not explicitly specified the primary index column. In this case, Teradata takes the first column as primary index. As such, your TPT load table is created with primary index on "#TIMESTAMP"  column.

 ('CREATE TABLE ' || @jobvar_tgt_tblname

|| '("#TIMESTAMP" VARCHAR(20),

X_IS_BACKEND_TIER VARCHAR(5),

X_IS_TRANSACTION_AVAILABLE VARCHAR(5),
.
.
.

C_OS_NAME VARCHAR(40),

ALL_LOGIN_NAMES VARCHAR(6));

But you have created your target table with primary index on different set of columns.

(x_session_id, #timestamp, c_session_start, x_transaction_name)

It looks like your TPT load table contains huge amount of data. Since the primary index of your source table is different from the primary index of target table, lot of data re-distribution seems to be happening when you are copying the data from TPT load to target table, and you don't seem to have enough spool for that.

You may overcome this issue by specifying the primary index on your TPT load table which is same as the primary index of your target table

i.e. Create your TPT load table by specifying the primary index on columns (X_SESSION_ID, #TIMESTAMP, C_SESSION_START, X_TRANSACTION_NAME) 

/**************************************************************/

/* */

/* Teradata Parallel Transporter */

/* Script will take Rum Files */

/* and will load them via TPT */

/* */

/**************************************************************/

/* */

/* Description: */

/* */

/* This example script uses two job steps. */

/* */

/* The first job step, called "Setup_Tables", uses the DDL */

/* Operator to setup the target table. */

/* */

/* The second job step, called "Load_RUM_Table", uses */

/* multiple instances of the DataConnector Operator to read */

/* rows from a file and uses multiple instances of the Load */

/* Operator to write the rows into an empty Teradata target */

/* table. */

/* */

/**************************************************************/

DEFINE JOB FILE_LOAD_EDW

DESCRIPTION 'Load a Teradata table from RUM Files'

(

DEFINE SCHEMA Input_RUM_Table_Schema

(

"#TIMESTAMP" VARCHAR(20),

X_IS_BACKEND_TIER VARCHAR(5),

X_IS_TRANSACTION_AVAILABLE VARCHAR(5),

X_IS_TRANSACTION_COMPLETE VARCHAR(5),

X_TRANSACTION_NAME VARCHAR(70),

X_SESSION_ID VARCHAR(40),

C_SESSION_START VARCHAR(20),

X_APPLICATION_NAME VARCHAR(40),

X_END_USER_USER_NAME VARCHAR(6),

X_GEO_NET_START_NUM VARCHAR(40),

X_GEO_NET_END_NUM VARCHAR(40),

C_HOST_NAME VARCHAR(40),

S_HOST_NAME VARCHAR(40),

S_SW_ELEMENT_NAME VARCHAR(60),

X_GEO_IP_NUM VARCHAR(40),

X_LOCATION_NAME VARCHAR(40),

X_LOCATION_PARENT_NAME1 VARCHAR(40),

X_LOCATION_PARENT_NAME2 VARCHAR(40),

X_LOCATION_PARENT_NAME3 VARCHAR(40),

X_LOCATION_PARENT_NAME4 VARCHAR(40),

X_LOCATION_PARENT_NAME5 VARCHAR(40),

X_RUM_PROBE_ID VARCHAR(40),

C_TRANS_GROSS_DOWNLOAD_MS VARCHAR(36),

C_TRANS_NET_DOWNLOAD_MS VARCHAR(36),

X_TRANS_SSL_TIME_MS VARCHAR(36),

X_TRANS_CONNECT_TIME_MS VARCHAR(36),

X_TRANS_NETWORK_TIME_MS VARCHAR(36),

S_TRANS_SERVER_FIRSTBUF_MS VARCHAR(36),

S_TRANS_SERVER_TIME_MS VARCHAR(36),

C_TRANS_CLIENT_TIME_MS VARCHAR(36),

X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

X_TRANS_BYTES VARCHAR(36),

X_TRANS_COMPONENTS VARCHAR(36),

X_TRANS_ERRORS_EVENTS_NUM VARCHAR(36),

X_TRANS_INFO_EVENTS_NUM VARCHAR(36),

X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

X_THRESHOLD_OFFSET_PERCENT VARCHAR(36),

C_BROWSER_NAME VARCHAR(40),

C_OS_NAME VARCHAR(40),

ALL_LOGIN_NAMES VARCHAR(6)

);

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_DDL_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR ErrorList = '3807'

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Input_RUM_Table_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_Reader_log',

VARCHAR DirectoryPath = '/opt/app/d1car1m6/opt/RumReports/Working',

VARCHAR FileName = 'TTU19867.TRANSACT.LOG',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = ','

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_Load_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR TargetTable = @jobvar_tgt_tblname,

VARCHAR LogTable = @jobvar_tgt_tblname || '_LOG',

VARCHAR ErrorTable1 = @jobvar_tgt_tblname || '_ET',

VARCHAR ErrorTable2 = @jobvar_tgt_tblname || '_UV'

);

STEP Setup_Tables

(

APPLY

('DROP TABLE ' || @jobvar_tgt_tblname || '_ET;'),

('DROP TABLE ' || @jobvar_tgt_tblname || '_UV;'),

('DROP TABLE ' || @jobvar_tgt_tblname || ';'),

('CREATE TABLE ' || @jobvar_tgt_tblname

|| '("#TIMESTAMP" VARCHAR(20),

X_IS_BACKEND_TIER VARCHAR(5),

X_IS_TRANSACTION_AVAILABLE VARCHAR(5),

X_IS_TRANSACTION_COMPLETE VARCHAR(5),

X_TRANSACTION_NAME VARCHAR(70),

X_SESSION_ID VARCHAR(40),

C_SESSION_START VARCHAR(20),

X_APPLICATION_NAME VARCHAR(40),

X_END_USER_USER_NAME VARCHAR(6),

X_GEO_NET_START_NUM VARCHAR(40),

X_GEO_NET_END_NUM VARCHAR(40),

C_HOST_NAME VARCHAR(40),

S_HOST_NAME VARCHAR(40),

S_SW_ELEMENT_NAME VARCHAR(60),

X_GEO_IP_NUM VARCHAR(40),

X_LOCATION_NAME VARCHAR(40),

X_LOCATION_PARENT_NAME1 VARCHAR(40),

X_LOCATION_PARENT_NAME2 VARCHAR(40),

X_LOCATION_PARENT_NAME3 VARCHAR(40),

X_LOCATION_PARENT_NAME4 VARCHAR(40),

X_LOCATION_PARENT_NAME5 VARCHAR(40),

X_RUM_PROBE_ID VARCHAR(40),

C_TRANS_GROSS_DOWNLOAD_MS VARCHAR(36),

C_TRANS_NET_DOWNLOAD_MS VARCHAR(36),

X_TRANS_SSL_TIME_MS VARCHAR(36),

X_TRANS_CONNECT_TIME_MS VARCHAR(36),

X_TRANS_NETWORK_TIME_MS VARCHAR(36),

S_TRANS_SERVER_FIRSTBUF_MS VARCHAR(36),

S_TRANS_SERVER_TIME_MS VARCHAR(36),

C_TRANS_CLIENT_TIME_MS VARCHAR(36),

X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

X_TRANS_BYTES VARCHAR(36),

X_TRANS_COMPONENTS VARCHAR(36),

X_TRANS_ERRORS_EVENTS_NUM VARCHAR(36),

X_TRANS_INFO_EVENTS_NUM VARCHAR(36),

X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

X_THRESHOLD_OFFSET_PERCENT VARCHAR(36),

C_BROWSER_NAME VARCHAR(40),

C_OS_NAME VARCHAR(40),

ALL_LOGIN_NAMES VARCHAR(6)
)PRIMARY INDEX (X_SESSION_ID, #TIMESTAMP, C_SESSION_START, X_TRANSACTION_NAME);'
)

TO OPERATOR (DDL_OPERATOR);

);

STEP Load_RUM_Table

(

APPLY

('INSERT INTO ' || @jobvar_tgt_tblname || '("#TIMESTAMP",

X_IS_BACKEND_TIER,

X_IS_TRANSACTION_AVAILABLE,

X_IS_TRANSACTION_COMPLETE,

X_TRANSACTION_NAME,

X_SESSION_ID,

C_SESSION_START,

X_APPLICATION_NAME,

X_END_USER_USER_NAME,

X_GEO_NET_START_NUM,

X_GEO_NET_END_NUM,

C_HOST_NAME,

S_HOST_NAME,

S_SW_ELEMENT_NAME,

X_GEO_IP_NUM,

X_LOCATION_NAME,

X_LOCATION_PARENT_NAME1,

X_LOCATION_PARENT_NAME2,

X_LOCATION_PARENT_NAME3,

X_LOCATION_PARENT_NAME4,

X_LOCATION_PARENT_NAME5,

X_RUM_PROBE_ID,

C_TRANS_GROSS_DOWNLOAD_MS,

C_TRANS_NET_DOWNLOAD_MS,

X_TRANS_SSL_TIME_MS,

X_TRANS_CONNECT_TIME_MS,

X_TRANS_NETWORK_TIME_MS,

S_TRANS_SERVER_FIRSTBUF_MS,

S_TRANS_SERVER_TIME_MS,

C_TRANS_CLIENT_TIME_MS,

X_TRANS_RETRANSMISSION_TIME_MS,

X_TRANS_BYTES,

X_TRANS_COMPONENTS,

X_TRANS_ERRORS_EVENTS_NUM,

X_TRANS_INFO_EVENTS_NUM,

X_TRANS_PERFORMANCE_EVENTS_NUM,

X_THRESHOLD_OFFSET_PERCENT,

C_BROWSER_NAME,

C_OS_NAME,

ALL_LOGIN_NAMES)

VALUES(:#TIMESTAMP,

:X_IS_BACKEND_TIER,

:X_IS_TRANSACTION_AVAILABLE,

:X_IS_TRANSACTION_COMPLETE,

:X_TRANSACTION_NAME,

:X_SESSION_ID,

:C_SESSION_START,

:X_APPLICATION_NAME,

:X_END_USER_USER_NAME,

:X_GEO_NET_START_NUM,

:X_GEO_NET_END_NUM,

:C_HOST_NAME,

:S_HOST_NAME,

:S_SW_ELEMENT_NAME,

:X_GEO_IP_NUM,

:X_LOCATION_NAME,

:X_LOCATION_PARENT_NAME1,

:X_LOCATION_PARENT_NAME2,

:X_LOCATION_PARENT_NAME3,

:X_LOCATION_PARENT_NAME4,

:X_LOCATION_PARENT_NAME5,

:X_RUM_PROBE_ID,

:C_TRANS_GROSS_DOWNLOAD_MS,

:C_TRANS_NET_DOWNLOAD_MS,

:X_TRANS_SSL_TIME_MS,

:X_TRANS_CONNECT_TIME_MS,

:X_TRANS_NETWORK_TIME_MS,

:S_TRANS_SERVER_FIRSTBUF_MS,

:S_TRANS_SERVER_TIME_MS,

:C_TRANS_CLIENT_TIME_MS,

:X_TRANS_RETRANSMISSION_TIME_MS,

:X_TRANS_BYTES,

:X_TRANS_COMPONENTS,

:X_TRANS_ERRORS_EVENTS_NUM,

:X_TRANS_INFO_EVENTS_NUM,

:X_TRANS_PERFORMANCE_EVENTS_NUM,

:X_THRESHOLD_OFFSET_PERCENT,

:C_BROWSER_NAME,

:C_OS_NAME,

:ALL_LOGIN_NAMES);')

TO OPERATOR (LOAD_OPERATOR[2])

SELECT * FROM OPERATOR(FILE_READER[2]);

);

);

Regards,

Enthusiast

Re: TPT Table Load

/**************************************************************/

/* */

/* Teradata Parallel Transporter */

/* Script will take Rum Files */

/* and will load them via TPT */

/* */

/**************************************************************/

/* */

/* Description: */

/* */

/* This example script uses two job steps. */

/* */

/* The first job step, called "Setup_Tables", uses the DDL */

/* Operator to setup the target table. */

/* */

/* The second job step, called "Load_RUM_Table", uses */

/* multiple instances of the DataConnector Operator to read */

/* rows from a file and uses multiple instances of the Load */

/* Operator to write the rows into an empty Teradata target */

/* table. */

/* */

/**************************************************************/

DEFINE JOB FILE_LOAD_EDW

DESCRIPTION 'Load a Teradata table from RUM Files'

(

DEFINE SCHEMA Input_RUM_Table_Schema

(

"#TIMESTAMP" VARCHAR(20),

X_IS_BACKEND_TIER VARCHAR(5),

X_IS_TRANSACTION_AVAILABLE VARCHAR(5),

X_IS_TRANSACTION_COMPLETE VARCHAR(5),

X_TRANSACTION_NAME VARCHAR(70),

X_SESSION_ID VARCHAR(40),

C_SESSION_START VARCHAR(20),

X_APPLICATION_NAME VARCHAR(40),

X_END_USER_USER_NAME VARCHAR(6),

X_GEO_NET_START_NUM VARCHAR(40),

X_GEO_NET_END_NUM VARCHAR(40),

C_HOST_NAME VARCHAR(40),

S_HOST_NAME VARCHAR(40),

S_SW_ELEMENT_NAME VARCHAR(60),

X_GEO_IP_NUM VARCHAR(40),

X_LOCATION_NAME VARCHAR(40),

X_LOCATION_PARENT_NAME1 VARCHAR(40),

X_LOCATION_PARENT_NAME2 VARCHAR(40),

X_LOCATION_PARENT_NAME3 VARCHAR(40),

X_LOCATION_PARENT_NAME4 VARCHAR(40),

X_LOCATION_PARENT_NAME5 VARCHAR(40),

X_RUM_PROBE_ID VARCHAR(40),

C_TRANS_GROSS_DOWNLOAD_MS VARCHAR(36),

C_TRANS_NET_DOWNLOAD_MS VARCHAR(36),

X_TRANS_SSL_TIME_MS VARCHAR(36),

X_TRANS_CONNECT_TIME_MS VARCHAR(36),

X_TRANS_NETWORK_TIME_MS VARCHAR(36),

S_TRANS_SERVER_FIRSTBUF_MS VARCHAR(36),

S_TRANS_SERVER_TIME_MS VARCHAR(36),

C_TRANS_CLIENT_TIME_MS VARCHAR(36),

X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

X_TRANS_BYTES VARCHAR(36),

X_TRANS_COMPONENTS VARCHAR(36),

X_TRANS_ERRORS_EVENTS_NUM VARCHAR(36),

X_TRANS_INFO_EVENTS_NUM VARCHAR(36),

X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

X_THRESHOLD_OFFSET_PERCENT VARCHAR(36),

C_BROWSER_NAME VARCHAR(40),

C_OS_NAME VARCHAR(40),

ALL_LOGIN_NAMES VARCHAR(6)

);

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_DDL_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR ErrorList = '3807'

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Input_RUM_Table_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_Reader_log',

VARCHAR DirectoryPath = '/opt/app/d1car1m6/opt/RumReports/Working',

VARCHAR FileName = 'TTU19867.TRANSACT.LOG',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = ','

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'TPTLoad_Load_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR TargetTable = @jobvar_tgt_tblname,

VARCHAR LogTable = @jobvar_tgt_tblname || '_LOG',

VARCHAR ErrorTable1 = @jobvar_tgt_tblname || '_ET',

VARCHAR ErrorTable2 = @jobvar_tgt_tblname || '_UV'

);

STEP Setup_Tables

(

APPLY

('DROP TABLE ' || @jobvar_tgt_tblname || '_ET;'),

('DROP TABLE ' || @jobvar_tgt_tblname || '_UV;'),

('DROP TABLE ' || @jobvar_tgt_tblname || ';'),

('CREATE TABLE ' || @jobvar_tgt_tblname

|| '("#TIMESTAMP" VARCHAR(20),

X_IS_BACKEND_TIER VARCHAR(5),

X_IS_TRANSACTION_AVAILABLE VARCHAR(5),

X_IS_TRANSACTION_COMPLETE VARCHAR(5),

X_TRANSACTION_NAME VARCHAR(70),

X_SESSION_ID VARCHAR(40),

C_SESSION_START VARCHAR(20),

X_APPLICATION_NAME VARCHAR(40),

X_END_USER_USER_NAME VARCHAR(6),

X_GEO_NET_START_NUM VARCHAR(40),

X_GEO_NET_END_NUM VARCHAR(40),

C_HOST_NAME VARCHAR(40),

S_HOST_NAME VARCHAR(40),

S_SW_ELEMENT_NAME VARCHAR(60),

X_GEO_IP_NUM VARCHAR(40),

X_LOCATION_NAME VARCHAR(40),

X_LOCATION_PARENT_NAME1 VARCHAR(40),

X_LOCATION_PARENT_NAME2 VARCHAR(40),

X_LOCATION_PARENT_NAME3 VARCHAR(40),

X_LOCATION_PARENT_NAME4 VARCHAR(40),

X_LOCATION_PARENT_NAME5 VARCHAR(40),

X_RUM_PROBE_ID VARCHAR(40),

C_TRANS_GROSS_DOWNLOAD_MS VARCHAR(36),

C_TRANS_NET_DOWNLOAD_MS VARCHAR(36),

X_TRANS_SSL_TIME_MS VARCHAR(36),

X_TRANS_CONNECT_TIME_MS VARCHAR(36),

X_TRANS_NETWORK_TIME_MS VARCHAR(36),

S_TRANS_SERVER_FIRSTBUF_MS VARCHAR(36),

S_TRANS_SERVER_TIME_MS VARCHAR(36),

C_TRANS_CLIENT_TIME_MS VARCHAR(36),

X_TRANS_RETRANSMISSION_TIME_MS VARCHAR(36),

X_TRANS_BYTES VARCHAR(36),

X_TRANS_COMPONENTS VARCHAR(36),

X_TRANS_ERRORS_EVENTS_NUM VARCHAR(36),

X_TRANS_INFO_EVENTS_NUM VARCHAR(36),

X_TRANS_PERFORMANCE_EVENTS_NUM VARCHAR(36),

X_THRESHOLD_OFFSET_PERCENT VARCHAR(36),

C_BROWSER_NAME VARCHAR(40),

C_OS_NAME VARCHAR(40),

ALL_LOGIN_NAMES VARCHAR(6)
)PRIMARY INDEX (X_SESSION_ID, #TIMESTAMP, C_SESSION_START, X_TRANSACTION_NAME);'
)

TO OPERATOR (DDL_OPERATOR);

);

STEP Load_RUM_Table

(

APPLY

('INSERT INTO ' || @jobvar_tgt_tblname || '("#TIMESTAMP",

X_IS_BACKEND_TIER,

X_IS_TRANSACTION_AVAILABLE,

X_IS_TRANSACTION_COMPLETE,

X_TRANSACTION_NAME,

X_SESSION_ID,

C_SESSION_START,

X_APPLICATION_NAME,

X_END_USER_USER_NAME,

X_GEO_NET_START_NUM,

X_GEO_NET_END_NUM,

C_HOST_NAME,

S_HOST_NAME,

S_SW_ELEMENT_NAME,

X_GEO_IP_NUM,

X_LOCATION_NAME,

X_LOCATION_PARENT_NAME1,

X_LOCATION_PARENT_NAME2,

X_LOCATION_PARENT_NAME3,

X_LOCATION_PARENT_NAME4,

X_LOCATION_PARENT_NAME5,

X_RUM_PROBE_ID,

C_TRANS_GROSS_DOWNLOAD_MS,

C_TRANS_NET_DOWNLOAD_MS,

X_TRANS_SSL_TIME_MS,

X_TRANS_CONNECT_TIME_MS,

X_TRANS_NETWORK_TIME_MS,

S_TRANS_SERVER_FIRSTBUF_MS,

S_TRANS_SERVER_TIME_MS,

C_TRANS_CLIENT_TIME_MS,

X_TRANS_RETRANSMISSION_TIME_MS,

X_TRANS_BYTES,

X_TRANS_COMPONENTS,

X_TRANS_ERRORS_EVENTS_NUM,

X_TRANS_INFO_EVENTS_NUM,

X_TRANS_PERFORMANCE_EVENTS_NUM,

X_THRESHOLD_OFFSET_PERCENT,

C_BROWSER_NAME,

C_OS_NAME,

ALL_LOGIN_NAMES)

VALUES(:#TIMESTAMP,

:X_IS_BACKEND_TIER,

:X_IS_TRANSACTION_AVAILABLE,

:X_IS_TRANSACTION_COMPLETE,

:X_TRANSACTION_NAME,

:X_SESSION_ID,

:C_SESSION_START,

:X_APPLICATION_NAME,

:X_END_USER_USER_NAME,

:X_GEO_NET_START_NUM,

:X_GEO_NET_END_NUM,

:C_HOST_NAME,

:S_HOST_NAME,

:S_SW_ELEMENT_NAME,

:X_GEO_IP_NUM,

:X_LOCATION_NAME,

:X_LOCATION_PARENT_NAME1,

:X_LOCATION_PARENT_NAME2,

:X_LOCATION_PARENT_NAME3,

:X_LOCATION_PARENT_NAME4,

:X_LOCATION_PARENT_NAME5,

:X_RUM_PROBE_ID,

:C_TRANS_GROSS_DOWNLOAD_MS,

:C_TRANS_NET_DOWNLOAD_MS,

:X_TRANS_SSL_TIME_MS,

:X_TRANS_CONNECT_TIME_MS,

:X_TRANS_NETWORK_TIME_MS,

:S_TRANS_SERVER_FIRSTBUF_MS,

:S_TRANS_SERVER_TIME_MS,

:C_TRANS_CLIENT_TIME_MS,

:X_TRANS_RETRANSMISSION_TIME_MS,

:X_TRANS_BYTES,

:X_TRANS_COMPONENTS,

:X_TRANS_ERRORS_EVENTS_NUM,

:X_TRANS_INFO_EVENTS_NUM,

:X_TRANS_PERFORMANCE_EVENTS_NUM,

:X_THRESHOLD_OFFSET_PERCENT,

:C_BROWSER_NAME,

:C_OS_NAME,

:ALL_LOGIN_NAMES);')

TO OPERATOR (LOAD_OPERATOR[2])

SELECT * FROM OPERATOR(FILE_READER[2]);

);

);
Enthusiast

Re: TPT Table Load

Thanks for the post.  It's funny because we just put in that primary key the other day and I actually forgot to update the load file with it.  So thanks for that.  But, prior to adding that primary key, and now that I have tested with this addition, I'm still getting an error on issuing any sql against that load table.  Here is an example of the same sql against the load table and the duplicate table where it was copied.  

Sql => select distinct(#timestamp (date)) ,count(*) from ONE_DAY_TABLES.RUM_Load_TPT group by 1;

DBD::ODBC::st execute failed: [Teradata][ODBC Teradata Driver][Teradata Database] Invalid date supplied for Rum_Load_TPT.#TIMESTAMP.  (SQL-22008)

Sql => select distinct(#timestamp (date)) ,count(*) from ONE_DAY_TABLES.RUM_Input_TPT group by 1;

2015-01-16, 500475

2015-01-17, 266962

2015-01-18, 94719

2015-01-19, 304455

2015-01-20, 326829

2015-01-21, 287748

2015-01-22, 292430

Teradata Employee

Re: TPT Table Load

The CREATE TABLE in your TPT job defines #TIMESTAMP as VARCHAR(20). Casting that string to DATE directly fails.

The INSERT/SELECT converts the string to a TIMESTAMP(0), which can be cast to DATE.

You could also SUBSTRING the VARCHAR and cast to DATE.