hello,

we have a table which will regularly undergo heavy data modifications. this
table is not used on remote sites, only on the consolidated. table is not
replicated. the modifications flood the log file.

do we have any options to somehow manipulate the table with no logging at
all? we aware of LOAD TABLE, but the situation is more difficult, updates
and deletes are also needed.

Krisztian,

GLOBAL TEMPORARY tables can be NOT TRANSACTIONAL, but the data can only be
seen from the connection that inserted it. From version 10, there is the
option to have these tables as SHARE BY ALL.
But in any case, the table contents is lost latest when the database is
shut down.

Depending on your requiremnts, this might or might not be an option.

Frank

On Thu, 15 Nov 2007 10:02:17 +0100, krisztian pinter
<pinter.krisztian (AT) chello (DOT) hu> wrote:

On Thu, 15 Nov 2007 12:11:39 +0100, Frank Ploessel
<fpl... (AT) d_e (DOT) i_m_s_h_e_a_l_t_h.c_o_m> wrote:

alas, not. data should be permanent.

now we thinking of a separate database and proxy tables, but the
performance might be a problem.

if all fails, we will use unload table, assemble the new data elsewhere,
truncate table and load table to completely replace the data.

Frank has already mentioned global temp tables, and share by all option,
and you have come up with the idea of using OMNI tables,... so I can't
think of any other easy solution....

but I would like to better understand your requirements.

You want to have a table that has:
- nothing added to the transaction log, but
- contents maintained across shutdown/startup of the database

So this implies that the table must be a permanent table - not a temp table.

What transactional semantics would you expect?
i.e. if the connection rolls back its transaction, would you expect the
changs to this table to also be updated?
Or would you be ok with the table being treated completely non-transactional
i.e. once a change was made to the table, it is immediately made permenant?

Also, if there is nothing recorded in the transaction log, there would
be no way of recovering the table data if the database is corrupted.
(since there is no record of what went into the table).
Would this be ok in your situation?

Depending on these answers, someone might have a solution for you?
--
Mark Culp
SQLAnywhere Research and Development
iAnywhere Solutions Engineering
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
krisztian pinter wrote:

On Thu, 15 Nov 2007 15:48:22 +0100, Mark Culp
<reply_to_newsgroups_only_please_nospam_mark.cul p (AT) iAnywhere (DOT) com> wrote:

that's correct

none. the table will be modified by only one specific process, and we
require no protection against anything. we can run the data load
process at night, on an exclusive connection. we will ensure data
integrity ourselves. no need to roll back, no need to recover from
failures. inconsistent data is OK.

to give a figure: this table is a sales data table which we will
receive daily. the table have some thousands of new rows every day,
but the new rows are computed based on the old ones and on external
data. it may change in the future to some hundreds of thousands
each day. this table in fact not part of our database, we don't
use it internally, only run some scheduled queries against it
joined with other tables that *are* ours, and produce some
reports.

if we had a separate database for that reason, we would have to
transfer all clients, users, products, organization hierarchy, etc
to that other database, which is a pain. not to mention many such
structures are not stored, but computed by procedures. we don't
want to mirror and maintain all these in the sales database.

thanks for your effort

Given these requirements, then external processing to compile the data
followed by two steps:
- truncate table ...
- load table ...
will be your best method. Note that load table does not record the
data in the transaction log.

Note: to reduce the "pain" in transfering all of your client data, etc
to another database, you could just start with a backup of the "real"
database, add/update/delete the sales data table in this copy db,
then unload the sale table from the copy, (and then throw away the copy),
truncate the table in the "real" database, and do a load table.

Note that the above mentioned "backup" copy is not referring to your
normal regularly scheduled backup copy that you should be making.

- Mark

krisztian pinter wrote:

On Thu, 15 Nov 2007 16:45:56 +0100, Mark Culp
<reply_to_newsgroups_only_please_nospam_mark.cul p (AT) iAnywhere (DOT) com> wrote:

thanks for advices.