Fileds shifted in the database using TPT

Tools

Fileds shifted in the database using TPT

Hello everybody,

I have a huge problem loading data from a flat file to Teradata DB.

What happen is that the data loads correctely in the DB except some rows which their fields contain a string with the letter "é" i tried to switch the "é" to the letter "e" and it works fine.

Please could anyone tells me how to solve this problem. Its realy urgent.

Thank you.

9 REPLIES
N/A

Re: Fileds shifted in the database using TPT

Sounds like an UTF8 Problem.

Can you share your DDL and TPT script?

Re: Fileds shifted in the database using TPT

Hello ulrich,

Thank you for your answer, however i cannot share the TPT script, but i can tell you an example of what i have in the row of the flat file which causes the problem :

ID         name          CD

15        alién            RR

After lunching the tpt script in the table i have this:

ID         name          CD

alién      RR             some data

I tried to convert the flat file to UTF-8 and the tpt file and used the USING CHARACTER SET before the job but nothing work.

Please some help i get stuck to this point.

Teradata Employee

Re: Fileds shifted in the database using TPT

It would help if you could provide the script. Just remove the sensitive information like TdpId, UserName and UserPassword. Otherwise we will have to continue asking questions to get more information. It saves time.

Also, what version of TPT are you using?

-- SteveF

Re: Fileds shifted in the database using TPT

Hello below is my TPT script:

/*USING CHAR SET UTF8*/

DEFINE JOB MyJob_jb

  DESCRIPTION 'Loading'

  (

        DEFINE OPERATOR LOAD_ALL

        TYPE {OPERATEUR}

        SCHEMA *

        ATTRIBUTES (

                TdpId             = @TDPID,

                UserName          = @UserName,

                UserPassword      = @Password,

                VARCHAR  LogTable = 'MyJob_jb_LOG',

                VARCHAR  ErrorTable1 = 'MyJob_ERR1',

                VARCHAR  ErrorTable2 = 'MyJob_ERR2',

                VARCHAR  TargetTable = 'MyJob_TMPTD',

                VARCHAR  WorkTable = 'MyJob_TMPTD_WK',

                DropErrorTable    = 'Yes',

                QueryBandSessInfo = 'UtilityDataSize={QBSI};'

        );

DEFINE SCHEMA DATA_SCHM_LMXXXX_MSG

(

 "ERRCODE" VARCHAR(20),

 "MESSAGE" VARCHAR(255),

 "MyTable" VARCHAR(100)

);

DEFINE OPERATOR FILE_WRTR_LMXXXX_MSG

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

 VARCHAR FileName = 'LMXXXX.msg',

 VARCHAR Indicator ='N',

 VARCHAR OpenMode = 'Write',

 VARCHAR TextDelimiter = '|',

 VARCHAR Format = 'Delimited'

);

DEFINE OPERATOR SQL_SEL_LMXXXX_MSG

TYPE SELECTOR

SCHEMA DATA_SCHM_LMXXXX_MSG

ATTRIBUTES

(

 tdpiD = @TDPID,

 UserName = @UserName,

 UserPassword = @Password,

 SelectStmt=' SELECT DISTINCT CAST(A.ETC_ErrorCode AS VARCHAR(20)),

  B.Errortext, ''L_M'' AS MyTable

  FROM L_M_ERR_IS

  A INNER JOIN DBC.ERRORMSGS B

 ON B.ERRORCODE=A.ETC_ErrorCode;'

);

DEFINE SCHEMA DATA_SCHM1_L_M

(

 "ID" VARCHAR(350), 

 "NAME" VARCHAR(350),

 "CD" VARCHAR(350)

);

DEFINE OPERATOR FILE_WRTR1_L_M

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

 VARCHAR FileName = @BadFile,

 VARCHAR Indicator = 'N',

 VARCHAR OpenMode = 'WriteAppend',

 VARCHAR TextDelimiter = '|',

 VARCHAR Format = 'Delimited'

);

DEFINE OPERATOR SQL_SEL1_L_M

TYPE SELECTOR

SCHEMA DATA_SCHM1_L_M

ATTRIBUTES

