DSP.Open Error writing to Oracle - again.

Ian · Post by **Ian** » Sun Jun 05, 2005 11:27 pm

A similar issues have been posted before, but with no real clear resolution in them, so here we are again.

I have DataStage 7.5 on a Windows 2000 box with Oracle 9.i client. Connecting to an Oracle 8.1.7 database on a Unix box.

When using either the 8i, or 9i stage, I am able to read (view data) through the DataStage OCI. But when I try and write to the database with just 1 record being written to a 1 column table, I get the following message.

DataStage Job 1402 Phantom 2016
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage Untitled2..Oracle_OCI_8i_5
DataStage Phantom Aborting with @ABORT.CODE = 3

Thoughts anyone?

Ian.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 06, 2005 2:04 am

Any nulls in the record being inserted into not null column? Doubt it, given the size of the target table, but it's worth asking for next time someone is searching this topic.

Any too-large character strings (for example trying to insert "STRAWBERRY ICE CREAM" into a Char(12) column?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 06, 2005 2:12 am

Access Violation may imply permission problems.

ArndW · Post by **ArndW** » Mon Jun 06, 2005 2:40 am

Usually the access violation$ error stems not from OS level file access but from memory access problems - a program is trying to modify a virtual memory address that is read-only [most commonly it is the code portion where an errant reference points to]. A quick look at the code shows that the error location referenced is just after the external call to $DSP.Open - so not much help unless you happen to have access to the source [i.e. Ascential Support].

What is the Oracle datatype you are using? Could you change it to another type (i.e. CHAR from VARCHAR or vice-versa) and see if the error remains the same?

elavenil · Post by **elavenil** » Mon Jun 06, 2005 3:31 am

Hi,

Could think of couple of issues, which could cause this problem.

1. Null value into Not Null column.

2. The SQL options that you choose to load data into Oracle table. Change the load options (update action in OCI stage) and see what happens. (This problem occurred in my current project when i am selecting the data from OCI stage).

Regards
Saravanan

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 06, 2005 3:34 am

I do not think loading a null into a non-null ora col will raise this error. If it is done, it may return a warning for that record.

elavenil · Post by **elavenil** » Mon Jun 06, 2005 6:03 am

Wrote a simple job, which extracts data from Oracle table and left the derivation blank for a column. When the job was ran, i got the same problem as DSP.Open error. So pls check the column derivation and the other options used in OCI stage.

Regards
Saravanan

Ian · Post by **Ian** » Mon Jun 06, 2005 5:02 pm

Thanks for the replies.

1. Ray, the input file has 1 row, with one value in it. "X". The target table contains null varchar2 (5) columns, and within datastage itself, the columns are defined as nullable varchar(5) columns.
2. Sainath, using the same username / password in a SQLPLUS session, I can insert / update into the table. Actually, I can also insert / update using another DataStage server running 7.1 with the same username / password. So I do not know what permissions it could be other than DataBase, which is OK.
3. ArndW. I have raised the problem with Ascential support, so waiting on that.
4. ArndW. Tried changing from Varchar to Char to Varchar - no good.
5. Elavenil. I am able to select data with no problem (derivations), only writing is the problem.

So for the moment - no idea what the problem is.

chulett · Post by **chulett** » Mon Jun 06, 2005 8:34 pm

Ian wrote:I have DataStage 7.5 on a Windows 2000 box with Oracle 9.i client. Connecting to an Oracle 8.1.7 database on a Unix box.

Just for the record, I'd be curious what exact version of the 9i Client you have installed on the DS Server.

I'd also be curious about the possibility of installing an 8i Client on your DS Server and using that (via the OCI8 stage) to access both the 8i and 9i databases. When dealing with multiple major versions of Oracle, I've found it generally works best taking the 'least common denominator' approach - i.e. using the lower version's Client software.

Ian · Post by **Ian** » Wed Jun 08, 2005 6:06 pm

We had been, and are hoping to use 9.2.0.4 client.

However, we have tried installing and running with 8.1.7 client, but we get exactly the same error. We can read from, but not write to Oracle.

As an additional piece of info, we are able to write to the database using the ODBC link, so I don't think it can be Oracle permissions.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 09, 2005 2:27 am

By permissions, I meant that of the library and corresponding objects within DataStage and not in Oracle.

Did you try to run as a root user or something similar?

You can reinstall the plugin and retry.

Ian · Post by **Ian** » Thu Jun 09, 2005 6:03 pm

We have uninstalled everything, re-installed as the local administratror, logged in and run as the local administrator (not that that should make much difference) - no joy.

We have also now uninstalled everything, and installed DataStage 7.1. And guess what - it works.

Ian · Post by **Ian** » Mon Jun 13, 2005 5:14 pm

A post to close this off which is worth putting on here.

With 7.1 working, we have re-installed the 7.5.1.a server. Using a different CD to install off. And guess what - it works.

We had 2 new DataStage servers, so just to prove that it wasn't working because of something that the install of 7.1 left behind, we "upgraded" our previous 7.5.1.a install using the new CD - and yes everything is now OK.

So it would appear that the problem was due to the CD we installed off.

Thanks to all who posted suggestions.

mholzlei · Post by **mholzlei** » Wed Nov 29, 2006 9:49 am

Hi,
even if the issue may be closed off, I'd like to add one observation/remark we made (DS 7.5 on Windows, Oracle 9i R2).
Talking about jobs reading data from an Oracle OCI stage with the NLS setting parametrized. A colleague started several jobs under (coded) job control with the NLS job parameter value of the OCI stage set to MS1251 (value taken from a job parameter flat file and propagated to the controlled jobs) and all jobs aborted with the exact same error Ian mentioned at the very beginning ("...Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation. "). Now MS1251 had NOT been loaded/installed as an NLS map in the DS server. When the colleague used "View data" on one of the OCI stages or when he ran one of the jobs manually the view data worked and the job finished successfully, because then the NLS job parameter value he used was UTF8.
Actually the hint to find this source of error was that in the DataStage Director log the line which states the NLS map used on the OCI stage (like "...Using NLS map MS1251") showed no NLS map (like "...Using NLS map"), where we crosschecked in the very first log line ("Starting Job ...") the NLS parameter value was definitely MS1251.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 29, 2006 1:54 pm

Welcome aboard, and thank you for making an extremely valuable contribution.

We sometimes assume that posters in the US don't have NLS installed, but that's not always the case. And posters elsewhere in the world often do have NLS installed, of course.

DSXchange

DSP.Open Error writing to Oracle - again.

DSP.Open Error writing to Oracle - again.

Re: DSP.Open Error writing to Oracle - again.