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 agai=
nst a network DB in Protected or Exclusive Update?
=20
For example if I want to do DELETE FROM PSCHEMA.CUSTOMER; then the number o=
f 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 re=
cord when I am deleting the whole lot. My system would generate several hun=
dred million locks and go SOS if I tried it in SU. But my journals are big =
enough to do it.=20
=20
At present I have to find a field which I can select on to break up the del=
etes into manageable chunks and then I can issue a Commit in between. But t=
hat doesn't solve the sharing the DB problem.
=20
Example:
=20
DELETE FROM PSCHEMA.CUSTOMER WHERE CUST_NO < 10000; COMMIT; DELETE FROM PSC=
HEMA.CUSTOMER WHERE CUST_NO < 20000; COMMIT; etc etc until all the CUSTOMER=
records have gone away.
=20
Actually it is more likely that I would be doing an Update and not a Delete=
but the principle is the same. =20
=20
As far as I can see, you always get Shared Update when you use an SQL. Obvi=
ously I should and do use DML to do such things but my question is still va=
lid.
=20
Before anybody tells me to use FORMAT on the AREA, I would point out that t=
he area can and does contain other record types. And anyway this is a mythi=
cal example. I need the Customers otherwise I don't get paid.=20
=20
And I don't want to run it local either. =20
=20
______________________________________________________________
=20
Chris Trayler, IXD
Bank Julius Baer & Co. Ltd.
P. O. Box, CH-8010 Z=FCrich, Switzerland
Telephone +41 (0)58 887 4332, Fax +41 (0)58 887 4969 www.juliusbaer.com <ht=
tp://www.juliusbaer.com/>=20
=20
______________________________________________________________
*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient o=
nly and may contain confidential or privileged information. If you have rec=
eived 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 p=
erson of its contents. If you send us messages by e-mail, we take this as y=
our authorization to correspond with you by e-mail, however, we will not ac=
cept 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 c=
annot be guaranteed to be secure or error-free as information could be inte=
rcepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or=
contain viruses. Neither the Julius Baer Group nor the sender accept liabi=
lity 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 commun=
ications to and from the Julius Baer Group may be monitored. This communica=
tion 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 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: PU or EU in SQL?
"Personally, I would never use dynamic SQL in such a fashion on a production database. It's too dangerous - SQL is notoriously easy to miscode. At least with DMLO, you can only mess up one record (and possibly its members) at a time. With SQL, you can mistakenly update or delete every occurrence of a record with a single command.

At the very least, I would execute the SQL using BCF in local mode (automatic EU), after getting a good backup first. It sounds as though you plan to discommode the users anyway by using PU or EU.

But that does not answer your question. I don't know of any way to specify PU or EU for dynamic SQL run in CV mode.

Outcomes