Print
All Forums Extensibility
wmmiteff 20 posts Joined 02/11
27 Apr 2015
Issue with Stored Procedure

I am working on a stored procedure for dropping all sessions for a specific user and I have written a SP to do this.  the SP creates but it fails when I run it with an id.  I can get the generated SQL out of DBQL and it runs but the SP fails.
 

REPLACE PROCEDURE AbortSession_sp(IN dbcidname VARCHAR(128))
  SQL SECURITY CREATOR
  BEGIN   
    DECLARE AbortStmt VARCHAR(1000);

    SET AbortStmt = 'SELECT count(syslib.ABORTSESSIONS(-1,ms.username,0,''Y'',''Y' ')) cnt
FROM TABLE (syslib.MONITORSESSION(-1,''*'',0)) AS ms WHERE USERNAME=''' || dbcidname || ''';' ;
-- DBC.SYSEXECSQL(AbortStmt);

    --PREPARE AbortSQL FROM AbortStmt;
    EXECUTE IMMEDIATE AbortStmt; --USING dbcidname;*/
  end;

 

Tags:
wmmiteff 20 posts Joined 02/11
30 Apr 2015

Does anyone have suggestions on how to get this stored procedure to work?

Fred 1096 posts Joined 08/04
30 Apr 2015

You can't use EXECUTE  / SysExecSQL for a SELECT. Use a dynamic cursor.
 

dnoeth 4628 posts Joined 11/04
30 Apr 2015

If you want to abort unconditionally you don't need dynamic SQL.
ABORTSESSIONS returns a single row with the count of aborted sessions, so simply return this into a variable:

    SELECT syslib.ABORTSESSIONS(-1,'bla',0,'Y','Y') INTO CNT
    FROM TABLE (syslib.MONITORSESSION(-1,'*',0)) AS ms WHERE USERNAME= :dbcidname;

 

Dieter

wmmiteff 20 posts Joined 02/11
12 May 2015
REPLACE PROCEDURE dba_tables.AbortSession_sp(IN dbcidname VARCHAR(128))
  SQL SECURITY CREATOR
  BEGIN   
    DECLARE AbortStmt VARCHAR(1000);

    SET AbortStmt = 'SELECT count(syslib.ABORTSESSIONS(-1,''' || dbcidname || ''',0,''Y'',''Y''));';
CALL dbc.sysexecsql(AbortStmt);
  end;

i revised the procedure, and get a 5568 saying the SQL is not supported in a SP.
when i look at what was ran in DBQL i can see that "SELECT count(syslib.ABORTSESSIONS(-1,'user1',0,'Y','Y'));" was ran and failed.
 
 
 
 

Fred 1096 posts Joined 08/04
13 May 2015

You can't execute a statement that returns data dynamically via EXECUTE or SysExecSQL. You would need to use a dynamic cursor. Or don't use dynamic SQL at all. For example
SELECT SYSLIB.ABORTSESSIONS(-1,:dbcidname,0,'Y','Y') INTO CNT;

wmmiteff 20 posts Joined 02/11
14 May 2015

That worked great, thank you so much.

You must sign in to leave a comment.