Re: Storing SQL script options

Another gen, IMO, is that we can use the ACS acsbundle.jar to process SELECT statements (or data transfers) - just saw this in a webcast on the COMMON site on ACS and Db2, by Scott Forstie and Jesse Gorzinski - I don't know that much about it yet, it's a command-line option using one of the plugins for ACS - easily called using QShell.

Later
Vern

On 3/27/2019 8:50 AM, Jim Oberholtzer wrote:

Don’t' forget that you can always call the 'db2' interface from CL as well.

Select into a temporary file anywhere, then process that file as you wish:

CHGVAR &Command ('db2 "' *cat &Command *tcat '"')
QSH cmd(&Command)

Clearly the SQL goes into the variable &Command

There's a small bit of set up, mainly to push the spool files to a null device etc. but it's easy to do, been around for quite some time, and costs nothing. I use it on V7R1 and earlier machines where the other tools do not exist.

In my view, rather than spend $0.10 on updating RUNSQLSTM I'd much rather IBM put an SQL precompiler into CLLE, (AKA like every other language has) so there is a CRTSQLCLE command.


--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steinmetz, Paul via MIDRANGE-L
Sent: Wednesday, March 27, 2019 8:31 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Steinmetz, Paul <PSteinmetz@xxxxxxxxxx>
Subject: RE: Storing SQL script options

We're using a version of RUNSQL, supplied by one of our 3rd party vendors, that does do SELECTS, and allows the 3 output options by changing your session defaults.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Wednesday, March 27, 2019 9:21 AM
To: Midrange Systems Technical Discussion
Subject: Re: Storing SQL script options

As a bit more info - the IBM RUNSQL also does not do SELECT statements.

Vern

On 3/27/2019 8:13 AM, Vernon Hamberg wrote:

Paul

There is no option for output for SELECT statements, because SELECT
statements are not allowed in RUNSQLSTM. I've asked innumerable times
for this to be added, but IBM are not going to do it. I really
disagree with this but see no change coming.

If you want to run a SELECT with output to printer or display, get one
of the commands we in the wild have written that are using Query
Management - they usually end up using STRQMQRY, which has an OUTPUT
parameter.

I have one, and I'm giving a presentation at COMMON this year on using
Query Management, as well as a lab. I think Buck Calabro has one, I
kind of recall that I based mine on his some many years ago. These
often use a *QMQRY object that has a "statement" that is a single
substitution variable for the entire statement.

Google for RUNSQL (not the new IBM command) or EXCSQL, you should find
something.

Cheers
Vern

On 3/27/2019 7:43 AM, Steinmetz, Paul via MIDRANGE-L wrote:

Thanks Rob and Paul.
Great article/link.

Question
How do specify/change the output options when running RUNSQLSTM
SRCSTMF('/...')?

I'm not seeing the output options
SELECT output . . . . . . . . 1 1=Display,
2=Printer 3=File

Paul


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Rob Berendt
Sent: Tuesday, March 26, 2019 12:02 PM
To: Midrange Systems Technical Discussion
Subject: RE: Storing SQL script options

RUNSQLSTM SRCSTMF('/...')

But I also suggest reading this article:
http://ibmsystemsmag.com/ibmi/administrator/systemsmanagement/create-
your-own-templates/



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Steinmetz, Paul via MIDRANGE-L
Sent: Tuesday, March 26, 2019 11:54 AM
To: 'Midrange Systems Technical Discussion'
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Steinmetz, Paul <PSteinmetz@xxxxxxxxxx>
Subject: Storing SQL script options

I've just recently started using the ACS "Run SQL Scripts".
I'm looking at the various options available for storing SQL
statements, some for IBM i catalog tables and views.
My initial option was to create a source member in our source library
for each SQL script.
With the 10 character limit, I'm finding it difficult to have meaning
names for these scripts.

What methods are others in the group using to store SQL scripts?

Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx
http://www.pencor.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@xxxxxxxxxxxx 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@xxxxxxxxxxxx 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@xxxxxxxxxxxx for any subscription related questions.

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