Question on dbschema rowsize

hi, i have a question, when i run dbschema on a particular table, it showed that my row size is 1346. is the row size the definite size of all rows or is just an estimate size? does this mean that all the rows i have row's size is 1346? what if some records have null values? what if i have varchar columns, would my row size still be 1346 for all of my rows? Thanks! Horacio

as a follow up question, sorry for this, given that the schema said my row size is 1346, would I be inserting into a new page everytime if my page size is 2048?

Rowsize is a maximum rowsize. Half-populated varchars will reduce that = to approximately what is actually used. What is important is that it is used to determine whether a page is full = or not. If you have 1344 bytes left on a page (in your case), the = engine will point to the next page for subsequent writes. On a 2K page, = this can be quite wasteful. You might want to consider a larger = pagesize to reduce waste in this case. j. On Feb 22, 2012, at 8:30 AM, NATYURAL HORACIO wrote: > hi,=20 >=20 > i have a question, when i run dbschema on a particular table, it = showed that=20 > my row size is 1346.=20 >=20 > is the row size the definite size of all rows or is just an estimate = size?=20 >=20 > does this mean that all the rows i have row's size is 1346?=20 >=20 > what if some records have null values?=20 >=20 > what if i have varchar columns, would my row size still be 1346 for = all of my=20 > rows?=20 >=20 > Thanks!=20 > Horacio=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

If your table has variable length columns then the ROWSIZE reported by dbschema is the maximum size of a row if all of the veriable length columns were each filled to their maximum lengths. The actual length of each row will depend on the contents of variable length columns. NULLs do not affect the lenght of fixed length columns, they simply contain a predefined value that is interpreted as NULL. Rows in tables that do not have variable length columns will always be the size reported by dbschema. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 22, 2012 at 8:30 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > i have a question, when i run dbschema on a particular table, it showed > that > my row size is 1346. > > is the row size the definite size of all rows or is just an estimate size? > > does this mean that all the rows i have row's size is 1346? > > what if some records have null values? > > what if i have varchar columns, would my row size still be 1346 for all of > my > rows? > > Thanks! > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3baf01d23fa804b98de53d

so for example, i have two pages here, my row size is 1346 page 1 - 2048 page 2 - 2048 I insert one record. size is 300 due to the varchars, i will insert into page 1 then. i have another records size is 300 due to the varchars, will i be inserting automatically to page 2? or will i insert still into page 1 since it is not yet filled up.

so if a row, does not fill up the page. another transaction can still insert into that page if it does not fit then? is this correct?

You have the right idea. j. On Feb 22, 2012, at 8:59 AM, NATYURAL HORACIO wrote: > so for example,=20 >=20 > i have two pages here, my row size is 1346=20 >=20 > page 1 - 2048=20 >=20 > page 2 - 2048=20 >=20 > I insert one record.=20 > size is 300 due to the varchars,=20 >=20 > i will insert into page 1 then.=20 >=20 > i have another records=20 > size is 300 due to the varchars,=20 >=20 > will i be inserting automatically to page 2?=20 > or will i insert still into page 1 since it is not yet filled up.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

well let me rephrase it, does informix fill up a page first before writing to the next page?

If the space left on the page is greater than rowsize (+ overhead) then = the next write will go to that page. If not, then it will go to a = subsequent page. j. On Feb 22, 2012, at 9:00 AM, NATYURAL HORACIO wrote: > so if a row, does not fill up the page.=20 > another transaction can still insert into that page if it does not fit = then?=20 >=20 > is this correct?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

hmmm, so does it mean that it fills up the 1st page first before going to the next page? Thanks

yes - if the row's varchar fields are maxed out. From: "NATYURAL HORACIO" <horacio.natyural@gmail.com> To: ids@iiug.org Date: 02/22/2012 07:50 AM Subject: Re: Question on dbschema rowsize [26309] Sent by: ids-bounces@iiug.org as a follow up question, sorry for this, given that the schema said my row size is 1346, would I be inserting into a new page everytime if my page size is 2048? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

great! thanks a lot

