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:
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]); ); );
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)
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.
I think I know the issue and it is something we are currently addressing.
@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?