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.

LOAD TABLE vs. INPUT INTO

8 posts in General Discussion Last posting was on 2004-02-16 15:12:04.0Z
Nob Posted on 2004-02-13 15:24:24.0Z
Sender: 6753.402cee46.1804289383@sybase.com
From: nob
Newsgroups: ianywhere.public.general
Subject: LOAD TABLE vs. INPUT INTO
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <402ceebf.675b.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 13 Feb 2004 07:35:27 -0800, 10.22.241.42
Lines: 7
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 07:06:14 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 07:24:24 -0800
X-Trace: forums-1-dub 1076685864 10.22.108.75 (13 Feb 2004 07:24:24 -0800)
X-Original-Trace: 13 Feb 2004 07:24:24 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2483
Article PK: 5624

I am using ASA 8.0.2.4215

I noticed in the documentation that LOAD TABLE statements do
NOT get recorded in to the db log. Does anybody know if
INPUT INTO statements get recorded in the db log?

Thanks,


Breck Carter [TeamSybase] Posted on 2004-02-13 15:38:16.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: LOAD TABLE vs. INPUT INTO
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com>
References: <402ceebf.675b.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 13 Feb 2004 07:49:05 -0800, bcarter.sentex.ca
Lines: 76
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 07:19:55 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 07:38:16 -0800
X-Trace: forums-1-dub 1076686696 10.22.108.75 (13 Feb 2004 07:38:16 -0800)
X-Original-Trace: 13 Feb 2004 07:38:16 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2485
Article PK: 5622

Yes.

Here is an excerpt from Section 2.8 ISQL INPUT, in Chapter 2,
Inserting, from SQL Anywhere Studio 9 Developer's Guide ISBN
1-55622-506-7 Wordware Publishing to be published soon:

=====
The INPUT statement doesn't take any of the shortcuts used by LOAD
TABLE, so it isn't nearly as fast. What INPUT actually does is pass
each row to the database engine, one at a time, to be inserted. You
can see this by turning on the "Request Level Logging" feature to see
what the engine sees; here is an example which compares LOAD TABLE
with INPUT when a two-line input file is loaded into a two-column
table:

CREATE TABLE t1 (
col_1 INTEGER NOT NULL,
col_2 INTEGER NOT NULL );

CALL sa_server_option ( 'Request_level_log_file', 'r.txt' );
CALL sa_server_option ( 'Request_level_logging', 'SQL+hostvars' );

LOAD TABLE t1 FROM 't1_f.txt';
INPUT INTO t1 FROM 't1_f.txt';

CALL sa_server_option ( 'Request_level_logging', 'NONE' );

Here is the contents of t1_f.txt:

1, 1
2, 2

The Request Level Logging file r.txt shows that the engine received
the LOAD TABLE command as it was coded:

STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
STMT_EXECUTE Stmt=66327

However, the INPUT statement got changed into an INSERT which was
executed twice, once for each record in the input file, with two host
variables used, one for each field:

STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2") VALUES (?,?)"
STMT_EXECUTE Stmt=66339
HOSTVAR 0 varchar '1'
HOSTVAR 1 varchar '1'
STMT_EXECUTE Stmt=66339
HOSTVAR 0 varchar '2'
HOSTVAR 1 varchar '2'

This explains why INPUT is slower than LOAD TABLE, but also why the
disadvantages of the LOAD TABLE shortcuts are avoided: the INPUT
statement does cause insert triggers to be fired, it does acquire
individual row locks, and it does write the inserts to the transaction
log.

The Request Level Logging feature is explained further in Chapter 12,
Tuning.
=====

Breck

On 13 Feb 2004 07:24:24 -0800, nob wrote:

>I am using ASA 8.0.2.4215
>
>I noticed in the documentation that LOAD TABLE statements do
>NOT get recorded in to the db log. Does anybody know if
>INPUT INTO statements get recorded in the db log?
>
>Thanks,

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Nob Posted on 2004-02-13 15:56:29.0Z
Sender: 67c7.402cf587.1804289383@sybase.com
From: nob
Newsgroups: ianywhere.public.general
Subject: Re: LOAD TABLE vs. INPUT INTO
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <402cf643.67d8.846930886@sybase.com>
References: <402ceebf.675b.846930886@sybase.com><jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 13 Feb 2004 08:07:31 -0800, 10.22.241.42
Lines: 90
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 07:38:20 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 07:56:29 -0800
X-Trace: forums-1-dub 1076687789 10.22.108.75 (13 Feb 2004 07:56:29 -0800)
X-Original-Trace: 13 Feb 2004 07:56:29 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2487
Article PK: 5625

