Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: isql + shell - output cut at 2 GB

    I need to create a dump of the data in our database.

    Following command is used:

    "$SYBASE_OCS/bin/isql -S$SERVER_NAME -U$USER_NAME -P$PASSWD \
    -w1000 -o$OUTPUT_FILE -A8192 -b -i$SQL_SCRIPT_TMP"

    Now, I reached more than 2 GB and the output is just stopped. Is there any limitation on the output of the isql? I'm using version 12.5.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You probably have an operating system limit not allowing large files
    The 2 GB limit was a problem on 32-bit systems

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Any reason for using isql instead of bcp?
    bcp will probably use less than half the time to create the data file

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    ISQL is currently imposed by our DBO's. I'll have a look at the bcp.

    Concerning the issue, I've been messing around and when I use the > iso -o, I can create larger files.

  5. #5
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    It is fine when the DBOs ask a technician to do a task "extract the contents of all the tables in the database in readable format"; it gets very silly when they tell you how to do that task "use isql only" or "use excel only" or "use two fingers only". Take the job, but do not take the silliness.

    PDreyer is right, bcp was created with this kind of task in mind, and it works around wintel limits such as 2gb filesize; plus it is much faster (you will have to attempt either isql or bcp a few times before you get it right).

    bcp -c to produce character format (human readable files)

    For files > 2gb, use bcp -F and -L to specify the First and Last row, and exec several bcps, producing several 2gb files.

    They cannot have silliness such as "give it to me in a single file" because the box they bought doesn't support it. See if you can train them up to handle "sets" of files rather than a single file. Zip the files together into a single archive, if you like.

    And ensure they understand, this is not a "dump" or a "backup", by any stretch of the imagination. There is no RI or integrity or currency between files or rows; the date/time context of the last row in the last file is somewhat different to that of the first row in the first file.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  6. #6
    Join Date
    Sep 2009
    Posts
    3
    Thx for the input.

    The 2gb issue has solved by using the '>' iso -o.
    Bcp we may not use at the moment as the current version is also not supporting the 2gb. A newer version is in trial but not released yet => disadvantage of being a big company.

    Now I get a logsegment full error => going from one error to the other.

    thx for your help

  7. #7
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    logsegment full is due to (a) not having thresholds to manage the log and (b) large transactions [poor transaction design]. It is not due to isql [select] or bcp [read mass]. Post another thread.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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