Solved

RowStamp on 2 tables do not match

Posted on 2006-10-26
9
1,965 Views
Last Modified: 2008-01-09
I have a Maximo application that has a SQL backend and I restored a table that contained a rowstamp from a previous day. Now, none of the screens that reference that table will work correctly and say that another user is updating the table.

Here is what the makers of the software have to say. I ran update stats and rebuild indexes on all tables.

MAXIMO uses optimistic locking to ensure that two users cannot update the same record without being aware of each other's updates.  Each MAXIMO table has a column named ROWSTAMP which is set to a TIMESTAMP datatype on SQL Server or is populated with the next value of the MAXSEQ sequence by a trigger on Oracle.   This ensures that when a record is updated the value of ROWSTAMP changes.  
When MAXIMO updates a record, it references both the table's primary key(s) and the rowstamp value in the where clause.  Since the rowstamp value changes each time a record is updated, this mechanism ensures that an update will fail if the record has been modified since it was first queried.  If this occurs, MAXIMO sees that there were zero rows updated by the SQL statement and displays the "Record has been updated by another user" message.   The user must re-query the record in order for MAXIMO to get the updated value of rowstamp, as well as any other changes.

Since MAXIMO always interprets the status "Zero rows updated" to mean that the rowstamp has changed, you may see the "Record has been updated by another user" message when no other user is involved.
0
Comment
Question by:itascent
  • 2
  • 2
  • 2
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17816406
OK, I think this is unclear but substantially correct. Optimistic locking is what SQL Server does. It is not good enough, so Maximo essentially forces pessimistic locking through this mechanism. But this mechanism as described only affects current operations.  

So first, see if you can get MAximo on the phone. they will have some idea of how to proceed.

Short of that, you can run the profiler to see what the application is actually doing. My guess is that you will see what it is doing and why it is failing.

I am not familiar with what happens when you restore a timestamp column from a backup. When you say restore, do you mean SQL Restore or some manual process? If the latter, you would probably end up creating new timestamps . It is possible that even a SQL restore does this. Not a lot of people in SQL Land use timestamp, which is not readily readable as a date.

I think the profiler is going to be your best bet to figure this out
0
 

Author Comment

by:itascent
ID: 17816653
Good tip. I came up with this statement

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null


It has something to do with rowstamp. If I do the following query:

SELECT     *
FROM         poline
WHERE     (polineid = '2067533353')

It shows me the line

If I run this update statement, it says zero rows affected:

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null

If I run this line:

update poline set rowstamp = null  where orgid = 'ASCENT'  and polineid = 2067533353  

It updates that to null, i guess it says 1 row affected

I run the second line again and it doesn’t update, it says zero rows affected.

If I take off the rowstamp = null on the selection criteria, it works

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  

But the application is written that way, I just messed up the rowstamps somehow, but how do I fix it if I can't set the rowstamp to null and what is the perm fix?


0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17816739
Hmm... my guess is that the application is getting a null where it should get a value, since i believe a timestamp is, by definition, never null.

this is really weird though - you should not have been able to update the rowstamp to null if it is actually a timestamp.  I get this message...

Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.


are the values in the rowstamp column hex or datetimes?  

(I may head to bed shortly ... i will be back online by 8am tomorrow eastern US time)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:itascent
ID: 17817074
It says <binary>

Reading below, I'm not sure why the rowstamp even matters on what it is. And why does it see that its not null.

MAXIMO creates a column called ROWSTAMP for Oracle and Microsoft SQL Server databases.


In Microsoft SQL Server, the ROWSTAMP column is defined as a timestamp column.  Whenever a row is inserted or updated, the ROWSTAMP column is modified internally by SQL Server.  Note that the timestamp value is a unique value, but it is not a true date/timestamp.

The ROWSTAMP is used to ensure that a user does not write over a record which has been modified by another user.  This can happen if two users query a record, one user changes and saves the record, and the second user attempts to save the record.  It is important to stop the second user from writing over (losing) changes that the first user has made.  

MAXIMO updates the table based on the ROWSTAMP.  For example, when updating the companies table, the following SQL statement is generated by MAXIMO to ensure that the ROWSTAMP column has not changed since the time the record was first queried:

       update companies
       set company='test', name='newtest',
       changeby='MAXIMO', changedate='2005-02-09-09.38.00.000000'
       where company = 'test' and rowstamp= '000000000000D4C3'

If the ROWSTAMP has changed, the user will be informed that the record has changed and the update does not complete.

When inserting into MAXIMO tables using outside interfaces such as Excel or Access, the ROWSTAMP column will be populated automatically.  If you are inserting using  SQR or another SQL interface, the insert should ignore the ROWSTAMP column.  

For example, the altitem table has three columns: itemnum,  altitemnum, rowstamp.  When performing the insert, the statement should be coded as follows:

       insert into altitem (itemnum, altitemnum) values ('ITEM1', 'ALTITEM1');

The rowstamp column will be populated automatically by the server.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17817085
Hello,

Lets have a table which have col1 int and col2 as timestamp.

now for inserting data u have to write

insert into table values (1, null)

now if u select * from table, u will see col2 has a hexa value which is a time stamp put by the sql server.

so if u search for select * from table where col2 is null, u wont get any results.

0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 17817095
Adding to the above points

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now