The question that is hounting my mind now a days is - Why Fastload does not support NUSI on table?
The popular ans which I came across is - in case of NUSI to table are there 1. The target table 2. The sub table created due to NUSI.
As Fast load only supports one Table upload so, NUSI is entertained in Fast Load. But I do have doubt on that one.
Multiload Support 5 table Uplaod at a Time and it does support NUSI. Any given day if Multiload is loading Five table having NUSI will it fails then? No I think( If Sub table considered as a separate table as in this case the count od table will increase to 5*2=10 then which is not supported by Multiload.)
Any thoughts -
IMHO there's no need for FastLoad to support NUSIs as the target table must be empty and you can easily add an index after load.
Regarding MLoad: you can load 5 tables with 32 NUSIs each using ML :-)
A table consists of subtables and ML loads tables and not subtables.
As ML supports populated tables there was a need to allow at least NUSIs (which are easy to maintain as they're AMP-local). Just USIs can't be loaded as they're not AMP-local and the overhead inter-AMP communication was probably too large.
Dieter gave a good reason why FastLoad doesn't need to support NUSIs.
Regarding the original question of why FastLoad doesn't support NUSIs -- it's the same reason why FastLoad doesn't support many features -- FastLoad is a very old and very primitive load protocol, and changing FastLoad to support more database features would make it slower. Making it slower would be unacceptable to customers who rely on tight overnight batch job timing windows.
FastLoad is a "capped" feature, and has been for a long time, meaning that the feature is supported, but the feature is not being extended for interoperability with any new database features.
i want to load data from file to TD_TBL through Fastload,
file is having date format some records like (2014/02/28)(20140228)
the file having 1 millian records two dif dare formats how to load with out geting error ?
there are 2 ways either request source team to send in one format.
if this is not possible then load it as varchar column in a temp table once loaded then based on number of characters cast and load to other table having column defined as Date.
i ahave 1 source file (diff date formats' (2014/02/28)(20140228)'),
using fast load- 1 target table , is it possible to load ?.
Set your target column to a character data type long enough to support either format, in your example char(10) or varchar(10) . Then transform the data into a date data type between stage and target.
I have one scenerio in which fast load needs to have NUSI. It is when we are fetching data from the target table1 (of fastload) loading to other table target table2 using one of the target table1 column (which is duplicate in other rows) in where clause. That column is not part of PI in table. How do we select data from the target table1 so that it gets proccessed fast in target table2.
We could have extra privelege by adding NUSI in target even while loading into target table because anyway subtables are not loaded it is actually the main tables getting loaded based on PI the load performance would not get impacted because of this. Moreover as you said maintenance is also easier as it local to amp then why not NUSI in fast load?