Page 1 of 1

ABSTRACT and SQLRGPLE

Posted: Mon Sep 27, 2021 9:08 pm
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!

Re: ABSTRACT and SQLRGPLE

Posted: Mon Sep 27, 2021 9:34 pm
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...?

Re: ABSTRACT and SQLRGPLE

Posted: Tue Sep 28, 2021 2:24 am
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