This is what I thought, but I have tried both statements and
got some strange results. I am using mobilink. When I use
either LOAD TABLE or INPUT INTO, the newly added data does
not get sent to the consolidated db on the next sync.

Form what you have said, and the docs, I would think that
the INPUT INTO statements would get replicated. But they
don't.

> Yes.
>
> Here is an excerpt from Section 2.8 ISQL INPUT, in Chapter
> 2, Inserting, from SQL Anywhere Studio 9 Developer's Guide
> ISBN 1-55622-506-7 Wordware Publishing to be published
> soon:
>
> =====
> The INPUT statement doesn't take any of the shortcuts used
> by LOAD TABLE, so it isn't nearly as fast. What INPUT
> actually does is pass each row to the database engine, one
> at a time, to be inserted. You can see this by turning on
> the "Request Level Logging" feature to see what the engine
> sees; here is an example which compares LOAD TABLE with
> INPUT when a two-line input file is loaded into a
> two-column table:
>
> CREATE TABLE t1 (
> col_1 INTEGER NOT NULL,
> col_2 INTEGER NOT NULL );
>
> CALL sa_server_option ( 'Request_level_log_file', 'r.txt'
> ); CALL sa_server_option ( 'Request_level_logging',
> 'SQL+hostvars' );
>
> LOAD TABLE t1 FROM 't1_f.txt';
> INPUT INTO t1 FROM 't1_f.txt';
>
> CALL sa_server_option ( 'Request_level_logging', 'NONE' );
>
> Here is the contents of t1_f.txt:
>
> 1, 1
> 2, 2
>
> The Request Level Logging file r.txt shows that the engine
> received the LOAD TABLE command as it was coded:
>
> STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
> STMT_EXECUTE Stmt=66327
>
> However, the INPUT statement got changed into an INSERT
> which was executed twice, once for each record in the
> input file, with two host variables used, one for each
> field:
>
> STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2") VALUES
> (?,?)" STMT_EXECUTE Stmt=66339
> HOSTVAR 0 varchar '1'
> HOSTVAR 1 varchar '1'
> STMT_EXECUTE Stmt=66339
> HOSTVAR 0 varchar '2'
> HOSTVAR 1 varchar '2'
>
> This explains why INPUT is slower than LOAD TABLE, but
> also why the disadvantages of the LOAD TABLE shortcuts are
> avoided: the INPUT statement does cause insert triggers to
> be fired, it does acquire individual row locks, and it
> does write the inserts to the transaction log.
>
> The Request Level Logging feature is explained further in
> Chapter 12, Tuning.
> =====
>
> Breck
>
> On 13 Feb 2004 07:24:24 -0800, nob wrote:
>
> >I am using ASA 8.0.2.4215
> >
> >I noticed in the documentation that LOAD TABLE statements
> do >NOT get recorded in to the db log. Does anybody know
> if >INPUT INTO statements get recorded in the db log?
> >
> >Thanks,
>
> --
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Breck Carter [TeamSybase] Posted on 2004-02-13 16:54:24.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: LOAD TABLE vs. INPUT INTO
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <l5vp205epbuqi7ga7lt802p96lsvs4os5p@4ax.com>
References: <402ceebf.675b.846930886@sybase.com><jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com> <402cf643.67d8.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 13 Feb 2004 09:05:29 -0800, bcarter.sentex.ca
Lines: 99
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 08:36:16 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 08:54:24 -0800
X-Trace: forums-1-dub 1076691264 10.22.108.75 (13 Feb 2004 08:54:24 -0800)
X-Original-Trace: 13 Feb 2004 08:54:24 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2490
Article PK: 5628

Look elsewhere for the problem, INPUT is just a wrapper around INSERT.

On 13 Feb 2004 07:56:29 -0800, nob wrote:

