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.

BCP to multiple files with a single scan of the source data.

7 posts in Product Futures Discussion Last posting was on 2002-06-17 10:26:01.0Z
Jason Webster Posted on 2002-06-14 18:55:51.0Z
From: "Jason Webster" <jason.webster`@mail.state.ky.us>
Subject: BCP to multiple files with a single scan of the source data.
Date: Fri, 14 Jun 2002 14:55:51 -0400
Lines: 15
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
Message-ID: <qqOT4Z9ECHA.195@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 162.114.54.51
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:436
Article PK: 93605

I would like to be able to bcp a large table into seperate files while only
making one pass of the table. This would work by specifying a switch to bcp
that would indicate the number of files to create. BCP would use this to
create multiple copies of the base file specified as normal.
For Example:
bcp mydb..mytable out mytable.bcp -c -Sserver -Uusr -Ppw -S3

Where "-S3" means make three files that start with mytable.bcp. This would
create somthing like mytable.bcp.01, mytable.bcp.02, and so on placing the
rows in three separate files. It would also be nice if you could specify
whether to distribute the rows round-robin or in contiguous chunks.

Jason Webster


Bret Halford Posted on 2002-06-14 19:01:18.0Z
Message-ID: <3D0A3D7E.A0C639B5@sybase.com>
Date: Fri, 14 Jun 2002 13:01:18 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Jason Webster <jason.webster`@mail.state.ky.us>
Subject: Re: BCP to multiple files with a single scan of the source data.
References: <qqOT4Z9ECHA.195@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 25
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:435
Article PK: 93603

I recently saw an interesting bcp trick that essentially accomplishes
this,
as I recall it involved creating a named pipe with mknod that lead to
a UNIX "split" command that would split the output into files of <n>
lines each.

-bret

Jason Webster wrote:
>
> I would like to be able to bcp a large table into seperate files while only
> making one pass of the table. This would work by specifying a switch to bcp
> that would indicate the number of files to create. BCP would use this to
> create multiple copies of the base file specified as normal.
> For Example:
> bcp mydb..mytable out mytable.bcp -c -Sserver -Uusr -Ppw -S3
>
> Where "-S3" means make three files that start with mytable.bcp. This would
> create somthing like mytable.bcp.01, mytable.bcp.02, and so on placing the
> rows in three separate files. It would also be nice if you could specify
> whether to distribute the rows round-robin or in contiguous chunks.
>
> Jason Webster


Jason Webster Posted on 2002-06-14 19:23:10.0Z
From: "Jason Webster" <jason.webster`@mail.state.ky.us>
References: <qqOT4Z9ECHA.195@forums.sybase.com> <3D0A3D7E.A0C639B5@sybase.com>
Subject: Re: BCP to multiple files with a single scan of the source data.
Date: Fri, 14 Jun 2002 15:23:10 -0400
Lines: 61
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
Message-ID: <cVWJJp9ECHA.196@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 162.114.54.51
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:434
Article PK: 93604

I've used the named pipe technique extensively, it's really handy for
compressing bcp datafiles, splitting non text/image data and so forth. You
can even use it to tie two bcp processes together like this:
mkfifo fifo
bcp db..table out fifo ....... &
bcp db..table in fifo .......
This is cool for long bcp's because the bcp in does not have to wait for the
bcp out to finish.

However, the unix split command has two ways of splitting:
1) split on each newline. This won't work if the table you're bcp'ing out
has say word docs stored in an image column because the newlines in the docs
will get split.

2) split on x number of bytes. This is inefficient as you have to do a
fixed length bcp with enough record size to handle the largest record you
may encounter in the bcp session. And, that still wouldn't likely work in
this scenario because when you copy it back in those extra bytes will
corrupt something like a word document or spreadsheet.

I think we may have discussed this Memorial Day weekend in the ase unix
group.

So my request still stands, and while we're at it, how about extended syntax
to specify a table to copy into and do the same thing as I mentioned above.

"Bret Halford" <bret@sybase.com> wrote in message
news:3D0A3D7E.A0C639B5@sybase.com...
> I recently saw an interesting bcp trick that essentially accomplishes
> this,
> as I recall it involved creating a named pipe with mknod that lead to
> a UNIX "split" command that would split the output into files of <n>
> lines each.
>
> -bret
>
>
>
> Jason Webster wrote:
> >
> > I would like to be able to bcp a large table into seperate files while
only
> > making one pass of the table. This would work by specifying a switch to
bcp
> > that would indicate the number of files to create. BCP would use this
to
> > create multiple copies of the base file specified as normal.
> > For Example:
> > bcp mydb..mytable out mytable.bcp -c -Sserver -Uusr -Ppw -S3
> >
> > Where "-S3" means make three files that start with mytable.bcp. This
would
> > create somthing like mytable.bcp.01, mytable.bcp.02, and so on placing
the
> > rows in three separate files. It would also be nice if you could
specify
> > whether to distribute the rows round-robin or in contiguous chunks.
> >
> > Jason Webster


Sherlock, Kevin Posted on 2002-06-14 20:03:56.0Z
Message-ID: <3D0A4C15.386576D6@qwest.com.nospam>
Date: Fri, 14 Jun 2002 15:03:56 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: BCP to multiple files with a single scan of the source data.
References: <qqOT4Z9ECHA.195@forums.sybase.com> <3D0A3D7E.A0C639B5@sybase.com> <cVWJJp9ECHA.196@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------FB8D99784B6BD2D54B0F06D7"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 59
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:433
Article PK: 93602


Jason Webster wrote:
>
> I've used the named pipe technique extensively, it's really handy for
> compressing bcp datafiles, splitting non text/image data and so forth. You
> can even use it to tie two bcp processes together like this:
> mkfifo fifo
> bcp db..table out fifo ....... &
> bcp db..table in fifo .......
> This is cool for long bcp's because the bcp in does not have to wait for the
> bcp out to finish.
>
> However, the unix split command has two ways of splitting:
> 1) split on each newline. This won't work if the table you're bcp'ing out
> has say word docs stored in an image column because the newlines in the docs
> will get split.
>
> 2) split on x number of bytes. This is inefficient as you have to do a
> fixed length bcp with enough record size to handle the largest record you
> may encounter in the bcp session. And, that still wouldn't likely work in
> this scenario because when you copy it back in those extra bytes will
> corrupt something like a word document or spreadsheet.
>
> I think we may have discussed this Memorial Day weekend in the ase unix
> group.
>
> So my request still stands, and while we're at it, how about extended syntax
> to specify a table to copy into and do the same thing as I mentioned above.

Yes, and in that thread I posted a perl implementation of a "split"
utility which allows you to specify characters other than "newline" to
split on. Were you able to try that out?

Here is a reference to that thread, and my sample perl script:

news://forums.sybase.com/3CF542AD.A2AA0B35%40qwest.com.nospam


Download VCard ksherlo.vcf


Jason Webster Posted on 2002-06-14 20:08:41.0Z
From: "Jason Webster" <jason.webster`@mail.state.ky.us>
References: <qqOT4Z9ECHA.195@forums.sybase.com> <3D0A3D7E.A0C639B5@sybase.com> <cVWJJp9ECHA.196@forums.sybase.com> <3D0A4C15.386576D6@qwest.com.nospam>
Subject: Re: BCP to multiple files with a single scan of the source data.
Date: Fri, 14 Jun 2002 16:08:41 -0400
Lines: 50
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
Message-ID: <#Xw5kC#ECHA.111@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 162.114.54.51
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:432
Article PK: 93601


"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3D0A4C15.386576D6@qwest.com.nospam...
> Jason Webster wrote:
> >
> > I've used the named pipe technique extensively, it's really handy for
> > compressing bcp datafiles, splitting non text/image data and so forth.
You
> > can even use it to tie two bcp processes together like this:
> > mkfifo fifo
> > bcp db..table out fifo ....... &
> > bcp db..table in fifo .......
> > This is cool for long bcp's because the bcp in does not have to wait for
the
> > bcp out to finish.
> >
> > However, the unix split command has two ways of splitting:
> > 1) split on each newline. This won't work if the table you're bcp'ing
out
> > has say word docs stored in an image column because the newlines in the
docs
> > will get split.
> >
> > 2) split on x number of bytes. This is inefficient as you have to do a
> > fixed length bcp with enough record size to handle the largest record
you
> > may encounter in the bcp session. And, that still wouldn't likely work
in
> > this scenario because when you copy it back in those extra bytes will
> > corrupt something like a word document or spreadsheet.
> >
> > I think we may have discussed this Memorial Day weekend in the ase unix
> > group.
> >
> > So my request still stands, and while we're at it, how about extended
syntax
> > to specify a table to copy into and do the same thing as I mentioned
above.
>
> Yes, and in that thread I posted a perl implementation of a "split"
> utility which allows you to specify characters other than "newline" to
> split on. Were you able to try that out?
>
> Here is a reference to that thread, and my sample perl script:
>
> news://forums.sybase.com/3CF542AD.A2AA0B35%40qwest.com.nospam

Oh, that was you. No, I haven't had the chance to try it yet. However, I
still say it would be a handy feature to have in bcp. Do we agree on that?


Sherlock, Kevin Posted on 2002-06-14 21:24:41.0Z
Message-ID: <3D0A5F19.8F3095F8@qwest.com.nospam>
Date: Fri, 14 Jun 2002 16:24:41 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: BCP to multiple files with a single scan of the source data.
References: <qqOT4Z9ECHA.195@forums.sybase.com> <3D0A3D7E.A0C639B5@sybase.com> <cVWJJp9ECHA.196@forums.sybase.com> <3D0A4C15.386576D6@qwest.com.nospam> <#Xw5kC#ECHA.111@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------9377CD9BD680365A28AE6B06"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 32
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:431
Article PK: 93600


Jason Webster wrote:
>
> "Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
> news:3D0A4C15.386576D6@qwest.com.nospam...

> > Here is a reference to that thread, and my sample perl script:
> >
> > news://forums.sybase.com/3CF542AD.A2AA0B35%40qwest.com.nospam
>
> Oh, that was you. No, I haven't had the chance to try it yet. However, I
> still say it would be a handy feature to have in bcp. Do we agree on that?

Sure. Just wondering if this was a valid work-around in the meantime ;)


Download VCard ksherlo.vcf


Roger Broadbent Posted on 2002-06-17 10:26:01.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.excise-this.com>
References: <qqOT4Z9ECHA.195@forums.sybase.com> <3D0A3D7E.A0C639B5@sybase.com> <cVWJJp9ECHA.196@forums.sybase.com> <3D0A4C15.386576D6@qwest.com.nospam> <#Xw5kC#ECHA.111@forums.sybase.com> <3D0A5F19.8F3095F8@qwest.com.nospam>
Subject: Re: BCP to multiple files with a single scan of the source data.
Date: Mon, 17 Jun 2002 11:26:01 +0100
Lines: 34
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
Message-ID: <Rdo9GreFCHA.196@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:429
Article PK: 93598

BCP is just a fairly thin layer on top of ct-lib - there's nothing magic
about it AFAIK. Writing a bcp-like utility that was capable of what you want
would not be a major task, particularly if you were happy to stick to one
output file format. You could even top Sybase and make the source code open
like sqsh.

In fact, piping sqsh bcp-style output into a splitting utility would be
another option (http://www.sqsh.org/sqsh_features.html#Styles).

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3D0A5F19.8F3095F8@qwest.com.nospam...
> Jason Webster wrote:
> >
> > "Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
> > news:3D0A4C15.386576D6@qwest.com.nospam...
>
> > > Here is a reference to that thread, and my sample perl script:
> > >
> > > news://forums.sybase.com/3CF542AD.A2AA0B35%40qwest.com.nospam
> >
> > Oh, that was you. No, I haven't had the chance to try it yet. However,
I
> > still say it would be a handy feature to have in bcp. Do we agree on
that?
>
> Sure. Just wondering if this was a valid work-around in the meantime ;)