dbexport failed with "Disk space full" message.

Hi Family. I just tried a dbexport on a database with some enormous tables. The dbexport failed with the message: Disk space full. Clearly, the dbspaces and the file system I am unloading from and to are far from full. Only at the second failure did it occur to me to look at the .unl files. ls -lt indicated the most recently written .unl file to have just hit the 2-GB boundary. My expletives at this revolting develpoment aside, I went looking for a similar victim in this newsgroup and I found a long thread, started by one CHENUS Thierry, that lamented over it. The only way around this stupid restriction in dbexport was to export to tape. And, according to the info in the thread, there is still a 2- GB restriction on the amount of data dbexport will write to a tape, even a 40-GB tape. I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) but it seems that 7.3 is no smarter. I don't know if it improves in 9.2 either. Next straw to clutch at: Does anyone out there know of a utility to replace dbexport and gets around the 2-GB constraint? Has anyone (i.e. Jonathan? Art?) written one? Thanks much, folks. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8l1vsb$thr$1@nnrp1.deja.com>, Jacob Salomon <JSalomon@bn.com> writes >Hi Family. > >I just tried a dbexport on a database with some enormous tables. The >dbexport failed with the message: Disk space full. > >Clearly, the dbspaces and the file system I am unloading from and to >are far from full. Only at the second failure did it occur to me to >look at the .unl files. ls -lt indicated the most recently written .unl >file to have just hit the 2-GB boundary. > >My expletives at this revolting develpoment aside, I went looking for a >similar victim in this newsgroup and I found a long thread, started by >one CHENUS Thierry, that lamented over it. > >The only way around this stupid restriction in dbexport was to export >to tape. And, according to the info in the thread, there is still a 2- >GB restriction on the amount of data dbexport will write to a tape, >even a 40-GB tape. > Wrong!! The limit is 2Tb, I once got slapped by June Tong for getting this wrong and my face is still red!! (A sore memory..!) 2Gb of 1K blocks = 2Tb. ^^^ ^^ >I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) but >it seems that 7.3 is no smarter. I don't know if it improves in 9.2 >either. > >Next straw to clutch at: Does anyone out there know of a utility to >replace dbexport and gets around the 2-GB constraint? Has anyone (i.e. >Jonathan? Art?) written one? > >Thanks much, folks. >-- >+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ >|------------------- Bulletin Board Announcement ----------------------| >| Congregants will please note that the bowl at the back of the church | >| bearing the sign "For the Sick" is for monetary contributions only. | >+----------------------------------------------------------------------+ > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams

In article <8l1vsb$thr$1@nnrp1.deja.com>, I, JSalomon@bn.com wrote: > Hi Family. > > I just tried a dbexport on a database with some enormous tables. The > dbexport failed with the message: Disk space full. > > Clearly, the dbspaces and the file system I am unloading from and to > are far from full. Only at the second failure did it occur to me to > look at the .unl files. ls -lt indicated the most recently written > .unl file to have just hit the 2-GB boundary. > > My expletives at this revolting develpoment aside, I went looking for > similar victim in this newsgroup and I found a long thread, started > by one CHENUS Thierry, that lamented over it. > > The only way around this stupid restriction in dbexport was to export > to tape. And, according to the info in the thread, there is still a > 2-GB restriction on the amount of data dbexport will write to a tape, > even a 40-GB tape. > > I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) > but it seems that 7.3 is no smarter. I don't know if it improves in > 9.2 either. > > Next straw to clutch at: Does anyone out there know of a utility to > replace dbexport and gets around the 2-GB constraint? Has anyone > (i.e. Jonathan? Art?) written one? Well, David Williams, still smarting from June Tong's correction, informed me that I was off base on the 2-GB tape limit. He wrote: > Wrong!! The limit is 2Tb, I once got slapped by June Tong for > getting this wrong and my face is still red!! (A sore memory..!) Thanks, David. You may be smarting we're all a bit smarter now. I got many e-mailed replies, but all aimed at getting me to use tape. For various reasons, this is less than convenient, though I will consider it. Now about a possible solution: I had my SA drop the file system I was using and recreate it as a file system that allows "Large" files - up to 128-GB in length. That should cover it. However, my first test of this new file system (unloading that same enormous table) faild at the same point - the 2-GB limit. However, I determined that my ulimit was set to exactly 4194304 [blocks of 512 bytes). So I may have run afoul of my ulimit, not a file-system limitation. I have e-mail out to them but maybe someone here knows it: How do I increase my ulimit? Typing "ulimit 8000000" does nothing. Perhaps it would if I were root but I can't do that. Is it something the SA can do for my account? Thanks. Oh BTW, even if I am successful with the "large-file" file system, I have a problem. I had intended to cross-mount my export directories and import to a server on another machine. My SA has found out from HP that a cross-mounted file system still will not handle files >2-GB. THis makes me more open-minded about exporting to tape. What was HP thinking? But that's an issue for another newsgroup. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

Jacob Salomon wrote: > In article <8l1vsb$thr$1@nnrp1.deja.com>, > I, JSalomon@bn.com wrote: > > Hi Family. > > > > I just tried a dbexport on a database with some enormous tables. The > > dbexport failed with the message: Disk space full. > > > > Clearly, the dbspaces and the file system I am unloading from and to > > are far from full. Only at the second failure did it occur to me to > > look at the .unl files. ls -lt indicated the most recently written > > .unl file to have just hit the 2-GB boundary. > > > > My expletives at this revolting develpoment aside, I went looking for > > similar victim in this newsgroup and I found a long thread, started > > by one CHENUS Thierry, that lamented over it. > > > > The only way around this stupid restriction in dbexport was to export > > to tape. And, according to the info in the thread, there is still a > > 2-GB restriction on the amount of data dbexport will write to a tape, > > even a 40-GB tape. > > > > I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) > > but it seems that 7.3 is no smarter. I don't know if it improves in > > 9.2 either. > > > > Next straw to clutch at: Does anyone out there know of a utility to > > replace dbexport and gets around the 2-GB constraint? Has anyone > > (i.e. Jonathan? Art?) written one? > > Well, David Williams, still smarting from June Tong's correction, > informed me that I was off base on the 2-GB tape limit. He wrote: > > > Wrong!! The limit is 2Tb, I once got slapped by June Tong for > > getting this wrong and my face is still red!! (A sore memory..!) > > Thanks, David. You may be smarting we're all a bit smarter now. > > I got many e-mailed replies, but all aimed at getting me to use tape. > For various reasons, this is less than convenient, though I will > consider it. > > Now about a possible solution: I had my SA drop the file system I was > using and recreate it as a file system that allows "Large" files - up > to 128-GB in length. That should cover it. However, my first test of > this new file system (unloading that same enormous table) faild at the > same point - the 2-GB limit. However, I determined that my ulimit was > set to exactly 4194304 [blocks of 512 bytes). So I may have run afoul > of my ulimit, not a file-system limitation. > > I have e-mail out to them but maybe someone here knows it: How do I > increase my ulimit? Typing "ulimit 8000000" does nothing. Perhaps it > would if I were root but I can't do that. > > Is it something the SA can do for my account? > > Thanks. > > Oh BTW, even if I am successful with the "large-file" file system, I > have a problem. I had intended to cross-mount my export directories > and import to a server on another machine. My SA has found out from HP > that a cross-mounted file system still will not handle files >2-GB. > THis makes me more open-minded about exporting to tape. > > What was HP thinking? But that's an issue for another newsgroup. > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy. Hi, There are 2 sides of the "larger than 2 GB files" coin that must both be regarded First, your file system must allow it (in AIX it is a Large File Enabled Filesystem). Second, "standard" UNIX applications use a 4-byte integer off_t, limiting the file size to 2 GB (the leftmost bit of off_t is used for negative status, -1 = error). To be able to write files larger than 2 GB, your application must be compiled accordingly, i.e. using 8 byte (long long) off_t. For details see information on about your operating system. AFAIK, normal Informix software (releases U..., not F...) are note prepared to use files larger than 2 GB. Hope, this helps you understanding what happens although it's no solution. Regards -- Helmut Leininger Bull AG / Vienna Open Systems Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.

> The only way around this stupid restriction in dbexport was to export > to tape. And, according to the info in the thread, there is still a 2- > GB restriction on the amount of data dbexport will write to a tape, > even a 40-GB tape. How about dbexport to raw disk?

OK, family, I may have a solution here. First, thanks to those who sent me e-mail replies. (I will not include their e-mails - if they wanted that known they would have posted.) Scott, Steve, Farmer, Noel, James, Stephen and Vivek. Most of your solutions had aspects that could be adapted. These came with undesireable side effects - like dropping - only temporarily - the large production tables for the dbexport. Yikes! Try telling that the OPS manager! Bear in mind that the reason I wanted to do an export was or the purpose of doing an import - that is, to copy all the tables from a production database to a testing database. If I could accomplish this without an dbexport/dbimport, I'd go for it. I was jut looking for an alternative. Because of differences in disk confurations & layouts, an archive up & down was not the way to go here. How'bout dbunload/dbload? ON the database level, it's kinda hard (read: impossible) to specify the target dbspaces of individual tables. Fragmentation? Don't ask me! HPL was not much of an option because it is designed for one table (or query) at a time. I hear it will get a new feature to generate the unload and load commands for an entire database. Good idea. But no help for poor li'l me. Fortunately, while searching the newsgroup, I ran across an irrelevant article, with Art K plugging his dbcopy program. I will be researching this as my real solution. In the meantime, I came up with a fun idea: The target system already has the tables but that is not so important; I could drop them and the recreate them using Art's "myschema" utility. I would unload the tables using a script like this (untested, of course): for TABLE in $( dbaccess imm - <<%% output to pipe "cat" without headings select tabname from systables where tabtype = "T" and tabid >= 100 order by 1;) do FIFO=${TABLE}-unl.fifo # Generate a name for a FIFO mkfifo $FIFO # Create the named pipeline gzip - <${FIFO} >${TABLE}.unl.gz & # Read & compress UNLOAD output dbaccess mydatabase - <<%% unload to ${FIFO} select * from ${TABLE} ;%% done I was amazed to see this work but the tech told me that dbexport uses lseek() to check where it is in the file. In a FIFO this will never be a big number. And gzip was not stuck when input stopped from the unload; it completed neatly: [1] + Done gzip - <unlpipe >ean_order_details.unl.gz & To load the tables, I would have a similar stunt, except that I would hgave to start the dbaccess with the LOAD command in background mode first, then the gzip -d to feed the FIFO. BTW, David, the tech told me that June was correct, provided the 20GB or so database all fits on 1 tape. If it exceeds 1 tape, then the dbimport will be unable to react past 2-GB on the first tape. Does this make sense? Nah! Is this a reason to discount it? Also, nah! Thanks Art, for just being there. -- Jake Salomon PS. Very relevant extra info: 1. I eventually got the file system to allow large (> 2-GB) files. Now I was hampered my a ulimit I could not raise. The HP tech person informed me that this is the case with the Korn shell under HP-UX. If I want no limit, I needed to use sh, not ksh. Under sh, my ulimit was unlimited (Try the command ulimit -a. It works only under sh, not under ksh.) 2. With the HP impediments out of the way, Informix itself has a 2-GB limit on anything it knowingly outputs to disk. The next release may have this restriction removed. In article <8l3ano$u45$1@nnrp1.deja.com>, JSalomon@bn.com wrote: > In article <8l1vsb$thr$1@nnrp1.deja.com>, > I, JSalomon@bn.com wrote: > > Hi Family. > > > > I just tried a dbexport on a database with some enormous tables. The > > dbexport failed with the message: Disk space full. > > > > Clearly, the dbspaces and the file system I am unloading from and to > > are far from full. Only at the second failure did it occur to me to > > look at the .unl files. ls -lt indicated the most recently written > > .unl file to have just hit the 2-GB boundary. > > > > My expletives at this revolting develpoment aside, I went looking > > for similar victim in this newsgroup and I found a long thread, > > started by one CHENUS Thierry, that lamented over it. > > > > The only way around this stupid restriction in dbexport was to > > export to tape. And, according to the info in the thread, there > > is still a 2-GB restriction on the amount of data dbexport will > > write to a tape, even a 40-GB tape. > > > > I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) > > but it seems that 7.3 is no smarter. I don't know if it improves in > > 9.2 either. > > > > Next straw to clutch at: Does anyone out there know of a utility to > > replace dbexport and gets around the 2-GB constraint? Has anyone > > (i.e. Jonathan? Art?) written one? > > Well, David Williams, still smarting from June Tong's correction, > informed me that I was off base on the 2-GB tape limit. He wrote: > > > Wrong!! The limit is 2Tb, I once got slapped by June Tong for > > getting this wrong and my face is still red!! (A sore memory..!) > > Thanks, David. You may be smarting we're all a bit smarter now. > > I got many e-mailed replies, but all aimed at getting me to use tape. > For various reasons, this is less than convenient, though I will > consider it. > > Now about a possible solution: I had my SA drop the file system I was > using and recreate it as a file system that allows "Large" files - up > to 128-GB in length. That should cover it. However, my first test of > this new file system (unloading that same enormous table) failed at > the same point - the 2-GB limit. However, I determined that my > ulimit was set to exactly 4194304 [blocks of 512 bytes). So I may > have run afoul of my ulimit, not a file-system limitation. > > I have e-mail out to them but maybe someone here knows it: How do I > increase my ulimit? Typing "ulimit 8000000" does nothing. Perhaps it > would if I were root but I can't do that. > > Is it something the SA can do for my account? > > Thanks. > > Oh BTW, even if I am successful with the "large-file" file system, I > have a problem. I had intended to cross-mount my export directories > and import to a server on another machine. My SA has found out from HP > that a cross-mounted file system still will not handle files >2-GB. > This makes me more open-minded about exporting to tape. > > What was HP thinking? But that's an issue for another newsgroup. > -- -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

