Error importing data in IDS 11.50.FC1
Hello, We are having some trouble when importing a large data volume. The database import several Gb on the table, then it returns this error: -19836 Extent size is too large. Maximum size of an extent is %sk. The maximum size specified for a disk extent (either the EXTENT SIZE or the NEXT SIZE clause) is 16 MB pages on 9.40 and the maximum size of a chunk on 9.3x and lower. ----------------------------------------------------- We are using the default pagesize of 2k, that´s the reason of this limitation? I´ve read the release notes and there are several limitation numbers on the section: Table-Level Parameters (based on 2K page size) My question is: if we re-create the dbspaces and the database with a larger page size, could we solve this matter??? Thanks a lot for your helps!
What extent size and next size are you using to create the table? Note that after every 9th extent IDS doubles the table's next size to make it more efficient, but, despite the error message you quote, a single extent cannot be larger than the smaller of 16Million pages or the size of a single chunk. In addition, a songle partition of a table cannot exceed 16Million pages total. How big are the chunks in that dbspace? If your chunks are smaller than 2GB it is possible to have fewer than 16M pages and still have a next extent size that's bigger than your chunk size. Larger page sizes will only increase the number of rows of data you can fit onto 16Million pages, not the extent sizes. Rebuild the dbspace using bigger chunks and make sure to start with a significant initial and next extent size when you create the table so yo don't hit the maximum number of extents instead. Art On Tue, Jul 29, 2008 at 3:03 PM, ALEXANDRE MARINI <amarini@fazenda.ms.gov.br > wrote: > Hello, > We are having some trouble when importing a large data volume. > The database import several Gb on the table, then it returns this error: > > -19836 Extent size is too large. Maximum size of an extent is %sk. > > The maximum size specified for a disk extent (either the EXTENT SIZE or the > NEXT SIZE clause) is 16 MB pages on 9.40 and the maximum size of a chunk on > 9.3x and lower. > > ----------------------------------------------------- > We are using the default pagesize of 2k, that´s the reason of this > limitation? > I´ve read the release notes and there are several limitation numbers on the > section: Table-Level Parameters (based on 2K page size) > > My question is: if we re-create the dbspaces and the database with a larger > page size, could we solve this matter??? > > Thanks a lot for your helps! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
I may sound too simplistic but being the table so big, is it fragmented?. Solution could be just increasing the number of fragments. Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, July 29, 2008 2:18 PM To: ids@iiug.org Subject: Re: Error importing data in IDS 11.50.FC1 [12955] What extent size and next size are you using to create the table? Note that after every 9th extent IDS doubles the table's next size to make it more efficient, but, despite the error message you quote, a single extent cannot be larger than the smaller of 16Million pages or the size of a single chunk. In addition, a songle partition of a table cannot exceed 16Million pages total. How big are the chunks in that dbspace? If your chunks are smaller than 2GB it is possible to have fewer than 16M pages and still have a next extent size that's bigger than your chunk size. Larger page sizes will only increase the number of rows of data you can fit onto 16Million pages, not the extent sizes. Rebuild the dbspace using bigger chunks and make sure to start with a significant initial and next extent size when you create the table so yo don't hit the maximum number of extents instead. Art On Tue, Jul 29, 2008 at 3:03 PM, ALEXANDRE MARINI <amarini@fazenda.ms.gov.br > wrote: > Hello, > We are having some trouble when importing a large data volume. > The database import several Gb on the table, then it returns this error: > > -19836 Extent size is too large. Maximum size of an extent is %sk. > > The maximum size specified for a disk extent (either the EXTENT SIZE or the > NEXT SIZE clause) is 16 MB pages on 9.40 and the maximum size of a chunk on > 9.3x and lower. > > ----------------------------------------------------- > We are using the default pagesize of 2k, that´s the reason of this > limitation? > I´ve read the release notes and there are several limitation numbers on the > section: Table-Level Parameters (based on 2K page size) > > My question is: if we re-create the dbspaces and the database with a larger > page size, could we solve this matter??? > > Thanks a lot for your helps! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hmmm.... Or you could just be starting with a very small extent and then the engine automatically increases the size of the next ones... If this is the case, then there is room for a bug, because obviously it should not exceed the maximum extent size... How are you creating you table (storage clause), and how many extents do you see at the end? On Tue, Jul 29, 2008 at 9:03 PM, Walter Milan <Walter.Milan@hilton.com>wrote: > I may sound too simplistic but being the table so big, is it fragmented?. > Solution could be just increasing the number of fragments. > > Walter Milan > DBA > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, July 29, 2008 2:18 PM > To: ids@iiug.org > Subject: Re: Error importing data in IDS 11.50.FC1 [12955] > > What extent size and next size are you using to create the table? Note that > after every 9th extent IDS doubles the table's next size to make it more > efficient, but, despite the error message you quote, a single extent cannot > be larger than the smaller of 16Million pages or the size of a single > chunk. In addition, a songle partition of a table cannot exceed 16Million > pages total. How big are the chunks in that dbspace? If your chunks are > smaller than 2GB it is possible to have fewer than 16M pages and still have > a next extent size that's bigger than your chunk size. Larger page sizes > will only increase the number of rows of data you can fit onto 16Million > pages, not the extent sizes. Rebuild the dbspace using bigger chunks and > make sure to start with a significant initial and next extent size when you > create the table so yo don't hit the maximum number of extents instead. > > Art > > On Tue, Jul 29, 2008 at 3:03 PM, ALEXANDRE MARINI < > amarini@fazenda.ms.gov.br > > wrote: > > > Hello, > > We are having some trouble when importing a large data volume. > > The database import several Gb on the table, then it returns this error: > > > > -19836 Extent size is too large. Maximum size of an extent is %sk. > > > > The maximum size specified for a disk extent (either the EXTENT SIZE or > the > > NEXT SIZE clause) is 16 MB pages on 9.40 and the maximum size of a chunk > on > > 9.3x and lower. > > > > ----------------------------------------------------- > > We are using the default pagesize of 2k, that´s the reason of this > > limitation? > > I´ve read the release notes and there are several limitation numbers on > the > > section: Table-Level Parameters (based on 2K page size) > > > > My question is: if we re-create the dbspaces and the database with a > larger > > page size, could we solve this matter??? > > > > Thanks a lot for your helps! > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
I'm sorry. Art already explained this with details. Regards, On Tue, Jul 29, 2008 at 11:05 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Hmmm.... Or you could just be starting with a very small extent and then > the > engine automatically increases the size of the next ones... > If this is the case, then there is room for a bug, because obviously it > should not exceed the maximum extent size... > How are you creating you table (storage clause), and how many extents do > you > see at the end? > > On Tue, Jul 29, 2008 at 9:03 PM, Walter Milan <Walter.Milan@hilton.com > >wrote: > > > I may sound too simplistic but being the table so big, is it fragmented?. > > Solution could be just increasing the number of fragments. > > > > Walter Milan > > DBA > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art > > Kagel > > Sent: Tuesday, July 29, 2008 2:18 PM > > To: ids@iiug.org > > Subject: Re: Error importing data in IDS 11.50.FC1 [12955] > > > > What extent size and next size are you using to create the table? Note > that > > after every 9th extent IDS doubles the table's next size to make it more > > efficient, but, despite the error message you quote, a single extent > cannot > > be larger than the smaller of 16Million pages or the size of a single > > chunk. In addition, a songle partition of a table cannot exceed 16Million > > pages total. How big are the chunks in that dbspace? If your chunks are > > smaller than 2GB it is possible to have fewer than 16M pages and still > have > > a next extent size that's bigger than your chunk size. Larger page sizes > > will only increase the number of rows of data you can fit onto 16Million > > pages, not the extent sizes. Rebuild the dbspace using bigger chunks and > > make sure to start with a significant initial and next extent size when > you > > create the table so yo don't hit the maximum number of extents instead. > > > > Art > > > > On Tue, Jul 29, 2008 at 3:03 PM, ALEXANDRE MARINI < > > amarini@fazenda.ms.gov.br > > > wrote: > > > > > Hello, > > > We are having some trouble when importing a large data volume. > > > The database import several Gb on the table, then it returns this > error: > > > > > > -19836 Extent size is too large. Maximum size of an extent is %sk. > > > > > > The maximum size specified for a disk extent (either the EXTENT SIZE or > > the > > > NEXT SIZE clause) is 16 MB pages on 9.40 and the maximum size of a > chunk > > on > > > 9.3x and lower. > > > > > > ----------------------------------------------------- > > > We are using the default pagesize of 2k, that´s the reason of this > > > limitation? > > > I´ve read the release notes and there are several limitation numbers on > > the > > > section: Table-Level Parameters (based on 2K page size) > > > > > > My question is: if we re-create the dbspaces and the database with a > > larger > > > page size, could we solve this matter??? > > > > > > Thanks a lot for your helps! > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > > those > > opinions reflect those of other individuals affiliated with any entity > with > > which I am affiliated nor those of the entities themselves. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...