Tuesday, January 12, 2016

How to find the plan and package name in DB2 from program name

Suppose we have a cobol-db2 module named 'PGM-ABC'
We want to bind it and run the program. But we are not sure about the package and plan details.So where do we find it from? SYSIBM tables will give us the details we need.Lets check it out.
With the program name we can query SYSIBM.SYSPACKAGE table giving the name of the program(PGM-ABC) against the column 'NAME'.  We can use DB2 file-aid tool or use the below  query in SPUFI.

Select location, collid,name ,contoken,timestamp,bindtime from SYSIBM.SYSPACKAGE where NAME = 'PGM-NAME' ;

Once we get the COLLID,we need to query the SYSIBM.SYSPACKLIST table to get the plan name

SELECT PLANNAME,LOCATION,COLLID FROM SYSIBM.SYSPACKLIST WHERE COLLID='colld-id-name-we-got-above';

From experience, we should try to select the latest rows from the tables checking the TIMESTAMP fields; or check what other ppl have used in recent times.