|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Wed, 20 Aug 2003 @ 16:23:36 GMT
-----Original Message----- Yes, I agree with you on the lock issue the way Teradata handles a "unit of work". I'm a little puzzled that it has enough sense to go 'idle' though, and still hold a lock and/or spool. In answer to your question, the user can abort a query while it is executing. (using the escape key) this does appear to release the lock. The issue is that Cognos has a unique feature that does not automatically retrieve all the data unless the user scrolls all the way to the bottom of a report. If the report is thousands of rows, ergo many pages in length, Cognos affords no way to obtain the bottom without scrolling one page at a time. Therefore, if a user becomes 'tired' of the report, or think they are through they can 'abandon' the report (even though they do not realize they are still in response mode on the Teradata side). Thus, they can 'close' the report, simply by clicking the normal X on the report window in the upper right-hand corner. When this happens, apparently the answer set is abandoned by Cognos and the user session, but not the lock. Go figure. I think there is an option, I have to look into it, that changes the result set to 'retrieve all', instead of a few rows at time, this might solve our problem, for now. Thanks for all your help. -----Original Message----- I think you've found it... You might ask the List, but my reaction is that you've probably identified the problem and what you're seeing is what I would have expected from the Teradata. Whether a select, an insert, update or delete, you need to think in terms of a unit-of-work. The locks are applied at the beginning of the unit-of-work and stay in-place until the unit-of-work has completed. In the case of a select, this means that the locks will stay in- place until all the data has been returned from the select. No doubt we could argue that the locks should be released earlier, but that's not the way the Teradata has been designed (and even if we could successfully make the argument, it would probably be years before anybody would see it implemented). And I don't believe that the locks and how Teradata deals with them is really the problem. It seems sloppy that Cognos submits a unit- of-work, starts to read the result and when the user losses interest in the answer set, Cognos simply abandons the answer-set until something else comes along. After all, in addition to keep the locks, it's also holding the final spool file. It would appear that Cognos needs to issue an abort once the user is no longer interested in retrieving data. Other than some kind of time-out, I don't know how anybody would programmatically determine that the answer-set had been abandoned without the user saying that they were done. Does Cognos allow the user to abort a report? Beyond educating your user, I'm not sure what you can do about your ETL jobs being blocked. I guess you could automate the process of forcing a log-off for your users when running your ETL jobs. Unfortunately, that's probably presents as many difficulties as educating the user. Oh well, congratulations on finding your problem John -----Original Message----- It appears from my testing with cognos impromptu that if a report has been executed but not all the data has been retrieved from Teradata (ie user in response mode) and the user closes the report (not having retrieved all the data), the, even though the session becomes idle, the access lock is not released. If a user IS NOT in response mode, in other words, all data has been passed to the hosts and the user goes idle, the lock is released normally. -----Original Message----- Thanks Mike. That leaves open the possibility that it could have something to do with BT/ET. Unfortunately, I don't know of anything on the machine that would let us know if a transaction was pending or where that transaction began. Michael, how narrowly can you track-down the problem? I know nothing about Cognos and so anything I could suggest would simply be a shot in the dark. That said, have you tried an ODBC trace? I realize that this could mean a giant log file, but I don't think that you're going to get very far without actually seeing what is going on between the workstation and the Teradata. BTW: I sent my note off-list so that Mike could see Michael's attachment. At this point, we should probably continue this thread on-list again (you never know when somebody is going to pop-up and say "What's the problem? Everybody knows about that ..."). John -----Original Message----- BT/ET will not show uop in the access logs since the log records are written as part of the access right checking ... and no rights are required to use BT/ET. (If he were really using Query Logging, in V2R5, I'm less sure about that, but I dont think they will be logged.) Mike. -----Original Message----- < Hi Mike, I hope you don't mind that I took this off-List, but I wanted to share the document that Michael sent. DBC.SESSIONS.OPTIONS does indicate that the session is in Teradata Mode. Go figure - I really expected it to be in ANSI mode. Going back to something that was said earlier in the thread, Michael said that he had turned-on access-logging (although he said query- logging, he's at V2R4). He said that he didn't capture a begin transaction (BT). I don't think that a BT would appear in the log - do you happen to know? Thanks, John
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
