ABSTRACT and SQLRGPLE

Any IBM i topic that does not fit in another forum
Post Reply
User avatar
Ticonderogah
Posts: 3
Joined: Wed Jul 28, 2021 12:05 pm

ABSTRACT and SQLRGPLE

Post by Ticonderogah »

We use the ABSTRACT command Work with File Usage (WRKFILU) quite often. We've noticed that any file that's used within an embedded SQL statement will not be retrieved unless the file is declared in the F-spec.

Obviously, defining these files in the f-spec in every SQLRPGLE program would solve the problem but it also makes things cumbersome for the developer moving forward. If the file needs to be declared with *update or *output usage we like to include the usropn keyword to the definition and perform a quick open/close in the program as well. This way the file isn't open with update access the entirety the program's uptime. Is this going to far?

Is there any other way to get file usage statistics from embedded SQL statements without going through all this?

Thanks!
Scott Klement
Site Admin
Posts: 636
Joined: Sun Jul 04, 2021 5:12 am

Re: ABSTRACT and SQLRGPLE

Post by Scott Klement »

I'm not familiar with ABSTRACT, but iirc, this is commercial software from HelpSystems. The first thing I would do is contact their tech support and ask them what they recommend.

Maybe others here have suggestions, too...?
vhamberg
Posts: 12
Joined: Thu Jul 29, 2021 1:33 am

Re: ABSTRACT and SQLRGPLE

Post by vhamberg »

We use ABSTRACT. It is a cross-reference tool from Help Systems. If it uses DSPPGMREF as part of the process, it should be getting embedded SQL information, at least if you are using things like DECLARE CURSOR or UPDATE directly in EXEC SQL constructs. I have a program where the only file declaration is for QPRINT.

I have several EXEC SQL statements - and the usage shows up in DSPPGMREF - not sure when that first appeared, but the usage, whether input, update, or output, is there now - this on a 7.4 box.

Now I don't have any PREPARE statements - I suspect that, just as with other cross-reference actions, the tool can't say anything about file names that are or are inside text variables. But you should contact Help Systems support and see what they say.

Good luck!
Vern
Post Reply