topic Re: Modify Database records and performance tuning in Application Development and Automation Discussions

Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 05:08:42 GMT

Hi all,

I have developed a report which modifies the data base records .

below is the code .

modify yikostk from table itab.

if sy-subrc = 0.

commit work.

else.

rollback.

endif.

in the above code

1) Is it mandatory to use commit work ? with out commit work, the database records can not be modified?

2)when i remove the commit work and rollback statement my report performance is good, if i use commit work the batabase utilisation is more.

can any one please explain what is the exact use of the commit work and rollback statements.

onemore doubt is while modifying the database recors we have to lock the database and unlock the same .

are there any function modules for locking and unlocking?

thanks

Re: Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 05:15:58 GMT

Hi,

I hope the below links will help you.

For Commit Work:

http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls154.htm

For Roll Back:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls643.htm

Thanks & Regards,

Khushboo.

Re: Modify Database records and performance tuning

GauthamV — Wed, 17 Dec 2008 05:19:00 GMT

hi,

you dont need to explicitly mention commit work.

for locking tables use these.


CALL FUNCTION 'ENQUEUE_E_TABLE'
EXPORTING
tabname = 'ZTEST'      <----- Your table name
EXCEPTIONS
foreign_lock = 1
system_failure = 2
OTHERS = 3.

Unlock: 
CALL FUNCTION 'DEQUEUE_E_TABLE'
EXPORTING
tabname = 'ZTEST'.    <------ Your table name

Re: Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 05:26:28 GMT

This message was moderated.

Re: Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 05:32:45 GMT

so u want to say there is no need of commit statement !

so Modify satement directly modifies the entries of the database ?

if so what is the exact use of commit and when it should be used.

please help me by clearing this issue.

thanks.

Re: Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 05:33:37 GMT

This message was moderated.

Re: Modify Database records and performance tuning

Former Member — Wed, 17 Dec 2008 16:38:46 GMT

Hello,

You do not need to explicitly commit changes to the database. An implicit commit will be executed

at the end of a logical unit of work(LUW) which will happen at the end of your transaction. At this time

all requested/queued changes to the db (your modify for example) will be executed and if successful, commited.

If you want to, you can explicity commit or rollback changes in your program, for example if you want to

update your changes in groups of a 100 or so. Then you must decide what happens if you have a modify that fails.

Regards

Greg Kern

Re: Modify Database records and performance tuning

Former Member — Tue, 23 Dec 2008 06:19:57 GMT

hi all

i have written the following code in my development system to change the records of my database.

SELECT * FROM zyikostl INTO TABLE itab.

LOOP AT itab INTO wtab.

tabix = sy-tabix.

DO.

READ TABLE itab INTO ktab WITH KEY kostl_alt = wtab-kostl_neu.

IF sy-subrc = 0.

wtab-kostl_neu = ktab-kostl_neu.

MODIFY itab FROM wtab INDEX tabix.

CONTINUE.

ELSE.

EXIT.

ENDIF.

ENDDO.

ENDLOOP.

MODIFY zyikostl FROM TABLE itab.

In the development system i have only few records its working fine,but when it is been moved to test system there are 2500 records in the database so the report is taking much time(more than 5 hours )

i sthere any solution for this ....

thanks,

vaasu.