All Forums Tools
DaveWellman 66 posts Joined 01/06
15 Jul 2013
TPT output from ODBC includes Indicator Bytes?

Hi,
I'm trying to transfer data from a MySQL table into a Teradata table. The export uses the ODBC operator and the inport uses the LOAD operator.
All records go into the ET table, usually with 2673 (source parcel length incorrect) errors, some of them fail with a 6760 (invalid timestamp field).
 
Looking at the DataParcel column what it appears is happening is that every data record sent to Teradata has an additional 2 bytes added to the front. I'm certain that these two bytes are indicator bytes. They are not 'record length' (or if they are then they are completely wrong !) or field length because the first field in the output is a BIGINT (i.e. a fixed length field).
Also, interpreting them as indicator bytes and comparing to the source data they match up. I confess that I haven't checked all records, but the first few match up.
So it looks like the data parcel includes indicator bytes which makes sense because the data may include nulls, but the LOAD operator is not expecting them (and therefore is not telling the DBMS to expect them). There is no "indicator bytes" attribute in the LOAD operator that I can see.
Looking at the TPT LOAD operator documentation it appears that you have to use the data connector operator to handle 'indicators'. Is this correct? (I have to say that if so then this would seem to be a missing piece of design/functionality in the TPT load operator).
I am using TPT 14.10.
Cheers,
Dave

Ward Analytics Ltd - information in motion www: http://www.ward-analytics.com
feinholz 1234 posts Joined 05/08
15 Jul 2013

Data is sent to Teradata in indicator mode by all of our operators, and it is not something the user can control.
The only option is for the user to tell us whether the incoming data is in indicator mode or not so that the Data Connector operator will know how to process the data.
During the transfer of data in the data streams from the DC operator to the Load operator, we add the indicator bytes (we also have to account for possible CASE logic in the script language which might NULL the data).
So, yes you are correct that those 2 bytes are indicator bytes.
And no, we do not have a missing piece of the design. This is all done on purpose.  :)
 

--SteveF

DaveWellman 66 posts Joined 01/06
16 Jul 2013

Hi,
Glad to hear it's done on purpose, thanks for that. I've resolved my problem, the SCHEMA defintion was wrong, I'd misunderstood the data format as it came out of the source.
Cheers,
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Chantis 1 post Joined 06/13
25 Jul 2013

Use TPT for
Loading ONE DATABASE TO ANOTHER DATABASE,DATABASE TO FILE, FILE TO DATABASE. that simply solve your problem.......

Br@m's

Kamma1166 4 posts Joined 07/13
31 Jul 2013

Hi I am trying to Load the data using TPT.. but i am getting one error TPT unicode data identifier between USING and INSERT but i didn't use any where USER key word, could you please helpm out

Arpan.Roy 24 posts Joined 04/16
06 Jul 2016

Thanks Steve and Dave. I'm also facing the same issue while reading data from Oracle table and loading into Teradata table. TTU version I'm using is 15.10. For reading from Oracle I'm using ODBC oparator and for loading data I'm using UPDATE operator.
 
So, what is the suggestion here? In the schema defination add a dummy column for Indicator bytes or is there any other better approach to handle this problem?
 
Thanks & Regards,
Arpan.

feinholz 1234 posts Joined 05/08
06 Jul 2016

If you are reading data from Oracle with the ODBC operator, there are no indicator bytes coming from Oracle.
They are added to the data when the rows are moved from the ODBC operator to the loading operator (Load, Update, Stream).
But these indicator bytes will not be the issue.
The issue is most likely with the schema definition.
The loading operator will use the schema definition to build the USING clause for the INSERT statement.
And it is possible that the column definitions in the schema do not match the actual data coming from Oracle.
If you can provide the script, and the layout of the source table, I might be able to assist more.

--SteveF

Arpan.Roy 24 posts Joined 04/16
07 Jul 2016

Hi Steve,
Thanks for your response.
Below is my TPT contril file:

DEFINE JOB LOAD_ACR_ACCTS_RECEIVABLE_TO_TABLE
DESCRIPTION 'export LOAD_ACR_ACCTS_RECEIVABLE_TO_TABLE'
     (
        DEFINE SCHEMA SCHEMA_ACR_ACCTS_RECEIVABLE
            (
            	AR_CTGRY_CDE_ACR VARCHAR(8),
				AR_CTGRY_DESC_ACR	VARCHAR(12),
				SUB_ACCT_NO_ACR	VARCHAR (16)
			);

        DEFINE OPERATOR o_ODBCOper
        TYPE ODBC
        SCHEMA SCHEMA_ACR_ACCTS_RECEIVABLE
        ATTRIBUTES (
            VARCHAR UserName            = @UserName
           ,VARCHAR UserPassword        = @UserPassword
           ,VARCHAR DSNName             = @DSNName
           ,VARCHAR PrivateLogName      = 'ODBCloadlog'
           ,VARCHAR SelectStmt          = @SelectClause || @SourceSchema || @SourceTable
           ,VARCHAR TraceLevel          = 'all'
           ,INTEGER DataBlockSize       = 2048
        );

        DEFINE OPERATOR o_MLoad
        TYPE UPDATE
        SCHEMA SCHEMA_ACR_ACCTS_RECEIVABLE
        ATTRIBUTES (
         	 VARCHAR TdpId       = @TdpId
		,VARCHAR UserName        = @TargetUserName
		,VARCHAR UserPassword    = @TargetUserPassword
		,INTEGER MaxSessions     = @MaxSessions
		,INTEGER MinSessions     = @MinSessions
		,VARCHAR TargetTable     = @TargetTable
		,VARCHAR WorkingDatabase = @WorkingDatabase
		,VARCHAR LogTable        = @LogTable
		,VARCHAR ErrorTable1     = @ErrorTable1
		,VARCHAR ErrorTable2     = @ErrorTable2
		,VARCHAR WorkTable       = @WorkTable
		,VARCHAR PrivateLogName  = 'Load'
		,VARCHAR AmpCheck        = 'None'
		,VARCHAR AccountId       = @TeraAccountId
        	,VARCHAR TraceLevel      = 'all'
        );
		
        APPLY 
		(
	'INSERT INTO ' || @WorkingDatabase || '.' || @TargetTable ||
		'(
			  :AR_CTGRY_CDE_ACR,
		      :AR_CTGRY_DESC_ACR,
			  :SUB_ACCT_NO_ACR
			);')
		
    TO OPERATOR (o_MLoad[@LoadInst])
    SELECT * FROM OPERATOR (o_ODBCOper[@ReadInst] ATTRIBUTES (SelectStmt=@SelectClause || @SourceSchema || @SourceTable || ' WHERE SYS_ACR = 8155 AND PRIN_ACR = 7000;'));
     );

Below is my sample record:

Added separator as '|' just to differentiate between columns:
8155|TAXES|TAXES FEES|1111111111111111

Below is Source table structure:

AR_CTGRY_CDE_ACR  VARCHAR2 (8 Byte)
AR_CTGRY_DESC_ACR VARCHAR2 (12 Byte)
SUB_ACCT_NO_ACR   CHAR (16 Byte)

Below is my tbuild command:

tbuild -C -h 100M -f /data/Sample/ctl/acr_accts_receivable.tpt.ctl -v /data/Sample/logon/aroy001c_tpt.logon -u " UserName='XXXXXXX' , UserPassword='YYYYYY' , DSNName='VCMC_Oracle' , load_op=o_ODBCOper , LoadInst=7 , ReadInst=7 ,MaxSessions=5, MinSessions=1 ,WorkingDatabase='NDW_TEMP' , TargetTable='acr_accts_receivable' , LogTable='NDW_TEMP.LG_acr_accts_receivable' , ErrorTable1='NDW_TEMP.ET1_acr_accts_receivable' , ErrorTable2='NDW_TEMP.ET2_acr_accts_receivable' , WorkTable='NDW_TEMP.WT_acr_accts_receivable', LOAD_DTS='2016-07-04 08:21:34' , SkipRows=1 , SourceSchema='SourceSchema.' , SourceTable='ACR_ACCTS_RECEIVABLE', SelectClause='SELECT AR_CTGRY_CDE_ACR, AR_CTGRY_DESC_ACR,SUB_ACCT_NO_ACR FROM '" ACR_ACCTS_RECEIVABLE

Thanks & Regards,
Arpan.

feinholz 1234 posts Joined 05/08
07 Jul 2016

Your schema has 3 columns.
Your sample data had 4 fields.
The schema does not match the source table.
Also, remove the -C from the command line; you do not want to round-robin your data to the instances of the Update operator.
It slows down performance.
Let TPT manage how best to process the data.
The -C command line option is only for when you want to write data out to multiple output files and have each file roughly the same size.
Also on the command line, SkipRows will not be in effect; that is only supported by the DataConnector operator (file reader).
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
08 Jul 2016

Thanks a lot Steve.
Just wanted to understand, what will be our focus while defining SCHEMA in tpt control file? Is it Source Table or Target Table? If Source and Target table is having different datatypes ( while moving data from Oracle to Teradata, this is very obvious case), DATE should be cast to VARCHAR(19). Are there any other datatypes that needs this casting?
One of my source column was having datatype as NUMBER(4) and in target it was having INTEGER. When I defined schema as INTEGER, it failed, but when I changed to NUMBER(4) it got succeeded.
 
