× 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. March 2019

Re: [EXT] Re: Storing SQL script options

Hi Eric - long time since visiting!

Yes, we can do nested SELECTs - as you point out, we can't do SELECTs as the main statement - that's what my comments were meant to say, and I remember many of us being unhappy that IBM's RUNSQL did not do what is really useful to many of us - be able to do quick reports with this kind of command.

And yes, the only thing you are restricted to is that commands can't be "embedded-only" stuff, IIRC. Something like that.

Cheers
Vern

On 3/27/2019 10:56 AM, DeLong, Eric wrote:
Not exactly true.

You can use SELECT within create table, insert, etc. I do this all the time to build a subset of history into qtemp, or to stage some data from a remote (cache remote data to my job qtemp). It’s never seemed like anything was missing from the RUNSQL* commands.

There’s just no support to produce a report from an sql statement. That’s what a query reporting tool is for.

-Eric DeLong

On Mar 27, 2019, at 8:22 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

CAUTION: EXTERNAL EMAIL

**********************************************************************
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:
https://urldefense.proofpoint.com/v2/url?u=http-3A__ibmsystemsmag.com_ibmi_administrator_systemsmanagement_create-2Dyour-2Down-2Dtemplates_&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=0sb01Qbs0o6Ghx2zcwKk-uFFfz9GVyve00IrJCACx7Y&e=


-----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
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.pencor.com_&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=TcY20BQ2NDiVeyW5DuQWgLLk_CwKezr0uUCNl6bwi4w&e=

--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=77sjXOZT2fnlMMMmqeiMKeiZr31MWfFtjChBmg9QaxY&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=Wwou4abHCYAt9uY5cjnEI8csXDupHZvx_MP_y_Efeis&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=wVpBwHzaS-Wx2FvqH-gAqMzm2FVV150HJR58D18kqKA&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=77sjXOZT2fnlMMMmqeiMKeiZr31MWfFtjChBmg9QaxY&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=Wwou4abHCYAt9uY5cjnEI8csXDupHZvx_MP_y_Efeis&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwIGaQ&c=kUT9u1ftLjMzSSPm_-knDdNvxOshAnRL8kekFKxW3Wg&r=9Ntyfp-OuZH8yZaiXsb9HlYdW65WTi1eFGJS7ZM5Mok&m=mX4FYa4t_CPF14uT7INpJVc_9BmC3ngN8WVlOzZbCsY&s=wVpBwHzaS-Wx2FvqH-gAqMzm2FVV150HJR58D18kqKA&e=


As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.