I am trying to load below two records using TPT( version 15.10.00.00) and able to load succusfully second record but first record is going to error table. The same records gets loaded in TPT version 14.00.00.08 .
This is comma delimitted file and 4th,5th,6th places are date field
i am already usind attribute
I suscept this problem may be because of version change as same file and same code is able to load the file to table correctely when TPT 14.00.00.08 is used and same has issue when using TPT 15.10.00.00
Any input on this woul be of great help
On which platform are you running?
Have you looked at the error table to see which column is being flagged as the error column?
Obviously, it does not make sense (to me) because the data is pretty much identical.
Also, I am hoping that your attributes were hand-written and not copied-and-pasted, because "QuotedData" is misspelled in your post.
It appears in the post as "Quaoteddata". A misspelled attribute name would not enable the feature (just checking).
Thanks Streve for responsding
Platform :TD 15.10
Yes error table show the column which is date
Error table :EXX_ATTR_e1
Yes that quotes and all are typo errors
Yes that quotes and all are type errors but i have them correctly in script. Concern here is same data is loaded correctly in 14.10 where as it is rejecting in 15.10
Can you provide me with the table definition and script you are using?
And at first you said it worked in 14.00.00.08, and then you say it works in 14.10.
Does it work in both 14.00 and 14.10, but not in 15.10?
CREATE MULTISET TABLE DEV.EXX_ATTR ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
Col2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
Col3 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
Col4_DATE DATE FORMAT 'YYYY-MM-DD',
Col5_DATE DATE FORMAT 'YYYY-MM-DD',
Col6_DATE DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX EXT_STORE_SECURITY_PI ( Col1 );
DEFINE JOB load_EXX_ATTR(
DEFINE OPERATOR W_1_op_loadEXX_ATTR
VARCHAR UserName, VARCHAR UserPassword, VARCHAR LogTable, VARCHAR TargetTable,
INTEGER BufferSize, INTEGER ErrorLimit, INTEGER MaxSessions, INTEGER MinSessions,
INTEGER TenacityHours, INTEGER TenacitySleep, VARCHAR AccountID, VARCHAR DateForm,
VARCHAR ErrorTable1, VARCHAR ErrorTable2, VARCHAR WorkTable ,VARCHAR NotifyExit, VARCHAR NotifyExitIsDLL,
VARCHAR NotifyLevel, VARCHAR NotifyMethod, VARCHAR NotifyString, VARCHAR PauseAcq,
VARCHAR PrivateLogName, VARCHAR TdpId, VARCHAR TraceLevel, VARCHAR WorkingDatabase,
VARCHAR QueryBandSessInfo );
DEFINE SCHEMA W_0_sc_loadEXX_ATTR
DEFINE OPERATOR W_0_op_loadEXX_ATTR
TYPE DATACONNECTOR PRODUCER
VARCHAR FileName, VARCHAR Format, VARCHAR OpenMode, INTEGER BlockSize,
INTEGER BufferSize, INTEGER RetentionPeriod, INTEGER RowsPerInstance, INTEGER SecondarySpace,
INTEGER UnitCount, INTEGER VigilElapsedTime, INTEGER VigilWaitTime, INTEGER VolumeCount,
VARCHAR AccessModuleName, VARCHAR AccessModuleInitStr, VARCHAR AcceptExcessColumns,
VARCHAR FileList, VARCHAR ExpirationDate, VARCHAR RowErrFileName,VARCHAR EscapeTextDelimiter,
VARCHAR IndicatorMode, VARCHAR PrimarySpace, VARCHAR PrivateLogName, VARCHAR RecordFormat,
VARCHAR RecordLength, VARCHAR SpaceUnit, VARCHAR TextDelimiter, VARCHAR VigilNoticeFileName,
VARCHAR VigilStartTime, VARCHAR VigilStopTime, VARCHAR VolSerNumber, VARCHAR UnitType,
VARCHAR NullColumns, VARCHAR QuotedData, VARCHAR OpenQuoteMark, VARCHAR CloseQuoteMark
'INSERT INTO DWBATCHDEV.EXX_ATTR(
:Col4_DATE (DATE,FORMAT ''YYYYMMDD''),
:Col5_DATE (DATE,FORMAT ''YYYYMMDD''),
:Col6_DATE (DATE,FORMAT ''YYYYMMDD'')
UserName = @UserName,
UserPassword = @PassWord,
LogTable = 'TMP.EXX_ATTR_lg',
ErrorTable1 = ' TMP.EXX_ATTR_e1',
ErrorTable2 = ' TMP.EXX_ATTR_e2',
WorkTable = ' TMP.EXX_ATTR_wk',
TargetTable = 'DEV.EXX_ATTR',
MaxSessions = 10,
PrivateLogName = 'LOAD',
TenacityHours = 1,
TenacitySleep = 1,
TdpId = @TdServer
FileList = 'Y' ,
FileName = '/dev/log/tdtst_DEV_EXX_ATTR.lst',
Format = 'DELIMITED',
QuotedData = 'Optional',
OpenQuoteMark = '"',
CloseQuoteMark = '"',
OpenMode = 'Read',
AcceptExcessColumns = 'Y',
PrivateLogName = 'READ',
NullColumns = 'Y',
TextDelimiter = ','
It worked on 14.00.00.08 not in 15.10.00.00
I have attached script and DDl
Thank you for all of the information.
I ran the same job on my system with TPT 15.10 and find that both rows end up in the error table. And this is correct in that both rows need to be treated the same way. With that data, there should never be a case where one row loads into the target table and the other one ends up in the error table.
Of course, that does not explain the problem, but on my system (and you can try the same thing), I exported the data from the DataParcel field in the error table to see what is causing the problem and I see what is happening.
If you do this:
1. run BTEQ
2. .export data file=<some file name>
3. sel dataparcel from <tablename>;
4. .export reset
5. exit out of BTEQ
Go take a look at the data file. If you are on Unix/Linux and can use 'od -c' you can dump out the contents of the file and look at the characters. And you will see that the quotes were shipped as part of the data. And this is what was causing the DATE fields to be rejected (the DBS only reports the first one it encounters that is bad, but all of the DATE fields are bad).
We are looking into why we are keeping the quotes with the data. That should not be happening. They should be stripped out.
Yes Steve , you are right both should go to error table , i think i was testing with different set and i have mention the same , my bad
Do you know why same thing is correctly loaded in TPT 14.00.00.08 but not in TPT 15.10.00.00
This is part of our testing where we are upgrating from 14.10 to 15.10 and file format cannot be chnaged because of too much work at our side and also there is no convience answer for changing all the source files