Dynamically setting PDQPRIORITY and OPTCOMPIND for a session within a Procedure

Hi, we are trying to dynamically setting the PDQPRIORITY and OPTCOMPIND values from a stored procedure (SP) and it works but the values do not persist after the SP finishes. We would need the session to maintain those PDQ/OPT values out of the scope of the SP, and after its execution. We are using Cognos and the SP is executed from a Cognos Session Macro within the 'Data Source Connection'. The macro is able to pass the internal cognos user to the SP, but is not able to evaluate conditions. So need the SP to do the evaluation. The internal cognos user is something Informix is not aware off and all cognos connections identify as user 'cognos' to informix, An example of SP for testing: drop table if exists cognos_pdq_debug; create table cognos_pdq_debug ( sid int, fechahora int, acc_pi_username varchar(50), pdq_set char(2) ); drop PROCEDURE if exists sp_set_pdq_debug; CREATE PROCEDURE sp_set_pdq_debug (acc_pi_username varchar(50))if acc_pi_username='SrvCognos' then SET PDQPRIORITY 40; SET ENVIRONMENT OPTCOMPIND '0'; SET ENVIRONMENT IMPLICIT_PDQ '40'; insert into cognos_pdq_debug values (DBINFO('sessionid'), DBINFO('utc_current'), acc_pi_username, '40');ELSE insert into cognos_pdq_debug values (DBINFO('sessionid'), DBINFO('utc_current'), acc_pi_username, '0');end if; system "sleep 60"; end procedure; Our system runs with default PDQ=0 and OPT=2 , and need to set them to PDQ=40 and OPT=0 when acc_pi_username='SrvCognos'. Can you think of a way to achieve what we are trying to do ? Thanks for any help ! Josem ------------------------------ Jose ------------------------------

Jose: Rename the procedure to sysdbopen() and create it owned by the actual user name that the cognos sessions use if that's only a single user name or owned by user informix if everyone needs the same privileges. The sysdbopen() routine is executed when a user connects to the server. If the specific user owns a sysdbopen() routine that is the owe executed, if not then if there is one owned by informix then that one is executed. Note that when a session disconnects, if there is a sysdbclose() routine that is executed as well (same rules). So, here is a simple one that works when I, as user "art" connect. CREATE PROCEDURE "art".sysdbopen (); SET PDQPRIORITY 40; SET ENVIRONMENT OPTCOMPIND '0'; SET ENVIRONMENT IMPLICIT_PDQ '40';end procedure; Here's a link to the documentation on sysdbopen: Using SYSDBOPEN and SYSDBCLOSE Procedures I see that you have logic making the settings specific to a value passed into the routine. Unfortunately the sysdbopen routine takes no arguments. You can, however, perform some SQL including querying details about the session's environment from the sysmaster database tables (ex: sysmaster:sysenvses) or local tables in the connected database to control what sessions get these settings and which do not. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thank you for the prompt reply Art, This would not work since the user visible to informix is always 'cognos', the session is opened by 'cognos' but different cognos internal users execute different stuff. Like the building of cognos cubes that get executed by internal cognos user 'SrvCognos'. This user is not visible to Informix but can be passed as a parameter from the Cognos Macro configured in the Datasource Cognos Connection. The problem is that even if we can insert this value ('SrvCognos') into a table and compare session_ID, the insertion will happen always after sysdbopen() gets executed completely so we will not be able to control the cognos internal user as desired. ------------------------------ Jose ------------------------------