Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Answered: change owner of stored procedure

    After an export/import (db2look + db2move) of a database the owner of the stored procedures has been change to the instance owner - which cause the 'REPLACE' command not to work.

    Code:
    select * from syscat.procedures where procname='ENABLE_DISABLE_FK_ALL';
    
     PROCSCHEMA 	PROCNAME              	SPECIFICNAME       	PROCEDURE_ID 	DEFINER  	PARM_COUNT 	PARM_SIGNATURE 	ORIGIN 	CREATE_TIME                	DETERMINISTIC 	FENCED 	NULLCALL 	LANGUAGE 	IMPLEMENTATION   	CLASS 	JAR_ID 	PARM_STYLE 	CONTAINS_SQL 	DBINFO 	PROGRAM_TYPE 	RESULT_SETS 	VALID 	TEXT_BODY_OFFSET 	TEXT                                                                                                 	REMARKS
     ---------- 	--------------------- 	------------------ 	------------ 	-------- 	---------- 	-------------- 	------ 	-------------------------- 	------------- 	------ 	-------- 	-------- 	---------------- 	----- 	------ 	---------- 	------------ 	------ 	------------ 	----------- 	----- 	---------------- 	---------------------------------------------------------------------------------------------------- 	-------
     CADM   	ENABLE_DISABLE_FK_ALL	SQL151130101140424	       66624	DB2INST1	         1	0038          	Q     	2015-11-30 10:11:40.433781	N            	      	Y       	SQL     	db2pvm!pvm_entry	NULL 	NULL  	          	M           	      	M           	          0	Y    	             124	CREATE PROCEDURE ENABLE_DISABLE_FK_ALL (p_action varchar(20))    DYNAMIC RESULT SETS 0    LANGUAG...	NULL
    I've created the ddl file by:
    Code:
    db2look -d $DB_NAME -e -o ${DB_NAME}_ddl.sql -l -x -f
    In that file it first connect to the DB with out credentials so it connects as IO and before run the create procedure it does:
    Code:
    SET CURRENT SCHEMA = "CADM ";
    So my question is how to change the procedure owner? and how can I avoid from that problem?

  2. Best Answer
    Posted by mark.b

    "TRANSFER OWNERSHIP OF SPECIFIC PROCEDURE CADM.SQL151130101140424 TO USER CADM

    Amend the file specifying a user needed in the CONNECT statement to avoid such problems."


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    TRANSFER OWNERSHIP OF SPECIFIC PROCEDURE CADM.SQL151130101140424 TO USER CADM

    Amend the file specifying a user needed in the CONNECT statement to avoid such problems.
    Last edited by mark.b; 12-01-15 at 06:03.
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •