TPT adding spaces for nvarchar datatype while loading

Tools & Utilities
Enthusiast

TPT adding spaces for nvarchar datatype while loading

Hi,

We are loading data from MSSQL server tables to Teradata using ODBc operator and load operator. for nvarchar datatype from MSSQL server, spaces are added after each character. If I change session to UTF16 and have integer as first datatype to be loaded with nvarchar as second data type, then it is putting everything into _ET tables.

Please advise.

Thanks.

8 REPLIES
Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

I'm having the same problem... Clould someone help me, please?

Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

Please provide your script and the DDL of the source and target tables.

-- SteveF
Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

That is my target table:

CREATE SET TABLE TEMP_TABLES.ADAPTIVE ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Lote_id INTEGER NOT NULL,

      Nro_cuit VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      id BIGINT,

      Tipo_Doc VARCHAR(5) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      Nro_doc VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,

      apellido VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      nombre VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      nombrecompleto VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,

      sexo VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,

      fnacimiento VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_cpa VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_codpostal VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_provincia VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_localidad VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_calle VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_numero VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_piso VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_dto VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_observaciones VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_barrio VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_prefijo VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_caracteristica VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_numero VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_latitud VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      dom_longitud VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_lab_prefijo VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_lab_caracteristica VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_lab_numero VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      tel_lab_mismo_prefijo VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      Fecha_adaptive TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS',

      Detalle VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,

      Enriquecido CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      Fecha_envio TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS',

      email VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC)

UNIQUE PRIMARY INDEX ( Lote_id ,Nro_cuit );

==========================================================================================

Thats the source:

Create Table MKTD_DEDUPLICACION.ADAPTIVE (

   Lote_id int Not Null,

   Nro_cuit varchar(20) Not Null,

   id bigint,

   Tipo_Doc varchar(5) Not Null,

   Nro_doc nvarchar(20),

   apellido nvarchar(100),

   nombre nvarchar(100),

   nombrecompleto nvarchar(200),

   sexo nvarchar(2),

   fnacimiento nvarchar(100),

   dom_cpa nvarchar(100),

   dom_codpostal nvarchar(100),

   dom_provincia nvarchar(100),

   dom_localidad nvarchar(100),

   dom_calle nvarchar(100),

   dom_numero nvarchar(100),

   dom_piso nvarchar(100),

   dom_dto nvarchar(100),

   dom_observaciones nvarchar(200),

   dom_barrio nvarchar(100),

   tel_prefijo nvarchar(100),

   tel_caracteristica nvarchar(100),

   tel_numero varchar(20),

   dom_latitud nvarchar(100),

   dom_longitud nvarchar(100),

   tel_lab_prefijo nvarchar(100),

   tel_lab_caracteristica nvarchar(100),

   tel_lab_numero nvarchar(100),

   tel_lab_mismo_prefijo nvarchar(100),

   Fecha_adaptive smalldatetime(0),

   Detalle varchar(200),

   Enriquecido char(1),

   Fecha_envio smalldatetime(0),

   email varchar(500))

=======================================================================================

That the script from TPT wizard:

DEFINE JOB mKTD_DEDU_ADAPTIVE

(

 DEFINE OPERATOR W_1_o_mKTD_DEDU_ADAPTIVE

 TYPE LOAD

 SCHEMA *

 ATTRIBUTES

 (

  VARCHAR UserName,

  VARCHAR UserPassword,

  VARCHAR LogTable,

  VARCHAR TargetTable,

  INTEGER BufferSize,

  INTEGER ErrorLimit,

  INTEGER MaxSessions,

  INTEGER MinSessions,

  INTEGER TenacityHours,

  INTEGER TenacitySleep,

  VARCHAR AccountID,

  VARCHAR DateForm,

  VARCHAR ErrorTable1,

  VARCHAR ErrorTable2,

  VARCHAR NotifyExit,

  VARCHAR NotifyExitIsDLL,

  VARCHAR NotifyLevel,

  VARCHAR NotifyMethod,

  VARCHAR NotifyString,

  VARCHAR PauseAcq,

  VARCHAR PrivateLogName,

  VARCHAR TdpId,

  VARCHAR TraceLevel,

  VARCHAR WorkingDatabase

 );

 DEFINE SCHEMA W_0_s_mKTD_DEDU_ADAPTIVE

 (

      Lote_id INTEGER,

      Nro_cuit VARCHAR(20),

      id_4 BIGINT,

      Tipo_Doc VARCHAR(5),

      Nro_doc VARCHAR(20),

      apellido VARCHAR(100),

      nombre VARCHAR(100),

      nombrecompleto VARCHAR(200),

      sexo VARCHAR(2),

      fnacimiento VARCHAR(100),

      dom_cpa VARCHAR(100),

      dom_codpostal VARCHAR(100),

      dom_provincia VARCHAR(100),

      dom_localidad VARCHAR(100),

      dom_calle VARCHAR(100),

      dom_numero VARCHAR(100),

      dom_piso VARCHAR(100),

      dom_dto VARCHAR(100),

      dom_observaciones VARCHAR(200),

      dom_barrio VARCHAR(100),

      tel_prefijo VARCHAR(100),

      tel_caracteristica VARCHAR(100),

      tel_numero VARCHAR(20),

      dom_latitud VARCHAR(100),

      dom_longitud VARCHAR(100),

      tel_lab_prefijo VARCHAR(100),

      tel_lab_caracteristica VARCHAR(100),

      tel_lab_numero VARCHAR(100),

      tel_lab_mismo_prefijo VARCHAR(100),

      Fecha_adaptive TIMESTAMP(0),

      Detalle VARCHAR(200),

      Enriquecido CHAR(1),

      Fecha_envio TIMESTAMP(0),

      email VARCHAR(500)

 );

 DEFINE OPERATOR W_0_o_mKTD_DEDU_ADAPTIVE

 TYPE ODBC

 SCHEMA W_0_s_mKTD_DEDU_ADAPTIVE

 ATTRIBUTES

 (

  VARCHAR UserName,

  VARCHAR UserPassword,

  VARCHAR SelectStmt,

  VARCHAR PrivateLogName,

  VARCHAR DSNName,

  VARCHAR ConnectString,

  VARCHAR TruncateData

 );

 APPLY

  (

   'INSERT INTO temp_tables.adaptive

    VALUES (:Lote_id,:Nro_cuit,:id_4,:Tipo_Doc,:Nro_doc,:apellido,:nombre,:nombrecompleto,:sexo,:fnacimiento,:dom_cpa,:dom_codpostal,:dom_provincia,:dom_localidad,:dom_calle,:dom_numero,:dom_piso,:dom_dto,:dom_observaciones,:dom_barrio,:tel_prefijo,:tel_caracteristica,:tel_numero,:dom_latitud,:dom_longitud,:tel_lab_prefijo,:tel_lab_caracteristica,:tel_lab_numero,:tel_lab_mismo_prefijo,:Fecha_adaptive,:Detalle,:Enriquecido,:Fecha_envio,:email);'

  )

 TO OPERATOR

 (

  W_1_o_mKTD_DEDU_ADAPTIVE[1]

  ATTRIBUTES

  (

   UserName = 'xxxxxxxxxxxx',

   UserPassword = 'xxxxxxxxx',

   LogTable = 'temp_tables.adaptive_log',

   TargetTable = 'temp_tables.adaptive',

   TdpId = 'xxxxxxxxxxxxx'

  )

 )

 SELECT * FROM OPERATOR

 (

  W_0_o_mKTD_DEDU_ADAPTIVE[1]

  ATTRIBUTES

  (

   UserName = 'xxxxxxxxxxxxx',

   UserPassword = 'xxxxxxxxxxxx',

   SelectStmt = 'SELECT TOP 1000 Lote_id,Nro_cuit,id,Tipo_Doc,Nro_doc,apellido,nombre,nombrecompleto,sexo,fnacimiento,dom_cpa,dom_codpostal,dom_provincia,dom_localidad,dom_calle,dom_numero,dom_piso,dom_dto,dom_observaciones,dom_barrio,tel_prefijo,tel_caracteristica,tel_numero,dom_latitud,dom_longitud,tel_lab_prefijo,tel_lab_caracteristica,tel_lab_numero,tel_lab_mismo_prefijo,Fecha_adaptive,Detalle,Enriquecido,Fecha_envio,email FROM mktd_deduplicacion..adaptive;',

   DSNName = 'PARADATA'

  )

 );

);

Thanks for your help.

Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

I believe that "nvarchar" is used when the data has Unicode characters.

Thus, the use of UTF16 is probably the right choice (but I do not know that content of your data).

The fact that rows end up in the error table is due to the provided data not able to be loaded into the target table (if you look at the content of the error table you will see the DBS error code and column name that caused the row to be placed in the error table).

Some of the CHAR/VARCHAR columns in your target table are defined as LATIN.

You chould see if the rows in the error table were caused by columns defined as LATIN.

If so, then it is possible that the content of the source data will not (or cannot) be loaded into those columns on Teradata.

-- SteveF
Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

Hi.

I tried your advise, but It didn't work, every row went to ET error table, the message said that the data parcel had to be longer or something like that... So I tried to change the character set on the table as you told me to UNICODE, but even so, It didn't work.

So I decided to transform the date from the source from NVARCHAR to VHARCHAR, then the JOB worked fine.

If you had other suggestions please tell me, because I need to migrate a lot of tables and, although this work will be done for the DBAs, I'm afraid, they could have the same problem.

Thanks so much.

Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

I would need to see more information (exact errors) in order to better figure out what is going on.

Also, I need the error code from the error table and the column name.

One more thing to keep in mind.

When you use UTF16, you need to double the size of all of the CHAR/VARCHAR columns in the schema in the TPT script.

That is because a database definition of (for example) VARCHAR(100) is in terms of characters.

Our schema is in terms of bytes.

Therefore, a VARCHAR(100) may not be large enough to hold UTF16 data coming from a source table defined as nvarchar(100).

The rule of thumb is, if the client session character set is UTF16, you need to double the size of the character columns. For UTF8 it is triple.

-- SteveF
Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

That the source table

Create Table MKTD_DEDUPLICACION.ADAPTIVE (

   Lote_id int Not Null,

   Nro_cuit varchar(20) Not Null,

   id bigint,

   Tipo_Doc varchar(5) Not Null,

   Nro_doc nvarchar(20),

   apellido nvarchar(100),

   nombre nvarchar(100),

   nombrecompleto nvarchar(200),

   sexo nvarchar(2),

   fnacimiento nvarchar(100),

   dom_cpa nvarchar(100),

   dom_codpostal nvarchar(100),

   dom_provincia nvarchar(100),

   dom_localidad nvarchar(100),

   dom_calle nvarchar(100),

   dom_numero nvarchar(100),

   dom_piso nvarchar(100),

   dom_dto nvarchar(100),

   dom_observaciones nvarchar(200),

   dom_barrio nvarchar(100),

   tel_prefijo nvarchar(100),

   tel_caracteristica nvarchar(100),

   tel_numero varchar(20),

   dom_latitud nvarchar(100),

   dom_longitud nvarchar(100),

   tel_lab_prefijo nvarchar(100),

   tel_lab_caracteristica nvarchar(100),

   tel_lab_numero nvarchar(100),

   tel_lab_mismo_prefijo nvarchar(100),

   Fecha_adaptive smalldatetime(0),

   Detalle varchar(200),

   Enriquecido char(1),

   Fecha_envio smalldatetime(0),

   email varchar(500))

====================================================================================================

That the table (with less fields)

CREATE SET TABLE temp_tables.adaptive_2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Lote_id INTEGER NOT NULL,

      Nro_cuit VARCHAR(40) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      id BIGINT,

      Tipo_Doc VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Nro_doc VARCHAR(40) CHARACTER SET UNICODE NOT CASESPECIFIC,

      apellido VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

       nombre VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC

      )

PRIMARY INDEX ( Lote_id );

==============================================================================================00

