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

It would be nice to have a tool that could find table/view/file/program/procedure references from any source in any language that is supported on IBM i, in traditional sources files or in the IFS, so I could have one place to ask things like "What apps/programs/objects/modules reference table/file EMPLOYEE?' "Or what apps/programs/objects/modules use column/field LAST_NAME in EMPLOYEE?" or "What apps/programs/objects/modules make a call for procedure getEmpMaster?" ABSTRACT was great when it was all RPG/DDS and all "F" spec file access and all in traditional source files, but anymore we find ourselves using ABSTRACT then doing RPG source scans and they doing IFS java source scans and asking programmers if they think anything is missing from our impact report, and hope we get 90% of objects identified.

Mmike Cunningham

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Monday, June 13, 2016 1:50 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Files used by SQL in RPGLE

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.

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