Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    19

    Unhappy Unanswered: Split the BCP data file

    HI All,

    I am using BCP to export a huge table around 30 Lakhs rows . I want to splt the file how do i do that . I have already exported the file how do i split it

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

    Use the given parameters

    3 Million rows is not "huge" by any means.

    1 Many Unix utilities to do that, basically specify first and last "lines" in each iteration. chop does a nice job and you can squeeze it through grep as well although it is not intended for that. Read the manual:
    %man chop
    %man grep


    2 But the easiest is to bcp out and specify the First and Last row parameters. If you do not specify these parameters, -F defaults to 1 and -L to the last row. If you want (eg) 3 output files:
    %bcp out ... -L1000000
    %bcp out ... -F1000001 -L2000000
    %bcp out ... -F2000001


    Note bcp ... -n is much faster than bcp ... -c but it is for loading into an ASE server on the same o/s platform.
    Last edited by DerekA; 02-07-05 at 08:08.
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    19
    thanx for yur solution but i am still facing the problem even after splitting the BCP dump file it inserts only a few records eg; if i have exported 1 lakh rows while using BCP in it loads only 75000 rows.

    Pls help

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

    Huh ?

    In that case your problem appears to be bcp in not bcp out as declared, and splitting the file may have no relevance. bcp can handle large files with ease, although splitting does allow you to multitask the import.

    Please post actual problem and errors encountered on the import. Does bcp hang or terminate with an error ?

    In case you are not aware, bcp runs in two 'modes': fast (no indexes, minimal logging) and slow (full logging). To ensure fast bcp (ordinary procedure for large imports):
    - set the select into/bulkcopy/pllsort db_option
    --- (the db is now unrecoverable)
    - drop indexes on the table
    - truncate the table
    - execute bcp in ...
    - create the indexes
    - clear the select into/bulkcopy/pllsort db_option
    - dump the database
    --- (the db is now recoverable)
    Otherwise you will suffer "transaction log full" and bcp will hang.
    Last edited by DerekA; 02-07-05 at 10:41.
    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
    Feb 2005
    Posts
    19
    Hi,
    I have done all the above mentioned tasks.It doesnt show any errors it starts and after importing around 75000 rows it just quits without and error and shows me that it has imported. thats all

  6. #6
    Join Date
    Dec 2004
    Posts
    25
    If there are no errors, then bcp has done what is was supposed to do.

    Are you using the same parameters (example -c -n -t) for bcp out/in ?
    Can you do a rowcount of the bcp out file to verify the number of rows ?

  7. #7
    Join Date
    Feb 2005
    Posts
    19
    Its still not hapenning Can some one help

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

    That's weird

    It is weird that bcp is not reporting errors, check that you are not redirecting stdout. For anyone to help you, we need explicit information, so it is up to you to find it and post it. "It does not work" is not enough information.

    Check the ASE errorlog for errors, particularly that the log may be full and therefore suspended.

    Check also that you have space in the db for more than 75,000 rows.
    Last edited by DerekA; 02-09-05 at 21:12.
    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

  9. #9
    Join Date
    Feb 2005
    Posts
    19
    i have table which contains not only text data but int data too some fields are NULL too is that creating a problem.

  10. #10
    Join Date
    Feb 2005
    Posts
    19
    i used BCP in and was abel to import the database on the windows server but it doest work on the Solaris server.

  11. #11
    Join Date
    Dec 2004
    Posts
    25
    what type of indexes do you have on this table ?
    any index which ignores duplicate rows ?
    i've never really used this type of index but i'm assuming that bcp would not report any errors even if rows are being ignored by the index.

Posting Permissions

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