Re: Dropping SQL Procedures in iNav

On 10-Sep-2015 08:32 -0600, Brian Piotrowski wrote:

We seem to have some quirky behavior with our DROP PROCEDURE function
in "Run SQL Scripts" within iNAV. What we're trying to do is delete
a procedure and then recreate it. The way we have tried to do it is
to right-click the procedure in iNav, select "Generate SQL", edit the
procedure and execute it to recreate it. When we use "DROP PROCEDURE
xxx" it does not drop the procedure and complains the procedure
already exists.

An "already exists" message seems problematic for a DROP; seems more likely that the DROP was not performed, and the error was issued instead for the CREATE. What message specifically; and what is the full messaging for processing the script, as obtained from the spooled joblog of the job serving the request?

Alternatively, issue the DROP PROCDURE using the Start Interactive SQL Session (STRSQL) [preferably with debug active] and collect that joblog if that shows the same issue.

When we use "DROP SPECIFIC PROCEDURE xxx" it will drop the procedure

If the name was overloaded and the non-specific name was not unique, and the name used in aforementioned failing DROP PROCEDURE, the expected outcome is the msg SQL0476 "Routine &1 in &2 not unique."

and go through the motions of recreating it,

implying, apparently, that the script continues with the next statement, being the CREATE PROCEDURE

but when you refresh the procedure list it does not exist and you
need to rerun the SQL script (minus the DROP command) to get it
recreated.

Perhaps the data in SYSROUTINE is correct, but the listing feature is incorrect? The first database relation [DSPDBR QSYS2/SYSROUTINE] for that catalog TABLE should be the file QASQRESL with a keyed access path defined on columns (RTNNAME, RTNSCHEMA). That catalog TABLE should be journaled to the QSQJRN in library QSYS2, such that the I/O logging the DROP and CREATE activity should be visible [with Display Journal (DSPJRN)]

Are we entering the command wrong? Should we be entering something
else to drop the procedure before it is recreated?

DROP SPECIFIC ROUTINE or DROP SPECIFIC PROCEDURE if the name is not [or might not be, hopefully only according to planning,] unique in the schema.

We are putting this command just before the "CREATE PROCEDURE"
command in the SQL script, so logically we thought it should perform
the DROP and then execute the CREATE function.

If performed as a script; noting that the capability exists, to run just one or selected statement(s) within the editing\scripting area that is provided for the SQL statements.

Any thoughts would be appreciated.

Possibly in the failing case(s), running the one\selected statement instead of effecting run of the entire script could give rise to such difficulties? Or perhaps there is an error in the DROP processing, such that the request fails to locate the named\specific-named routine. I would try the scripted requests sequentially in the STRSQL environment [with debug active to see details about implementation for what SQL is performed against SYSROUTINES for each request] to see if there is any difference; and\or in the Run SQL Statements (RUNSQLSTM) feature.