DSXchange

If you paste your insert and update statement into SQLplus from Oracle stage and replace the orchestrate.something with some values, do you get disconnected?
Maybe your DBA could help and examine your session from Datastage (he could see the exact SQL statement that Oracle can see) and see what happens.

There are no EOT or EOF , Yes I am workign with the DBA to point this one out for me. Will update this once I get soem info.

1. Generated the SQL statements (Insert) and ran them in SQL Plus. The SQL runs fine. With no discconection issue.

2. Replaced the DRS stage with an Oracle OCI stage , the job works fine.

Now the question is why DRS stage is not working fine with this one?

DeepakCorning wrote:1. Generated the SQL statements (Insert) and ran them in SQL Plus. The SQL runs fine. With no discconection issue.

2. Replaced the DRS stage with an Oracle OCI stage , the job works fine.

Now the question is why DRS stage is not working fine with this one?

I never used DRS, and in your SQL, if you are only connecting to oracle, then why do you need to use DRS, it will be slower than Oracle OCI stage.

anyway so if you eliminated all the other possible sources of error ( and I think you did), this could be a bug with the DRS stage, I would advise you to contact IBM for this issue.

Yeppeeeeee...

I do not know the reason why this can be an issue ! But the last column in the DRS stage was delared as Varchar (2000). I changed it to Char 255 (Tested it with Var char 255 as well) and now the job just works fine.
I changed it back to Varchar 2000 , and the job aborts again with the same reason.

Any idea WHY?

2000 might be more than allowed per column for an odbc handle maybe.

Also Just an FYI - The maximum limit of the Descrlong in the DRS stage for which this job works is "666" , if the length is greater than this then the job aborts.

May be its true with other jobs as well.

What is the length of the entire record with your desc column set to 666?

666? The devil you say!

chulett wrote:666? The devil you say!

Well the devil jut worked fine for me...

DSguru2B wrote:What is the length of the entire record with your desc column set to 666?

768 with the descr column defined as 666...

MAXKEYSIZE in uvconfig file has a limit of 768 but its not connected so dont change that limit. Maybe someone here can shed some light on this behaviour.

I am not sure if below comments will help you or not. But on my pervious job I encounter similar issue after experimenting I found answer.

Which data stage version you using. If you are using 6.0 then you will face this kind of problem with oracla9i stage. If you using oracla9i stage then you can't able to connect to oracle to resolve this issue you have to use oracle8i stage or OCI stage.

Your situation might be different then mine if you not using version 6.0 and oracla9i stage then I am not sure why you getting this error.

There are also limits on row size and column count for ODBC that are set in the uvodbc.config file. Defaults are 8KB and 400 respectively; they are set using MAXFETCHBUFF and MAXFETCHCOLS.
Are you using ODBC as the connection type in your Dynamic RDBMS stage?

ray.wurlod wrote: Are you using ODBC as the connection type in your Dynamic RDBMS stage?

Ray Can you please explain this because I am Not sure what you mean by this?