Deploy new/unused packages without causing performace impacts

With current DB2(V11.1 FP2, FP4), Deploying a new or an unused packages causing variation and catalog locks impacting performance.

HOLDHDL    WAITHDL HOLDER     WAITER     HOLDMODE OBJTYPE    TABNAME    SCHEMA     SECS        STMTTEXT

---------- ------- ---------- ---------- -------- ---------- ---------- ---------- ----------- -----------------------------------

14570      12638   PROF       PROF       X        VARIATION  -          -                    0 -

14902      14710   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14670   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14650   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14702   PROF       PROF       X        VARIATION  -          -                    0 -

15519      14570   BFORGE     PROF       X        CATALOG    -          -                    0 -

14570      14754   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14675   PROF       PROF       X        VARIATION  -          -                    0 -

14570      12405   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14470   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14746   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14844   PROF       PROF       X        VARIATION  -          -                    0 -

14570      13810   PROF       PROF       X        VARIATION  -          -                    0 -

14570      13757   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14803   PROF       PROF       X        VARIATION  -          -                    0 -

14570      14724   PROF       PROF       X        VARIATION  -          -                    0 -

15519      14967   BFORGE     PROF       X        CATALOG    -          -                    1 -

14797      14638   PROF       PROF       X        VARIATION  -          -                    1 -

14902      14697   PROF       PROF       X        VARIATION  -          -                    1 -

14621      14914   PROF       PROF       X        VARIATION  -          -                    1 -

14621      12315   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14940   PROF       PROF       X        VARIATION  -          -                    1 -

14902      14736   PROF       PROF       X        VARIATION  -          -                    1 -

14902      13361   PROF       PROF       X        VARIATION  -          -                    1 -

14783      14861   PROF       PROF       X        VARIATION  -          -                    1 -

14902      14828   PROF       PROF       X        VARIATION  -          -                    1 -

14797      13703   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14933   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14788   PROF       PROF       X        VARIATION  -          -                    1 -

14902      14709   PROF       PROF       X        VARIATION  -          -                    1 -

14621      13472   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14860   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14820   PROF       PROF       X        VARIATION  -          -                    1 -

14967      14866   PROF       PROF       X        VARIATION  -          -                    1 -

14967      14787   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14925   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14846   PROF       PROF       X        VARIATION  -          -                    1 -

14797      13471   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14655   PROF       PROF       X        VARIATION  -          -                    1 -

14967      14747   PROF       PROF       X        VARIATION  -          -                    1 -

14621      13372   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14661   BFORGE     PROF       X        CATALOG    -          -                    1 -

14783      14957   PROF       PROF       X        VARIATION  -          -                    1 -

14967      13753   PROF       PROF       X        VARIATION  -          -                    1 -

14621      13720   PROF       PROF       X        VARIATION  -          -                    1 -

14783      13470   PROF       PROF       X        VARIATION  -          -                    1 -

14967      14733   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14621   BFORGE     PROF       X        CATALOG    -          -                    1 -

14785      13476   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14614   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14785   BFORGE     PROF       X        CATALOG    -          -                    1 -

14902      14660   PROF       PROF       X        VARIATION  -          -                    1 -

14661      14956   PROF       PROF       X        VARIATION  -          -                    1 -

14783      14752   PROF       PROF       X        VARIATION  -          -                    1 -

14783      14969   PROF       PROF       X        VARIATION  -          -                    1 -

14967      13469   PROF       PROF       X        VARIATION  -          -                    1 -

14783      14890   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14824   PROF       PROF       X        VARIATION  -          -                    1 -

14797      13495   PROF       PROF       X        VARIATION  -          -                    1 -

14621      13666   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14896   PROF       PROF       X        VARIATION  -          -                    1 -

14785      14692   PROF       PROF       X        VARIATION  -          -                    1 -

14621      13488   PROF       PROF       X        VARIATION  -          -                    1 -

14661      14830   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14797   BFORGE     PROF       X        CATALOG    -          -                    1 -

14902      14718   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14902   BFORGE     PROF       X        CATALOG    -          -                    1 -

14621      13369   PROF       PROF       X        VARIATION  -          -                    1 -

14783      14770   PROF       PROF       X        VARIATION  -          -                    1 -

14621      12441   PROF       PROF       X        VARIATION  -          -                    1 -

14797      14691   PROF       PROF       X        VARIATION  -          -                    1 -

15519      14842   BFORGE     PROF       X        CATALOG    -          -                    2 -

14842      13480   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14822   PROF       PROF       X        VARIATION  -          -                    2 -

15519      14618   BFORGE     PROF       X        CATALOG    -          -                    2 -

14842      14789   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14835   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13473   PROF       PROF       X        VARIATION  -          -                    2 -

14842      12440   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14966   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14979   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14775   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14617   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14959   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13755   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13722   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14939   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14610   PROF       PROF       X        VARIATION  -          -                    2 -

