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