Thanks & Regards,
Arpan.

feinholz 1234 posts Joined 05/08
08 Jul 2016

The schema must match the source.
We must know what the data looks like as it comes to TPT.
Oracle is a little challenging because of the different formatting of some of the data types.
INTEGER is problematic because Oracle does not support INTEGER.
Internally, Oracle stores integers as NUMBER.
And the ODBC driver tells us NUMBER; the driver does not convert to a data type called INTEGER.
The same with DATE.
DATE in Oracle equates to a TIMESTAMP in Teradata.
But DATE in a TPT script equates (to TPT) to a Teradata DATE data type.
 
For the DATE field, you can either CAST in the SELECT, or you can use a feature in TPT that allows us to convert for you. You define the column in the script as VARDATE and there are FORMATIN and FORMATOUT qualifers for the column in the schema where you can provide the input format and the output format and we will convert for you. Of course, that does use CPU on the client machine and CASTing might be more proficient by having the database do the conversion. But for those who do not want to CAST in their SELECT statements, it is an option.
 
Back to INTEGER.
Please provide me with the error you get when you put INTEGER in the schema.
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
12 Jul 2016

Hi Steve,
When I put INTEGER in schema defination, the script is not failing, but all the records are going to ET1 table.
Below is the console log.

Teradata Parallel Transporter Version 15.10.01.00 64-Bit
Job log: /opt/teradata/client/15.10/tbuild/logs/ACR_ACCTS_RECEIVABLE-57.out
Job id is ACR_ACCTS_RECEIVABLE-57, running on ndwetlapp-mit-a1m.sys.comcast.net
Teradata Parallel Transporter Update Operator Version 15.10.01.00
o_MLoad: private log specified: Load-1
Teradata Parallel Transporter ODBC Operator Version 15.10.01.00
o_ODBCOper: private log specified: ODBCloadlog-1
o_ODBCOper: connecting sessions
o_MLoad: connecting sessions
o_MLoad: preparing target table(s)
o_MLoad: entering DML Phase
o_MLoad: entering Acquisition Phase
o_ODBCOper: sending SELECT request
o_ODBCOper: data retrieval complete
o_ODBCOper: Total Rows Exported: 412111
o_MLoad: entering Application Phase
o_MLoad: Statistics for Target Table: 'acr_accts_receivable'
o_MLoad: Rows Inserted: 0
o_MLoad: Rows Updated: 0
o_MLoad: Rows Deleted: 0
o_MLoad: entering Cleanup Phase
o_MLoad: Error Table Statistics for Target Table : 'acr_accts_receivable'
o_MLoad: Total Rows in Error Table 1: 412111
o_MLoad: Total Rows in Error Table 2: 0
o_MLoad: disconnecting sessions
o_ODBCOper: disconnecting sessions
o_ODBCOper: Total processor time used = '22.26 Second(s)'
o_ODBCOper: Start : Mon Jul 11 08:31:19 2016
o_ODBCOper: End : Mon Jul 11 08:39:10 2016
Job step MAIN_STEP terminated (status 4)
Job ACR_ACCTS_RECEIVABLE completed successfully, but with warning(s).
o_MLoad: Total processor time used = '1.9 Second(s)'
o_MLoad: Start : Mon Jul 11 08:31:19 2016
o_MLoad: End : Mon Jul 11 08:39:10 2016
Job start: Mon Jul 11 08:31:09 2016
Job end: Mon Jul 11 08:39:10 2016
Below is one of the data in ET1 table:

ýÿÿÿ VIDEO (" VIDEO ŠD
0001-01-01
0001-01-01 N b" ("
2016-07-09 Y1111111111111111N
 

feinholz 1234 posts Joined 05/08
12 Jul 2016

Can you look at the content of the error table and make sure the rows are placed in the error table due to the column that you defined as INTEGER?
And let me know the error code?
 

--SteveF

Arpan.Roy 24 posts Joined 04/16
13 Jul 2016

Hi Steve,
The ErrorField is "AR_CTGRY_CDE_ACR". Below is the schema defination from tpt control file.

SYS_ACR INTEGER, --- Column I changed from NUMBER(4) to INTEGER
PRIN_ACR NUMBER(4),
AGNT_ACR NUMBER(4),
AR_CTGRY_CDE_ACR VARCHAR(8), -- Column I'm getting error
AR_CTGRY_DELQ_TOTAL_ACR NUMBER(15,2)

The error code I'm getting is "2673".
Thanks & Regards,
Arpan.

You must sign in to leave a comment.