unloaded Date value issue by tpt script

Tools & Utilities
Enthusiast

unloaded Date value issue by tpt script

Hi Steve, I faced a new issue in date format of tbuild. This is the o/p of simple bteq query from that table-

select D_CLOSE_DATE from schema.table;

D_CLOSE_DATE

------------

    01/01/01

    15/07/07

    01/01/01

    15/07/07

    15/07/03

           ?

           ?

    15/07/07

    01/01/01

    15/07/07

    15/07/03

           ?

           ?

    15/07/07

    15/07/03

           ?

    01/01/01

    15/07/07

    15/07/03

           ?

           ?

    15/07/07

           ?

    01/01/01

           ?

    15/07/03

--------------------

Now I am unloading the data by tbuild and loading with tdload. But the tdload is sending above bold 5 records(01/01/01) into error table.

While I checked the unload file , that value is not unloaded into file correctly. they unloaded as below and it's going to error while loading.Can you please advise the reason and solution.

awk -F'^^' '{print $9}' $unload_file

1/01/

2015/07/07

1/01/

2015/07/07

2015/07/03

2015/07/07

1/01/

2015/07/07

2015/07/03

2015/07/07

2015/07/03

1/01/

2015/07/07

2015/07/03

2015/07/07

1/01/

2015/07/03

---------------------

Column definition-

D_CLOSE_DATE DATE FORMAT 'YY/MM/DD',

tpt job variable file content

-------------------------------

UsrId = 'xxx'

SOURCE_TAB_NAME = 'DRI_ADS_D'

SOURCE_DB = 'schema'

Tdp = 'xxx'

PrivateLog1 = 'file_writer_privatelog1_36045428'

PrivateLog2 = 'file_writer_privatelog2_36045428'

PrivateLog3  = 'file_writer_privatelog3_36045428'

Database_Table = 'schema.DRI_ADS_D'

OP_FILE ='DRI_ADS_D_UNLOAD_201507030849.dat'

DIR_PATH = '/tmp'

SQL = 'select * FROM schema.DRI_ADS_D;'

unload execution

------------------

tbuild -e 'UTF-8' -f  ${WORKING_DIR}/tpt_unload.ksh -u "Pwd='${Pwd}'"  -v $tpt_job_variable_file

tpt_unload.ksh

--------------

DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE

DESCRIPTION 'EXPORT SAMPLE WZ1D02_BITPROC TABLE TO A FILE'

(

 DEFINE SCHEMA WZ1D02_BITPROC_SCHEMA FROM TABLE @Database_Table;

   DEFINE OPERATOR DDL_OPERATOR()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'

   TYPE DDL

   ATTRIBUTES

   (

      VARCHAR PrivateLogName = @PrivateLog1,

      VARCHAR TdpId          = @Tdp,

      VARCHAR UserName       = @UsrId,

      VARCHAR UserPassword   = @Pwd,

      VARCHAR AccountID,

      VARCHAR ErrorList      = '3807'

   );

   DEFINE OPERATOR FILE_WRITER()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

   TYPE DATACONNECTOR CONSUMER

   SCHEMA WZ1D02_BITPROC_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName    = @PrivateLog2,

      VARCHAR DirectoryPath     = @DIR_PATH, 

      VARCHAR FileName          = @OP_FILE,

      VARCHAR IndicatorMode     = 'N',

      VARCHAR OpenMode          = 'Write',

      VARCHAR Format            = 'Delimited',

      VARCHAR TextDelimiter     = 'ÇÇ'

   );

   DEFINE OPERATOR SQL_SELECTOR

   TYPE SELECTOR

   SCHEMA WZ1D02_BITPROC_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName = @PrivateLog3,

      VARCHAR TdpId= @Tdp,

      VARCHAR UserName= @UsrId,

      VARCHAR UserPassword= @Pwd,

      VARCHAR SelectStmt= @SQL,

      VARCHAR ReportMode='Y',

      INTEGER MaxDecimalDigits=38

   );

   STEP setup_export_to_file

   (

      APPLY TO OPERATOR (FILE_WRITER() [1] )

      SELECT * FROM OPERATOR (SQL_SELECTOR);

   );

);

33 REPLIES
Teradata Employee

Re: unloaded Date value issue by tpt script

I am surprised your job ran at all.

You used the DEFINE SCHEMA to pull the table definition from a table.

This would have created a schema with various datatypes.

However, the SQL Selector is defind in the script to work in ReportMode, which requires a schema of all VARCHAR fields.

There should have been a schema mismatch error.

The best thing to do is to leave the DEFINE SCHEMA the way it is, but switch the exporting operator from the SQL Selector to the Export operator. That operator will run faster, all of the data will be exported in binary and the DC operator (as a file writer) will still convert the binary data to text when writing out the delimited data.

-- SteveF
Enthusiast

Re: unloaded Date value issue by tpt script

Hi Steve,  Thanks a lot. I changed the script as you told and updated script is as below.

but still face the same issue.records for those date are coming in that format. Also after unloading,

I have to load the file to another table with tdload. is there anything I need to change ? I have given my tdload command also below for loading.

awk -F'^^' '{print $9}' $unload_file

2015/07/07

2015/07/07

2015/07/07

2015/07/07

2015/07/07

2015/07/07

2015/07/07

1/01/

1/01/

2015/07/03

1/01/

2015/07/03

2015/07/03

1/01/

2015/07/03

1/01/

2015/07/03

New unload script

DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE

DESCRIPTION 'EXPORT SAMPLE WZ1D02_BITPROC TABLE TO A FILE'

(

 DEFINE SCHEMA WZ1D02_BITPROC_SCHEMA FROM TABLE @Database_Table;

   DEFINE OPERATOR DDL_OPERATOR()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'

   TYPE DDL

   ATTRIBUTES

   (

      VARCHAR PrivateLogName = @PrivateLog1,

      VARCHAR TdpId          = @Tdp,

      VARCHAR UserName       = @UsrId,

      VARCHAR UserPassword   = @Pwd,

      VARCHAR AccountID,

      VARCHAR ErrorList      = '3807'

   );

   DEFINE OPERATOR FILE_WRITER()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

   TYPE DATACONNECTOR CONSUMER

   SCHEMA WZ1D02_BITPROC_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName    = @PrivateLog2,

      VARCHAR DirectoryPath     = @DIR_PATH,

      VARCHAR FileName          = @OP_FILE,

      VARCHAR IndicatorMode     = 'N',

      VARCHAR OpenMode          = 'Write',

      VARCHAR Format            = 'Delimited',

      VARCHAR TextDelimiter     = 'ÇÇ'

   );

    DEFINE OPERATOR EXPORT_OPERATOR()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

   TYPE EXPORT

   SCHEMA WZ1D02_BITPROC_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName    = @PrivateLog3,

      INTEGER MaxSessions       =  32,

      INTEGER MinSessions       =  4,

      VARCHAR TdpId             = @Tdp,

      VARCHAR UserName          = @UsrId,

      VARCHAR UserPassword      = @Pwd,

      VARCHAR AccountId,

      INTEGER MaxDecimalDigits  = 38,

      VARCHAR SelectStmt        = @SQL

   );

   STEP setup_export_to_file

   (

      APPLY TO OPERATOR (FILE_WRITER() [1] )

      SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1]);

   );

);

Tdload script

tdload -c UTF8 -f $unload_file -t schema.DRI_ADS_D -u xxx -p xxx -h xxx -L /tmp --TargetWorkingDatabase util_schema \

-d 'ÇÇ' --ErrorTable1 util_schema.tabl1 --ErrorTable2 util_schema.tabl2 \

--LogTable util_schema.tabl3 --TargetMaxSessions 8 --TargetMinSessions 1

now tdload also failing to load that unload file.

LOAD: connecting sessions

$LOAD: preparing target table

$LOAD: TPT10508: RDBMS error 3621: Cannot load table DRI_ADS_D unless secondary indexes and join indexes are removed.

$LOAD: disconnecting sessions

Teradata Employee

