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

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.