The Datamart Extraction job fails with the following error message:
PMD application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - : ORA-20000: Error in NBI_CLEAN_DATAMART_SP: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "NIKU.NBI_EXTRACT_SP", line 362
ORA-06512: at line 1
After this failure, we executed the job again and it's successfully completed. How can we determine what caused this error?
The NOWAIT error means that something else had a database resource such as a specific table referenced, locked. Perhaps the previously submitted Datamart Extraction (DME) job had not finished running yet due to interruption in BG service. Or a job/process was running that was also updating the NBI tables, which caused a lock.
This error could also mean that the amount of connection parallelism needs to be reduced to half of the server capacity to reduce resources overflowing. Please check with DBA to see if they can enable this
The Datamart Extraction is I/O intensive as it detects incremental changes in the transaction tables and then writes those changes to the Datamart reporting tables. In a typical environment, there is enough CPU power to launch multiple parallel processes to access the disks in parallel, therefore minimizing the overall Datamart run times. Please check installation guide to check how to enable it.