Out of Memory

Question

Out of Memory

db2l

Ten Centuries

Points: 1386
More actions
September 15, 2005 at 7:51 pm

#68486

Hi,
I have a DTS package that runs nightly to transfer data from SQL Server to DB2. It drops and re-creates the table on DB2, then transfers a sub-set of the data from the SQL Server table. When the DB2 table grows, the DTS fails with the following error:
OUT OF MEMORY
MICROSOFT CURSOR ENGINE
I have tried increasing the memory on the SQL Server instance, but it still fails with the same error. There are no errors reported in the DB2 log.
Anything else I can try?
tks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Meghana SSC Eights! Points: 840 More actions · Answer 1

How are you transferring the data between the two databases ? Are you using any query to transform the data from sql server ( in Transform task) or is it a direct mapping ?

Regards,

Meghana

db2l Ten Centuries Points: 1386 More actions · Answer 2

db2l

Ten Centuries

Points: 1386

September 17, 2005 at 5:10 am

#590787

Hi,

I am using a query to tranform the data.

Meghana SSC Eights! Points: 840 More actions · Answer 3

Can you run the quey in QA and see if it is running fine?

Regards,

Meghana

db2l Ten Centuries Points: 1386 More actions · Answer 4

Hi,

The query is fine, it does transform a number of rows, but then I guess it runs out of memory and fails...

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 5

Is the query running fine or has it as result Query batch completed ?

Are there other processes running at night such as db optimalisation ?

JV

db2l Ten Centuries Points: 1386 More actions · Answer 6

Hi,

The query runs fine, if I reduce the number of rows to be copied, the job completes successfully. If I increase the number of rows, the job fails with "out of memory".

When this job runs, there are no other jobs running.

tks.

Gay Grymes SSC Rookie Points: 35 More actions · Answer 7

Hi, I'm getting this same error using Asp/ADO. If I reduce the number of rows to 100000 using the maxrecords method, the problem is resolved. Any answers yet?

Vichka Ten Centuries Points: 1218 More actions · Answer 8

I found this in BOL :

Microsoft OLE DB Provider for Access buffers all inserts in memory and only commits them when the DTS Import/Export Wizard completes operation. As a result, you can face a low memory situation when you export large tables. However, you can resolve this issue by constructing SELECT statements that send smaller numbers of rows in multiple passes.

Maybe something similar is going on in your cases?

db2l Ten Centuries Points: 1386 More actions · Answer 9

thanks.

So I guess the only way around this problem would be to create two DTS packages that have different SELECT ranges? Would anyone agree?