Archives of the TeradataForum

Message Posted: Mon, 11 Sep 2000 @ 20:35:50 GMT

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

The easiest thing would be, if possible:

1. Create all the new ids on the new system, using same script that created them on the old system. This assumes large quantities of new userids were spawned at once using some automated or batch process.

2. If you have their accessrights granted at the database level [eg. everyone under a DB has the same rights on all the tables under the database that contains the appl tables], then users would implicitly, automatically have the accessrights needed, as soon as created on the new system. In other words, all users under databaseA should have the same rights on all tables under databaseB, and to all ViewMacro rights under databaseC.

If these ids were created piecemeal over time, yes you have a headache and a huge manual effort. And if all those users have rights granted at the TVM level rather than at the objects' database level, then you have even more of a nightmare, and a manual effort.

If users' rights are uniquely granted to varied TVM, then you may also have a humongous dbc.accessrights table, which can affect performance through synonym chains, and overhead cpubusy by hidden tasks. That worker task is SYSTEMUSERID.

You can query dbc.ampusage via view dbc.acctg to see if SYSTEMUSERID is sucking up cpu cycles to manage dbc.accessrights due to granting at too level of a granularity.

Here are two queries:

This answer set will have one row for every amp:

      SEL VPROC, VPROCTYPE,
       CPU, IO
       FROM DBC.ACCTG
       WHERE USERNAME = 'SYSTEMUSERID'
        ORDER BY 3 DESC;

Think before you run this next one.

This next one could be huge since you are asking for how much usage is done by EVERY user on EACH amp, since the records were last deleted:

      SEL VPROC, VPROCTYPE,
       CPUTIME, DISKIO, USERNAME
       FROM DBC.AMPUSAGE
       ORDER BY 3 DESC;

The number under cputime accumulates until the value is deleted. We delete ours every 24 hours after capturing to a history file.

That's also a good script to find amps with skewed data , since an id spends far too long daily on one [out of many] amps.

I realize that you did not ask about accessrights management, but granular rights granting has repercussions, by increasing the size of that table enormously, and SYSTEMUSERID must work harder to manage it all. Id creation should take this into account.

That is all I can think of off-hand.

Keith / teradude

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