SQL Anywhere database - Verify: Load into temporary local doesn't perform automatic commit

Author

Verify: Load into temporary local doesn't perform automatic commit

2005-07-12, 7:26 am

Using ASA 9.0.2.3131

As it is not stated in the manual, as far as I can see,
could some please verify, that if I load into a local
temporary non transactional table, commit is not
automatically performed in the transaction.

This is the scenario, I'm talking about:

create table test ( nr int );
insert into test values ( 1 );
unload select * from test to 'c:\test.txt';
declare local temporary table tmp_for_load ( nr int ) not transactional
load into table tmp_for_load from 'c:\test.txt';
rollback;

There are no rows in test, even though the manual states,
that is does automatic commit;

regards/Peter Simonsen

Breck Carter [TeamSybase]

2005-07-12, 9:23 am

I don't see that in 9.0.2.3124 (oops, I'm out of date :)

This code displays the row in dbisql:

create table test ( nr int );
insert into test values ( 1 );
unload select * from test to 'c:\test.txt';
begin
declare local temporary table tmp_for_load ( nr int ) not
transactional;
load into table tmp_for_load from 'c:\test.txt';
rollback;
select * from tmp_for_load;
end;

You have specified NOT TRANSACTIONAL so commits and rollbacks should
have no affect whatsoever on the table.

Breck

On 12 Jul 2005 02:08:51 -0700, <Peter Simonsen> wrote:

>Using ASA 9.0.2.3131
>
>As it is not stated in the manual, as far as I can see,
>could some please verify, that if I load into a local
>temporary non transactional table, commit is not
>automatically performed in the transaction.
>
>This is the scenario, I'm talking about :
>
> create table test ( nr int );
> insert into test values ( 1 );
> unload select * from test to 'c:\test.txt';
> declare local temporary table tmp_for_load ( nr int ) not transactional
> load into table tmp_for_load from 'c:\test.txt';
> rollback;
>
>There are no rows in test, even though the manual states,
>that is does automatic commit;
>
>regards/Peter Simonsen
>

--
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-12, 11:23 am

Breck Carter [TeamSybase] wrote:
> select * from tmp_for_load;
>
> You have specified NOT TRANSACTIONAL so commits and rollbacks should
> have no affect whatsoever on the table.

It is not "tmp_for_load" he is wanting to see a row in....it is "test".

Peter,

This appears to be a documentation bug. ASA does not auto commit when
work is the LOAD is working on a temporary table. I will post an
enhancement request.

But recognize that relying on a side-effect (the COMMIT from a DDL
statement) is usually a bad thing. When it comes to coding, be explicit.

Hope this helps,
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-12, 1:23 pm

On 12 Jul 2005 08:37:13 -0700, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>It is not "tmp_for_load" he is wanting to see a row in....it is "test".

Doh! :)

--
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

2005-07-13, 3:23 am

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:42d3e3a2$1@foru
ms-2-dub...
> This appears to be a documentation bug. ASA does not auto commit when
> work is the LOAD is working on a temporary table. I will post an
> enhancement request.

OK. Excellent.

> But recognize that relying on a side-effect (the COMMIT from a DDL
> statement) is usually a bad thing. When it comes to coding, be explicit.

Well, actually I was hoping that I it didn't commit, since I 'd otherwise
have to create a new transaction, and do al sorts of extra work.

I'm happy you could verify this, since I was a bit uneasy using a feature
that wasn't documented. Thanks.

regards/Peter Simonsen

Greg Fenton

2005-07-13, 9:23 am

Peter Simonsen wrote:
>
> Well, actually I was hoping that I it didn't commit, since I 'd otherwise
> have to create a new transaction, and do al sorts of extra work.
>

I'd still be somewhat leary of putting DDL in the middle of a
transaction hoping that it did/did not commit...

I wonder why you chose to use UNLOAD/LOAD rather than:

INSERT INTO tmp_for_load SELECT * FROM test;

It is cleaner, more efficient, does not rely on the external filesystem,
and transactionally sound (IMO).

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

2005-07-14, 3:23 am

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:42d52a6d$1@foru
ms-2-dub...
> Peter Simonsen wrote:
> I wonder why you chose to use UNLOAD/LOAD rather than:
> INSERT INTO tmp_for_load SELECT * FROM test;

We are moving result sets from one database to another.
This was done using fetch and insert. Unfortunately moving 400,000 rows
would take several hours because of high network latency.
So I re-wrote the routine to do unloaded, copy the file
(using select compress ( xp_read_file) and xp_write_file (decompress)
and then reload.
This can be done on the same network within 1½ minute.
As this was called from all over the app, I couldn't risk
commiting the transaction.

regards/Peter Simonsen

Sponsored Links