Re: Files used by SQL in RPGLE -- RPG400-L

Hi Jeff

I know you say you don't want to search all sources. I understand that - except that the iSphere search is SOOOOO much faster, that it made this feasible for me.

I put together a filter in RDi that included all the libraries that have source. Then a right-click in RSE and taking the iSphere source file search option does it nicely - very fast. And you can export the results to Excel, at least.

Now another option might be one of the cross-reference products - they will catch a lot of stuff, and might miss things like dynamic SQL. But they all have a way to manually or programmatically add things to their repository. We have ABSTRACT - we've not used the commands for adding things, but they are there. And there's Hawkey Pathfinder and there are a couple others that don't come to mind immediately.

HTH
Vern

On 6/13/2016 11:40 AM, Jeff Young wrote:

Booth,
I am using RDi, and have been using it and its predecessors since it first
came out.
I think it is the best thing since SEU was first introduced on the AS/400
release 2 (before that they had EDTSRC which was a PITA) :).
What I need to be able to do is find all programs using a file without
having to search all possible source. Normally, with RLA and Static SQL,
this is available via DSPPGMREF. With Dynamic SQL, this information is not
available.
The question for the list was basically what would be the best way to get
this information.
The 2 main solutions that I came up with were adding dummy "F" specs with
USROPN or defining external DS referencing the file name.

Jeff Young
Sr. Programmer Analyst

On Mon, Jun 13, 2016 at 8:42 AM, Duane Scott <dscott@xxxxxxxxxxx> wrote:

So, in programs that do a dynamic SQL, would it be of value to place a
simple "dummy" (but well documented) static statement that simply selects
all records of the file(s) that would be dynamically used elsewhere later,
just to get the PRTSQLINF to recognize that the file is used in the program.

Kind of hokey, but a trick used often in "do while 1 = 2" code to perform
similar functions... which elude me right now for examples.

I wouldn't typically suggest excess code, but I understand Jeff's dilemma.
It's similar to trying to find all the QRY400s that use files or fields.

Of course the alternate would be a well-documented change management
system.

Duane

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
MichaelQuigley@xxxxxxxxxx
Sent: Friday, June 10, 2016 3:45 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Files used by SQL in RPGLE

"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 06/10/2016 01:00:05
PM:

----- Message from MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx> on Fri,
10 Jun 2016 16:54:04 +0000 (UTC) -----

To:

"RPG programming on the IBM i (AS/400 and iSeries)"

<rpg400-l@xxxxxxxxxxxx>

Subject:

Re: Files used by SQL in RPGLE

Hi John,
Try the PRTSQLINF command. See if that gives you what you need.
R,Mark.

PRTSQLINF will give you the files used for static SQL. If you're using
dynamic SQL all it will give you is the fact that you're preparing a
statement from a host variable. Someone will have to analyze what can be in
the host variable at the time of the prepare.

This is another gotcha in addition to the performance implications of
dynamic SQL.

Michael Quigley
Computer Services
The Way International
www.TheWay.org
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

________________________________
________________________________
CONFIDENTIALITY NOTICE: This electronic message transmission is intended
only for the person or entity to which it is addressed and may contain
information that is privileged, confidential or otherwise protected from
disclosure. If you have received this transmission, but are not the
intended recipient, you are hereby notified that any disclosure, copying,
distribution or use of the contents of this information is strictly
prohibited. If you have received this e-mail in error, please contact NALC
Health Benefit Plan at 703-729-4677 and delete and destroy the original
message and all copies.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.