That Cursor Thing 'cos I did a COMMIT - CX_SY_OPEN_SQL_DB

[Former Member]

Hi ABAPers

I have a massive amount of data to churn through as part of a migration over a number of tables,so naturally to minimize the risk of a program crash due to memory I want to commit my inserts in nice sized chunks.

I cannot risk having over a million records or more tied up in memory before the program performs it standard commit on exit.

Typically I do my reads in Package Size chunks and that works great for performance, but as I also need to commit the cleansed data within that Package read...which naturally on the next package read, results in my friend the CX_SY_OPEN_SQL_DB Error...because the "Cursor already closed or not open yet'.

So I've read a stack of threads on using the OPEN CURSOR with HOLD and all of that jazz...we'll that's not working either.

As a real last resort I could multi-thread the Insert into standalone call Asynchronous set of processes but I'd rather not give away that much control as this data must be 100% accounted for once the processing is complete. To code all of that in would be a last resort.

Surely this should be possible within the same process and I'm just missing something obvious.

As always, just like every other thread on SCN this is so totally urgent...

Looking forward to your responses.

Many Thanks

Arden

[Former Member]

Hi ABAPers

I have a massive amount of data to churn through as part of a migration over a number of tables,so naturally to minimize the risk of a program crash due to memory I want to commit my inserts in nice sized chunks.

I cannot risk having over a million records or more tied up in memory before the program performs it standard commit on exit.

Typically I do my reads in Package Size chunks and that works great for performance, but as I also need to commit the cleansed data within that Package read...which naturally on the next package read, results in my friend the CX_SY_OPEN_SQL_DB Error...because the "Cursor already closed or not open yet'.

So I've read a stack of threads on using the OPEN CURSOR with HOLD and all of that jazz...we'll that's not working either.

As a real last resort I could multi-thread the Insert into standalone call Asynchronous set of processes but I'd rather not give away that much control as this data must be 100% accounted for once the processing is complete. To code all of that in would be a last resort.

Surely this should be possible within the same process and I'm just missing something obvious.

As always, just like every other thread on SCN this is so totally urgent...

Looking forward to your responses.

Many Thanks

Arden

[Sandra_Rossi]

The ABAP documentation says:

If the addition WITH HOLD is specified, the database cursor is not closed by a database commit executed using Native SQL. The addition has no influence, however, on implicit database commits or on any rollbacks which always close the database cursor.

Note : A Native SQL database commit can be executed with the DB_COMMIT function module, for example.

So, if it doesn't work, you should contact the SAP support.

[SuhaSaha]

but as I also need to commit the cleansed data within that Package read...which naturally on the next package read, results in my friend the CX_SY_OPEN_SQL_DB Error...

For the sake of clarity, how are you "committing" the data? As Sandra has already pointed out, WITH HOLD prevents the SQL cursor from being closed by "explicit" DB-commits.

As a real last resort I could multi-thread the Insert into standalone call Asynchronous set of processes but I'd rather not give away that much control as this data must be 100% accounted for once the processing is complete. To code all of that in would be a last resort.

Of late i have been using parallel processing capabilities of ABAP and from my experience i can say that it's not that complex to code Of course because since the data integrity is of utmost importance in your program, strong fallback mechanisms have to coded as well

BR,

Suhas

[Former Member]

Thank you both for your guidance here.

I'm using the good old fashioned COMMIT WORK AND WAIT.

I suspect I should instead be using the FM DB_COMMIT or alternatively coding that into my program.

I'll give it a try in the morning

Arden

[Former Member]

So it was the DB_COMMIT that I should have been using.

Another day another lesson learned