tag:blogger.com,1999:blog-4516533711330247058.post5471184784397532351..comments2023-03-30T05:43:28.132-07:00Comments on Robert's Db2 blog: How big can a DB2 for z/OS index be?Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-4516533711330247058.post-49476025975650844012017-12-08T13:58:07.369-08:002017-12-08T13:58:07.369-08:00Sorry about the delayed response.
The information...Sorry about the delayed response.<br /><br />The information you cited in your comment, on maximum non-partitioned index size, was added to the Db2 10 for z/OS SQL Reference via (I believe) a documentation update to that manual that came out after Db2 10 became generally available. The full text of that informational item (and it's the same in the Db2 11 SQL Reference) is as follows:<br /><br />-------------------------<br /><br />Maximum size of a non-partitioned index for a partitioned table space:<br /><br />For 5-byte EA table spaces:<br />16 TB for 4 KB pages<br />32 TB for 8 KB pages<br />64 TB for 16 KB pages<br />128 TB for 32 KB pages<br />For table spaces that are defined with LARGE:<br />16 TB<br /><br />-------------------------<br /><br />Here is the link to the Db2 11 information in the IBM Db2 for z/OS Knowledge Center on the Web (look in Table 5 on the linked-to Web page): https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.html<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-48164417290202836612017-12-04T10:07:25.271-08:002017-12-04T10:07:25.271-08:00Is this still valid for Db2 11? SQL Reference says...Is this still valid for Db2 11? SQL Reference says,for a NPI of a Partitioned TS:<br />"16 TB for 4 KB pages"<br />"32 TB for 8 KG pages", etc.<br />And 16 TB for tablespaces defined as LARGE. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-75395222562763816662017-11-14T05:52:52.221-08:002017-11-14T05:52:52.221-08:00Sorry about the delay in responduing.
So, is the ...Sorry about the delay in responduing.<br /><br />So, is the table essentially replicated in the index? That is, are all of the table's 11 columns in the index?<br /><br />Was DSN1COMP executed for the index before it was compressed? If so, what was the estimate of disk space savings achievable through compressing the index? If DSN1COMP was not executed before the index was compressed, I believe it could be executed for a full image copy of the index (if the index is defined with COPY YES).<br /><br />For the table space, what is the value in the PAGESAVE column in the SYSTABLEPART catalog table?<br /><br />Is the index key continuously ascending (i.e., do new entries always go to the end of the index), or do new entries go into the "middle" of the index?<br /><br />Has the index been reorganized recently?<br /><br />What is the type of the underlying table space? Traditional segmented? Simple? UTS PBG? UTS PBR? If traditional segmented or simple, is there more than one table in the table space?<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-90224110657519811082017-11-10T22:23:52.600-08:002017-11-10T22:23:52.600-08:00Hi Robert
We have a table with 11 columns and a c...Hi Robert <br />We have a table with 11 columns and a column with varchar(1800) that there is unique index on it.<br />Indexspace for this index has been reached 64G (32 datasets ) but tablespace's size is 40G.<br />Would you please tell me why indexspace is grater than tablespace ? and how ?<br /> - The page size of index is 32K and table is 4K<br /> - Both of them (table and index) are compress yes<br /> - index created with NOT PADDED<br />Thank you in advance <br /> Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-52128259610066905672016-02-25T06:07:21.899-08:002016-02-25T06:07:21.899-08:00Thank you very much Robert.
I must confess I misr...Thank you very much Robert.<br /><br />I must confess I misread the comment "but how large can each index partition be" before the formula.<br /><br />Thank you for confirming!<br /><br />Cheers,<br />RichardRichardnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-89379099561755579802016-02-24T17:22:08.240-08:002016-02-24T17:22:08.240-08:00Here is an excerpt from this blog entry, Richard:
...Here is an excerpt from this blog entry, Richard:<br /><br />"...how large can each index partition be? You can't specify PIECESIZE for a partitioned index, but DB2 determines the index partition size via the formula referenced previously in this blog entry:<br /><br />Index partition size = MIN(x, 2^32 /(MIN(4096, 2^32 / (x / y))) * z)<br /><br />Where:<br /><br />x is the DSSIZE of the associated table space<br />y is the page size of the table space<br />z is the page size of the index"<br /><br />So, plug in the new DSSIZE value that you choose for the table space, and the table space's page size and the index's page size, and that should tell you how big a partition of a partitioned index on the table can be.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-19100649652708911042016-02-24T08:20:07.522-08:002016-02-24T08:20:07.522-08:00Hi Robert,
I read over your post, and several oth...Hi Robert,<br /><br />I read over your post, and several others including the IBM manuals, but still cannot find the answer to a specific situation with the size of individual partitions of a partitioned index.<br /><br />We have a 10-part tablespace, and have defined a partitioned index on the table (also 10 parts by default). We cannot change the partitioning strategy on the table without significant programming changes.<br /><br />The table will grow to have about 12GB of data per partition, which can be addressed via UTS-PBR DSSIZE. For the index we have calculated that each part will be about 6GB is size.<br /><br />What we cannot determine from the documentation (or your post) is if each index part can grow to be 6GB+ in size. The DSSIZE and PIECESIZE parameters do not apply to partitioned indexes.<br /><br />Any comments on how big individual partitioned index parts can be in our case?<br /><br />Thank you,<br />RichardRichardnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-1146828158961448472012-10-25T08:52:31.694-07:002012-10-25T08:52:31.694-07:00Apologies for the much-delayed response, Sevugan.
...Apologies for the much-delayed response, Sevugan.<br /><br />I believe that you may be right. The information you cite in the documentation regarding the default PIECE size for a non-partitioned index does indeed conflict with some other information that I have. What I don't know is which is correct - the documentation to which you've referred, or this other information I have (which I included in the blog entry above). I've been trying to get this question resolved, but I have not yet gotten a response to my inquiry. If I get an update I'll provide it in another comment to this blog entry.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-9825946534107565062012-10-10T03:25:33.554-07:002012-10-10T03:25:33.554-07:00Hi Robert, Thanks for theinformation about the num...Hi Robert, Thanks for theinformation about the number of datasets. I believe the default index size (4 GB)mentioned is wrong too. Isn't it?<br /><br />Sevuganhttps://www.blogger.com/profile/07397200969603385249noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-8435554446083538632012-10-10T02:57:35.007-07:002012-10-10T02:57:35.007-07:00Hi Robert,
For the above mentioned question
"...Hi Robert,<br />For the above mentioned question<br />"<br />If I have an NPI with piecesize of 4G on 60 parts partitioned tablespace with DSSIZE of 64G with pagesize of 4 Kb, what will be the maximum size of NPI ? is it 60*4GB or 2048*4GB?<br />" <br />as per the formula <br />MIN(4096, 2^32/(table space partition size/table space page size))<br />the maximum number of partition is 256. so the max size of NPI should be 256 * 4GB, right?<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-91618934692595863482012-10-10T02:52:39.943-07:002012-10-10T02:52:39.943-07:00This comment has been removed by the author.Sevuganhttps://www.blogger.com/profile/07397200969603385249noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-86847380459015538822012-09-04T15:02:00.247-07:002012-09-04T15:02:00.247-07:00Sorry about the delay in responding to your commen...Sorry about the delay in responding to your comment, Xiang - I had to do some research to determine whether or not the information you've cited is correct. In fact, it is NOT correct. Yes, the latest version of the SQL Reference (updated in June of 2012 for DB2 V8 and DB2 10, and in May of 2012 for DB2 9) indicates, in a table in the section on CREATE INDEX, the following: if a partitioned table space is defined with a DSSIZE or LARGE specification, or with a NUMPARTS value that is equal to or greater than 65, the maximum number of pieces for an NPI (non-partitioned index) defined on the table in the table space is 32. This is NOT CORRECT. In fact, an NPI defined on such a table space can have as many pieces as the table space can have partitions. That maximum partitions value can be calculated via this formula:<br /><br />MIN(4096, 2^32/(table space partition size/table space page size))<br /><br />The DB2 for z/OS documentation team is aware of this situation, and I expect that the information will be corrected through a documentation update in the near future.Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-10707477690068955822012-08-29T17:06:16.358-07:002012-08-29T17:06:16.358-07:00Hi Robert,
Thank you for the detailed illustration...Hi Robert,<br />Thank you for the detailed illustration.<br />I am not sure if this is up to date, but in the V10 sql manual http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_altertablespace.htm<br /><br />it says that for a non partitioned index the max number of pieces is 32 even for a partitioned large/dssize>4G tablespace.<br />Xiang<br />xiang.wang@cba.com.auAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-12878723768030309492012-08-22T12:19:29.938-07:002012-08-22T12:19:29.938-07:00Yes, I can comment on this.
The specific informat...Yes, I can comment on this.<br /><br />The specific information in the DB2 for z/OS SQL Reference, in the section on CREATE INDEX, is as follows:<br /><br />"Remember that 32 data sets is the limit if the underlying table space is not defined as LARGE or with a DSSIZE parameter and that the limit is 4096 for objects with greater than 254 parts."<br /><br />The phrase, "if the underlying table space is not defined as LARGE or with a DSSIZE parameter" means "if the underlying table space is not partitioned," as LARGE and DSSIZE are valid only for partitioned table spaces. So, the limit of 32 data sets applies only to an index on a non-partitioned table space (e.g., a segmented, non-universal table space).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-7225948954680611582012-08-22T08:50:53.714-07:002012-08-22T08:50:53.714-07:00Hi Robert,
In your text you say the following:
&...Hi Robert,<br /><br />In your text you say the following:<br /><br />"If the partitioned table space was defined with the LARGE or DSSIZE options, an associated non-partitioned index can have as many data sets as the table space can have partitions. "<br /><br />However, in the IBM-documentation with the CREATE INDEX statement I read that such indexes only can have 32 pieces. <br /><br />Can you comment on this.<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-44103999829810662502012-06-01T14:55:07.552-07:002012-06-01T14:55:07.552-07:00Sorry about the delay in responding.
I believe in...Sorry about the delay in responding.<br /><br />I believe in this case the maximum size of the NPI would be 2048x4GB. I'm not aware that NUMPARTS (assuming that this is a range-partitioned table space) affects the number of pieces into which an NPI can grow.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-80545144121402188352012-05-27T10:10:37.427-07:002012-05-27T10:10:37.427-07:00Hi Robert,
If I have an NPI with piecesize of 4G o...Hi Robert,<br />If I have an NPI with piecesize of 4G on 60 parts partitioned tablespace with DSSIZE of 64G with pagesize of 4 Kb, what will be the maximum size of NPI ? is it 60*4GB or 2048*4GB?Anonymousnoreply@blogger.com