>This is what I thought, but I have tried both statements and
>got some strange results. I am using mobilink. When I use
>either LOAD TABLE or INPUT INTO, the newly added data does
>not get sent to the consolidated db on the next sync.
>
>Form what you have said, and the docs, I would think that
>the INPUT INTO statements would get replicated. But they
>don't.
>
>
>> Yes.
>>
>> Here is an excerpt from Section 2.8 ISQL INPUT, in Chapter
>> 2, Inserting, from SQL Anywhere Studio 9 Developer's Guide
>> ISBN 1-55622-506-7 Wordware Publishing to be published
>> soon:
>>
>> =====
>> The INPUT statement doesn't take any of the shortcuts used
>> by LOAD TABLE, so it isn't nearly as fast. What INPUT
>> actually does is pass each row to the database engine, one
>> at a time, to be inserted. You can see this by turning on
>> the "Request Level Logging" feature to see what the engine
>> sees; here is an example which compares LOAD TABLE with
>> INPUT when a two-line input file is loaded into a
>> two-column table:
>>
>> CREATE TABLE t1 (
>> col_1 INTEGER NOT NULL,
>> col_2 INTEGER NOT NULL );
>>
>> CALL sa_server_option ( 'Request_level_log_file', 'r.txt'
>> ); CALL sa_server_option ( 'Request_level_logging',
>> 'SQL+hostvars' );
>>
>> LOAD TABLE t1 FROM 't1_f.txt';
>> INPUT INTO t1 FROM 't1_f.txt';
>>
>> CALL sa_server_option ( 'Request_level_logging', 'NONE' );
>>
>> Here is the contents of t1_f.txt:
>>
>> 1, 1
>> 2, 2
>>
>> The Request Level Logging file r.txt shows that the engine
>> received the LOAD TABLE command as it was coded:
>>
>> STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
>> STMT_EXECUTE Stmt=66327
>>
>> However, the INPUT statement got changed into an INSERT
>> which was executed twice, once for each record in the
>> input file, with two host variables used, one for each
>> field:
>>
>> STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2") VALUES
>> (?,?)" STMT_EXECUTE Stmt=66339
>> HOSTVAR 0 varchar '1'
>> HOSTVAR 1 varchar '1'
>> STMT_EXECUTE Stmt=66339
>> HOSTVAR 0 varchar '2'
>> HOSTVAR 1 varchar '2'
>>
>> This explains why INPUT is slower than LOAD TABLE, but
>> also why the disadvantages of the LOAD TABLE shortcuts are
>> avoided: the INPUT statement does cause insert triggers to
>> be fired, it does acquire individual row locks, and it
>> does write the inserts to the transaction log.
>>
>> The Request Level Logging feature is explained further in
>> Chapter 12, Tuning.
>> =====
>>
>> Breck
>>
>> On 13 Feb 2004 07:24:24 -0800, nob wrote:
>>
>> >I am using ASA 8.0.2.4215
>> >
>> >I noticed in the documentation that LOAD TABLE statements
>> do >NOT get recorded in to the db log. Does anybody know
>> if >INPUT INTO statements get recorded in the db log?
>> >
>> >Thanks,
>>
>> --
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Nob Posted on 2004-02-13 17:04:02.0Z
Sender: 6a69.402d0214.1804289383@sybase.com
From: nob
Newsgroups: ianywhere.public.general
Subject: Re: LOAD TABLE vs. INPUT INTO
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <402d0382.6a7a.846930886@sybase.com>
References: <402ceebf.675b.846930886@sybase.com><jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com> <402cf643.67d8.846930886@sybase.com><l5vp205epbuqi7ga7lt802p96lsvs4os5p@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Feb 2004 09:04:02 -0800
X-Trace: forums-1-dub 1076691842 10.22.241.41 (13 Feb 2004 09:04:02 -0800)
X-Original-Trace: 13 Feb 2004 09:04:02 -0800, 10.22.241.41
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2491
Article PK: 5629

Actually I think I may have figured it out. Is this
possible?
If one of the records in my .dat file were to fail on the
IMPORT INTO (say due to an RI constrant), would this cause
the remaining records in that .dat file to fail also? Even
if the remaining records didn't violate an RI constrant?

