Stored Procedure assigned to CONNECT_PROC

chiun keat wan

Stored Procedure assigned to CONNECT_PROC

Dear All,

   I found something work differently in db2 database. I have a stored procedure to count distinct session_id and workstation_name, if more than 1 means, the user has login more than one workstation_name.

Furthermore, I encounter if one user in one workstation open more than 1 session, it will pops up the message box. 

Following is the stored procedure for easy reference.

CREATE OR REPLACE PROCEDURE audit.trace_connect()

BEGIN

    DECLARE vcount integer;

select count(1) into vcount from (

   select distinct session_auth_id,client_wrkstnname

FROM table(mon_get_connection(NULL,NULL,0))

WHERE session_auth_id not in ('db2inst1','dsadm')

and session_auth_id = session_user

)

;

if (vcount > 1)

then

    SIGNAL SQLSTATE '42502' SET MESSAGE_TEXT = 'Connection refused. More than 1 workstation not allowed!';

end if;

@

Thanks.