Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
18%
200 - 500 GB
29%
500 - 800 GB
3%
800 - 1200 GB
8%
1200 - 1500 GB
8%
1500 - 2000 GB
12%
> 2000 GB
22%
Total votes: 76

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th August 2019, 10:50
Rinkashiki Rinkashiki is offline
Member
 
Join Date: Apr 2013
Posts: 32
Rinkashiki is on a distinguished road
Baan: 10.4 - DB: oracle - OS: windows 7
delete from
Baan: Infor ERP 10.4
C/S: None/Unknown

hello
somebody faced such design?
delete from table
enddelete
commit.transaction ()

gives out an error of Error 107 (Record is locked)
though works at night
Reply With Quote
Sponsored Links
  #2  
Old 6th August 2019, 14:01
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,365
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Not sure I understand. What do you mean it works at night? Are you locking the records before the delete? I have usually seen something like this when the commit.transaction was not doe at the right spot and the transaction was not ended successfully.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 6th August 2019, 14:29
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,807
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox Tools 10.7 - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
db.clear.table ?

Hello,
why not ?
Code:
db.clear.table()
Syntax:

function long  db.clear.table  (long table_id [, long flag] [, long comp_nr])

Description


This deletes all data from a specified table. Reference counters are automatically updated.

By default, all deleted records are saved in rollback segments. Consequently, using this function to clear a large table can result in reduced performance and, in some cases, can cause overflow of the internal rollback segments.

Note that this function cannot be used within a transaction if the option NO.ROLLBACK is given; see Transaction handling.


Arguments

long table_id The table ID, as returned by db.bind().
 
[long flag ] Use this optional argument to indicate whether the delete operation must be performed as a single transaction or as multiple transactions. The possible values are:

0 WITH.ROLLBACK The table is cleared in a single transaction. All records are saved in rollback segments. This is the default option. 
1 NO.ROLLBACK The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.
 
The table is cleared in a single transaction. All records are saved in rollback segments. This is the default option.

The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.
 
[long comp_nr ] This optional argument specifies a company number for the table. The default company is the company of the user.
 
Return values

0 success

<>0 error

Context

This function can be used in all script types.
__________________
//Bernd
Reply With Quote
  #4  
Old 7th August 2019, 05:29
Rinkashiki Rinkashiki is offline
Member
 
Join Date: Apr 2013
Posts: 32
Rinkashiki is on a distinguished road
Baan: 10.4 - DB: oracle - OS: windows 7
about this db.clear.table I know. but it is necessary to remove records on a condition:
delete from tisfc001
where tisfc001.osta=1
enddelete
commit.transaction ()
Reply With Quote
  #5  
Old 7th August 2019, 13:48
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,365
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
My code would look like
Code:
db.retry.point()
select tisfc001.* 
from tisfc001 for update
where tisfc001.osta=1
  db.delete(ttisfc001, db.retry)
  commit.transaction()
endselect
Now we can debate where to put the commit, but inside the select it will commit each record. This way if something errors out you can re-run it and not have to re-delete a bunch of records. Now depending on what I am doing I might commit every 50 or 100 records. But if I am delete 1000's of records I typically never commit after all the records.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #6  
Old 7th August 2019, 18:41
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,104
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
Quote:
Now we can debate where to put the commit, but inside the select it will commit each record. This way if something errors out you can re-run it and not have to re-delete a bunch of records. Now depending on what I am doing I might commit every 50 or 100 records. But if I am delete 1000's of records I typically never commit after all the records.
From the Design Principles Guide -
Quote:
Size of database transactions
Problem
For optimal performance, database transactions must not be too small and not too large. However they must always be logical transactions.

Large transactions will also have relatively long locking times. Depending on the frequency of updates done by other users, this can cause locking problems.

Other problems can also occur, for example, run-time tools (bshell, driver, and so on) consume more memory and CPU, more rollback segments are needed in the RDBMS.

Solution
If possible, do not implement the commit.transaction() for each single update. On the other hand, if possible, do not implement the commit.transaction() for too many updates.

It is difficult to give hard numbers for this. The optimal transaction size depends on many factors.

Currently a general rule can be about 100 to 250 updates per commit. (The commit rate). Apply these numbers only if the chances are small that other users update the same records.

Example
Bad situation: one commit per updatedb.retry.point()

select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
commit.transaction()
endselect
Bad situation: commit for (more than) 1000 updates

db.retry.point()
select table.*
from table for update
selectdo |* table can have more than
... |* 1000 rows !
db.update(table, db.retry)
endselect
commit.transaction()
Improved: commit per 100 updates

long number.of.updates

db.retry.point()
number.of.updates = 0
select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
number.of.updates = number.of.updates + 1
if number.of.updates = 100 then
commit.transaction()
number.of.updates = 0
endif
selecteos
commit.transaction()
endselect
The selecteos section with the commit.transaction() is needed. It will be explained in help page 'implementation of selecteos'.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #7  
Old 7th August 2019, 18:47
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,104
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
Quote:
delete from table
enddelete
commit.transaction ()

gives out an error of Error 107 (Record is locked)
Rinkashiki,

To delete a record, you need exclusive access i.e., no other transaction has a lock on the same record.

It appears like you are trying to delete via MS-SQL than Baan/LN-SQL code-
To control the transaction size you can specify the number of rows or conditions etc. Refer to - SQL Server DELETE
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #8  
Old 9th August 2019, 02:51
Rinkashiki Rinkashiki is offline
Member
 
Join Date: Apr 2013
Posts: 32
Rinkashiki is on a distinguished road
Baan: 10.4 - DB: oracle - OS: windows 7
thanks to all for answers. the matter is that year the program worked without problems. and now started giving out a mistake. I will test still
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update the data to another table when delete the one data tracylee Tools Development 6 28th August 2015 09:38
Delete Button is not enabled charlesirwincbe Tools Development 2 6th August 2015 10:13
Delete Millions Of Records with Less Time and Performance bhuvaneshwari Blogs and Web links 1 29th May 2009 11:30
How to delete record in scheme? yurong Tools Administration & Installation 4 30th December 2004 23:35


All times are GMT +2. The time now is 19:10.


©2001-2018 - Baanboard.com - Baanforums.com