If your table is fixed length, then yes only one row will fit on each 2K page. If the row is variable length, and you are running in Informix version 10.00 or earlier, or 11.xx with MAX_FILL_DATA_PAGES set to 0 (or unset) then the same is true because the engine will only place a row onto a page (assuming the rowsize is less than pagesize) if the full maximum size of the row will fit in the available free space on the page. If you are running version 11.xx+ and MAX_FILL_DATA_PAGES is set to 1 then the engine will place more than one row on a page as long as there is sufficient free space on the page for the current size of the row and the insertion leaves at least 10% of the page still free to allow the rows on the page to expand somewhat without having to be relocated later. On a 2K page the engine will want at least 203bytes of free space left after inserting all rows on the page. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 22, 2012 at 8:50 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > as a follow up question, > > sorry for this, > > given that the schema said my row size is 1346, > would I be inserting into a new page everytime if my page size is 2048? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3baf01f61ee404b98ed945

Ok, no matter what the second row will likely go onto page 1 since together they are only ~600 bytes and the current used (300bytes) leaves more than the maximum length free for the second row. Indeed with two rows on the page there will by 1412 bytes left (subtract the 24byte header, 4 byte page trailer, and 4bytes for each row for the slot table entry each row has from 2048). That would leave room for a third row up to the maximum size to be placed on the same page but that would be the last row that would fit without MAX_FILL_DATA_PAGES set to 1 - of if you are running Informix 10.00 or earlier. Now if you are running 11.xx with MAX_FILL_DATA_PAGES set (BTW it is always a good idea to post your version and platform information so we don't have to repeat these proviso over and over) then you would be able to fit more rows on that first page until there was less than 203 bytes free. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 22, 2012 at 8:59 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > so for example, > > i have two pages here, my row size is 1346 > > page 1 - 2048 > > page 2 - 2048 > > I insert one record. > size is 300 due to the varchars, > > i will insert into page 1 then. > > i have another records > size is 300 due to the varchars, > > will i be inserting automatically to page 2? > or will i insert still into page 1 since it is not yet filled up. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93404611d2a8e04b98ef3cc

Most of the time. If there are many parallel sessions writing it may write to multiple pages in parallel. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 22, 2012 at 9:07 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > well let me rephrase it, > > does informix fill up a page first before writing to the next page? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21224bdea5f704b98ef6e1

Interesting. Will have to read up. j. On Feb 22, 2012, at 10:12 AM, Art Kagel wrote: > Ok, no matter what the second row will likely go onto page 1 since = together=20 > they are only ~600 bytes and the current used (300bytes) leaves more = than=20 > the maximum length free for the second row. Indeed with two rows on = the=20 > page there will by 1412 bytes left (subtract the 24byte header, 4 byte = page=20 > trailer, and 4bytes for each row for the slot table entry each row has = from=20 > 2048). That would leave room for a third row up to the maximum size to = be=20 > placed on the same page but that would be the last row that would fit=20= > without MAX_FILL_DATA_PAGES set to 1 - of if you are running Informix = 10.00=20 > or earlier.=20 >=20 > Now if you are running 11.xx with MAX_FILL_DATA_PAGES set (BTW it is = always=20 > a good idea to post your version and platform information so we don't = have=20 > to repeat these proviso over and over) then you would be able to fit = more=20 > rows on that first page until there was less than 203 bytes free.=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions=20 > and do not reflect on my employer, Advanced DataTools, the IIUG, nor = any=20 > other organization with which I am associated either explicitly,=20 > implicitly, or by inference. Neither do those opinions reflect those = of=20 > other individuals affiliated with any entity with which I am = affiliated nor=20 > those of the entities themselves.=20 >=20 > On Wed, Feb 22, 2012 at 8:59 AM, NATYURAL HORACIO <=20 > horacio.natyural@gmail.com> wrote:=20 >=20 >> so for example,=20 >>=20 >> i have two pages here, my row size is 1346=20 >>=20 >> page 1 - 2048=20 >>=20 >> page 2 - 2048=20 >>=20 >> I insert one record.=20 >> size is 300 due to the varchars,=20 >>=20 >> i will insert into page 1 then.=20 >>=20 >> i have another records=20 >> size is 300 due to the varchars,=20 >>=20 >> will i be inserting automatically to page 2?=20 >> or will i insert still into page 1 since it is not yet filled up.=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --14dae93404611d2a8e04b98ef3cc=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20