SQL Anywhere database - ASA copy data to antoher table

Author

ASA copy data to antoher table

Eric Verhorstert

2005-07-14, 11:23 am

If i copy the data from one table to another in the same database I use
the following sql commands:

// delete table
DELETE table_EDI WHERE ......

// fill table again
INSERT INTO table_EDI( column 1, colum2, colum 3)
SELECT table_1.column1,
table_1.column2,
table_1.column3,
FROM table_1, table 2
WHERE blah blah blah

Since I do this with about 10 tables once a week the log files growth
with an incredible speed.

Is there antoher way to copy the information?

Thanks
Eric

Breck Carter [TeamSybase]

2005-07-14, 8:23 pm

You could use dbbackup to rename and restart the log file on a regular
basis, and delete old backups when they are no longer needed for
disaster recovery.

You could also run with dbeng9 -m (generally not recommended).

If you use unload table and load table, the log file won't grow so
much.

Breck

On 14 Jul 2005 08:13:13 -0700, Eric Verhorstert <ontsnapt@hotmail.com>
wrote:

>If i copy the data from one table to another in the same database I use
>the following sql commands:
>
>// delete table
>DELETE table_EDI WHERE ......
>
>// fill table again
>INSERT INTO table_EDI( column 1, colum2, colum 3)
>SELECT table_1.column1,
> table_1.column2,
> table_1.column3,
>FROM table_1, table 2
>WHERE blah blah blah
>
>Since I do this with about 10 tables once a week the log files growth
>with an incredible speed.
>
>Is there antoher way to copy the information?
>
>Thanks
>Eric

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-07-14, 8:23 pm

Breck Carter [TeamSybase] wrote:
>
> You could also run with dbeng9 -m (generally not recommended).
>

"Generally" ??

Just to drive this point completely home, let me say that running with
"-m" is *never* recommended.

You only use -m when you don't care about your data. If losing data is
a viable option for your application, then using -m is viable.

Don't use -m.
Don't use -m (that one is for Google searches).
Don't use -m (that one is for Reg).

But Breck is right, it is an option (that you should not use).

Any questions? :-)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-07-14, 8:23 pm

I stand by "generally not recommended".

Truncating a transaction log after a checkpoint does not necessarily
imply one does not care about the data. Different environments have
different risks. Risk analysis compares the probability and cost of
failure with the cost of prevention; not every risk is worth avoiding
"at all costs", but that doesn't imply "doesn't care about the data".

Consider an environment where the risk of database file loss is
considered to be very low, but the risk of engine failure is very high
due to external events (power failure etc). Old log information prior
to the last checkpoint is not required for the normal
recovery-on-restart process. Plus, occasional (or frequent) full
backups mitigate the (assumed low, in this case) risk of database file
loss.

Would I recommend -m for a large central database with hundreds of
active connections? Haven't yet, probably never will.

How about an embedded database in a resource-limited and DBA-free
environment where a log management scheme would be expensive to
develop? Perhaps. In this case, probably not, but I don't know the
full story.

Breck

On 14 Jul 2005 13:17:48 -0700, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Breck Carter [TeamSybase] wrote:
>
>"Generally" ??
>
>Just to drive this point completely home, let me say that running with
>"-m" is *never* recommended.
>
>You only use -m when you don't care about your data. If losing data is
>a viable option for your application, then using -m is viable.
>
>Don't use -m.
>Don't use -m (that one is for Google searches).
>Don't use -m (that one is for Reg).
>
>But Breck is right, it is an option (that you should not use).
>
>Any questions? :-)
>
>greg.fenton

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

David Kerber

2005-07-15, 7:23 am

In article <42d6c86c$1@forums-1-dub>, greg. fenton_NOSPAM_@ianyw
here.com
says...
> Breck Carter [TeamSybase] wrote:
>
> "Generally" ??
>
> Just to drive this point completely home, let me say that running with
> "-m" is *never* recommended.
>
> You only use -m when you don't care about your data. If losing data is
> a viable option for your application, then using -m is viable.
>
> Don't use -m.
> Don't use -m (that one is for Google searches).
> Don't use -m (that one is for Reg).
>
> But Breck is right, it is an option (that you should not use).
>
> Any questions? :-)

Yes: if my database had strictly static information, and was never
updated in normal operation, would -m be a reasonable option?

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Reg Domaratzki $iAnywhere Solutions$

2005-07-15, 9:23 am

> Yes: if my database had strictly static information, and was never

> updated in normal operation, would -m be a reasonable option?

Log file growth would not be an issue in this environment at all, but I'd
still suggest the EVENTS from my reply to Breck's post.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"David Kerber" < ns_dkerber@ns_wraenv
iro.com> wrote in message
news:MPG. 1d409499e0d2bdf19897
79@forums.sybase.com...
> In article <42d6c86c$1@forums-1-dub>, greg. fenton_NOSPAM_@ianyw
here.com
> says...
>
> Yes: if my database had strictly static information, and was never
> updated in normal operation, would -m be a reasonable option?
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).

Reg Domaratzki $iAnywhere Solutions$

2005-07-15, 9:23 am

