The concept of packages in DB2 conflicts with the package understanding that comes with an Oracle DBA who is expanding his/her horizon to include DB2 as an added skill-set.
Please refer to this excellent article http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0606chun/index.html about DB2 packages. In a nutshell, DB2 packages contain compiled SQL statements that DB2 engine uses to execute without having to go through the parse and compile. These type of SQLs in DB2 are called static SQL and Oracle DBAs try to simulate same by using Oracle profiles but DB2 packages are simpler to maintain and virtually they are way simpler and automated than Oracle way of creating profiles which is way complicated.
But still, DB2 packages require a little bit of understanding and knowing them will be very helpful. Please refer to the above article for other details and this article covers an issue of SQL0818N that is most commonly discussed and asked for in the DB2 forums.
When you are in the development mode, you will keep on compiling and testing your program either by using embedded SQL in C/C++ or in Java SQLJ program. In the development environment, it is recommended that you turn off KEEPFENCED parameter. When this parameter is turned off, DB2 will load package every time when it needs to execute it. But, you can not keep this parameter to NO in your production environment due to the performance reasons. If your application is of high throughput nature, the performance impact can be anywhere from 5-20 times just for this parameter.
For development environment:
db2 update dbm cfg using keepfenced off db2stop force db2start
For production environment:
db2 update dbm cfg using keepfenced on db2stop force db2start
Generally, you start getting SQL0818N error when this parameter is ON and you compile your program again and try to execute it. This complain comes from the fact that the package is already in the memory and the consistency token of the package in memory differs from the package that you just created. This leads to an issue of compiling programs in production systems. In an ideal world, DB2 developer’s pious thinking was that you would not compile a program in a production environment and the real world is not so pious. But, there is a workaround to this that will help you to keep compiling even in a production environment where KEEPFENCED is set to YES.
This issue is solved by using an option in PREP command to specify an unique name for the package every time you compile the program.
Traditionally, you will use following steps to compile a C/C++ program using embedded SQL.
A sample C program
$ cat test.sqc
#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#define NAME_COLUMN_SIZE 27
int main()
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char name[NAME_COLUMN_SIZE];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR GOTO error_section;
EXEC SQL CONNECT TO SAMPLE;
EXEC SQL VALUES CURRENT TIMESTAMP INTO :name;
EXEC SQL CONNECT RESET;
printf ("Server time is %s\n", name);
return 0;
error_section:
{
printf ("Error occured SQLCODE = %d\n", sqlca.sqlcode);
return -1;
}
}
Create explain tables
db2 connect to sample db2 -tf ~/sqllib/misc/EXPLAIN.DDL db2 connect reset
Compile using a makefile in development environment
$ cat makefile
DB2PATH = $(HOME)/sqllib
ERASE=rm -f
all : \
test
clean :
$(ERASE) *.o
cleanall : \
clean
$(ERASE) *.bnd
test :
./prepcmd sample test
precmd file
$ cat prepcmd db2 -v connect to $1 db2 -v "prep $2.sqc bindfile explain yes explsnap yes preprocessor 'gcc -I$HOME/sqllib/include -E -o $2.i'" db2 -v bind $2.bnd gcc $2.c -L$HOME/sqllib/lib -ldb2 -o $2prg db2 -v connect reset
Compile and run program
$ make $ ./testprg Server time is 2007-05-16-08.47.31.503218
Compile using a makefile in production environment
The makefile remains the same but we make some changes in prepcmd script as shown below:
$ cat prepcmd PACKAGETOKEN=$(date +"%j%M%S") db2 -v connect to $1 db2 -v "prep $2.sqc bindfile explain yes explsnap yes package using P$PACKAGETOKEN preprocessor 'gcc -I$HOME/sqllib/include -E -o $2.i'" gcc $2.c -L$HOME/sqllib/lib -ldb2 -o $2prg db2 -v connect reset
In above script, we create a package token using date command and by formating the output.
$ date +"%j%M%S" 1365432
In above command, we formatted a date by using number of days elapsed since beginning of the year and minutes and seconds. Since DB2 will not allow package name greater than 8 chars, our so called this unique identifier will work most of the time and it is fool-proof but not intelligent proof.
For the package token that we created, we use an option PACKAGE USING p$PACKAGETOKEN in PREP command to specify a unique package name for each compile that we perform. What this will do for you? You have just circumvented the SQL0818N problem by using a new package name every time you do a compile and execute the program. But this has a negative side effect that you should be aware. If you do lots of compile using this approach, you will have too many packages in your system catalog since we are not using package name same as our program unit. You will need to do cleanup of the redundant packages. This is simple since we are using PACKAGETOKEN and the first three letter of this package token is the number of days elapsed since beginning of the year. You can write a script that will drop packages of the previous day if you have done a fresh compile today.
Let us try to run the same make this time after above changes in the prepcmd file.
Compile and run program
$ make
./prepcmd sample test
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.1.2
SQL authorization ID = VIKRAM
Local database alias = SAMPLE
prep test.sqc bindfile explain yes explsnap yes package using P1360653 preprocessor 'gcc -I/home/vikram/sqllib/include -E -o test.i'
LINE MESSAGES FOR test.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL4017W Preprocessing has completed successfully.
SQL4018W Starting to process the preprocessed file
"/home/vikram/test/test.i".
SQL4019W Completed processing the preprocessed file
"test.i".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
connect reset
DB20000I The SQL command completed successfully.
$ ./testprg
Server time is 2007-05-16-09.08.10.647828
If you notice, the package name that we used above was P1360653. Try running same make again.
$ make
./prepcmd sample test
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.1.2
SQL authorization ID = VIKRAM
Local database alias = SAMPLE
prep test.sqc bindfile explain yes explsnap yes package using P1360942 preprocessor 'gcc -I/home/vikram/sqllib/include -E -o test.i'
LINE MESSAGES FOR test.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL4017W Preprocessing has completed successfully.
SQL4018W Starting to process the preprocessed file
"/home/vikram/test/test.i".
SQL4019W Completed processing the preprocessed file
"test.i".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
connect reset
DB20000I The SQL command completed successfully.
$ ./testprg
Server time is 2007-05-16-09.10.18.965980
This time the package name created is P1360942 and when you execute this program, DB2 will load this new package in memory to execute it but remember that earlier package P1360942 is still in memory. Since you do not this package anymore, you may drop this package using simple drop package command.
$ db2 drop package P1360653
If you write a script to remove old packages as I suggested above, please share that with me so that others can also use same.