Job Hangs

thumsup9 · Post by **thumsup9** » Mon Apr 23, 2007 2:29 pm

Hi,

Job is trying to Insert or Update rows based on a Flag I or U. One of the fields on Target table(SQLSERVER) is unique, though thats not a Pkey.
When we get duplicates on this field, I expected the job to abort. Instead the job hangs and I can also see a lock on the table.

ODBC stage
IsolationLevel None
Rows per tran 0
Array Size 1
Any inputs would be helpful.

Thanks,

nick.bond · Post by **nick.bond** » Mon Apr 23, 2007 4:44 pm

If you were using EE I would say that the 2 updates are taking place on separate nodes without a commit, causing a deadlock, however as you are using Server this doesn't hold. With SQL Server, if you just take the two update statements I take it you can run then manually without a commit?

DSguru2B · Post by **DSguru2B** » Mon Apr 23, 2007 4:58 pm

Is anyone updating the table at the same time your running your job? Get in touch with your DBA, run your job and ask your DBA to monitor whats going on at the database level. He/She will be able to tell you whats going on.

thumsup9 · Post by **thumsup9** » Wed Apr 25, 2007 9:32 am

Thanks for the response. Even I am confused as I never had this issue anytime in the past. I dont see anyone esle accessing that table for any other purpose. I would touch base with our DBA and see if he can give any inputs.

Am i correct in assuming that if we try to insert a duplicate into an Unique column, it would throw a warning Unique constraint violation and would abort the job.

Thanks,

DeepakCorning · Post by **DeepakCorning** » Wed Apr 25, 2007 9:36 am

Yes if it is a key (in DB and your Target Stage) and the strategy has INSERT in it.

Also Are you running any other jobs at the same time as this one.

thumsup9 · Post by **thumsup9** » Wed Apr 25, 2007 1:14 pm

Unfortunately our DBA are busy, but meanwhile i had a similar situation where I got this warning today morning.

SQL statement:UPDATE dbo.Product SET Name = ?, ProductKey = ?, TypeId = ?, ReqNotes = ?, UpdateDate = ?, InsertDate = ? WHERE (Id = ?)
SQLSTATE=23000, DBMS.CODE=547
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted with COLUMN CHECK constraint 'CK_ProductReqNotes'. The conflict occurred in database 'CDDP', table 'Product', column 'ReqNotes'.
SQLSTATE=01000, DBMS.CODE=3621
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Id = 1
Name = "LIMITED "
ProductKey = "AB01"
TypeId = 0
ReqNotes " "UpdateDate = "2007-04-25 13:29:14.000"
InsertDate = "2007-04-18 10:09:47.000"

The constriants on the table are like this.

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [CK_ProductReqNotes] CHECK ([ReqNotes] <> '')
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [CK_ProductName] CHECK ([Name] <> '')

The datastage job is hung though I can see 1 warning in the log. There is also a lock on the target table.

Will update you once I touchbase with my DBA

Thanks,

thumsup9 · Post by **thumsup9** » Wed Apr 25, 2007 3:18 pm

Contuinuation to the above, I would imagine the job would abort with the kind of warning mentioned above. It Didnt. I checked with our DBA and he believes there is nothing wrong on the Database side, may be the datastage application isnt responding properly.

Any ideas if we need to contact Ascential to check why the job is not aborting after a warning.

Thanks,

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 4:11 pm

Is REQ_NOTES a nullable column? Try to send one record with some data in that column. See if the job hangs again. Just a shot in the dark. If that does not help either, then you can get in touch with support.
Did you ask your dba what exactly the thread, originated by your id, is doing at the database level? Just ask him to turn monitoring on and give you the analysis report.