Activation groups may be the most important concept to understand when creating *PGM and *SRVPGM objects on the System i (AS/400). Get this wrong and you will be chasing your tail for hours trying to figure out why data being returned to you is not what you were expecting.

I ran across an issue where an external stored procedure (implemented in SQLRPGLE) that returned a list of products to an ODBC client would sometimes return the wrong information. The embedded SQL query was simple so the problem had to lie elsewhere.

The problem was that when the CRTPGM command was run the ACTGRP parameter was set to *ENTMOD when it should have been set to *CALLER.

The ODBC client daemon process on the System i (QZDASOINIT) keeps the connection open to facilitate connection performance, and therefore the SQL cursor (ODP) from a prior execution would sometimes be returned.

The fix was to specify ACTGRP(*CALLER), thus directing the returned results to the calling ODBC client.

Further reading from IBM: