ca.portal.admin

Re:PU or EU in SQL?

Discussion created by ca.portal.admin on Jul 28, 2010
Does anybody know if it is possible to run an SQL update using IDMSBCF against a network DB in Protected or Exclusive Update?

For example if I want to do DELETE FROM PSCHEMA.CUSTOMER; then the number of record locks generated is enormous and I want at least PU or even better EU anyway because clearly I don't want anybody trying to add or modify a record when I am deleting the whole lot. My system would generate several hundred million locks and go SOS if I tried it in SU. But my journals are big enough to do it.

At present I have to find a field which I can select on to break up the deletes into manageable chunks and then I can issue a Commit in between. But that doesn't solve the sharing the DB problem.

Example:

DELETE FROM PSCHEMA.CUSTOMER WHERE CUST_NO < 10000; COMMIT; DELETE FROM PSCHEMA.CUSTOMER WHERE CUST_NO < 20000; COMMIT; etc etc until all the CUSTOMER records have gone away.

Actually it is more likely that I would be doing an Update and not a Delete but the principle is the same.

As far as I can see, you always get Shared Update when you use an SQL. Obviously I should and do use DML to do such things but my question is still valid.

Before anybody tells me to use FORMAT on the AREA, I would point out that the area can and does contain other record types. And anyway this is a mythical example. I need the Customers otherwise I don't get paid.

And I don't want to run it local either.

______________________________________________________________

Chris Trayler, IXD
Bank Julius Baer & Co. Ltd.
P. O. Box, CH-8010 Zürich, Switzerland
Telephone +41 (0)58 887 4332, Fax +41 (0)58 887 4969 www.juliusbaer.com <http://www.juliusbaer.com/>

______________________________________________________________
*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient only and may contain confidential or privileged information. If you have received this e-mail by mistake, please contact us immediately and completely delete it (and any attachments) and do not forward it or inform any other person of its contents. If you send us messages by e-mail, we take this as your authorization to correspond with you by e-mail, however, we will not accept the electronic transmission of orders/instructions without a specific agreement being in place to govern the same. If you do not wish to receive any further e-mail correspondence please let us know. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Neither the Julius Baer Group nor the sender accept liability for any errors or omissions in the content of this message which arise as a result of its e-mail transmission. Please note that all e-mail communications to and from the Julius Baer Group may be monitored. This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: PU or EU in SQL?
"It isn't production. We don't allow anybody to run un-controlled SQL in =
Prod.

The developers love SQL. Even though I won't let them use it in Prod, =
they use it extensively to verify their test results or to set up data =
scenarios in the test system.

I started by giving them a free hand in test. After all it is, in =
theory, their data and they are responsible people - not. After a couple =
of weeks (and this is several years ago) I took away their general =
rights to use SQL in update mode in test. This was because, as you =
rightly point out, it is very easy to mis-code an SQL and either wreck =
large tracts of the database or to place so many locks that the CV goes =
SOS. Either way it was down to DBA to sort it out. It is like giving =
matches to a baby - it isn't their fault when the house burns down.

Even so with just Select access they can still occasionally wreck a =
system by cavalier use of Group By or Order by or by messing up or =
missing out join parameters on large tables. I also let them create =
temporary tables - probably a bad move as someone just tried to create =
one with 250 Million rows in it. These are usually the same users who go =
to management meetings and make useful suggestions like migrating the =
IDMS system to MS Access and running it on a PC.

Now they have to convince me that the update SQL they want to run is =
sensible before I will grant them the rights. I also have to take into =
account which developers they are as - just like with babies - some of =
them are naturally careful and responsible and others are psychos.

I didn't think there was a way to do it. I have gone through all the =
manuals. I just thought someone might have come across a technique that =
is not documented.

Chris =20

Outcomes