RE: Problem with SQL variables

Let me reiterate stuff that I've learned on my journey.
Stop thinking of a sql variable as a sql variable. Think of them instead in the terms of an RPG variable. This RPG variable is in a service program called MYSRVPGM. And the MYSRVPGM is set with
Activation group attribute . . . . . . . . . . . : *CALLER
Shared activation group . . . . . . . . . . . . : *
So, when you run the following in SQL
SET MYLIB.MYSRVPGM = 'HRSHIPPEDD';
It basically calls MYSRVPGM with that as a parameter and it stores that into an appropriate RPG variable.
When you run the following
VALUES MYLIB.MYSRVPGM;
It calls MYSRVPGM and retrieves the content of that variable.

However if you use the SET from something running in one activation group, and you use the VALUES from something running in a different activation group, you are going to run into issues. Basically what will happen is that VALUES will return a null variable.
This is exactly what is happening.

Yes, you can rewrite this to stop using sql variables.
Then again, you can stop trying to duct tape SQL into CL and just use a different language, one that can do SET, retrieves, etc all in the same activation group.

I come from a slightly different background. I was pretty adept at CL. Then, back when it was languishing way behind and had no structured programming operations, etc I abandoned it. I abandoned it to the extreme. Even for "system" type programs I would use RPG with QCMDEXC or some of the better API's for calling commands. So, if I had to write something which needed to use SQL and system commands even today I would not write it in CL but I would write it in RPG.

Rob Berendt