Oracle ALTER SESSION in Cognos Data Manager

[tale103108]

How/where does one do a Oracle ALTER SESSION in Cognos Data Manager?

[MFGF]

You could try it in a jobstream SQL node, or a jobstream procedure node or build derivation or variable utilising the SQL() function.  What are you trying to achieve?

MF.

[tale103108]

What I am trying to achieve is to set cursor_sharing=similar for a particular jobstream (i.e. piece of SQL).  I can't do that in the dimension build as it only excepts (expects?) a SELECT statement.  But I want that SELECT statement to run with cursor_sharing=similar.  There are some queries we have that perform better with cursor_sharing=similar.  The instance is set -- and I believe Cognos FrameWork and Report Writer -- set this to cursor_sharing=exact for their sessions.  ::)

[MFGF]

In that case, your best bet is probably to try setting this using a variable on a jobstream.  In the variable initialization, use a SQL() function around your ALTER SESSION command, then make sure the same jobstream calls the relevant dimension/fact builds.

I have never tried this, but it seems like it stands the best chance of being successful.

MF.

[tale103108]

Also, I am not familiar with what you mentioned about "...or build derivation or variable utilising the SQL() function."  Where does one do this -- in the fact build?  How does one do this?
It seems to me that it has to be put in the fact build because if we put it in a jobstream it will just execute and then the subsequent fact build would start a new session, thus rendering the previous SQL session -- with the ALTER SESSION -- as meaningless.

[MFGF]

Hi,

Set up a jobstream which calls all the relevant builds in order.  Then go to the properties of the jobstream, onto the Variables tab, and add a new variable.  In the Initial Expression dialog, code an expression that uses a SQL function to call your ALTER SESSION command.

My thinking is that the variable will be evaluated in memory at the beginning of the jobstream, and initiate a database transaction in order to perform the ALTER SESSION command.  This will (hopefully) remain active until the end of the jobstream, and will thus be inherited by any builds called from within the jobstream.

Hope that makes things a little clearer.

Regards,

MF.

[vermilyeacognoise]

MFGF, Do you recall if tale103108 ever let you know if this approach worked?  I have the exact same challenge and had the exact same concerns that he voiced in his last reply, but it wasn't clear if he ever tried what you suggested and whether or not it was successful....

[vermilyeacognoise]

I decided to give this a try myself but found myself unable to issue the following statement from within the script editor due to the single quote required by the ALTER SESSION SET EVENTS syntax before the 10053 and after the level 1:

sql('ORACLE_HBI_SALES','alter SESSION SET EVENTS '10053 trace name context forever, level 1';');

(Yes, I tried using the 'double-single-tick' notation ('') to specifiy the single quote, but that produced an ORA-00911 invalid character from Oracle 11g.  I also tried \' no  no avail.)

Anyone have any ideas how to force Data Manager to pass the single quote to the database?

[MFGF]

How about:
sql('ORACLE_HBI_SALES', concat('alter SESSION SET EVENTS ', char(39), '10053 trace name context forever, level 1', char(39), ';'));

MF.

[vermilyeacognoise]

Clever idea - thanks - but that didn't work either; however, the good news is that my original 'double-tick' syntax was mostly correct - it just didn't like the semicolon before the last single quote.

Now that I've gotten around the syntactical challenge, I am running the following code from within a single procedure node and am curious to see if all three statements will in fact run within the same database session as intended.  Will post update on results later.

SQL('ORACLE_HBI_SALES', 'alter session set max_dump_file_size = unlimited');
SQL('ORACLE_HBI_SALES', 'ALTER SESSION SET EVENTS ''10053 trace name context forever, level 1''');
SQL('ORACLE_HBI_SALES', 'CALL SPROC_INSERT_FCDV()');

[MFGF]

Oh good! Glad you managed to get past the pesky single quote issue! Let us know what you find with regards to running the statements.

Good luck!!

MF.

[vermilyeacognoise]

Two steps forward, one step back.

All code within one procedure node is getting issued within a single session.  That's good.   Next problem: 

My DBA has asked me if I can run this additional statement before the job:
'exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);'

I thought this would be easy by simply adding this:
SQL('ORACLE_HBI_SALES', 'CALL DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE)');

But this produces a ORA-06576: not a valid function or procedure name.  What am I missing?

[MFGF]

Hi,

Sounds like an Oracle issue to me rather than being anything Data Manager is doing. I did a quick google and found this thread:

http://stackoverflow.com/questions/12040261/execute-recognizes-a-stored-procedure-call-does-not

I'm not an expert in Oracle, sorry. I don't know if this is your issue or not.

Cheers!

MF.