Re: Outrageous Extent sizes

Hi All, To wrap up this topic: After working with HCL, we discovered a mismatch between the Next Size value in dbschema (and appropriately, in systables) versus 'oncheck -pT' (and thus sysmaster:sysptnhdr). As the Next Size is automatically doubled, the new value is stored in sysptnhdr, but not reflected in systables; therefore, the DBA has no idea that the doubling has occurred. IBM, of course, claims this is an "as designed feature". Hmmm... similar data in two different tables that don't sync? Sounds like a BUG to me. Best bet: create a script that monitors sysptnhdr against systables. Result: we did discover that "alter table .. modify next size..." will in fact reset the sysptnhdr value to the requested size. The doubling counter will restart, so constant monitoring is still necessary, but at least the extents will be sized as the DBA requested them to be. Thanks, Michael Hoffman In Response To: Re: Outrageous Extent sizes (Art Kagel) Art & David, Seeing as the issue really pertains to slowly-growing large tables, is there a way to turn OFF Extent Size doubling? Either on the database or table level? I'd even take instance level. As David points out, if I am doing my DBA job, I have already done due diligence and worked out how fast my tables grow, and what the appropriate NEXT size should be based on that growth. There should be a way to force Informix to acknowledge that work, to acknowledge that maybe the DBA knows their data patterns better than IBM does. Thanks, Michael

Hi, Try oncheck -cc I would expect this to complain, it did in earlier releases. If someone manually updates systables (which is NOT supported) then this can happen but it is flagged by oncheck. Regards, David. > On 02 October 2017 at 19:34 MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > > > Hi All, > To wrap up this topic: > After working with HCL, we discovered a mismatch between the Next Size value > in dbschema (and appropriately, in systables) versus 'oncheck -pT' (and thus > sysmaster:sysptnhdr). > As the Next Size is automatically doubled, the new value is stored in > sysptnhdr, but not reflected in systables; therefore, the DBA has no idea that > the doubling has occurred. > > IBM, of course, claims this is an "as designed feature". Hmmm... similar data > in two different tables that don't sync? Sounds like a BUG to me. > > Best bet: create a script that monitors sysptnhdr against systables. > > Result: we did discover that "alter table .. modify next size..." will in fact > reset the sysptnhdr value to the requested size. The doubling counter will > restart, so constant monitoring is still necessary, but at least the extents > will be sized as the DBA requested them to be. > > Thanks, > Michael Hoffman > > In Response To: Re: Outrageous Extent sizes (Art Kagel) > > Art & David, > Seeing as the issue really pertains to slowly-growing large tables, is there a > way to turn OFF Extent Size doubling? Either on the database or table level? > I'd even take instance level. > > As David points out, if I am doing my DBA job, I have already done due > diligence and worked out how fast my tables grow, and what the appropriate > NEXT size should be based on that growth. > > There should be a way to force Informix to acknowledge that work, to > acknowledge that maybe the DBA knows their data patterns better than IBM does. > > Thanks, > Michael > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi David, No errors reported by 'oncheck -cc'. I ran an example in dbaccess, linked systables with sysptnhdr. It shows the difference in the 'next size' values (I cut the results to just 2 tables). 'oncheck -cc' shows nothing wrong with this. As I said - IBM doesn't consider the internal mismatch an error; it's a feature designed to cut down on extents. Example: --------- dbaccess: select bz.tabname, bz.nextsize, sm.nextsiz from sysmaster:sysptnhdr sm, biz:systables bz where bz.partnum = sm.partnum and bz.tabname[1,3] <> 'sys' ---tabname attachment_dom nextsize 16 nextsiz 8 tabname certificates nextsize 16 nextsiz 4096 --- > oncheck -cc biz Validating database biz Validating systables for database biz Validating syscolumns for database biz Validating sysindices for database biz [.. blah blah blah .. ]