4286Re: PeopleSoft DBA Forum enabledbmonitoring question
- Aug 16, 2010Thanks for the responses... Yes you understood my question perfectly. In SQL Server you can basically create a "classifier function" that executes whenever a connection occurs. And you can use anything that sql knows about at the time of connection to classify it. Ie. username, servername, memberof(usersname), etc... Basically making one large if or case statement and then applying it to the correct resource pool.
The little I do know about PS I did think of trying what you said... it just sounds completely nasty and probably unreasonable to maintain. Thanks for your suggestions again...
--- In firstname.lastname@example.org, "David Kurtz" <david.kurtz@...> wrote:
> I believe that SET CONTEXT INFO is the equivalent of the call to
> DBMS_APPLICATION_INFO that PeopleSoft does on Oracle. This happens at the
> top of each service call, and allows you to see which PeopleSoft Operator is
> executing some code.
> I do not know of any way to inject the additional call.
> If I understand you correctly, you are trying to manage the amount of
> resource that a query can consume. On Oracle you can only do this for an
> specific user. By default, All PeopleSoft processes connect as the same
> Oracle user. You need to find a way to make the PSQRYSRV processes connect
> to a different user.
> This is not easy on PeopleSoft on Oracle either. You have to create an
> additional access profiles so that when the QRYSRV connects as a different
> user it uses the alternative access profile, and lots of grants and synonyms
> to the new database user account. The connect information is held in
> psappsrv.cfg, and you need to provide an alternative config file for the
> psqrysrv processes. That requires some editing of the Tuxedo config
> template file psappsrv.ubx. I have tried this on PT8.48, and I didn't get a
> satisfactory solution. The query servers still used the original config
> file. Even if you can get it working, you end up with something that is
> dificult to maintain.
> You can set query timeouts so that the query terminates after a while, but
> (on Oracle) this doesn't work well with queries that have larges sorts or
> hash operations (caused by use of DISTINCT, UNION or ORDER BY) where the
> database takes a long time to fetch the first row in the result set.
> Best you can do is discourage users from running long running queries
> on-line, restrict maximum number of PSQRYSRV process, and encourage users to
> schedule queries on the process scheduler.
> David Kurtz
> Go-Faster Consultancy Ltd.
> tel: +44 (0)7771 760660
> fax: +44 (0)7092 348865
> web: www.go-faster.co.uk
> Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
> DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
> >-----Original Message-----
> >From: email@example.com [mailto:firstname.lastname@example.org]
> >On Behalf Of m19peters
> >Sent: Wednesday, August 11, 2010 10:07 PM
> >To: email@example.com
> >Subject: PeopleSoft DBA Forum enabledbmonitoring question
> >I'm unfortunate enough to have to work with PeopleSoft on SQL
> >Server. I'm trying to figure out a way to use Resource
> >Governor. For those that don't know what that is... it's a way
> >to assign a connection within sql server to a "resource pool".
> > This "pool" might only be allowed 10% of the cpu... or
> >memory.. or whatever. It's a way to "govern" your resources
> >on your sql box.
> >I'd like to govern on if its coming from PS query for
> >instance... In Oracle this would be very easy. As the program
> >name is set in your client_info as well as your program column
> >in v$session (you see psqrysrv.exe), along with the ps user
> >id, server, etc. Well in sql server our equivilents are
> >context_info and program_name. But for context_info all I see
> >is <ps user or ouid>, <os user>, <app server>, and sometimes
> ><domain>... and program_name just says "PeopleSoft"...
> >Does anyone know how this context info or app_name are set.
> >Is there some configuration parameter that needs set or some
> >code that needs changed or whatever... that would have sql
> >server display the program name much like oracle does.
> >When I trace the db I see "SET CONTEXT INFO <some binary>"
> >displayed before each transaction. I just need some way to
> >jump in before that and write "psqrysrv.exe" on the end of it
> >if its coming from ps query.
> >Sorry for the sql server question... but your the only group I
> >could find for peoplesoft dba's.
> >Also, just knowing what you know about peoplesoft... is there
> >some other way you would think of doing this. I'm new to
> >peoplesoft... I use to primarily work with SAP.
> >PeopleSoft for the Oracle DBA is published by Apress - see
> >http://www.psftdba.com The PeopleSoft DBA Forum is managed by
> >http://www.go-faster.co.uk Yahoo! Groups Links
- << Previous post in topic