× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. January 2021

Re: Problem with SQL variables

I'm thinking that session may not the whole job...

Looks like RUNSQLSTM may be OPM has is RUNSQL...unless they are calling
something ILE behind the scenes.

That should rule out activation groups.

But perhaps RUNSQLSTM starts/ends a session as does RUNSQL.

idk..never really made much use of global variables...

Charles

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

Thanks for ALL your replies Rob
Im learning a huge $#!+ load at the moment
In STRSQL I ran
values nbjdta.HIRADINTERFACENAMEOPENAR
by itself
It displayed the variable - but had null value
Is that because I am using STRSQL?
It looks like I have to change my insert to what you suggested earlier in
this e-mail to the following, but using the correct column names
Boy - the times when I appear dense, thick, moronic seem to be hitting me
more often nowadays


0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP ( +
0101.00 col1, col2, col3, col4, col5, col6, col6
0102.00 values (
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 +
0108.00 )') COMMIT(*NONE)


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


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Rob Berendt
Sent: Thursday, January 7, 2021 4:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

I suspect we were both thinking they were more like a *DTAARA. They
obviously are not.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Thursday, January 7, 2021 4:14 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Problem with SQL variables


https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzvaluestmt.htm

Instead of using SELECT you use VALUES.
Often used for displaying system registers.
values current date
instead of
select current date from sysibm.sysdummy1

But my suggestion to display one with VALUES displayed my ignorance of sql
variables. I just discovered they are only a transitional value stored in
memory in a service program.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 4:08 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Thanks for your reply Rob
Oh - you meant SYSIBM.SYSDUMMY1
That's how I can display the contents of the SQL variables
I ran the SQL
select *
from qsys2.sysvariables
where variable_schema='NBJDTA'
This is nice (didn't know about it)
Then you say
Pick one and display the contents with:
Values rob. HIRADAS400FILENAMESHPD

Im not seeing how to do this - In STRSQL, where?

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


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Rob Berendt
Sent: Thursday, January 7, 2021 3:40 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

Read the bottom as to your issue but let me ramble first...

I didn't say the file on the insert is a dummy file I said get rid of the
dummy file as in replace
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)
With
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP ( +
0101.00 col1, col2, col3, col4, col5, col6, col6
0102.00 values (
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 +
0108.00 )') COMMIT(*NONE)

But, let's see if your variables exist:

select *
from qsys2.sysvariables
where variable_schema='ROB'
;

Pick one and display the contents with:
Values rob. HIRADAS400FILENAMESHPD

There's no reason (for this process) to use SYSDUMMY1. It brings nothing
to the party.

WHAT THE ISSUE IS:
<snip>
Session scope: Global variables have a session scope. This means that
although they are available to all sessions that are active on the
database, their value is private for each session.
</snip>

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzcvariable.htm
If I created that variable with Run SQL Scripts and then I go into STRSQL
and do Values rob. HIRADAS400FILENAMESHPD I get a null value.

So now you are probably wondering: What is the definition of a session
scope? A job? What?
Good luck with that exercise.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 3:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Hi Rob
Thanks for your reply
The file that these variables are being entered into is not a dummy file,
but a control file, which is to be used by other programs
However - looking at your code doesn't seem to be that much different from
mine
Except in mine - when attempting to insert the SQL variables - its failing
because the variable is null - when according to my understanding - it
shouldn't be


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

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Rob Berendt
Sent: Thursday, January 7, 2021 2:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

Forget using the dummy file and use the variables directly.
This is a working run sql scripts session:

CREATE or REPLACE VARIABLE rob.HIRADAS400FILENAMESHPD CHARACTER (10);
SET rob.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
values rob.HIRADAS400FILENAMESHPD;
create table rob.HRCTRLFP (
testcolOne char(10)
);
insert into rob.hrctrlfp (
testcolOne)
values (
rob.HIRADAS400FILENAMESHPD
);
select * from rob.hrctrlfp;



Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 2:35 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


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
--
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

--
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
--
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

--
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
--
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
--
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

--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.