Drop index from a table used in CICS protected threads

Eugene Zeev

Drop index from a table used in CICS protected threads

Hi community members.

I would like to drop an index that is not in use. Its table in in use in some packages and these packages are used by CICS transactions with protected threads. Could there be some contention if I try to drop the index while these

protected threads are up? 

Charles Brown

Drop index from a table used in CICS protected threads
(in response to Eugene Zeev)
Good question
All packages particular static packages have dependencies. If it's CICS chances are it is static. If it's static and valid then there is an entry in a table called SYSIBM.SYSPACKDEP. For safety and to minimize the impact -- query this table to identify the package that may have dependencies on this index. example ( SELECT BNAME FROM SYSIBM.SYSPACKDEP WHERE DNAME = index-you-intend-to-drop). I'd advise you make a note of the package / packages names that will be impacted and keep in a safe place.
To answer your question, here are the contentions and challenges you may encounter.
- if the package/s are being used db2 will not allow the drop
- however if drop a package dependency.

Sent from my iPhone

> On Aug 6, 2017, at 7:11 AM, Eugene Zeev <[login to unmask email]> wrote:
>
> Hi community members.
>
> I would like to drop an index that is not in use. Its table in in use in some packages and these packages are used by CICS transactions with protected threads. Could there be some contention if I try to drop the index while these
>
> protected threads are up?
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Charles Brown

Drop index from a table used in CICS protected threads
(in response to Eugene Zeev)
Good question
All packages particular static packages have dependencies. You've guessed it -- If it's CICS chances are it is static. If it's static and valid then there is an entry in a table called SYSIBM.SYSPACKDEP. For safety and to minimize outages -- before you drop the index, query this table to identify the package that may have dependencies on this index. example ( SELECT BNAME FROM SYSIBM.SYSPACKDEP WHERE DNAME = index-you-intend-to-drop). I'd advise you make a note of the package / packages names that will be impacted and keep in a safe place.
Now to answer your question on contentions, repercussions, negative impact or outages.
- if the package/s is actively being used, db2 will not allow the drop because of contention
- however if you drop an index, meaning - this index with package dependencies. Db2 will invalidate the package and issue 904 upon your next attempt to use.
-- here is the frustrating problem. The 904 message will not identify what is unavailable. PACKDEP will have no entry for that index.
-- resolution? REBIND the Package.
-- possible negative impact? Db2 will develop a new access strategy without the index.

Sent from my iPhone

> On Aug 6, 2017, at 7:11 AM, Eugene Zeev <[login to unmask email]> wrote:
>
> Hi community members.
>
> I would like to drop an index that is not in use. Its table in in use in some packages and these packages are used by CICS transactions with protected threads. Could there be some contention if I try to drop the index while these
>
> protected threads are up?
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>