Index space is over-allocated

JITINDER CHOUDHARY

Index space is over-allocated

Hi All,

Could you please explain me what need to do if our Index is over-allocated. How to make space available for index spaces.

Thanks in advance!!!

Roy Boxwell

Index space is over-allocated
(in response to JITINDER CHOUDHARY)
On Db2 on z/OS a REORG will help you - but you might also have to ALTER the PRIQTY if you really want it to shrink in size.

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 5:37 AM
To: [login to unmask email]
Subject: [DB2-L] - Index space is over-allocated


Hi All,

Could you please explain me what need to do if our Index is over-allocated. How to make space available for index spaces.

Thanks in advance!!!

-----End Original Message-----

JITINDER CHOUDHARY

RE: Index space is over-allocated
(in response to Roy Boxwell)

Some indexspaces that are  over-allocated by at least 1,000 cylinders. please let me know how they can be reduced.

JITINDER CHOUDHARY

RE: Index space is over-allocated
(in response to Roy Boxwell)

 ALTER the PRIQTY means you want it to change it as  PRIQTY -1 and SECQTY -1

Roy Boxwell

Index space is over-allocated
(in response to JITINDER CHOUDHARY)
As long as you have the ZPARM MGEXTSZ set to YES (which is default these days) set to handle -1 -1 then yes, otherwise no!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 8:51 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Index space is over-allocated


ALTER the PRIQTY means you want it to change it as PRIQTY -1 and SECQTY -1

-----End Original Message-----

JITINDER CHOUDHARY

RE: Index space is over-allocated
(in response to Roy Boxwell)

We have changed PRIQTY -1 and SECQTY -1 for indexes but still not able to save more space. Could you please let me know if any other way to save space.

Bill Gallagher

Index space is over-allocated
(in response to JITINDER CHOUDHARY)
Did you run a REORG of the indexspaces after changing the PRIQTY and SECQTY?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 1:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Index space is over-allocated


We have changed PRIQTY -1 and SECQTY -1 for indexes but still not able to save more space. Could you please let me know if any other way to save space.

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Philip Sevetson

Index space is over-allocated
(in response to JITINDER CHOUDHARY)
Jtinder,

If you’ve:

· Already considered index compression

· Have PADDED NO for any variable-length index fields

· Minimize values for PCTFREE and FREEPAGE minimized

· Aren’t putting up huge wasteful secondary extents


You’ve covered about everything.

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 1:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Index space is over-allocated


We have changed PRIQTY -1 and SECQTY -1 for indexes but still not able to save more space. Could you please let me know if any other way to save space.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Larry Jardine

Index space is over-allocated
(in response to JITINDER CHOUDHARY)
What are the Reorg parms you used after alter the PRIQTY/SECQTY?

Larry Jardine
Aetna

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 1:33 PM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Index space is over-allocated

**** External Email - Use Caution ****

We have changed PRIQTY -1 and SECQTY -1 for indexes but still not able to save more space. Could you please let me know if any other way to save space.

-----End Original Message-----

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Michael Hannan

RE: Index space is over-allocated
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

Jtinder,

If you’ve:

· Already considered index compression

· Have PADDED NO for any variable-length index fields

· Minimize values for PCTFREE and FREEPAGE minimized

· Aren’t putting up huge wasteful secondary extents

You’ve covered about everything.

Apologies in advance for any spelling errors. My fingers get out of sync and I leave out words too. LOL

Yes good ideas. I tune some applications by reducing PCTFREE and FREEPAGE (when a lot more than necessary). This can make good Getpage reductions for scanners. Could be as little as PCTFREE 5 FREEPAGE 0 for indexes. Of course reorg then needed.

Padded NO can be very important for VARCHAR, but unfortunately does not help some customers that used quite long CHAR columns. 

Some customers could have too many columns in an index that are not needed, especially if a well clustered index, then Index Only access is not needed (often). i.e. Some indexes are much larger than the compressed table.

If your index always has more than one Equals matching column, make sure that the leading column is not a very long one with low cardinality (if possible), since that severely limits the non-leaf entry key truncation, not saving that much space I guess (digressing), but keeping the number of levels down. More important for very long keys.

Index space allocation is not normally a big problem, unless site has many different envirorment copies of same set of tables. Some sites allocated in Cylinder multiples for large numbers of trivial tiny indexes (and tables) wasting a lot of space (when space was short).

I am not at all keen on Index Compression. I once asked IBM if it had any performance advantages, since I could not see the point in it, and basically got a big NO. It is true that it could save some space if you are really desperate. Last resort. For me, somewhat of a solution looking for a problem. Would only consider it for gi-normous indexes when really cannot buy storage. I don't recommend sites to use Index Compression just because it is there. Larger page size without compression can be more useful to cut index levels and scan Getpages.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd