Topic
  • 2 replies
  • Latest Post - ‏2012-10-16T22:19:40Z by SystemAdmin
SystemAdmin
SystemAdmin
1731 Posts

Pinned topic Replace a routine defined in a routine

‏2012-10-15T05:46:49Z |
Hi,

I am developing an application in DB2 with SQL PL, and the routines (procedures and functions) are being defined in a module.

However, each time I change a routine, I cannot replace it, and I have to drop the module body, and recreate it.

Is there any way to do a REPLACE for a routine inside a module?

Something like:

ALTER MODULE mod PUBLISH PROCEDURE p1 (); ALTER MODULE mod ADD PROCEDURE p1 () BEGIN ... END; ALTER MODULE mod REPLACE PROCEDURE p1 () -- This is the useful option. BEGIN ... END;
Updated on 2012-10-16T22:19:40Z at 2012-10-16T22:19:40Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Replace a routine defined in a routine

    ‏2012-10-16T12:10:51Z  
    Ok,

    This may be again due to the lack of accuracy in the documentation. As you say there is no REPLACE option for the ALTER MODULE, but you do not need to drop the whole module as you say. Just try to drop only the definition of your modified procedure, function, type, condition or variable in you module and create it again with either the ADD clause of the ALTER MODULE statement or the PUBLISH clause, depending whether will be used only from inside of the module or out side.

    
    CREATE TABLE modtable(c VARCHAR(100))@   CREATE OR REPLACE MODULE mod@   ALTER MODULE mod PUBLISH PROCEDURE p1() BEGIN INSERT INTO modtable VALUES(
    'INSERTING FROM mod.p1 NEWLY CREATED'); END@   CALL mod.p1@   ALTER MODULE mod DROP PROCEDURE p1@   ALTER MODULE mod PUBLISH PROCEDURE p1() BEGIN INSERT INTO modtable VALUES(
    'INSERTING FROM mod.p1 RECREATED'); END@   CALL mod.p1@
    
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Replace a routine defined in a routine

    ‏2012-10-16T22:19:40Z  
    Ok,

    This may be again due to the lack of accuracy in the documentation. As you say there is no REPLACE option for the ALTER MODULE, but you do not need to drop the whole module as you say. Just try to drop only the definition of your modified procedure, function, type, condition or variable in you module and create it again with either the ADD clause of the ALTER MODULE statement or the PUBLISH clause, depending whether will be used only from inside of the module or out side.

    <pre class="jive-pre"> CREATE TABLE modtable(c VARCHAR(100))@ CREATE OR REPLACE MODULE mod@ ALTER MODULE mod PUBLISH PROCEDURE p1() BEGIN INSERT INTO modtable VALUES( 'INSERTING FROM mod.p1 NEWLY CREATED'); END@ CALL mod.p1@ ALTER MODULE mod DROP PROCEDURE p1@ ALTER MODULE mod PUBLISH PROCEDURE p1() BEGIN INSERT INTO modtable VALUES( 'INSERTING FROM mod.p1 RECREATED'); END@ CALL mod.p1@ </pre>
    Thanks for both responses. They were very descriptive and useful.