Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: export of large table

    Hi all,
    version 7.2 on aix 5.

    We are trying to export a table with 115 million rows. As soo as it reaches 64GB it abends giving folowing error message
    "SQL3002C An I/O error occurred while writing to the output data file."

    Is 64 GB limit of DB2 UDB or AIX file? If yes, How do we export the complete data?

    Thanks in advance
    j

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is probably an operationg system limit, although I believe it can usually be overriden with the proper operating system parameter.

    64GB is the maximum size of a table with 4K page size, but that is probably not directly related to your problem.

    Check with your AIX system administrator. If that is you, hit the manuals.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You are right, jfs has a 64Gig upper limit ..

    To confirm you filesystem is jfs, do

    lsfs <filesystem name>

    A jfs2 filesystem (which is available from AIX v5 should help )

    A note from IBM Website :

    The maximum size of a regular file in a JFS file system enabled for large files is slightly less than 64 gigabytes (68589453312). In other file systems that are enabled for large files and in other JFS file system types, all files not listed as regular in the previous table have a maximum file size of 2 gigabytes minus 1 (2147483647). The maximum size of a file in JFS2 is limited by the size of the file system itself.

    The architectural limit on the size of a JFS2 file system is 2^52 bytes, or 4 petabytes. In AIX 5.2, the maximum JFS2 file size supported by the 32-bit kernel is 2^40 - 4096 bytes, or just under 1 terabyte. The maximum file size supported by the 64-bit kernel is 2^44 - 4096 bytes, or just less than 16 terabytes.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Posts
    63
    Yes, This is a JFS file system. That means it has ;imit of 64 GB.
    Now the question is how do I export the data out of this table?
    I need to load the data into other database server.

    Thanks,
    j

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need a mount point with a file system that supports more than 64GB files. This is an AIX issue.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Define a jfs2 filesystem ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by toby25
    Yes, This is a JFS file system. That means it has ;imit of 64 GB.
    Now the question is how do I export the data out of this table?
    I need to load the data into other database server.

    Thanks,
    j
    Can you find something to toss into the WHERE clause of the SELECT to split it into two (or more) EXPORTs?
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Mar 2004
    Posts
    46
    There's one more option -

    Essentially what you need to do is create a "pipe file" (actually a pipe or fifo file), export to that "pipe file" and then compress the data being pumped into that file.

    Here's a sample -

    mkfifo pipefile <--- creat a pipe file

    db2 'export to pipefile of ixf select * from tablename' & <--- export and put in background !

    gzip -f < pipefile > abc.gz <--- compress the file

    If you wish, you can send the export output to a message file.

Posting Permissions

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