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,
Webfocus 7.6.4 Windows 2003 Server, SQL Server 2005 Excel, HTML , JavaScript ,and PDF. Reportcaster, BID, Tomcat
October 18, 2011, 03:52 PM
Kerry
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
Kerry Zhan Focal Point Moderator Information Builders, Inc.
October 20, 2011, 11:23 AM
jammer
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.
Webfocus 7.6.4 Windows 2003 Server, SQL Server 2005 Excel, HTML , JavaScript ,and PDF. Reportcaster, BID, Tomcat
June 15, 2020, 10:59 AM
sarahe
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 signatureThis message has been edited. Last edited by: sarahe,
Sarah WebFOCUS 8.2.01 Windows 10, All Outputs
June 15, 2020, 02:58 PM
FP Mod Chuck
sarahe
Is this with DataMigrator or WebFOCUS?
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 15, 2020, 03:26 PM
sarahe
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!
Sarah WebFOCUS 8.2.01 Windows 10, All Outputs
June 16, 2020, 08:46 AM
jgelona
There is a little discussed property of .acx files called WRITE. The default is YES. Set it to NO like this:
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.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
June 16, 2020, 09:48 AM
FP Mod Chuck
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 for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 16, 2020, 01:52 PM
sarahe
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.