> Look elsewhere for the problem, INPUT is just a wrapper
> around INSERT.
>
> On 13 Feb 2004 07:56:29 -0800, nob wrote:
>
> >This is what I thought, but I have tried both statements
> and >got some strange results. I am using mobilink. When
> I use >either LOAD TABLE or INPUT INTO, the newly added
> data does >not get sent to the consolidated db on the next
> sync. >
> >Form what you have said, and the docs, I would think that
> >the INPUT INTO statements would get replicated. But they
> >don't.
> >
> >
> >> Yes.
> >>
> >> Here is an excerpt from Section 2.8 ISQL INPUT, in
> Chapter >> 2, Inserting, from SQL Anywhere Studio 9
> Developer's Guide >> ISBN 1-55622-506-7 Wordware
> Publishing to be published >> soon:
> >>
> >> =====
> >> The INPUT statement doesn't take any of the shortcuts
> used >> by LOAD TABLE, so it isn't nearly as fast. What
> INPUT >> actually does is pass each row to the database
> engine, one >> at a time, to be inserted. You can see this
> by turning on >> the "Request Level Logging" feature to
> see what the engine >> sees; here is an example which
> compares LOAD TABLE with >> INPUT when a two-line input
> file is loaded into a >> two-column table:
> >>
> >> CREATE TABLE t1 (
> >> col_1 INTEGER NOT NULL,
> >> col_2 INTEGER NOT NULL );
> >>
> >> CALL sa_server_option ( 'Request_level_log_file',
> 'r.txt' >> ); CALL sa_server_option (
> 'Request_level_logging', >> 'SQL+hostvars' );
> >>
> >> LOAD TABLE t1 FROM 't1_f.txt';
> >> INPUT INTO t1 FROM 't1_f.txt';
> >>
> >> CALL sa_server_option ( 'Request_level_logging', 'NONE'
> ); >>
> >> Here is the contents of t1_f.txt:
> >>
> >> 1, 1
> >> 2, 2
> >>
> >> The Request Level Logging file r.txt shows that the
> engine >> received the LOAD TABLE command as it was coded:
> >>
> >> STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
> >> STMT_EXECUTE Stmt=66327
> >>
> >> However, the INPUT statement got changed into an INSERT
> >> which was executed twice, once for each record in the
> >> input file, with two host variables used, one for each
> >> field:
> >>
> >> STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2")
> VALUES >> (?,?)" STMT_EXECUTE Stmt=66339
> >> HOSTVAR 0 varchar '1'
> >> HOSTVAR 1 varchar '1'
> >> STMT_EXECUTE Stmt=66339
> >> HOSTVAR 0 varchar '2'
> >> HOSTVAR 1 varchar '2'
> >>
> >> This explains why INPUT is slower than LOAD TABLE, but
> >> also why the disadvantages of the LOAD TABLE shortcuts
> are >> avoided: the INPUT statement does cause insert
> triggers to >> be fired, it does acquire individual row
> locks, and it >> does write the inserts to the transaction
> log. >>
> >> The Request Level Logging feature is explained further
> in >> Chapter 12, Tuning.
> >> =====
> >>
> >> Breck
> >>
> >> On 13 Feb 2004 07:24:24 -0800, nob wrote:
> >>
> >> >I am using ASA 8.0.2.4215
> >> >
> >> >I noticed in the documentation that LOAD TABLE
> statements >> do >NOT get recorded in to the db log. Does
> anybody know >> if >INPUT INTO statements get recorded in
> the db log? >> >
> >> >Thanks,
> >>
> >> --
> >> bcarter@risingroad.com
> >> Mobile and Distributed Enterprise Database Applications
> >> www.risingroad.com
>
> --
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Breck Carter [TeamSybase] Posted on 2004-02-13 17:11:54.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: LOAD TABLE vs. INPUT INTO
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <e40q20hactaoqfmajs8rcchhclbp9vm5ha@4ax.com>
References: <402ceebf.675b.846930886@sybase.com><jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com> <402cf643.67d8.846930886@sybase.com><l5vp205epbuqi7ga7lt802p96lsvs4os5p@4ax.com> <402d0382.6a7a.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 13 Feb 2004 09:22:57 -0800, bcarter.sentex.ca
Lines: 121
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 08:53:44 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 09:11:54 -0800
X-Trace: forums-1-dub 1076692314 10.22.108.75 (13 Feb 2004 09:11:54 -0800)
X-Original-Trace: 13 Feb 2004 09:11:54 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2493
Article PK: 5631