That is the new script with few fields (in the VARCHAR fields I've put twice the size as the original because the CHARACTER SET UTF16 as you said).

/* 1 */  USING CHARACTER SET UTF16

/* 2 */  DEFINE JOB mKTD_DEDU_ADAPTIVE

/* 3 */  (

/* 4 */   DEFINE OPERATOR W_1_o_mKTD_DEDU_ADAPTIVE

/* 5 */   TYPE LOAD

/* 6 */   SCHEMA *

/* 7 */   ATTRIBUTES

/* 8 */   (

/* 9 */    VARCHAR UserName,

/* 10 */    VARCHAR UserPassword,

/* 11 */    VARCHAR LogTable,

/* 12 */    VARCHAR TargetTable,

/* 13 */    INTEGER BufferSize,

/* 14 */    INTEGER ErrorLimit,

/* 15 */    INTEGER MaxSessions,

/* 16 */    INTEGER MinSessions,

/* 17 */    INTEGER TenacityHours,

/* 18 */    INTEGER TenacitySleep,

/* 19 */    VARCHAR AccountID,

/* 20 */    VARCHAR DateForm,

/* 21 */    VARCHAR ErrorTable1,

/* 22 */    VARCHAR ErrorTable2,

/* 23 */    VARCHAR NotifyExit,

/* 24 */    VARCHAR NotifyExitIsDLL,

/* 25 */    VARCHAR NotifyLevel,

/* 26 */    VARCHAR NotifyMethod,

/* 27 */    VARCHAR NotifyString,

/* 28 */    VARCHAR PauseAcq,

/* 29 */    VARCHAR PrivateLogName,

/* 30 */    VARCHAR TdpId,

/* 31 */    VARCHAR TraceLevel,

/* 32 */    VARCHAR WorkingDatabase

/* 33 */   );

/* 34 */ 

/* 35 */   DEFINE SCHEMA W_0_s_mKTD_DEDU_ADAPTIVE

/* 36 */   (

/* 37 */        Lote_id INTEGER,

/* 38 */        Nro_cuit VARCHAR(40),

/* 39 */        id_4 BIGINT,

/* 40 */        Tipo_Doc VARCHAR(10),

/* 41 */        Nro_doc VARCHAR(40),

/* 42 */        apellido VARCHAR(200),

/* 43 */        nombre VARCHAR(200)

/* 44 */   );

/* 45 */ 

/* 46 */   DEFINE OPERATOR W_0_o_mKTD_DEDU_ADAPTIVE

/* 47 */   TYPE ODBC

/* 48 */   SCHEMA W_0_s_mKTD_DEDU_ADAPTIVE

/* 49 */   ATTRIBUTES

/* 50 */   (

/* 51 */    VARCHAR UserName,

/* 52 */    VARCHAR UserPassword,

/* 53 */    VARCHAR SelectStmt,

/* 54 */    VARCHAR PrivateLogName,

/* 55 */    VARCHAR DSNName,

/* 56 */    VARCHAR ConnectString,

/* 57 */    VARCHAR TruncateData

/* 58 */   );

/* 59 */ 

/* 60 */   APPLY

/* 61 */    (

/* 62 */     'INSERT INTO temp_tables.adaptive_2

/* 63 */      VALUES (:Lote_id, :Nro_cuit, :id_4, :Tipo_Doc, :Nro_doc, :apellido ,:nombre);'

/* 64 */    )

/* 65 */   TO OPERATOR

/* 66 */   (

/* 67 */    W_1_o_mKTD_DEDU_ADAPTIVE[1]

/* 68 */ 

/* 69 */    ATTRIBUTES

/* 70 */    (

/* 71 */     UserName = 'L0607460',

/* 72 */     UserPassword = 'TERADATA74',

/* 73 */     LogTable = 'temp_tables.adaptive_log',

/* 74 */     TargetTable = 'temp_tables.adaptive_2',

/* 75 */     TdpId = 'xxxxxxxxxxxxxx'

/* 76 */    )

/* 77 */   )

/* 78 */   SELECT * FROM OPERATOR

/* 79 */   (

/* 80 */    W_0_o_mKTD_DEDU_ADAPTIVE[1]

/* 81 */ 

/* 82 */    ATTRIBUTES

/* 83 */    (

/* 84 */     UserName = 'xxxxxxxxxxxxxx',

/* 85 */     UserPassword = 'xxxxxxxxxxxxxx',

/* 86 */     SelectStmt = 'SELECT TOP 1000 Lote_id, Nro_cuit, id, Tipo_Doc, Nro_doc, apellido ,nombre FROM MKTD_DEDUPLICACION..ADAPTIVE;',

/* 87 */     DSNName = 'PARADATA'

/* 88 */    )

/* 89 */   );

/* 90 */  );

====================================================================================================

Here the log file

Teradata Parallel Transporter Version 13.10.00.02

Job log: C:\Archivos de programa\Teradata\client\13.10\Teradata Parallel Transporter/logs/MKTD_DEDUPLICACION_ADAPTIVE-86.out

Job id is MKTD_DEDUPLICACION_ADAPTIVE-86, running on GAL082262

Teradata Parallel Transporter Load Operator Version 13.10.00.02

W_1_o_mKTD_DEDU_ADAPTIVE: private log not specified

Teradata Parallel Transporter ODBC Operator Version 13.10.00.02

W_0_o_mKTD_DEDU_ADAPTIVE: private log not specified

W_0_o_mKTD_DEDU_ADAPTIVE: connecting sessions

W_0_o_mKTD_DEDU_ADAPTIVE: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5701,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'MKTD_DEDUPLICACION'.'

W_0_o_mKTD_DEDU_ADAPTIVE: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5703,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.'

W_1_o_mKTD_DEDU_ADAPTIVE: connecting sessions

W_1_o_mKTD_DEDU_ADAPTIVE: preparing target table

W_1_o_mKTD_DEDU_ADAPTIVE: entering Acquisition Phase

W_0_o_mKTD_DEDU_ADAPTIVE: sending SELECT request

W_0_o_mKTD_DEDU_ADAPTIVE: data retrieval complete

W_0_o_mKTD_DEDU_ADAPTIVE: Total Rows Exported:  1000

W_1_o_mKTD_DEDU_ADAPTIVE: entering Application Phase

W_1_o_mKTD_DEDU_ADAPTIVE: Statistics for Target Table:  'temp_tables.adaptive_2'

W_1_o_mKTD_DEDU_ADAPTIVE: Total Rows Sent To RDBMS:      1000

W_1_o_mKTD_DEDU_ADAPTIVE: Total Rows Applied:            0

W_1_o_mKTD_DEDU_ADAPTIVE: Total Possible Duplicate Rows: 1000

W_1_o_mKTD_DEDU_ADAPTIVE: disconnecting sessions

W_0_o_mKTD_DEDU_ADAPTIVE: disconnecting sessions

W_0_o_mKTD_DEDU_ADAPTIVE: Total processor time used = '0.078125 Second(s)'

W_0_o_mKTD_DEDU_ADAPTIVE: Start : Thu May 29 18:37:57 2014

W_0_o_mKTD_DEDU_ADAPTIVE: End   : Thu May 29 18:38:12 2014

W_1_o_mKTD_DEDU_ADAPTIVE: Total processor time used = '0.75 Second(s)'

W_1_o_mKTD_DEDU_ADAPTIVE: Start : Thu May 29 18:37:57 2014

W_1_o_mKTD_DEDU_ADAPTIVE: End   : Thu May 29 18:38:18 2014

Job step MAIN_STEP completed successfully

Job MKTD_DEDUPLICACION_ADAPTIVE completed successfully

====================================================================================================

The Error table ET now have the error 6705 in the fiel Nro_cuit 

Teradata Employee

Re: TPT adding spaces for nvarchar datatype while loading

6705 An illegally formed character string was encountered during translation.

Explanation: This error occurs when in a string containing Multi-Byte Characters (MBC), the MBCs are not completely

formed

Are you sure the data is valid UTF16 data?

-- SteveF