I am testing Data Migrator Version 7.703 Windows 2008 64 Bit. SQL 2008

I have two etl flows which could write to the same table. I get and SQL Deadlock Error.

I have done the following. I tried with and without.

ENGINE SQLMSS SET ISOLATION RU
ENGINE SQLMSS SET COMMIT WORK

That did not help

I tried to change the confguration of SQL

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 1
RECONFIGURE WITH OVERRIDE
GO

Still no help

I changed the fex properties
Number of attempts: 25
Restart from: Last Commit

At least the fex does not fail but I do not really like this option.

What can I do to prevent these DEADLOCKS?

Below is my stat log

2011/10/14 06:09:50 (ICM18974) ------ Start of Log Record for fad_etl/x_cert_psc2 ------ fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18122) Request - fad_etl/x_cert_psc2 (Owner: FOLKSRE\jaadmin) submitted. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18015) DEP_2: procedure irf_summary_date_control_ja started. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 20111013031501000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 XXX 1960/06/01 00:00:00.000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 CURRDATETIME IS 2011/10/14 00:00:00.000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18039) DEP_2 irf_summary_date_control_ja Return Code = 0 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18742) dt_contract_psc type MS SQL Server Existing target fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18742) dt_contract type MS SQL Server Existing target fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18429) Issuing PREPARE fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18743) Starting Load fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:10:53 (ICM18745) Commit forced at: 500 for 500 row(s) fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (FOC1400) SQLCODE IS 1205 (HEX: 000004B5) XOPEN: 40001 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : Microsoft OLE DB Provider for SQL Server: [40001] Transaction (Process I fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : D 51) was deadlocked on lock resources with another process and has been fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : chosen as the deadlock victim. Rerun the transaction. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (FOC1740) EXECUTE ERROR : DT_CONTRACT fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18745) Commit forced at: 513 for 513 row(s) fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18041) -- stats for source file fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18516) 513 : Row(s) processed by job fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18519) 0 : Row(s) rejected due to format error fad_etl x_cert_

This message has been edited. Last edited by: FP Mod Chuck,

Hi all,

FYI, John already has a case regarding this issue, and the case is currently in research.

John, when you hear a solution/update from the case, can you please post it here? Thank you in advance for sharing with all.

Cheers,

Kerry

No solution yet - but I did determine it has nothing to do with the version of Webfocus or SQL. Same error occurs on production server.

Hi,

I just encountered this error for the first time on a regularly scheduled report. If this happens again, I'd like to automatically re-attempt running the fex a few times as described above, but I can't find "Number of attempts" and "Restart from" in the list of fex properties. Are these properties still configurable, and if so, where?

Thanks!

Edit: added signature

This message has been edited. Last edited by: sarahe,

sarahe

Is this with DataMigrator or WebFOCUS?

Hi,

This is with WebFOCUS - rereading the original post I see that the previous discussion was specific to DataMigrator. Is there similar setting in ReportCaster or WebFOCUS that I could use?

Setup is that I have a WebFOCUS report that is scheduled to run hourly with ReportCaster, and for the first time ever it failed with this error. It ran perfectly the next hour with no modifications. If possible, I'd like to try setting the report to re-attempt one or two times on error.

Thanks!

There is a little discussed property of .acx files called WRITE. The default is YES. Set it to NO like this:

SEGNAME=tablename, TABLENAME=tablename,
        KEYS=3, WRITE=NO, CONNECTION=adatpername,$

I believe this tells WebFOCUS to open the file as Read Only an you can avoid these locks.

We've been reporting off Oracle using the FOCUS language for almost 25 years, long before there was WebFOCUS, and we have never had locks and we have jobs that run every 10 minutes, 24/7, in Report Caster.

Sarah

ReportCaster does not have the ability to restart if there is an error. On the reporting server console if your fex resides in an application folder there is a separate scheduler but I don't see any flags to re-run if it encounters an error. I think it may be worth opening a case with techsupport to see if there is a way with the reporting server scheduler.

PS. I agree with John's suggestion on WRITE=NO.

This message has been edited. Last edited by: FP Mod Chuck,

Thank you both! I opened a ticket with tech support and they confirmed that in ReportCaster there is no option to have the schedule execute again on error. I'll give John's suggestion of the WRITE parameter in the .acx a go in our dev environment.