Tuesday, October 8, 2013

I'm not you - Ways to customize a DB2 application environment

I am not you, she is not him. Users differ, applications differ. There are two interesting concepts in DB2 that help to adapt an application environment and application and that are not widely known. One is the connect_proc database configuration parameter, the other is conditional compilation of SQL. Let me give you some ideas of what it is and links to dig deeper into it.

Introduced in DB2 9.7 FP3, the connect procedure let's use configure a stored procedure that is invoked whenever someone (an application) connects to DB2. Thus, the session context can be tailored to the user or application by setting the locale, the path to resolve functions, optimization levels and more. Even though the procedure is not allowed to modify the database it could invoke another routine with an autonomous transaction. That way some information could be logged ("track", "audit") or another event be triggered. The DB2 Information Center has some examples on how to use connect_proc for session customization. Serge Rielau showed how to implement some logging.

Conditional compilation allows to tailor the compiled SQL to the actual environment. You could just use procedure or function code for little or for big endian - making it execute faster, implement your own routine in different ways depending on the DB2 version - use a more efficient implementation with newer DB2 versions, or plug in a lighter version of code - maybe without debug code. The key is to use SQL_CCFLAGS, flags for conditional SQL compilation. The variable can be set at the database or session level and then referenced within the actual SQL code. Special "_IF"/"_ELSEIF"/"_THEN"/"_END" directives do the trick of selecting the code you want. This is similar to shell scripting and programming languages.
In the DB2 system catalog you can find out which flags were set when a routine or trigger was compiled ("whom to blame..."). Of course the currently set value can be retrieved using the CURRENT SQL_CCFLAGS special registry.

Of course it is possible to combine the two. Develop your own library of connect-related actions, audit, and setup routines, then set SQL_CCFLAGS to implement just the mix you need for a specific application environment.