After setting up a new Development project on a separate server we started to encounter a the ORA-03127: no new operations allowed until the active operation ends fatal error.
The DS Admin pointed me in the direction of the DBA, the DBA had never heard of the error and didn't have any information about it. Since there wasn't a great deal of information available about the error I thought I'd post how it was resolved.
The code in Development was promoted to Test and ran without error. The target now being Test instead of Dev the job finished successfully. I then ran the same code from DS Test but with a target of Dev. That job also ran successfully.
At this point the database was removed as the prime suspect and the DataStage servers moved to the top of the list. The 2 job logs were compared and we found several Environment Variables present in Dev that weren't on the Test project. Once the DS Admin synced Dev EV's with Test we were able to successfully run jobs again.
Differences from the logs:
APT_DATASET_FLUSH_NOSYNC=1
CC_ORA_DISABLE_BATCH_ERRORS=True
DS_FORCE_ABORT_AT_WARN_LIMIT=1
It is quite possible that the initial error was caused by how data was being inserted into a Clob. When a new stage was added the input side was incorrectly formatted as VarChar instead of LongVarChar. Another possibility is that in Dev we may have attempted to Insert an Empty string into a Clob. The derivation handling the Insert into the Clob has been changed to use SetNull when empty per an IBM Tech Note I found researching the error.
This doesn't explain why the error persisted, but for now our Dev environment is working again.
ORA-03127 piece-wise
Moderators: chulett, rschirm, roy
Interesting, thanks for posting that. Was curious about the "piece-wise" in your subject, found this when searching for the ORA error:
ORA-03127: no new operations allowed until the active operation ends
Cause: An attempt was made to execute a new operation before the active non-blocking operation completed or a new operation was attempted before all the pieces of a column were inserted or fetched.
Action: Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done, execute the new operation after all the pieces have been inserted or fetched.
Okie dokie, clear as mud now.
ORA-03127: no new operations allowed until the active operation ends
Cause: An attempt was made to execute a new operation before the active non-blocking operation completed or a new operation was attempted before all the pieces of a column were inserted or fetched.
Action: Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done, execute the new operation after all the pieces have been inserted or fetched.
Okie dokie, clear as mud now.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers