2016-07 – Migration Planning : Stuck in a BIND

Migration planning to DB2 11 z/OS:

Two queries to list the NULLID and the PLAN packages that need a DB2 REBIND

I was at a customer site the other week and we were getting weird data out of the IFCIDs to read the Dynamic Statement Cache (DSC) – in fact, it was ending with an RC 00E60833, which was very odd as that has nothing to do with the DSC!

Old Structure problem?

Anyway, I had a hunch that perhaps old structures were causing grief as, at this site, the problems were happening only for about four hours overnight; never during peak load and absolutely never, ever in Test or QA.

HealthCheck time!

So I ran our little PerformanceHealthCheck freeware and, among a ton of other info, got the following data:

PHC009W Packages last bound in DB2 V0710         :     358

Now this is OK, as long as they are *not* being executed! So, looking into our WorkloadExpert tool, we could see that there were lots of SQLs—both static and dynamic—that were running in these DB2 V7 last bound packages.

NULLID Problem?

What was especially worrying, was the high number of NULLID packages that were in the list. These NULLID Collections are normally used by JAVA and other remote access routines. They are normally always allocated and in-use—making a REBIND a bit of a challenge. I wrote a little SQL to list out the possible bad guys:

SELECT SUBSTR(A.COLLID, 1, 18) AS COLLID 
, SUBSTR(A.NAME, 1, 8) AS PACKAGE
, A.RELBOUND 
, A.LASTUSED
FROM SYSIBM.SYSPACKAGE A 
WHERE A.RELBOUND IN (' ', 'K', 'L', 'M', 'O')
AND   A.LASTUSED > CURRENT DATE - 14 DAYS 
ORDER BY A.LASTUSED DESC, A.NAME 
WITH UR
;

The contents of the column RELBOUND are:

Blank is before DB2 V7, K is DB2 V7, L is DB2 V8, M is DB2 9, O is DB2 10, and P is DB2 11.

Of course your query must be changed, depending on what level of DB2 system you are currently running on. If running on a DB2 10 NFM then remove the ‘O’ in the IN list. It will then return all executed (so in use!) packages that have run within the last 14 days – Feel free to change the ORDER BY too, if you like.

Our output was quite scary as lots of packages were there.

REBIND the world

The DBAs rebound all of the static packages that had been executed this year, (not quite the world, but still way more than I thought could still be running anywhere in the world!) and then, on a Sunday morning, when they could “break in” – they rebound all of the NULLID packages on the list.

Since then – no problem! But is this the end of my story? Naturally not!

Now this particular customer is on DB2 10 NFM, and so these very old structures made we wonder…I did a post on LISTSERV about this. Pat Bossman, from IBM, answered (Corrections from Pat came in a later mail and I have cut out some extra clutter) :

PLAN Ahead

So now you know! Even PLANs should get a REBIND *before* you migrate, here you can use our EarlyPrecheck or BIX software to aid in the pre-migration checklist, in order to stop any AUTOBIND funnies that could occur. In fact, I would recommend REBINDing the PLAN after successful completion of the DB2 release migration. It will save you a step in a year or two.

Finally I wrote another little SQL to give the “overview” of all “old” PLANs:

-- LIST OUT ALL EXECUTED PLANS BOUND IN "OLDER VERSIONS"
 -- OF DB2
 SELECT C.NAME AS PLAN
 FROM SYSIBM.SYSPLAN     C
 WHERE C.RELBOUND IN (' ', 'K', 'L', 'M', 'O'))
 ORDER BY 1
 WITH UR
 ;

(Again, remove the ‘O’ is you are in DB2 10 NFM.) This then gives you, hopefully, a small list of PLANs that need a REBIND and you are ready to go!

I hope you liked this month’s topic.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell