I am having 4 databases (2 DB2 & 2 IMS) running on IBM mainframes. There are many applications written mainly in PL1 (with few COBOL and Assembler) which are using these 4 databases. There is currently no information available on the number of applications involved, the programs and the business logic involved. Also the number of lines of code is in millions (around 50 million).
Is there any tool available to to identify all the impacted applications and programs that directly or indirectly use the 4 databases.

You can find all the programs that use embedded static SQL, but not those that use dynamic SQL. If the programs are PL/I, COBOL, or assembler, it is likely that each program that uses DB2 has at least one embedded static SQL statement (but no guarantees). I am not 100% sure about assember, since I have never used it to access DB2.

You will need to create a PLAN_TABLE under the schema name of your sysadm user. The PLAN_TABLE DDL is in the manuals (see the section on the Explain).

Then the sysadm will need to rebind all the plans and packages with explain=yes, so it will insert rows into the PLAN_TABLE for each embedded static SQL statement contained in the plans and packages. Included in that row inserted is the program name which you can query with regular SQL select statements. Check the Explain function in the manuals for more information on how to interpret the PLAN_TABLE.

Here is the right direction:
For DB2 applications, all DB objects that application packages depend on are recorded in SYSIBM.SYSPACKDEP table.
For IMS applications, check your PSB sorce library (PDS). All IMS DBs in use should be found there.

Hi ajithnayak,
on witch mainframe are you running DB2? There are 4 mainframe (zSeries) operating system: zOS, zVM, zVSE and zLinux.

DBA-Jr that could be solution for static SQLs, but for dynamic SQLs there is probably no record in database tables. So ajithnayak are you using static or dynamic SQL aproach?

Hope this helps,
Grofaty

I don't see the program name in SYSIBM.SYSPACKDEP. Perhaps it is in some other catalog table. But if the Plan name or package name is different than the program name, then it may not be in the catalog, but I know it shows up in the PLAN_TABLE when the plan or package is explained.