If you have large file support and an unlimited ulimit try my new myexport package which uses Jonathan's sqlcmd and myschema a simulate dbexport and dbimport with complete cross compatibility EXCEPT no tape support and myexport and myimport will NOT choke on files larger than 2GB if the FS supports it! Certainly for the application you describe dbcopy will also do the trick. Try running at least 5 copies of dbcopy each copying a subset of the table this will improve throughput by about 350% over one copy. I have run as many as 40 copies of dbcopy in parallel with positive, though incremental, throughput gains from each subsequent additional copy going over Tbase100 or FDDI. Art S. Kagel Jacob Salomon wrote: > > OK, family, I may have a solution here. > > First, thanks to those who sent me e-mail replies. (I will not include > their e-mails - if they wanted that known they would have posted.) > Scott, Steve, Farmer, Noel, James, Stephen and Vivek. Most of your > solutions had aspects that could be adapted. These came with > undesireable side effects - like dropping - only temporarily - the > large production tables for the dbexport. Yikes! Try telling that the > OPS manager! > > Bear in mind that the reason I wanted to do an export was or the > purpose of doing an import - that is, to copy all the tables from a > production database to a testing database. If I could accomplish this > without an dbexport/dbimport, I'd go for it. I was jut looking for an > alternative. > > Because of differences in disk confurations & layouts, an archive up & > down was not the way to go here. > > How'bout dbunload/dbload? ON the database level, it's kinda hard (read: > impossible) to specify the target dbspaces of individual tables. > Fragmentation? Don't ask me! > > HPL was not much of an option because it is designed for one table (or > query) at a time. I hear it will get a new feature to generate the > unload and load commands for an entire database. Good idea. But no help > for poor li'l me. > > Fortunately, while searching the newsgroup, I ran across an irrelevant > article, with Art K plugging his dbcopy program. I will be researching > this as my real solution. > > In the meantime, I came up with a fun idea: > > The target system already has the tables but that is not so important; > I could drop them and the recreate them using Art's "myschema" utility. > I would unload the tables using a script like this (untested, of > course): > > for TABLE in $( > dbaccess imm - <<%% > output to pipe "cat" without headings > select tabname > from systables > where tabtype = "T" > and tabid >= 100 > order by 1;> ) > do > FIFO=${TABLE}-unl.fifo # Generate a name for a FIFO > mkfifo $FIFO # Create the named pipeline > gzip - <${FIFO} >${TABLE}.unl.gz & # Read & compress UNLOAD output > dbaccess mydatabase - <<%% > unload to ${FIFO} select * from ${TABLE} ;> %% > done > > I was amazed to see this work but the tech told me that dbexport uses > lseek() to check where it is in the file. In a FIFO this will never be > a big number. And gzip was not stuck when input stopped from the > unload; it completed neatly: > [1] + Done gzip - <unlpipe >ean_order_details.unl.gz & > > To load the tables, I would have a similar stunt, except that I would > hgave to start the dbaccess with the LOAD command in background mode > first, then the gzip -d to feed the FIFO. > > BTW, David, the tech told me that June was correct, provided the 20GB > or so database all fits on 1 tape. If it exceeds 1 tape, then the > dbimport will be unable to react past 2-GB on the first tape. Does > this make sense? Nah! Is this a reason to discount it? Also, nah! > > Thanks Art, for just being there. > > -- Jake Salomon > > PS. Very relevant extra info: > > 1. I eventually got the file system to allow large (> 2-GB) files. Now > I was hampered my a ulimit I could not raise. The HP tech person > informed me that this is the case with the Korn shell under HP-UX. > If I want no limit, I needed to use sh, not ksh. Under sh, my ulimit > was unlimited (Try the command ulimit -a. It works only under sh, > not under ksh.) > > 2. With the HP impediments out of the way, Informix itself has a 2-GB > limit on anything it knowingly outputs to disk. The next release > may have this restriction removed. > > In article <8l3ano$u45$1@nnrp1.deja.com>, > JSalomon@bn.com wrote: > > In article <8l1vsb$thr$1@nnrp1.deja.com>, > > I, JSalomon@bn.com wrote: > > > Hi Family. > > > > > > I just tried a dbexport on a database with some enormous tables. The > > > dbexport failed with the message: Disk space full. > > > > > > Clearly, the dbspaces and the file system I am unloading from and to > > > are far from full. Only at the second failure did it occur to me to > > > look at the .unl files. ls -lt indicated the most recently written > > > .unl file to have just hit the 2-GB boundary. > > > > > > My expletives at this revolting develpoment aside, I went looking > > > for similar victim in this newsgroup and I found a long thread, > > > started by one CHENUS Thierry, that lamented over it. > > > > > > The only way around this stupid restriction in dbexport was to > > > export to tape. And, according to the info in the thread, there > > > is still a 2-GB restriction on the amount of data dbexport will > > > write to a tape, even a 40-GB tape. > > > > > > I am still using 7.2 (don't worry! We're upgrading soon to 9.2! ;-) > > > but it seems that 7.3 is no smarter. I don't know if it improves in > > > 9.2 either. > > > > > > Next straw to clutch at: Does anyone out there know of a utility to > > > replace dbexport and gets around the 2-GB constraint? Has anyone > > > (i.e. Jonathan? Art?) written one? > > > > Well, David Williams, still smarting from June Tong's correction, > > informed me that I was off base on the 2-GB tape limit. He wrote: > > > > > Wrong!! The limit is 2Tb, I once got slapped by June Tong for > > > getting this wrong and my face is still red!! (A sore memory..!) > > > > Thanks, David. You may be smarting we're all a bit smarter now. > > > > I got many e-mailed replies, but all aimed at getting me to use tape. > > For various reasons, this is less than convenient, though I will > > consider it. > > > > Now about a possible solution: I had my SA drop the file system I was > > using and recreate it as a file system that allows "Large" files - up > > to 128-GB in length. That should cover it. However, my first test of > > this new file system (unloading that same enormous table) failed at > > the same point - the 2-GB limit. However, I determined that my > > ulimit was set to exactly 4194304 [blocks of 512 bytes). So I may > > have run afoul of my ulimit, not a file-system limitation. > > > > I have e-mail out to them but maybe someone here knows it: How do I > > increase my ulimit? Typing "ulimit 8000000" does nothing. Perhaps it > > would if I were root but I can't do that. > > > > Is it somet