14783      13485   PROF       PROF       X        VARIATION  -          -                    2 -

14783      14794   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14965   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14636   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14978   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14695   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13662   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14800   PROF       PROF       X        VARIATION  -          -                    2 -

14618      13721   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14767   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13451   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14793   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14964   PROF       PROF       X        VARIATION  -          -                    2 -

15519      14885   BFORGE     PROF       X        CATALOG    -          -                    2 -

14618      14806   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14977   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13477   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14944   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14819   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14865   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13490   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14740   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14786   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14799   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14641   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13483   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13496   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14851   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14897   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14818   PROF       PROF       X        VARIATION  -          -                    2 -

14842      12443   PROF       PROF       X        VARIATION  -          -                    2 -

14783      13706   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13752   PROF       PROF       X        VARIATION  -          -                    2 -

14618      13798   PROF       PROF       X        VARIATION  -          -                    2 -

14842      13719   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14686   PROF       PROF       X        VARIATION  -          -                    2 -

14783      13482   PROF       PROF       X        VARIATION  -          -                    2 -

14783      14633   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14883   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14804   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14567   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14580   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14968   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13527   PROF       PROF       X        VARIATION  -          -                    2 -

14885      13323   PROF       PROF       X        VARIATION  -          -                    2 -

14783      14823   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14869   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14790   PROF       PROF       X        VARIATION  -          -                    2 -

14885      14895   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14941   PROF       PROF       X        VARIATION  -          -                    2 -

14618      13612   PROF       PROF       X        VARIATION  -          -                    2 -

14842      14612   PROF       PROF       X        VARIATION  -          -                    2 -

15519      14783   BFORGE     PROF       X        CATALOG    -          -                    2 -

14885      14954   PROF       PROF       X        VARIATION  -          -                    2 -

14618      14875   PROF       PROF       X        VARIATION  -          -                    2 -

14783      13500   PROF       PROF       X        VARIATION  -          -                    2 -

 

Opened a case aswell(TS002626325)

 

Below is the summary given by our AVP support

The contention happens during proc schema lookup in catalog cache which  causes the variation and catcache lock waits you are seeing. here is the explanation i have provided while working on case  TS001295694: 1. The code changes we are going to provide is only to prevent the locking  on sysdatatypes and only relevant to DDL changes that creates datatypes.  This may also improve the deadlocks. 2. The code changes don't apply to locking/deadlocks on catalog cache as  part of procschema lookup. The code is too complex to add changes which will not only be too risky  but also it is something that needs to go through full testing which is  usually done during release or fix pack releases. At this time no plans to make changes in this area due to complexity. The code changes on sysdatatypes may prevent locking/deadlocks during  procschema lookup in catalog cache. Hence if the sysdatatypes locking fix on its own does not address the  deadlocks or if lock waits are still problematic, then versioning the  function and procedure names in the new version of the module will avoid  the lock collision encountered due to the procschema lookup. 3. with the code change we are going to provide, once you test it out and  if all good, no need to version the type names in procedures or declare  them globally when the same type is being used in different modules or  procedures. 4.When asked whether any other scenarios that may cause similar locking on  sysdatatypes, the answer was as follows: With the planned code changes there is still locking on sysdatatypes  during the module creation, but the locking is reduced to just X lock the  row associated with the datatype in the new module. With this locking we  should not collide with uses of the data type of the same name in other  modules.

 

I have listed the samples from our QA DB with multiple combinations of same object name used in multiple Package/Modules,

The enhancement form IBM need to cover all use cases , where the instance of an object can deployed independently without outage while other instances are in use .

 

NOTE: There can be multiple combination of same object name repeating across or cross referenced in various other objects .

 

 

1. Same SP name /Function name used in Different Packages

 

