Re: Problem with SQL variables

Global variables are scoped to the session...

The SQL Procedures, Triggers, and Functions Redbooks says
"On DB2 for i, the session corresponds to a connection or a job."

However, given what you're seeing, I wonder if a session is actually
scoped to the activation group rather than the job...

Charles



On Thu, Jan 7, 2021 at 12:34 PM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi everyone
We are on V7r3
I thought I understood how SQL variables worked
Now Im thinking that I don't
Im testing a CLLE program
Here is the code that I am having a problem with

0093.00 /* The file NBJDTA/HRSHIPPEDD is the delta for the Customer
extract */
0094.00 /* This first job initializes the SQL variables for the Customer
extract */
0095.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJA03A)
+
0096.00 COMMIT(*NONE)
0097.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJB03A)
+
0098.00 COMMIT(*NONE)
0099.00 /* The next job inserts the SQL Variables into the control file
*/
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP (select +
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD from +
0108.00 SYSIBM.SYSDUMMY1)') COMMIT(*NONE)
0109.00

Here is HRVARJA03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADAS400FILENAMESHPD CHARACTER (10);
SET NBJDTA.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
CREATE or REPLACE VARIABLE NBJDTA.HIRADSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADSTATUSSHPD = 'N';

Here is HRVARJB03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADINTERFACENAMESHPD CHARACTER (50);
SET NBJDTA.HIRADINTERFACENAMESHPD =
'HIGH RADIUS SHIPMENT EXTRACT';
CREATE or REPLACE VARIABLE NBJDTA.HIRADFILENAMESHPD CHARACTER (100);
SET NBJDTA.HIRADFILENAMESHPD = ' ';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCH#SHPD DECIMAL (12,0);
SET NBJDTA.HIRADBATCH#SHPD =
( NBTYGPL.NEXTNUMBERSQL('HIGH RADIUS INTERFACE'));
CREATE or REPLACE VARIABLE NBJDTA.HIRADFINALSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADFINALSTATUSSHPD = 'U';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCHTIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADBATCHTIMESTAMPSHPD =
CURRENT_TIMESTAMP;
CREATE or REPLACE VARIABLE NBJDTA.HIRADODITIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADODITIMESTAMPSHPD =
CURRENT_TIMESTAMP;

When I test this code (interactively) - both HRVARJA03A & HRVARJB03A are
successful
Well - the spool file that is generated shows the variables have been
created and populated
However - the insert fails with
Null values not allowed in column or variable HRINERFNAM.
Which is being populated from the variable nbjdta.HIRADINTERFACENAMESHPD

If I then run the following in a STRSQL session
select
nbjdta.HIRADINTERFACENAMESHPD,
nbjdta.HIRADFILENAMESHPD,
nbjdta.HIRADAS400FILENAMESHPD,
nbjdta.HIRADBATCH#SHPD,
nbjdta.HIRADSTATUSSHPD,
nbjdta.HIRADBATCHTIMESTAMPSHPD,
nbjdta.HIRADODITIMESTAMPSHPD from
SYSIBM.SYSDUMMY1
All the SQL variables come back null
Which verifies the error - but it NOT what I expected
If anyone can point out what I am doing wrong - or explain why this wont
work - I would be VERY grateful

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com