Access violation on phantom job

[RodBarnes]

I've searched and found a good many instances of folks experiencing an "access violation" error but they all seem to be very different -- probably a general type of error message.

Here's my message:

Code: Select all

DataStage Job 618 Phantom 1636
Access violation.
Attempting to Cleanup after ABORT was raised in stage tst_SyncSystemDIS..ITDW
DataStage Phantom Aborting with @ABORT.CODE = 3

I've imported all my jobs from 7.5 to 8.1 for evaluation and test purposes. The same drivers and databases are being used in both the 7.5 and the 8.1 environment. Most jobs work fine; a few, not so fine. I have done a lot to narrow down what is causing this error. It happens only with the Oracle OCI stage but not in all of them. In several, just recreating the stage and link was sufficient to have the error go away. In one, I've gone as far as completely recreating the job from scratch but it still throws this error. In that one, I've found that it appears to be related to a join it doesn't like; i.e., trimming the SQL down to remove the join removes the error.

This same statement works fine under 7.5 and when run via SQLPlus. But it fails with this error under 8.1. Next step would be to go to IBM support but thought I'd see if anyone has any other ideas before I go there. This group is way more helpful.

[datastageETL]

were you able to solve this issue?

I am performing exactly the same like you did. Imported jobs from 7.5 to 8.1

My job gets the data from Oracle using OCI connects and inserts into SQL Server using DRS. After the insert is done, I have many update statements in the AfterSQL which are run. It is during this "Executing AfterSQL" gives me this message

[RodBarnes]

were you able to solve this issue?

I don't believe so but it has been a while since I was working on this. We opted to not move to 8.1 at the time (for this and several other reasons) but still plan to at some point in the future. So, this may come back to haunt me.

[datastageETL]

thanks for your quick response

[datastageETL]

Just for others information - I solved this issue by changing the entire sql statements in the AfterSQL to a stored procedure. What we noticed was that after 33 lines of sql statements, the job is being aborted with Access violation message.

I am not sure if this is a limitation with 8.1 but the same is running fine in 7.5.2

Hope this helps others

[chulett]

You might want to check and see if there is a patch available for that.

[asorrell]

We saw some similar issues and Customer Service advised us to remove the formatting from the SQL. We had inserted extra carriage returns and blank lines to make it easier to read in the stage. Once we removed the formatting it all worked just fine.

Apparently they modified the parser in 8.0+ and it isn't as good as it was about handling formatted SQL.

[chulett]

I've seen that issue in earlier versions as well. 'People' formatting with extra returns and/or blank lines cause issues, removing all that (in essence making the SQL 'one line') made DataStage much more happier.

[dec1177]

We are experiencing a similar issue except that we are having the problem with the RDBMS stage. The job works fine in 7.5, however when we migrated it to 8.1 and tested it, same error message as stated in the top of this thread. I was able to convert the RDBMS stage to an ODBC stage and got the job to work, however we are going to be moving MANY jobs from 7.5 to 8.1 and I would like to resolve the root issue if anyone has any suggestions.

BTW, I know that SQL formatting is not the issue because "Viewing the Data" from the RDBMS stage returns the dataset without flaw.

Any help is much appreciated.

[ray.wurlod]

I suspect that this is different enough to warrant a separate thread.

[dec1177]

Why is it a different thread? The individual that added his own problems to the original thread discussion had an issue with DRS as well...I thought this would be the perfect place to post in the event that the problems were being caused by the same underlying issue anyone in the future could possibly come here and resolve mutliple issues with the same cause.