30 Apr 2015
Does anyone have suggestions on how to get this stored procedure to work?
30 Apr 2015
You can't use EXECUTE / SysExecSQL for a SELECT. Use a dynamic cursor.
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
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.
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.