Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: bcp has limited file size?

    Dear,

    I'm doing a Sybase --> Oracle migration and have noticed that several of my bcp's of large tables seem to end writing to file after 4Gb of data.
    The bcp continues and the log keeps growing but the data is no longer put in the bcp-file.
    When loading into oracle I see that only 6 million of the 32 million rows are available...
    The OS is Windows (disk is NTFS).

    Do you have any idea what could be the problem?

    Rik

  2. #2
    Join Date
    May 2005
    Location
    Paris
    Posts
    46
    Hi,

    Do you have any idea what could be the problem?
    YES ....

    Stay with Sybase SGBD
    it is better than Or.....

    More seriously .... could you post the bcp out syntax ?
    do you have some errors on errorlog ?
    your FS is not full ?
    Did you try to extract yours bcp via another OS ... ?

    Cheers,
    Chris

  3. #3
    Join Date
    May 2006
    Posts
    6
    Well Chris, so far I'm not impressed ;-) .

    This is the bcp statement I'm using in a Windows-batch-file:

    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY.dat -c -t "<ec>" -r "<er>" -Usa -P -SDBSYBASE

    We've tried it some days ago on another db on the same machine and it worked ok... FS is not full, I'm now splitting it up into pieces (which takes a whole lot of time by the way).

    Rik

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    http://manuals.sybase.com:80/onlinebooks/group-as/asg1251e/utility/@ebt-link;pt=1905?

    It is not bcp, but the OS (if you call it that) that has a limit of 4gb for an OS filesize. bcp may well be continuing without realising that the OS has stopped writing the file, which is a problem that you may want to report to Sybase, but that's another story. You will have to break up the table into six (given that you have 32m rows and only 6m in the 4gb file) OS files. Use the FirstRow/LastRow operators for bcp.

    You can also do this in parallel (both in and out) with consideration for no of CPUs (replace the "&" with whatever windaz needz to put the task in the background)

    Try:
    Code:
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_1.dat -c -t "<ec>" -r "<er>" -L06000000 -Usa -P -SDBSYBASE &
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_2.dat -c -t "<ec>" -r "<er>" -F06000001 -L12000000 -Usa -P -SDBSYBASE &
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_3.dat -c -t "<ec>" -r "<er>" -F12000001 -L18000000 -Usa -P -SDBSYBASE &
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_4.dat -c -t "<ec>" -r "<er>" -F18000001 -L24000000 -Usa -P -SDBSYBASE &
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_5.dat -c -t "<ec>" -r "<er>" -F24000001 -L30000000 -Usa -P -SDBSYBASE &
    bcp C2M_DATA.dbo.ACTIVATION_SPECIALTY_mwb out ACTIVATION_SPECIALTY_6.dat -c -t "<ec>" -r "<er>" -F30000001 -Usa -P -SDBSYBASE &
    Last edited by DerekA; 07-31-06 at 01:34.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    May 2006
    Posts
    6
    Derek, thx. Strange thing however that I do have files > 4Gb and last week it worked (on another sybase db on the same machine.)...
    For the moment I've done it like you mention it, I did get strange results for 2 tables where my option -Fxxxxxx -Lxxxxx0 gave one extra record (5000001 instead of 5000000).

    Rik

  6. #6
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    1 Ensure that you remove the OS files before you bcp out.
    2 Based on my experience, the anomalies are related to your OS, not Sybase. You can chase up either one (service pack/EBF levels last week and this; your actual [not thought] values submitted; etc).
    3 For the two tables with strange results, you can check the tables (keys, rows)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •