Archives of the TeradataForum

Message Posted: Wed, 20 Mar 2002 @ 21:42:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, March 20, 2002 11:26 -->

The NCR CRM application (formerly CERES) uses generic userids to sign on to Teradata via a mid-tier taskbroker. The generic userids (CRM1, CRM2 etc) use sessions that they leave open for days at a time. In this way many SQL requests are executed by a generic CRM userid via a long-established session.

CRM users have there own username and are assigned CRM-specific session identifiers via the CRM interface when they initiate work requests.

We end up with the following scenario:

USER : Fred
CRM session : 123
CRM task description : 'current month big spenders'
Generic Teradata CRM userid : CRM1
Teradata session : 999999
SQL : 'NICE SQL...'

USER : Bill
CRM session : 456
CRM task description : 'previous quarter poor spenders'
Generic Teradata CRM userid : CRM2
Teradata session : 1000000
SQL : 'NOT-SO-NICE SQL...'

Bill has no idea that the work request he has initiated results in a piece of SQL that produces a resource-hungry job. We can see from the delta cpu/io figures in PMON/Session Information that one of the CRM users has initiated such a job. However, there appears to be no way of tracing a piece of CRM SQL that is currently running under a generic userid, with a long-established session, back to a CRM user's task.

Are there any workarounds for this?

Also, the way in which sessions are left open means that cpu/io usage reporting via dbc.acctg is devalued. Although we have session level cpu/io reporting via ASE, the CRM userids execute thousands of SQL requests within each session. The cpu/io usage is recorded against the session so we can't see the cpu/io used by an individual piece of SQL.

	<Prev	Next>		<<First	<Prev	Next>	Last>>