SPECIFICNAME	ROUTINESCHEMA	ROUTINEMODULENAME	ROUTINENAME	ROUTINETYPE
SQL190301003255632	PROF	PKG_CIS_MERGE	IS_VALID_STATUS	F
SQL190822234527061	PROF	PKG_CIS_MERGE_B	IS_VALID_STATUS	F
SQL190822234002700	ENT	PKG_ENTITLEMENT	IS_VALID_STATUS	F
SQL190822234613294	ENT	PKG_ENTITLEMENT_B	IS_VALID_STATUS	F
SQL190822233934344	PROF	PKG_FULL_PROFILE_V3	IS_VALID_STATUS	F
SQL190822234700063	PROF	PKG_FULL_PROFILE_V3_B	IS_VALID_STATUS	F
SQL190822233640484	PROF	PKG_PROFILE	IS_VALID_STATUS	F
SQL190822234437245	PROF	PKG_PROFILE_B	IS_VALID_STATUS	F
SQL180722230508005	PROF	PKG_CIS_ACCOUNT	SP_LIST_PROFILE	P
SQL190822234501423	PROF	PKG_CIS_ACCOUNT_B	SP_LIST_PROFILE	P
SQL190822233937646	PROF	PKG_FULL_PROFILE_V3	SP_LIST_PROFILE	P
SQL190822234703265	PROF	PKG_FULL_PROFILE_V3_B	SP_LIST_PROFILE	P
SQL150127093844035	JMBPRF	PKG_JUMBO	SP_LIST_PROFILE	P
SQL190822234607055	JMBPRF	PKG_JUMBO_B	SP_LIST_PROFILE	P
SQL190816015445188	PROF	PKG_PRODUCT	SP_LIST_PROFILE	P
SQL190822234718412	PROF	PKG_PRODUCT_B	SP_LIST_PROFILE	P
SQL190822233636766	PROF	PKG_PROFILE	SP_LIST_PROFILE	P
SQL190822234433927	PROF	PKG_PROFILE_B	SP_LIST_PROFILE	P
SQL190822233957654	PROF	PKG_PUSH_DEVICE	SP_LIST_PROFILE	P
SQL190822234508650	PROF	PKG_PUSH_DEVICE_B	SP_LIST_PROFILE	P

 

1. Same SP/Function Name used within Same Package multiple times and also in different Package Version.

 

 

SPECIFICNAME	ROUTINESCHEMA	ROUTINEMODULENAME	ROUTINENAME	ROUTINETYPE
SQL190822233929028	PROF	PKG_CIS_TRIGGER	FN_RETRIEVE_CUSTOMERS	F
SQL190822233929329	PROF	PKG_CIS_TRIGGER	FN_RETRIEVE_CUSTOMERS	F
SQL190822234536778	PROF	PKG_CIS_TRIGGER_B	FN_RETRIEVE_CUSTOMERS	F
SQL190822234537379	PROF	PKG_CIS_TRIGGER_B	FN_RETRIEVE_CUSTOMERS	F
SQL140701163655328	PROF	PKG_SLOT	SP_ADD_TRANSACTION	P
SQL140701163655629	PROF	PKG_SLOT	SP_ADD_TRANSACTION	P
SQL190822234641274	PROF	PKG_SLOT_B	SP_ADD_TRANSACTION	P
SQL190822234641375	PROF	PKG_SLOT_B	SP_ADD_TRANSACTION	P

 

1. Custom SP name same as SYSTEM package SP name

 

SPECIFICNAME	ROUTINESCHEMA	ROUTINEMODULENAME	ROUTINENAME	ROUTINETYPE
DBMS_ALERT_SLEEP	SYSIBMADM	DBMS_ALERT	SLEEP	P
SQL140701163419882	PROF	DBMS_LOCK	SLEEP	P
SQL190822234507630	PROF	DBMS_LOCK_B	SLEEP	P

 

 

1. Data type referenced in Custom packages same as SYSTEM packages

 

SPECIFICNAME	ROUTINESCHEMA	ROUTINEMODULENAME	ROUTINENAME	ROUTINETYPE
SQL140630104617228	SYSIBMADM	DBMS_OUTPUT	ARRAY	F
SQL140630104622467	SYSIBMADM	DBMS_SQL	ARRAY	F
SQL140630104622941	SYSIBMADM	DBMS_UTILITY	ARRAY	F
SQL140630104632509	SYSIBMADM	MONREPORT	ARRAY	F
SQL180214001434251	PROF	PKG_ARRAY_OBJ	ARRAY	F
SQL160708024518986	BATCHJOB	PKG_BATCH	ARRAY	F
SQL190822234630218	BATCHJOB	PKG_BATCH_B	ARRAY	F
SQL180722230505080	PROF	PKG_CIS_ACCOUNT	ARRAY	F
SQL190822234458798	PROF	PKG_CIS_ACCOUNT_B	ARRAY	F
SQL140701163640520	PROF	PKG_CIS_SYNCHRONIZER	ARRAY	F
SQL190822234541991	PROF	PKG_CIS_SYNCHRONIZER_B	ARRAY	F
SQL190822233959674	ENT	PKG_ENTITLEMENT	ARRAY	F
SQL190822234610368	ENT	PKG_ENTITLEMENT_B	ARRAY	F
SQL141031075048575	BATCHJOB	PKG_MIS	ARRAY	F
SQL190822234651232	BATCHJOB	PKG_MIS_B	ARRAY	F
SQL190822234015114	ENT	PKG_PURGE_ENTITLEMENT	ARRAY	F
SQL190822234552135	ENT	PKG_PURGE_ENTITLEMENT_B	ARRAY	F
SQL140630104628448	SYSIBMADM	UTL_SMTP	ARRAY	F
SQL140630140145967	MMSBATCH	NULL	ARRAY	F