Is it possible to issue an update command that does not
write the transaction log?

Phil Harris

SQL Anywhere 9.0.2.2451

Phil wrote:
You would have to start the database in batch mode with the -b switch,
meaning that every operation on the database would not be written to the
transaction log.

This is not something I would recommend.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Please reply only to the newsgroup

Documentation : Exercise your WRITE @DocCommentXchange: DCX.sybase.com
SQL Anywhere Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere
-> Optionally set filter to "Display ALL platforms IN ALL MONTHS"

What are your reasons for wanting to do this? There may be alternative
approaches; for example:

- Putting the log on a disk drive that's separate from the database
can speed I/O.

- dbbackup / BACKUP can be used rename / restart the log to reduce the
file size.

Tell us what you are trying to accomplish.

BTW it may not help at all but temporary tables can be NOT
TRANSACTIONAL.

Breck




On 6 Nov 2009 04:49:20 -0800, Phil wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Hi guys, thanks for the replies.

We distribute remote databases to our customers by DVD. The
database has certain encrypted tables (by altering the
column types and running an update command to encrypt the
data). During installation, tables are decrypted (based on
customer entitlement) creating many entries in the
transaction log slowing down the installation process
significantly.

At the moment our workaround is to ship the database without
a transaction log, and once the decryption is complete the
installer re-enables the log with dblog which means shipping
that dependency with the installer.
The original post came up after one of our dev’s asked me
“cant we make just these update commands non
transactional?”

Phil

It sounds like Reg's suggestion is best. Be sure to read and
understand the Help discussion of -b.

No, there is no other way to suspend and restart logging on the fly.

FWIW the InstallShield setup for the Foxhound database monitor does
not include *any* logic, it is all embedded within the database
itself, in SQL. That includes registration-key-activation, as well as
all sorts of internal initializations. There is a DatabaseStart event
that determines if this is a first-time-start or not. There is some
funky stuff which I could go look up if you are interested in the
approach.

Anyway, you could launch the database with -b, do your thing in the
DatabaseStart, and then issue a STOP DATABASE.

Or do the same thing from your application, with a database table
telling you whether this was a first-time or not.

Breck




On 12 Nov 2009 16:03:47 -0800, Phil wrote:

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail