Improving the response time of Native SQL procedures in DB2 v10

IBM claims better performance “out of the box” when upgrading to DB2 version 10. Some additional manual steps are however recommended to gain additional performance of your DB2 application and/or environment. One of which is about Native SQL Procedures.

DB2 version 9 introduced a new type of stored procedures called Native SQL Procedures. These procedures execute in DBM1 address space, and provide SQL performance improvements due to less cross-memory calls compared to external stored procedures.

Several improvements were done in this area in DB2 version 10. IBM however mentions that the response time improvement (up to 20% improvement) can be achieved only if the existing Native SQL Procedures are dropped and re-created. That is, if you had created Native SQL Procedures under DB2 version 9 and upgraded to DB2 version 10, you might want to DROP/CREATE those.

I created a SQL statement to locate Native SQL Procedures that were created prior the migration to DB2 version NFM. Since I did not know the exact date when the subsystems were upgraded, I figure out that I could use a trick to get this information. As in every new DB2 version, the DB2 catalog contains additional tables that are created during the migration process. I took one of them, SYSIBM.SYSAUTOALERTS, and queried SYSIBM.SYSTABLES to get the CREATEDTS value. That is a timestamp that indicated when a table was created. So the following SQL statement indicates when DB2 was either installed at the version 10 level, or upgraded to version 10 NFM:

-- When was DB2 upgraded to v10 NFM ?

SELECT CREATEDTS               

  FROM SYSIBM.SYSTABLES        

 WHERE NAME    = 'SYSAUTOALERTS'

        AND CREATOR = 'SYSIBM' ;

The game is then to locate Native SQL Procedures created prior that date. Stored procedures are listed in SYSIBM.SYSROUTINES, and the column ORIGIN = 'N' indicates that we deals with a Native SQL Procedure. The following statement will thus provide a list of the Native SQL Procedures created prior DB2 was upgraded to version 10 NFM :

-- List of Native SQL Procedures to re-create

SELECT CREATEDBY,OWNER,NAME,ORIGIN,CREATEDTS        

  FROM SYSIBM.SYSROUTINES                           

 WHERE ORIGIN = 'N'                                 

   AND CREATEDTS < ( SELECT CREATEDTS               

                       FROM SYSIBM.SYSTABLES        

                      WHERE NAME    = 'SYSAUTOALERTS'

                        AND CREATOR = 'SYSIBM' );    

With that, I got the list of Native SQL Procedures that I want to DROP / RECREATE. Hopefully, you have the DDL for these objects stored in dataset, but nothing is less obvious. If not, you can use tools to generate the DDL statements from the information in the catalog, in this example I used CA RC/Query for DB2 for z/OS to locate a particular Native SQL Procedure and generate its DDL:

This command produces the following output :

All what’s needed is to update the SQL, add the DROP syntax and a couple of COMMITs, and the job is done!

I hope that this post is useful to you. If you used the technique described above, I will be glad to read your comments, especially if you noticed and quantified performance improvements when dropping and re-creating your Native SQL Procedures!