It depends on how you are running ISQL, I think. It's just another
client application, and applications tend to ROLLBACK after an error.
ISQL may run INPUT as an atomic operation which means all or nothing.

Breck

On 13 Feb 2004 09:04:02 -0800, nob wrote:

>Actually I think I may have figured it out. Is this
>possible?
>If one of the records in my .dat file were to fail on the
>IMPORT INTO (say due to an RI constrant), would this cause
>the remaining records in that .dat file to fail also? Even
>if the remaining records didn't violate an RI constrant?
>
>
>> Look elsewhere for the problem, INPUT is just a wrapper
>> around INSERT.
>>
>> On 13 Feb 2004 07:56:29 -0800, nob wrote:
>>
>> >This is what I thought, but I have tried both statements
>> and >got some strange results. I am using mobilink. When
>> I use >either LOAD TABLE or INPUT INTO, the newly added
>> data does >not get sent to the consolidated db on the next
>> sync. >
>> >Form what you have said, and the docs, I would think that
>> >the INPUT INTO statements would get replicated. But they
>> >don't.
>> >
>> >
>> >> Yes.
>> >>
>> >> Here is an excerpt from Section 2.8 ISQL INPUT, in
>> Chapter >> 2, Inserting, from SQL Anywhere Studio 9
>> Developer's Guide >> ISBN 1-55622-506-7 Wordware
>> Publishing to be published >> soon:
>> >>
>> >> =====
>> >> The INPUT statement doesn't take any of the shortcuts
>> used >> by LOAD TABLE, so it isn't nearly as fast. What
>> INPUT >> actually does is pass each row to the database
>> engine, one >> at a time, to be inserted. You can see this
>> by turning on >> the "Request Level Logging" feature to
>> see what the engine >> sees; here is an example which
>> compares LOAD TABLE with >> INPUT when a two-line input
>> file is loaded into a >> two-column table:
>> >>
>> >> CREATE TABLE t1 (
>> >> col_1 INTEGER NOT NULL,
>> >> col_2 INTEGER NOT NULL );
>> >>
>> >> CALL sa_server_option ( 'Request_level_log_file',
>> 'r.txt' >> ); CALL sa_server_option (
>> 'Request_level_logging', >> 'SQL+hostvars' );
>> >>
>> >> LOAD TABLE t1 FROM 't1_f.txt';
>> >> INPUT INTO t1 FROM 't1_f.txt';
>> >>
>> >> CALL sa_server_option ( 'Request_level_logging', 'NONE'
>> ); >>
>> >> Here is the contents of t1_f.txt:
>> >>
>> >> 1, 1
>> >> 2, 2
>> >>
>> >> The Request Level Logging file r.txt shows that the
>> engine >> received the LOAD TABLE command as it was coded:
>> >>
>> >> STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
>> >> STMT_EXECUTE Stmt=66327
>> >>
>> >> However, the INPUT statement got changed into an INSERT
>> >> which was executed twice, once for each record in the
>> >> input file, with two host variables used, one for each
>> >> field:
>> >>
>> >> STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2")
>> VALUES >> (?,?)" STMT_EXECUTE Stmt=66339
>> >> HOSTVAR 0 varchar '1'
>> >> HOSTVAR 1 varchar '1'
>> >> STMT_EXECUTE Stmt=66339
>> >> HOSTVAR 0 varchar '2'
>> >> HOSTVAR 1 varchar '2'
>> >>
>> >> This explains why INPUT is slower than LOAD TABLE, but
>> >> also why the disadvantages of the LOAD TABLE shortcuts
>> are >> avoided: the INPUT statement does cause insert
>> triggers to >> be fired, it does acquire individual row
>> locks, and it >> does write the inserts to the transaction
>> log. >>
>> >> The Request Level Logging feature is explained further
>> in >> Chapter 12, Tuning.
>> >> =====
>> >>
>> >> Breck
>> >>
>> >> On 13 Feb 2004 07:24:24 -0800, nob wrote:
>> >>
>> >> >I am using ASA 8.0.2.4215
>> >> >
>> >> >I noticed in the documentation that LOAD TABLE
>> statements >> do >NOT get recorded in to the db log. Does
>> anybody know >> if >INPUT INTO statements get recorded in
>> the db log? >> >
>> >> >Thanks,
>> >>
>> >> --
>> >> bcarter@risingroad.com
>> >> Mobile and Distributed Enterprise Database Applications
>> >> www.risingroad.com
>>
>> --
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Robert Waywell Posted on 2004-02-16 15:12:04.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <402ceebf.675b.846930886@sybase.com><jlqp20t8jb032hqd3q522sff86b66in3t2@4ax.com> <402cf643.67d8.846930886@sybase.com><l5vp205epbuqi7ga7lt802p96lsvs4os5p@4ax.com> <402d0382.6a7a.846930886@sybase.com> <e40q20hactaoqfmajs8rcchhclbp9vm5ha@4ax.com>
Subject: Re: LOAD TABLE vs. INPUT INTO
Lines: 152
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <4030ddc4@forums-1-dub>
Date: 16 Feb 2004 07:12:04 -0800
X-Trace: forums-1-dub 1076944324 172.31.142.236 (16 Feb 2004 07:12:04 -0800)
X-Original-Trace: 16 Feb 2004 07:12:04 -0800, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2512
Article PK: 5650

Check the value of the On_error option. If this is set to "Continue" then
ISQL will skip the bad record and continue with the next record in the input
file.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:e40q20hactaoqfmajs8rcchhclbp9vm5ha@4ax.com...
> It depends on how you are running ISQL, I think. It's just another
> client application, and applications tend to ROLLBACK after an error.
> ISQL may run INPUT as an atomic operation which means all or nothing.
>
> Breck
>
> On 13 Feb 2004 09:04:02 -0800, nob wrote:
>
> >Actually I think I may have figured it out. Is this
> >possible?
> >If one of the records in my .dat file were to fail on the
> >IMPORT INTO (say due to an RI constrant), would this cause
> >the remaining records in that .dat file to fail also? Even
> >if the remaining records didn't violate an RI constrant?
> >
> >
> >> Look elsewhere for the problem, INPUT is just a wrapper
> >> around INSERT.
> >>
> >> On 13 Feb 2004 07:56:29 -0800, nob wrote:
> >>
> >> >This is what I thought, but I have tried both statements
> >> and >got some strange results. I am using mobilink. When
> >> I use >either LOAD TABLE or INPUT INTO, the newly added
> >> data does >not get sent to the consolidated db on the next
> >> sync. >
> >> >Form what you have said, and the docs, I would think that
> >> >the INPUT INTO statements would get replicated. But they
> >> >don't.
> >> >
> >> >
> >> >> Yes.
> >> >>
> >> >> Here is an excerpt from Section 2.8 ISQL INPUT, in
> >> Chapter >> 2, Inserting, from SQL Anywhere Studio 9
> >> Developer's Guide >> ISBN 1-55622-506-7 Wordware
> >> Publishing to be published >> soon:
> >> >>
> >> >> =====
> >> >> The INPUT statement doesn't take any of the shortcuts
> >> used >> by LOAD TABLE, so it isn't nearly as fast. What
> >> INPUT >> actually does is pass each row to the database
> >> engine, one >> at a time, to be inserted. You can see this
> >> by turning on >> the "Request Level Logging" feature to
> >> see what the engine >> sees; here is an example which
> >> compares LOAD TABLE with >> INPUT when a two-line input
> >> file is loaded into a >> two-column table:
> >> >>
> >> >> CREATE TABLE t1 (
> >> >> col_1 INTEGER NOT NULL,
> >> >> col_2 INTEGER NOT NULL );
> >> >>
> >> >> CALL sa_server_option ( 'Request_level_log_file',
> >> 'r.txt' >> ); CALL sa_server_option (
> >> 'Request_level_logging', >> 'SQL+hostvars' );
> >> >>
> >> >> LOAD TABLE t1 FROM 't1_f.txt';
> >> >> INPUT INTO t1 FROM 't1_f.txt';
> >> >>
> >> >> CALL sa_server_option ( 'Request_level_logging', 'NONE'
> >> ); >>
> >> >> Here is the contents of t1_f.txt:
> >> >>
> >> >> 1, 1
> >> >> 2, 2
> >> >>
> >> >> The Request Level Logging file r.txt shows that the
> >> engine >> received the LOAD TABLE command as it was coded:
> >> >>
> >> >> STMT_PREPARE "LOAD TABLE t1 FROM 't1_f.txt'"
> >> >> STMT_EXECUTE Stmt=66327
> >> >>
> >> >> However, the INPUT statement got changed into an INSERT
> >> >> which was executed twice, once for each record in the
> >> >> input file, with two host variables used, one for each
> >> >> field:
> >> >>
> >> >> STMT_PREPARE "INSERT INTO "t1" ("col_1","col_2")
> >> VALUES >> (?,?)" STMT_EXECUTE Stmt=66339
> >> >> HOSTVAR 0 varchar '1'
> >> >> HOSTVAR 1 varchar '1'
> >> >> STMT_EXECUTE Stmt=66339
> >> >> HOSTVAR 0 varchar '2'
> >> >> HOSTVAR 1 varchar '2'
> >> >>
> >> >> This explains why INPUT is slower than LOAD TABLE, but
> >> >> also why the disadvantages of the LOAD TABLE shortcuts
> >> are >> avoided: the INPUT statement does cause insert
> >> triggers to >> be fired, it does acquire individual row
> >> locks, and it >> does write the inserts to the transaction
> >> log. >>
> >> >> The Request Level Logging feature is explained further
> >> in >> Chapter 12, Tuning.
> >> >> =====
> >> >>
> >> >> Breck
> >> >>
> >> >> On 13 Feb 2004 07:24:24 -0800, nob wrote:
> >> >>
> >> >> >I am using ASA 8.0.2.4215
> >> >> >
> >> >> >I noticed in the documentation that LOAD TABLE
> >> statements >> do >NOT get recorded in to the db log. Does
> >> anybody know >> if >INPUT INTO statements get recorded in
> >> the db log? >> >
> >> >> >Thanks,
> >> >>
> >> >> --
> >> >> bcarter@risingroad.com
> >> >> Mobile and Distributed Enterprise Database Applications
> >> >> www.risingroad.com
> >>
> >> --
> >> bcarter@risingroad.com
> >> Mobile and Distributed Enterprise Database Applications
> >> www.risingroad.com
>
> --
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Reg Domaratzki Posted on 2004-02-13 15:46:22.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <402ceebf.675b.846930886@sybase.com>
Subject: Re: LOAD TABLE vs. INPUT INTO
Lines: 63
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <402cf3e5$1@forums-2-dub>
X-Original-Trace: 13 Feb 2004 07:57:25 -0800, rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Feb 2004 07:28:13 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Feb 2004 07:46:22 -0800
X-Trace: forums-1-dub 1076687182 10.22.108.75 (13 Feb 2004 07:46:22 -0800)
X-Original-Trace: 13 Feb 2004 07:46:22 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2486
Article PK: 5623

Both commands are written to the log, but slightly differently. The actual
LOAD TABLE command you execute (with the filename) is written to the log
when you execute it. The docs say that "Inserts are not recorded in the log
file", but the actual LOAD TABLE command is written. If recovery is
performed and the LOAD TABLE command needs to be re-executed, the file you
specified must still exist or recovery will fail. Also, since the inserts
aren't logged, the effects of the LOAD TABLE can't be parsed by dbmlsync or
dbremote, so this shouldn't be used in a replicating or synchronizing
enviornment.

With INPUT INTO, the actual inserts generated by parsing the file are
written to the log.


--BEGIN TRANSACTION-1008-0000493690
BEGIN TRANSACTION
go
--SQL-1008-0000493691
load into table t1 from 'c:\\temp\\t1.txt'
go
--COMMIT-1008-0000493743
COMMIT WORK
go



--BEGIN TRANSACTION-1008-0000493773
BEGIN TRANSACTION
go
--INSERT-1008-0000493794
INSERT INTO DBA.t1(c1,c2)
VALUES (1,'one')
go
--INSERT-1008-0000493804
INSERT INTO DBA.t1(c1,c2)
VALUES (2,'two')
go
--COMMIT-1008-0000493814
COMMIT WORK
go



--
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
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<nob> wrote in message news:402ceebf.675b.846930886@sybase.com...
> I am using ASA 8.0.2.4215
>
> I noticed in the documentation that LOAD TABLE statements do
> NOT get recorded in to the db log. Does anybody know if
> INPUT INTO statements get recorded in the db log?
>
> Thanks,