unloaded Date value issue by tpt script

Tools & Utilities
Enthusiast

Re: unloaded Date value issue by tpt script

Error

$UPDATE: TPT10508: RDBMS error 3707: Syntax error, expected something like ',' or ')' between the word 'Daily' and the word 'Total'.

$UPDATE: disconnecting sessions

Version

Teradata Parallel Transporter Version 14.10.00.05

Script

USING CHARACTER SET UTF8

DEFINE JOB tpt_table_to_table_load

(

  APPLY $INSERT TO OPERATOR($UPDATE()[1] )

  SELECT * FROM OPERATOR($EXPORT() [1]);

);


Teradata Employee

Re: unloaded Date value issue by tpt script

Can you please run the tbuild command with the -P command line option?

In the current working directory you should find some trace files and the expanded script that is generated under the covers.

Please send all of those files to me:

steven.feinholz@teradata.com

-- SteveF
Fan

Re: unloaded Date value issue by tpt script

How save current_Date in JOB variable tpt.

I need save a filename with current_date

Teradata Employee

Re: unloaded Date value issue by tpt script

We do not currently support that feature.

It is being considered for a future release.

-- SteveF
Enthusiast

TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

With TPT 16.10, is there any way to load the binary files that contain ANSIDATE values without specifying/defining schema column by column?

I still have trouble loading the files. I could not figure a way to configure the job in ANSIDATE mode

**** 14:58:02 Options in effect for this job:
              OperatorType:                StandAlone
              Instances:                   1
              Character set:               'UTF8'
              Checkpoint:                  600 second(s)
              Encryption:                  No data encryption requested
              Unicode Pass Through:        Not enabled
              Date format:                 INTEGERDATE
              Stored Procedure Option:     Enabled

I put ANSIDATE in multiple places in the job definition file, but still no luck. Please advise.

SET DateForm='ANSIDATE';

USING CHARACTER SET @Characterset
DEFINE JOB LOAD_JOB
DESCRIPTION 'Loading Data From File To Teradata Table'
(
set LogTable=@Substr26TargetTable||'_LTT';
set ErrorTable1=@Substr26TargetTable||'_ETT';
set ErrorTable2=@Substr26TargetTable||'_UVT';
set WorkTable=@Substr26TargetTable||'_WTT';
set ErrorTable=@Substr26TargetTable||'_ETT';

set LoadPrivateLogName=@TargetTable||'_load.log'
set UpdatePrivateLogName=@TargetTable||'_update.log'
set StreamPrivateLogName=@TargetTable||'_stream.log'
set InserterPrivateLogName=@TargetTable||'_inserter.log'
set FileReaderPrivateLogName=@TargetTable||'_filereader.log'

DEFINE OPERATOR SCHEMAMAPPER_OPERATOR
DESCRIPTION 'SCHEMAMAPPER OPERATOR'
TYPE SCHEMAMAPPER
SCHEMA *
ATTRIBUTES
(
VARCHAR DumpFieldsWithTrans = 'Yes'
);

DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TPT LOAD OPERATOR'
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR DateForm = 'AnsiDate'
);

STEP PRE_PROCESSING_DROP_ERROR_TABLES
(
APPLY
('release mload '||@TargetWorkingDatabase||'.'||@TargetTable||';'),
('drop table '||@TargetWorkingDatabase||'.'||@LogTable||';'),
('drop table '||@TargetWorkingDatabase||'.'||@ErrorTable||';'),
('drop table '||@TargetWorkingDatabase||'.'||@ErrorTable1||';'),
('drop table '||@TargetWorkingDatabase||'.'||@ErrorTable2||';'),
('drop table '||@TargetWorkingDatabase||'.'||@WorkTable||';')
TO OPERATOR ($DDL);
);


STEP LOADING
(
    APPLY $INSERT TO OPERATOR ($LOADERUTILITY() [@LoadInstances] ATTR(DateForm='ansiDate'))
    SELECT * FROM OPERATOR ($FILE_READER() [@FileInstances]);
);
);
Teradata Employee

Re: TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

Are you getting an error?

Or are the rows ending up in the error table?

(I think I know what the issue is, and if it is the same as reported by another customer, we are currently working on the fix)

 

 

-- SteveF
Enthusiast

Re: TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

Hi @feinholz

The error is all about data parsing error for the next columns and afterwards. I re-generated the files in INTEGERDATE format, and then the load is working. 

 

The problem is: I don't have an effective way to tell the TPT LOAD and FILE_READER to parse ANSIDATE; the default setting always rules.

 

Also did one more test with TPT 15.10 and found the exactly opposite issue, 15.10 seems to have no (easy) way to automatically infer INTDATE column type in TPT control file, because the inferred column type is always CHAR(10).

WIth both the above observations for auto-inferred schema based on target table structure, I feel that the DATE type handling can cause problem in 15.10 and 16.10 for fastload format.

Teradata Employee

Re: TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

I think I know the issue and it is something we are currently addressing.

 

-- SteveF
Enthusiast

Re: TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

@feinholz great to hear that.

 

Just to confirm, that we have the issue identified for both 15.10 and 16.10 (though the symptons are opposite), and the fix will be for both too?

Teradata Employee

Re: TPT Load ANSIDATE value from Binary/Formatted without specifying column schema

We will ship the fix to 16.10 and 15.10.

-- SteveF