problem loading huge data into dataset

Nareshkotti · Post by **Nareshkotti** » Sat Oct 29, 2011 1:39 am

Hi All,
I want to write huge amount of data from a table into a dataset.
Number of records are almost equal to 17crores.

After loading 11crore records job is getting aborted. I'm getting the below errors

TGT_RM_IMAGE_LOC: ODBC function "SQLEndTran(SQL_ROLLBACK)" reported: SQLSTATE = 08S01: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver]Socket closed.

TGT_RM_IMAGE_LOC: [IIS-CONN-ODBC-000004] ODBC function "SQLEndTran(SQL_ROLLBACK)" reported: SQLSTATE = 08S01: Native Error Code = -4: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead. (CC_OdbcConnection::rollback, file CC_OdbcConnection.cpp, line 953)

Job design:
ODBC---->COPY STAGE--->DATASET

can someone help me out.

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Sat Oct 29, 2011 5:27 am

It's nothing to do with the Data Set. Your connection to Oracle is dead. You need to investigate that.

PaulVL · Post by **PaulVL** » Sun Oct 30, 2011 9:22 am

And fyi: cores have nothing to do with data volume.

chulett · Post by **chulett** » Sun Oct 30, 2011 9:30 am

PaulVL wrote:And fyi: cores have nothing to do with data volume.

They said crores not "cores".

PaulVL · Post by **PaulVL** » Sun Oct 30, 2011 9:52 am

I think my reply may have been affected by 17 Coors.

chulett · Post by **chulett** » Sun Oct 30, 2011 9:52 am

ray.wurlod · Post by **ray.wurlod** » Sun Oct 30, 2011 11:47 am

That's over a hexadeciCoors !

Nareshkotti · Post by **Nareshkotti** » Wed Nov 02, 2011 10:56 am

Hi ray,

The issue got resolved, but i used fileset instead of dataset then the job ran successfully.

The job nearly took 12hrs to complete, can you suggest any other alternative so that it will take less time, again i need to use this data as a reference in other job.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 02, 2011 6:39 pm

Use a Data Set. The operator is copy (whereas it's export for a File Set), which is bound to be more efficient.

The problem was with Oracle, not with your chosen staging technique,