Null Padding on Unicode Char When Using tbuild

Tools
N/A

Null Padding on Unicode Char When Using tbuild

I’m running into an issue in which tbuild seems to be padding Unicode char values with null (0000) rather than space(0020).

 

I’m moving data from an Oracle table which has an NCHAR(4) column holding 2-character values. I’m moving to a Teradata table which has a CHAR(4) CHARACTER SET UNICODE column.

 

The problem appears when attempting to compare an existing table with this newly moved data.

In the existing table, the 2-character value of ‘NC’ is shown as as 004E004300200020 when selected with CHAR2HEXINT(column).

In the new data from Oracle, the 2-character value of ‘NC’ is stored as 004E004300000000 when selected with CHAR2HEXINT(column).

 

The existing table and the new table are otherwise identical.

 

Are there any settings in tbuild that I might be missing?

 

Thanks!

 

Script:

DEFINE JOB "Load To Teradata"

(

                DEFINE SCHEMA SourceTable

                (

                AREACODE DECIMAL(6,0),

                CITY VARCHAR(20),

                PHONE DECIMAL(11,0),

                "STATE" CHAR(4),

                STREET VARCHAR(30),

                SUBSCRIBER_NO DECIMAL(11,0),

                ZIP DECIMAL(11,0)

                );

 

                DEFINE OPERATOR ODBCOperator

                TYPE ODBC

                SCHEMA SourceTable

                ATTRIBUTES (

                                                VARCHAR SelectStmt = 'SELECT AREACODE ,CITY ,PHONE ,STATE ,STREET ,SUBSCRIBER_NO ,ZIP FROM SQL_SANDBOX.ADDRESSES'

                                                ,VARCHAR DSNName = 'Oracle_System'

                                                ,VARCHAR UserName = 'USER'

                                                ,VARCHAR UserPassword = 'password'

                );

 

                DEFINE OPERATOR StreamOperator

                TYPE Stream

                SCHEMA *

                ATTRIBUTES (

                                                VARCHAR Tdpid = 'tdat1'

                                                ,VARCHAR UserName = 'user'

                                                ,VARCHAR UserPassword = 'password'

                                                ,VARCHAR WorkingDatabase = 'SANDBOX'

                                                ,VARCHAR MacroDatabase = 'SANDBOX'

                                                ,VARCHAR ErrorTable = 'SANDBOX.ADDRESSES_ET'

                                                ,VARCHAR LogTable = 'SANDBOX.ADDRESSES_tlog'

                );

 

                APPLY 'INSERT INTO SQL_SANDBOX.ADDRESSES(:AREACODE, :CITY, :PHONE, :"STATE", :STREET, :SUBSCRIBER_NO, :ZIP);'

                TO OPERATOR (StreamOperator[1])

                SELECT * FROM OPERATOR (ODBCOperator[1]);

);

 

Oracle DDL

CREATE TABLE "SQL_SANDBOX"."ADDRESSES"

   (           "SUBSCRIBER_NO" NUMBER(11,0),

                "STREET" NVARCHAR2(30),

                "CITY" NVARCHAR2(20),

                "STATE" NCHAR(2),

                "ZIP" NUMBER(11,0),

                "AREACODE" NUMBER(6,0),

                "PHONE" NUMBER(11,0)

   )

 

Teradata DDL

CREATE MULTISET TABLE SANDBOX.ADDRESSES,

     FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      AREACODE DECIMAL(6,0),

      CITY VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,

      PHONE DECIMAL(11,0),

      STATE CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC,

      STREET VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SUBSCRIBER_NO DECIMAL(11,0),

      ZIP DECIMAL(11,0))

PRIMARY INDEX ( SUBSCRIBER_NO );

1 REPLY
Teradata Employee

Re: Null Padding on Unicode Char When Using tbuild

TPT does not modify the data coming from Oracle.

The ODBC driver will provide the data to TPT and TPT sends it on to Teradata.

TPT does not look at the data, nor does it modify it.

So, either the driver is modifying the data, or Teradata is.

-- SteveF