(

 tdpId = @TDPID,

 UserName = @UserName,

 UserPassword = @Password,

 SelectStmt = 'SELECT

  A."ID",

  A."NAME",

  A."CD"

 FROM L_M_TMPTD A

 INNER JOIN L_M_ERR_IS B

 ON A.ROWID=B.ETC_ROWID;'

);

DEFINE SCHEMA DATA_SCHM1_L_M_PI



 "ID" VARCHAR(350), 

 "NAME" VARCHAR(350),

 "CD" VARCHAR(350)

);

DEFINE OPERATOR SQL_SEL1_L_M_PI

TYPE SELECTOR

SCHEMA DATA_SCHM1_L_M_PI

ATTRIBUTES

(

 tdpId = @TDPID,

 UserName = @UserName,

 UserPassword = @Password,

 SelectStmt = 'SELECT  

 "ID", 

 "NAME",

 "CD"

 FROM L_M_TMPTD

 WHERE FLAG=''1'';'

);

        DEFINE SCHEMA SCHEMA_GENERIC

        (

 COL0 VARCHAR(350),

 COL1 VARCHAR(350),

 COL2 VARCHAR(350),

 COL3 VARCHAR(350)

        );

        DEFINE OPERATOR DDL_OPERATOR

        TYPE DDL

        ATTRIBUTES (

                TdpId = @TDPID,

                UserName = @UserName,

                UserPassword = @Password,

                DateForm = 'ansiDate',

                DropErrorTable    = 'Yes',

                ARRAY ErrorList = ['3807']

         );

        DEFINE OPERATOR READER_G

        TYPE DATACONNECTOR PRODUCER

        SCHEMA SCHEMA_GENERIC

        ATTRIBUTES (

                AcceptMissingColumns = 'Y',

                AcceptExcessColumns = 'Y',

                FileName        = @FileName,

                Format          = 'Delimited',

                TextDelimiter   = ';',

                OpenMode        = 'Read'

        );

        STEP DROPG_STG(

                APPLY

                    ('DROP TABLE MyJob_jb_LOG;'),

                    ('DROP TABLE MyJob_ERR1;'),

                    ('DROP TABLE MyJob_ERR2;'),

                    ('DROP TABLE MyJob_TMPTD_WK;'),

                    ('DROP TABLE MyJob_TMPTD;')

                TO OPERATOR (

                        DDL_OPERATOR

                );

        );

        STEP CREATE_ERROR_TABLE(

                APPLY

 ('DROP TABLE L_M_ERR_IS;'),

 ('CREATE ERROR TABLE L_M_ERR_IS FOR L_M;')

            TO OPERATOR (

                DDL_OPERATOR

        );

);

        STEP CREATE_STG(

                APPLY

 'CREATE MULTISET TABLE MyJob_tmptd

( COL0 VARCHAR(350),

 COL1 VARCHAR(350),

 COL2 VARCHAR(350),

 COL3 VARCHAR(350)

 NO PRIMARY INDEX;'

                TO OPERATOR (

                        DDL_OPERATOR

          );

);

        STEP CREATE_INTER(

                APPLY

 ('DROP TABLE L_M_TMPTD;'),

 ('CREATE MULTISET TABLE L_M_TMPTD

 ( 

 "ID" VARCHAR(350), 

 "NAME" VARCHAR(350),

 "CD" VARCHAR(350), 

 FLAG BYTEINT)

 ;')

                TO OPERATOR (

                        DDL_OPERATOR

          );

);

        STEP LOAD_TABLE_STG

        (

             APPLY

            (

                'INSERT INTO MyJob_TMPTD

                (

   "COL0",

   "COL1",

   "COL2",

   "COL3"

                )

                VALUES

               (

   :COL0,

   :COL1,

   :COL2,

   :COL3

               );'

             )

               TO OPERATOR (

                    LOAD_ALL[1]

             )

                  SELECT

   "COL0",

   "COL1",

   "COL2",

   "COL3"

                 FROM OPERATOR (

                      READER_G[1]

               );

         );

 STEP LOAD_DWH1(

 APPLY(

 'INSERT INTO L_M_TMPTD

 (  

 "ID", 

 "NAME",

 "CD", 

 "FLAG"

  )

 SELECT

  COL1,

  COL2,

  COL3,

  CASE WHEN (G_COL1 ='''') THEN 1 ELSE 0 END

 FROM MyJob_TMPTD

  ;'

 )

 TO OPERATOR(

  DDL_OPERATOR

  );

 );

 STEP ERROR_EXP_L_M_PI(

 APPLY

 TO OPERATOR (FILE_WRTR1_L_M())

 SELECT * FROM OPERATOR (SQL_SEL1_L_M_PI());

 );

 STEP DEL_EXP_L_M_TMPTD(

 APPLY

  ('DELETE FROM L_M_TMPTD WHERE FLAG = ''1'';')

 TO OPERATOR(DDL_OPERATOR);

 );

 STEP LOAD_DWH101(

 APPLY(

 'INSERT INTO L_M

 (

 "ID", 

 "NAME",

 "CD"

  )

 SELECT

 "ID", 

 "NAME",

 "CD" 

 FROM L_M_TMPTD

 LOGGING ALL ERRORS WITH NO LIMIT ;'

 )

 TO OPERATOR(

  DDL_OPERATOR

  );

 );

 STEP ERROR_EXP_JJ(

 APPLY

 TO OPERATOR (FILE_WRTR1_L_M())

 SELECT * FROM OPERATOR (SQL_SEL1_L_M());

);

 STEP ERROR_MSG_LMXXXX(

 APPLY

  TO OPERATOR (FILE_WRTR_LMXXXX_MSG())

 SELECT * FROM OPERATOR(SQL_SEL_LMXXXX_MSG());

 );

        STEP DROP_ERROR_TABLE(

                APPLY

 ('DROP TABLE L_M_ERR_IS;')

                TO OPERATOR (

                        DDL_OPERATOR

                );

        );

        STEP DROP_INTER_TABLE(

                APPLY

 ('DROP TABLE L_M_TMPTD;')

                TO OPERATOR (

                        DDL_OPERATOR

                );

        );

        STEP DROPG_STG_END(

                APPLY

                    ('DROP TABLE MyJob_TMPTD;')

                TO OPERATOR (

                        DDL_OPERATOR

                );

        );

 );

Teradata Parallel Transporter Version 14.10.00.08

Teradata Employee

Re: Fileds shifted in the database using TPT

Thank you! This helps.

One thing I would like you to add to the script.

If you are using the SQL Selector operator to pull data out of Teradata and you want to write the data out in a delimited record format, you need to set the ReportMode attribute for the Selector operator. This tells the operator to tell the DBS to return the data in field mode (all character data) and avoids the binary mode which prepends a record length indicator in front of each row of data (even though you may be CASTing the data to VARCHAR).

Let me know if that helps.

-- SteveF

Re: Fileds shifted in the database using TPT

Thank you for your suggestion.

Just to add something which may help, the data comes with the same problem from the flat file to the temporary table( i checked it). Could it be an attribut of the TYPE DATACONNECTOR PRODUCER which i forget to add?

Teradata Employee

Re: Fileds shifted in the database using TPT

This script has a lot of steps, so not always clear which step is the culprit, but I would have asked eventually.

In following the "trail", I see that the first load comes from a flat file and loads into MyJob_TMPTD.

The flat file has delimited records, with the semicolon (";") as the delimiter.

The DC operator (acting as the file reader) reads records made up of 4 fields.

The schema is SCHEMA_GENERIC (4 VARCHAR fields).

If that is the "same problem from the flat file) to which you are referring, we can just focus on that step first, and ignore the other ones.

If you are not referring to that step, let me know which step is the one you are talking about and I can investigate.

Please (if the data is not sensitive), provide a sample few rows of data that seem to have an issue.

If you want to do this through email, you can reach me at steven.feinholz@teradata.com.

-- SteveF

Re: Fileds shifted in the database using TPT

Thank you all.

This project get out of my hands and my boss prefered to deliver it. So i could not be aware of the changes that will be made on tpy script. In anycase thanks for your help and i will investigate to bare that problem next time.




Re: Fileds shifted in the database using TPT

Hello everyone.

Hello feinholz, ulrich.

I get back the project with the same problem of shifted fields.

 feinholz: i added the attribut ReportModeOn='Y' in TYPE SELECTOR  but still have the some problem.

Could anyone helps me with that issue please