Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

15.0.3 bcp with initstring: is it all done inside a transaction?

3 posts in General Discussion Last posting was on 2012-10-12 06:32:44.0Z
John Flynn Posted on 2012-10-11 23:15:32.0Z
Reply-To: "John Flynn" <jflynn@miqs.com>
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: 15.0.3 bcp with initstring: is it all done inside a transaction?
Lines: 1
Organization: MIQS, Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50775314$1@forums-1-dub>
Date: 11 Oct 2012 16:15:32 -0700
X-Trace: forums-1-dub 1349997332 172.20.134.152 (11 Oct 2012 16:15:32 -0700)
X-Original-Trace: 11 Oct 2012 16:15:32 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31424
Article PK: 74313

Hi.

I frequently use bcp to replace an existing table with new contents. So I
first login and truncate the table to empty it. Then I run the bcp command
to load the new contents. Generally I tell bcp to do the whole thing in one
batch, i.e. one transaction.

The problem is that this opens a window of time where a user could select
from the table and see it empty, which then leads to an application error.
For sure, it's a tiny window, but nevertheless it has happened and I'd like
to avoid it.

Can I use the --initstring option to first delete the contents? I know I can
technically do that, and it will have the effect that the delete and the
load will happen inside one login session. But I need them to happen inside
one transaction. That way, if a user selects from the table he'd be
guaranteed to see either the old contents or the new contents, either of
which is fine with me. I realize this can only have a chance of working if
the whole table is done in one bcp batch/transaction.

I suspect the answer will be no. Does anyone know? I'm not even sure how I
can test this myself. Maybe I could build a WAITFOR delay into the
initstring, thereby making the window longer and allowing me to test the
theory?

Thanks.
- John.


Cory Sane [TeamSybase] Posted on 2012-10-12 03:28:08.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <50775314$1@forums-1-dub>
In-Reply-To: <50775314$1@forums-1-dub>
Subject: Re: 15.0.3 bcp with initstring: is it all done inside a transaction?
Lines: 34
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50778e48$1@forums-1-dub>
Date: 11 Oct 2012 20:28:08 -0700
X-Trace: forums-1-dub 1350012488 172.20.134.152 (11 Oct 2012 20:28:08 -0700)
X-Original-Trace: 11 Oct 2012 20:28:08 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31425
Article PK: 74314

John,

I do not believe this is possible because BCP by default can have multiple transactions even though you may not use multiple
transactions.

You may be able to write a perl utility to wrap the delete and bulk insert as a single transaction.
I have limited experience in DBD::Sybase perl coding and perl has worked when other tools could not do what I needed.

--
Cory Sane
[TeamSybase]
Certified Sybase ASE 15.0 Administrator Associate
Certified Sybase ASE 15.0 SQL Developer Professional

"John Flynn" <jflynn@miqs.com> wrote in message news:50775314$1@forums-1-dub...
> Hi.
>
> I frequently use bcp to replace an existing table with new contents. So I first login and truncate the table to empty it. Then
> I run the bcp command to load the new contents. Generally I tell bcp to do the whole thing in one batch, i.e. one transaction.
>
> The problem is that this opens a window of time where a user could select from the table and see it empty, which then leads to
> an application error. For sure, it's a tiny window, but nevertheless it has happened and I'd like to avoid it.
>
> Can I use the --initstring option to first delete the contents? I know I can technically do that, and it will have the effect
> that the delete and the load will happen inside one login session. But I need them to happen inside one transaction. That way,
> if a user selects from the table he'd be guaranteed to see either the old contents or the new contents, either of which is
> fine with me. I realize this can only have a chance of working if the whole table is done in one bcp batch/transaction.
>
> I suspect the answer will be no. Does anyone know? I'm not even sure how I can test this myself. Maybe I could build a WAITFOR
> delay into the initstring, thereby making the window longer and allowing me to test the theory?
>
> Thanks.
> - John.
>


Rob V Posted on 2012-10-12 06:32:44.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:16.0) Gecko/20121005 Thunderbird/16.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: 15.0.3 bcp with initstring: is it all done inside a transaction?
References: <50775314$1@forums-1-dub>
In-Reply-To: <50775314$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5077b98c$1@forums-1-dub>
Date: 11 Oct 2012 23:32:44 -0700
X-Trace: forums-1-dub 1350023564 172.20.134.152 (11 Oct 2012 23:32:44 -0700)
X-Original-Trace: 11 Oct 2012 23:32:44 -0700, vip152.sybase.com
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31428
Article PK: 74316


On 12-Oct-2012 01:15, John Flynn wrote:
> Hi.
>
> I frequently use bcp to replace an existing table with new contents. So
> I first login and truncate the table to empty it. Then I run the bcp
> command to load the new contents. Generally I tell bcp to do the whole
> thing in one batch, i.e. one transaction.
>
> The problem is that this opens a window of time where a user could
> select from the table and see it empty, which then leads to an
> application error. For sure, it's a tiny window, but nevertheless it has
> happened and I'd like to avoid it.
>
> Can I use the --initstring option to first delete the contents? I know I
> can technically do that, and it will have the effect that the delete and
> the load will happen inside one login session. But I need them to happen
> inside one transaction. That way, if a user selects from the table he'd
> be guaranteed to see either the old contents or the new contents, either
> of which is fine with me. I realize this can only have a chance of
> working if the whole table is done in one bcp batch/transaction.
>
> I suspect the answer will be no. Does anyone know? I'm not even sure how
> I can test this myself. Maybe I could build a WAITFOR delay into the
> initstring, thereby making the window longer and allowing me to test the
> theory?
>
> Thanks.
> - John.
>

I *think* the initstrign action is performed as it is, without begin
tran/commit around it. This is becuase it was originally designed only
to facilitate 'set replication off' before BCP'ing data into a
replicated table.
This would mean it is a separate transaction. You can find out for sure
by BCP'in onlly 1 row into the table and using 'dbcc log' to inspect the
generated log records.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------