Re: unloaded Date value issue by tpt script

Just for the sake of trying, can you please pick a single byte delimiter that is part of the normal 7-bit ASCII code page?

I would like to see if that enables the date data to be written correctly.

-- SteveF
Enthusiast

Re: unloaded Date value issue by tpt script

Hi Steve I tried with normal '|' delimited also but it's the same result in unload. previously with sql_select the load was working and only those invalid records were going to error table, but now load process with tdload also failing as mentioned above.

Changed unload script-

  DEFINE OPERATOR FILE_WRITER()

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

   TYPE DATACONNECTOR CONSUMER

   SCHEMA WZ1D02_BITPROC_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName    = @PrivateLog2,

      VARCHAR DirectoryPath     = @DIR_PATH, 

      VARCHAR FileName          = @OP_FILE,

      VARCHAR IndicatorMode     = 'N',

      VARCHAR OpenMode          = 'Write',

      VARCHAR Format            = 'Delimited',

      VARCHAR TextDelimiter     = '|'

   );

Teradata Employee

Re: unloaded Date value issue by tpt script

The DBS 3621 issue is a separate issue and not related to which operator is used to export the data.

I would like to address them separately.

For the DATE data issue, is it true that only the dates with values 01/01/01 are the ones that are not written out properly?

BTW, it does not pay to try to load the data if it was exported incorrectly.

We need to just figure out why certain date values are not being written properly.

The DBS 3621 error should never be seen. It means that Easy Loader was picking the wrong operator to do the load. And yet Easy Loader is supposed to look at the target table and notice it has secondary indexes and not pick the Load operator.

Can you please run the tdload command with the -x and -S command line options?

The -S will keep the script.

I need you to send that to me.

The -x command line option will turn on trace.

I will need you to send me that information.

Please send all of this via email to me.

Thanks!

-- SteveF
Teradata Employee

Re: unloaded Date value issue by tpt script

Also, please provide me with the SELECT statement you are passing in as a job variable.

(When I try it on my system, I get 2001/01/01 in my data file.)

-- SteveF
Enthusiast

Re: unloaded Date value issue by tpt script

Hi Steve, my sleect statement is 'select * from table;"

I tried with some other value like '1001-01-01','2001-01-01' , '0009-01-01' etc

whenever year is less than '1000' it's facing that problem.

Teradata Employee

Re: unloaded Date value issue by tpt script

When your BTEQ output shows a date of 01/01/01, what is the real date?

2001?

1901?

When I enter a date as:

INSERT INTO TABLE abc ('0001/01/01');

I get this on output from TPT:

2901/01/01

In the DataConnector Consumer operator, add this attribute:

VARCHAR TraceLevel = 'all'

and send me the entire log file (the binary .out file).

I would like to take a look. The trace will show me what the DBS is passing to the operator.

I need to know if the weird data is coming from the DBS, or whether the data is correct, but the DC operator is not converting it properly.

So, if you could just extract the data for the one column/row that has a value of 01/01/01, that is preferable, as it cuts down on the amount of trace information.

Also, I never asked what version of TPT and what version of Teradata are you using?

-- SteveF
Enthusiast

Re: unloaded Date value issue by tpt script

Steve I tried that with TPT also to unload in delimited file and load it to another table. But there I am facing below issues-

1.when date is less than (1000-01-01) e.x "0001-01-01" it's not unloading correctly and load process failing.

2.while any field value is ''(blank) it's unloading but during loading it's taking as NULL and trying to load nut null field and failing.

3. unicode fields have unicode character. but they are unloading. but while trying to load, they are getting rejected.looks like either CONSUMER or PRODUCER operator not performing correctly for all unicode char.

 

I am using export and DATACONNECTOR_CONSUMER operator for unload and DATACONNECTOR_PRODUCER and UPDATE operator for loading.

even I tried to unload with selector and DATACONNECTOR_CONSUMER operator, but in that uload itself failed with  error-

"Column #1 in schema is too small to hold 8 bytes of data"

My requirement is to create the unload file so that later I can ready without teradata for archival and also load that file to another table.