| Author |
Topic |
|
alvin
Starting Member
3 Posts |
 Posted - 2003-02-27 : 02:58:26
|
| Hi,Is there any way to undo processing of a job other then using transaction?actually i want something like the rollback feature of transaction but is there any other way to do it?alvin |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-27 : 07:22:20
|
| There is no way to rollback a transaction without using the ROLLBACK command.You could, however, restore you database from a backup taken before the transaction ran.Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-27 : 07:25:36
|
| Lumigent makes a product called Log Explorer that may allow you to undo the work that the job did, but if you've performed log backups since the job ran it may not be able to. If you anticipate wanting or needing to roll back work then you should absolutely use transactions. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-27 : 08:48:38
|
| Why not use ROLLBACK?Under what conditions would you want to "UNDO" your changes?If you can ask "IF THIS HAPPENS"...I don't like it, and ROLLBACK. I don't think you should leave it up to a whim that you'd like to reverse changes.Also if you're worried about the users doing "bad" things (too bad it's their fault anyway), why not dump your logs more frequently or add history tables to undo what they've done.Just my thoughtsBrett8-) |
|
|
|
alvin
Starting Member
3 Posts |
Posted - 2003-02-27 : 19:55:09
|
| Thanks alot for the helpi tried implementing transaction but there seems to be some problems which i'm not if it's caused by transaction but i doubt so.the records are not sorted correctly, the date will be sorted something like 1,10,11...2,20,21 instead of 1,2,3..10,11...20,21which i strongly believe that there is nothing to do with transaction.i was told to remove the transaction and try to implement it another way.The reason why i want to undo processing is because i want to cater for fault tolerance. there are cases of failed jobs and many times the records will be missing when a job fails. the job is being processed and when a job fails, records that have been inserted into tables will not be removed.Thanks againalvin |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 20:46:36
|
| Is your data being sorted due to a clustered index? Maybe the index just needs to be updated and it hasn't gotten updated yet. |
|
|
|
alvin
Starting Member
3 Posts |
Posted - 2003-02-28 : 02:02:21
|
| clustered index? sorry what is that? i'm sort of a newbie on SQL and my knowledge on SQL is limited sorrydo u mean auto-incremental numbering? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-28 : 03:50:55
|
| >. The reason why i want to undo processing is because i want to cater for fault tolerance. there are cases of failed jobs and many times the records will be missing when a job fails. the job is being processed and when a job fails, records that have been inserted into tables will not be removed. Bad design.The jobs should either clear up after themselves i.e. error processing to clear things up or should not commit until everything is correct.The clear up option needs a process to run checking the status of he job and for nothing else depending on the data to run until everything is ok.The transaction option will hold locks on rows and will cause the transaction log to grow. It also means that the job can't continue from where it left off as everything will be rolled back.The clear up option means that you will have to make sure nothing tries to access the data in mid process.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-28 : 07:18:37
|
quote:
the records are not sorted correctly, the date will be sorted something like 1,10,11...2,20,21 instead of 1,2,3..10,11...20,21
"records"? Do much scratchin' on your 12's, dj?"date"? Mabye you mean DATA ... it sure doesn't look like your sample data here is DATETIME typed ?!? What datatype is this data? If it is INT, I'd recommend you start praying cause the apocalypse is impending. If it is CHAR, it looks right to me...Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-28 : 08:03:17
|
I truly mean this question in the most sincere way:Why must it be "rows" and "columns" and not "records" and "fields"? Why is it wrong to call a row of data a "record" or a column in a table a "field"? By refering to a row/column with the older-style record/field terminology, is something implied that violates the rules of a relational database?It just seems opposite to me ... the connotations of the word "row" and "column" imply that both of those objects have ordinal positions, whereas the word "field" does not and neither does the word "record" .... I am truly just wondering and not trying to start an argument or anything. Jay, go easy on me please .... - Jeff |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-28 : 08:42:19
|
Good question Jeff!! I don't believe anyone has ever asked that!! Let me defer to Celko ....quote:
A procedural language is a detailed description of how a task is accomplished, and it operates on one record or one unit of data at a time. Such languages are also called 3GL (third-generation languages) and are familiar programming languages such as Fortran, Cobol, BASIC, ADA and Pascal. When they operate on data, these languages use files that have theses characteristics:- Data is stored in files in records. The programmer must know the physical location, logical location, and structure of a file to use it.
- The program has the information needed to read a file and accesses it one record at a time.
- The structure of the records is defined within each program.
- The order of the records within a file is important.
- The order of the data within a record is important.
- Records can be complex data structures.
In contrast, a nonprocedural language is a description of what you want; the system figures out how to get it. SQL is such a language. SQL is not considered an end-user language; it has no input or output statements, so it has to be used with a host language that can handle those functions. When SQL operates on data, it models the world with tables that have these characteristics: - Data is stored in a database made up of tables; the tables can be real or virtual. Users are connected to the whole database, not to individual tables.
- The program asks SQL to return data to it, without any concern as to the physical structure or location of the data.
- The structure of the tables is defined within the database, not within each program.
- The order of the tables within the database is not important. They are identified by name.
- The order of the columns within a table is not important. They are identified by name or a combination of table name and column name.
- The order of the rows within a table is not important. They are identified by unique values within their columns(keys).
- The data is always present as a table to the user, no matter what internal structure is used by the database.
SQL often presents a problem for an experienced programmer who has to unlearn old habits developed while using 3GLs. But think of this process as similar to learning recursion in 3GL. At first, it is impossible to see things recursively; then suddenly it all makes sense, and you write your first recursive program. In SQL, complex queries look impossible until the day you can think in sets and formal logic, and then you simply write the query.-Joe Celko, SQL for Smarties, pages 2-4
So yes, on one hand we all know what Alvin means when he says “record”. And since we all know what he really means, I’m a dick for posting a mocking joke about turntables.However, if you think about what Celko is saying in the above quote, you see that the issue goes much deeper than nomenclature. It is actually quite fundamental to our profession. RDBMSs operate in the world of set-based operations, not iterative processing. “Rows” is a set-based term and “records” is an iterative term. Truly understanding the difference between a row and a record is an absolute prerequisite for being a competent database professional. And if one truly understands the difference between a record and a row, one doesn’t make the mistake of mixing them up.Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-28 : 09:04:25
|
| Thanks for the Celko quote, but ... hopefully you understand I wasn't asking what the difference is between a "record" and a "row", but rather WHY those terms were chosen because they seem backwards.But I see what you are saying ... the record/field terms were already used to refer to the older COBOL-style data structures accessed sequentially from a disk, so the row/column terms were introduced to make sure there is no confusion between when we are talking Relational databases vs. old flat file DB's.And that is why it is a good idea to not mix terms because it can imply that someone is still thinking in terms of flat-file DB's instead of true relational DB's.It still does seem backwards to me .... row/column makes more sense in an ordered, physical flat file, wherease record/field makes more sense in a relational database with no ordering .... Thanks for the reply!Edited by - jsmith8858 on 02/28/2003 09:11:23 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-28 : 09:12:13
|
| I guess its the same reason why you park in your driveway but drive on the parkway ...Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-28 : 09:21:11
|
| EXACTLY!!!! I couldn't have said it better myself ! that is a perfect analogy.Thank you very much Jay.- Jeff |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-02-28 : 09:59:23
|
| Jay,That must be an american thing, in England we drive on the driveway to get to where we want to park. And because of all the cars, really we park on the Parkway (although there's no such thing in England), you should see the traffic over here ;o) |
|
|
|
|
anyway to undo processing?