SqlDbx Forum

SqlDbx

You are not logged in.

#1 2016-04-14 15:06:52

mrschwarz
Member

Editing stored procedures

When I right click on a stored procedure and select 'Edit Procdure', I get most of the procedure, but I am missing a lot of header information. For example, here is the header information from one I completed recently:

CREATE PROCEDURE SQLLIB.HORIZON ( )
    LANGUAGE SQL
    SPECIFIC SQLLIB.HORIZON
    NOT DETERMINISTIC
    MODIFIES SQL DATA
--    CALLED ON NULL INPUT --
--    SET OPTION  ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX  --
    BEGIN

After I compile it and open it for editing in sqlDBX, here is what I get for the same procedure:

DROP PROCEDURE SQLLIB.HORIZON ();

CREATE PROCEDURE SQLLIB.HORIZON ()
    LANGUAGE SQL
    SPECIFIC SQLLIB.HORIZON
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NO DBINFO
BEGIN

I have highlight the missing statements. Without them, the procedure doesn't work. What can do to get all the header statements?

Also, the CREATE OR REPLACE statement is better the the DROP and CREATE. If there is an error in the compile, the former leaves the existing one intact. The latter deletes it before producing an error. Is there a way to substitute the first one?

Last edited by mrschwarz (2016-04-14 15:10:16)

Offline

#2 2016-04-15 08:17:04

sqldbxhelp
Administrator

Re: Editing stored procedures

What version of DB2 you use?
To change how scripting works for CREATE go to Tools->Options->Scripting/Log and check "Use ALTER/REPLACE if possible"

Offline

#3 2017-01-18 13:07:04

mrschwarz
Member

Re: Editing stored procedures

Sorry for taking so long to respond to this. My settings are already how you describe. The real issue is editing existing stored procedures. I have to generate the SQL in IBM System I Navigator and copy the header into SQLdbx.

Offline

Board footer

Powered by FluxBB