Loading Data into Oracle partitions based on months

[SwathiCh]

Hi All,

I have a table which is partitoined into 12 partitions based on month, from ETL job I need to load the data into separate partitions based on month coming in my source.

Can any one help me how can we load the data into a particular partition from datastage parallel job?


All JAN data should go into JAN partiton and all FEb data should go to FEB partitin etc.

And also i have a requirement to update records in particular partition from datastage job.

The question here is, How can we specify partitoin number or name in Oracle EE stage in datastage parallel?

[nayanpatra]

The table should be database partioned based on the column date eg. FISCAL_MONTH. This will enable the database engine to configure the data according to the partitions. Datastage will not require any special treatment to load the data in a particular partition. However, if a table is database partitioned then it may take toll on the datastage job performance measures. But in spite of that this design is recommendable for huge tables. However, while reading data from a particular partition you can always modify the where clause in SQL to restrict the row fetch to a particular partition. One particular approach will be usage to 2 job parameters, say MIN_DATE and MAX_DATE, wherein MIN_DATE holds the minimum value of the partition and MAX_DATE holds the maximum value of the partition. By using these as job parameters, you can provide the values at runtime. There may be other techniques as well to handle this functionality.