Archives of the TeradataForum

Message Posted: Wed, 28 May 2003 @ 17:41:51 GMT

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

<-- Anonymously Posted: Wednesday, May 28, 2003 11:29 -->

We share Philippe's pain regarding visibility of locks.

We are still trying to discover what tables are locked by loads, despite training and tens of thousands of dollars in professional services. Maybe someone has updated a row via a secondary index and hasn't committed the update. That would lock the whole table, and it isn't due to the load.

We can use the database window or cnsterm to run the showlocks utility, to show locks held by arcmain. pmon will show sessions that are blocked by other locks, but I don't recall for sure whether there's a way to show locks held, but not blocking another user. locking logger is a facility that records lock conflicts historically, but it addresses dml locks only and not the condition created by a utility load that prevents access to the table.

Those facilities that do expose locks of any kind, aside from pmon, do it in a way that makes it difficult to see what's going on. query session, handily spelled qrysessn, created thousands of lines of output scrolling across the screen. Qrysessn shows locks, but it was hard to find them in with all the gratuitous punctuation and white space.

We think we can get to DML locks in the PM/API, but it's a difficult interface.

We don't know of a way to determine the status of load jobs. They can be

- in process. During the day that's a good thing.

- interrupted intentionally to load multiple files into phase 1 -- not something we'll be doing with our ETL tool.

- failed, restart able, but how to know whether we want to restart them, finish them, drop/create the table?

If we see a row in sysadmin.loadlog it can mean a fastload is running, which is okay, except late at night when the backup is trying to run. But what is an appropriate action? It could be a failed job we want to restart to complete phase 2 after adding space. Or it could be a job we want to finish and start over, or it could be left there from some time ago when someone who didn't know how to finish the job gracefully dropped and recreated the table.

We don't know at all how to discover the status of a multiload job. The only suggestion we've heard is to scan for tables that look like error tables and work tables and figure out the table is inaccessible. Maybe an explain against a table that's locked will return an error?

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