ALTER FRAGMENT to a dbspace with a differnt page s

Greetings. My client has a monstrous table fragmented by expression. (half-year intervals but that's not important to the point) Well, this one partition has reached the limit of 16,777,215 pages - the maximum 24-bit page number in a rowid. And his dbload job dies with a "no more extents" error. Tentatively, my scheme for a cure is to alter fragment on the table modify the fragment to a new fragment in another dbspace, where that new dbspace has a defined page size of 16K. (Yeah, I know - add a BUFFERPOOL line on onconfig). The fly in the ointment (where that gross expression come from?): A remark in the Guide to SQL Syntax (rel 12.1), where it states: [ All of the dbspaces must have the same page size. ] Does that mean all the dbspaces of the various partitions must all have the same page size? This seems unlikely, since it is common practice to have data and index partitions with different page sizes. Yet, this is exactly my worry. Or does it mean that if I'm modifying multiple fragments in the one statement, all of those named must have the same page size? Still seems like a silly restriction but with only one fragment being modified I'm not running afoul of this. Anyone have a decent interpretation for this IBM-esque ambiguity? Thanks much! -- Jacob S.

[ ... snip ... ] > Does that mean all the dbspaces of the various partitions must all have the > same page size? This seems unlikely, since it is common practice to have data > and index partitions with different page sizes. Yet, this is exactly my worry. Hi Jacob, all DATA partitions must be placed in dbspaces of the same page size. Detached indexes can be placed in dbspaces with another page size than the data partitions. If indexes are partitioned as well, all index partitions must be placed in dbspaces having the same page size and the page size of the index partitions is not requred to be the same as for data partitions. Here is an example (V12.10.FC4) [ output of INFO FRAGMENTS for <table name> ] Idx/Tbl name Dbspace Partition Type Expression avgload datadbs1 datadbs1 T (rs_id < 100 ) avgload datadbs2 datadbs2 T (rs_id >= 100 ) ix01pk_avgload data8dbs1 data8dbs1 I The dbspaces datadbs1 and datadbs2 have 2 KiB page size but dbspace data8dbs1 has 8 KiB page size, as shown in onstat -d (edited) Dbspaces address ... pgsize flags owner name ........ 45a874b0 ... 2048 N BA informix datadbs1 45a876e0 ... 2048 N BA informix datadbs2 45a87b40 ... 8192 N BA informix data8dbs1 ........ HTH dic_k

Indeed, Richard. Thanks for the explicit clarity. I couldn't devise this test last night (it was after midnight) but here is an ample demonstration of what will not be permitted: Two dbspaces: - rptdbs with page size of 2k - gutz with page size of 14k create table fragtest ( arc_data char(8), payment_num integer,customer_num integer ) fragment by expression ( (mod(customer_num,2) = 0 )) in rptdbs, ( (mod(customer_num,2) = 1 )) in gutz ; 26015: All fragments of the table or index need to be of same pagesize. So much for that idea! I've suggested my client split the fragment into two expressions. Again, thanks. -- Jacob S.

All data fragments of a table must have the same page size and all index fragments for an individual index must have the same page size. I am not sure why you think this is silly, maybe because it seems like this restriction is not based on solid reasoning. This is not the case, in fact having a single index or table data pages span multiple page sizes would provide a very difficult problem for the optimizer. Currently when scanning for a specific page size we have a very good estimate of how many pages we must scan. Once you introduce multiple page sizes for a single object the difficultly increases exponentially. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 12/02/2014 09:58:26 PM: > From: "JACOB SALOMON" <jakesalomon@yahoo.com> > To: ids@iiug.org > Date: 12/02/2014 09:59 PM > Subject: ALTER FRAGMENT to a dbspace with a differnt page s [34272] > Sent by: ids-bounces@iiug.org > > Greetings. > > My client has a monstrous table fragmented by expression. (half-yearintervals > but that's not important to the point) Well, this one partition has reached > the limit of 16,777,215 pages - the maximum 24-bit page number in a > rowid. And > his dbload job dies with a "no more extents" error. > > Tentatively, my scheme for a cure is to alter fragment on the table > modify the > fragment to a new fragment in another dbspace, where that new dbspace has a > defined page size of 16K. (Yeah, I know - add a BUFFERPOOL line on onconfig). > The fly in the ointment (where that gross expression come from?): A remark in > the Guide to SQL Syntax (rel 12.1), where it states: > [ All of the dbspaces must have the same page size. ] > > Does that mean all the dbspaces of the various partitions must all have the > same page size? This seems unlikely, since it is common practice to have data > and index partitions with different page sizes. Yet, this is exactlymy worry. > > Or does it mean that if I'm modifying multiple fragments in the one > statement, > all of those named must have the same page size? Still seems like a silly > restriction but with only one fragment being modified I'm not > running afoul of > this. > > Anyone have a decent interpretation for this IBM-esque ambiguity? > > Thanks much! > > -- Jacob S. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks, John. I was thinking strictly as someone who just wants to do what he wants to do. I appreciate your insight and it makes a good deal of sense, until someone decides to run different optimizer schemes, one for each partitions. SO until someone opens *that* can of worms, I will accept this restriction and work within it. -- Jacob S.