I need to get details on large databases for a presentation. The idea is to scare newcomers with some idea of much they will have to scale up their applications to meet the volume of data. Ideally, I would like to get a retail database (I am thinking about a book on Wal-Mart that just came out from Morgan-Kaufmann Pulbishers), finance or banking industry, e-commerce, scienctific (I am thinking about CERN labs) and so forth. The reaosn I am lutting a general call for help is that I just moved and all my reference material is in cardboard boxes in the garage or still in Atlanta. I am sleeping on the floor in a sleeping bag. I need this stuff by next week. Help. Help. Help. --CELKO-- Joe Celko, SQL and Database Consultant When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated. Sent via Deja.com http://www.deja.com/ Before you buy.

Joe, Some large database stats from the financial industry: News: current dbsize: 400GB, growth: 30000 stories/day @4KB average. Traders P&L data: current 550GB, growth 4-8GB/month Edgar (SEC filings): 400GB, growth 90-120GB/year Art S. Kagel Joe Celko wrote: > > I need to get details on large databases for a presentation. The idea > is to scare newcomers with some idea of much they will have to scale up > their applications to meet the volume of data. > > Ideally, I would like to get a retail database (I am thinking about a > book on Wal-Mart that just came out from Morgan-Kaufmann Pulbishers), > finance or banking industry, e-commerce, scienctific (I am thinking > about CERN labs) and so forth. > > The reaosn I am lutting a general call for help is that I just moved > and all my reference material is in cardboard boxes in the garage or > still in Atlanta. I am sleeping on the floor in a sleeping bag. > > I need this stuff by next week. > > Help. Help. Help. > > --CELKO-- > Joe Celko, SQL and Database Consultant > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) > which can be cut and pasted into Query Analyzer is appreciated. > > Sent via Deja.com http://www.deja.com/ > Before you buy.

>Ideally, I would like to get a retail database (I am thinking about a >book on Wal-Mart that just came out from Morgan-Kaufmann I have no reference for this, but I understand Wal-Mart has a db that is in the terabyte range. ----------------------------------------------------------- Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free! http://www.keen.com