> How about an embedded database in a resource-limited and DBA-free
> environment where a log management scheme would be expensive to
> develop? Perhaps. In this case, probably not, but I don't know the
> full story.

Although I agree that different environments have different risks, there is
ALWAYS an easy alternative to the switch that I shall not name, even in
resource-limited and DBA-free environment.

CREATE EVENT TruncateLogSize
TYPE GrowLog
WHERE event_condition( 'LogSize' ) > 1
ENABLE
HANDLER
BEGIN
BACKUP DATABASE DIRECTORY ''
TRANSACTION LOG ONLY
TRANSACTION LOG TRUNCATE;
END;

CREATE EVENT TruncateLogFreeSpace

TYPE LogDiskSpace
WHERE event_condition( 'LogFreeSpace' ) < 2
AND event_condition( 'Interval' ) > 1800
ENABLE
HANDLER
BEGIN
BACKUP DATABASE DIRECTORY ''
TRANSACTION LOG ONLY
TRANSACTION LOG TRUNCATE;
END;

With the two events above defined, you log file will never be greater than
1MB and if free space if less than 2MB where the log file resides, we'll
truncate the log every 30 minutes.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:r7mdd1hffregee2
gvc0alerlabg3slrt46@
4ax.com...
> I stand by "generally not recommended".
>
> Truncating a transaction log after a checkpoint does not necessarily
> imply one does not care about the data. Different environments have
> different risks. Risk analysis compares the probability and cost of
> failure with the cost of prevention; not every risk is worth avoiding
> "at all costs", but that doesn't imply "doesn't care about the data".
>
> Consider an environment where the risk of database file loss is
> considered to be very low, but the risk of engine failure is very high
> due to external events (power failure etc). Old log information prior
> to the last checkpoint is not required for the normal
> recovery-on-restart process. Plus, occasional (or frequent) full
> backups mitigate the (assumed low, in this case) risk of database file
> loss.
>
> Would I recommend -m for a large central database with hundreds of
> active connections? Haven't yet, probably never will.
>
> How about an embedded database in a resource-limited and DBA-free
> environment where a log management scheme would be expensive to
> develop? Perhaps. In this case, probably not, but I don't know the
> full story.
>
> Breck
>
>
> On 14 Jul 2005 13:17:48 -0700, Greg Fenton
> <greg. fenton_NOSPAM_@ianyw
here.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-07-15, 11:23 am

What is the difference between TRANSACTION LOG TRUNCATE every 30
minutes, and a checkpoint with -m every 30 minutes?

Breck

On 15 Jul 2005 06:01:41 -0700, "Reg Domaratzki $iAnywhere
Solutions$" <FirstName.LastName@ianywhere.com> wrote:

>
>Although I agree that different environments have different risks, there is
>ALWAYS an easy alternative to the switch that I shall not name, even in
>resource-limited and DBA-free environment.
>
>CREATE EVENT TruncateLogSize
>TYPE GrowLog
> WHERE event_condition( 'LogSize' ) > 1
>ENABLE
>HANDLER
>BEGIN
> BACKUP DATABASE DIRECTORY ''
> TRANSACTION LOG ONLY
> TRANSACTION LOG TRUNCATE;
>END;
>
>CREATE EVENT TruncateLogFreeSpace

>TYPE LogDiskSpace
> WHERE event_condition( 'LogFreeSpace' ) < 2
> AND event_condition( 'Interval' ) > 1800
>ENABLE
>HANDLER
>BEGIN
> BACKUP DATABASE DIRECTORY ''
> TRANSACTION LOG ONLY
> TRANSACTION LOG TRUNCATE;
>END;
>
>With the two events above defined, you log file will never be greater than
>1MB and if free space if less than 2MB where the log file resides, we'll
>truncate the log every 30 minutes.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Stephen Rice

2005-07-18, 1:23 pm

Breck Carter [TeamSybase] wrote:
> I stand by "generally not recommended".
>
> Truncating a transaction log after a checkpoint does not necessarily
> imply one does not care about the data. Different environments have
> different risks. Risk analysis compares the probability and cost of
> failure with the cost of prevention; not every risk is worth avoiding
> "at all costs", but that doesn't imply "doesn't care about the data".
>
> Consider an environment where the risk of database file loss is
> considered to be very low, but the risk of engine failure is very high
> due to external events (power failure etc). Old log information prior
> to the last checkpoint is not required for the normal
> recovery-on-restart process. Plus, occasional (or frequent) full
> backups mitigate the (assumed low, in this case) risk of database file
> loss.
>
> Would I recommend -m for a large central database with hundreds of
> active connections? Haven't yet, probably never will.
>
> How about an embedded database in a resource-limited and DBA-free
> environment where a log management scheme would be expensive to
> develop? Perhaps. In this case, probably not, but I don't know the
> full story.
>
> Breck
>
>
> On 14 Jul 2005 13:17:48 -0700, Greg Fenton
> <greg. fenton_NOSPAM_@ianyw
here.com> wrote:
>
>
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

ahh Breck ... If only everyone looked at the world the way you do. If
they did then the media failure at 5:30pm on the Friday before a long
weekend while ruining "the critical app" would not result in me being
yelled at because the data is gone :)

/steve (All this fun and I get paid too!)

--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com

Sponsored Links