SQLCODE detection in Datastage

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

SQLCODE detection in Datastage

Post by admin »

Hello everyone,

We are encountering a problem detecting the error code returned from oracle . I have ellaborated the details below. If anyone has experience with this can you please help us ?


Software :
----------
Oracle 8.1.6 on NT
Ardent Data Stage designer 4.1

What we are doing
----------------------
- Read records from a flat file
- Insert into a table which is an OCI8 stage
- There is one transformer stage in between the flat file and the target OCI8 stage
- Want to detect the error code returned (sqlcode)
- We examine the following Link variables

insert_into_fact.DBMSCODE
insert_into_fact.SQLSTATE

Both these variables are not populated
- We notice the error codes in the log files
- Example : ora-0001 unique constraint violation . This
is noticed in the datastage job log. We
expected the DBMSCODE and/or SQLCODE to be populated. But it is not Our questions
--------------
- How do we detect any non zero sqlcode returned by the Oracle rdbms from Datastage when using OCI8 ?


thanks and regards,
derick

_________________________________________________________
For Rs. 2,000,000 worth of Aptech scholarships click below http://events.rediff.com/aptechsch/scholarship.htm
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Derick,

Although we use UDB instead of Oracle, weve had a similar problem. We solved this by using the ODBC stage instead of the native UDB plug-in.

Regards,
An
-----Original Message-----
From: derick j JOSE [mailto:datastage@rediffmail.com]
Sent: woensdag 25 juli 2001 10:38
To: datastage-users@oliver.com
Subject: SQLCODE detection in Datastage


Hello everyone,

We are encountering a problem detecting the error code returned from oracle . I have ellaborated the details below. If anyone has experience with this can you please help us ?


Software :
----------
Oracle 8.1.6 on NT
Ardent Data Stage designer 4.1

What we are doing
----------------------
- Read records from a flat file
- Insert into a table which is an OCI8 stage
- There is one transformer stage in between the flat file and the target OCI8 stage
- Want to detect the error code returned (sqlcode)
- We examine the following Link variables

insert_into_fact.DBMSCODE
insert_into_fact.SQLSTATE

Both these variables are not populated
- We notice the error codes in the log files
- Example : ora-0001 unique constraint violation . This
is noticed in the datastage job log. We
expected the DBMSCODE and/or SQLCODE to be populated. But it is not Our questions
--------------
- How do we detect any non zero sqlcode returned by the Oracle rdbms from Datastage when using OCI8 ?


thanks and regards,
derick

_________________________________________________________
For Rs. 2,000,000 worth of Aptech scholarships click below http://events.rediff.com/aptechsch/scholarship.htm
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

I have datastage 3.6.3 installed and using Oracle OCI 8.0 plugin to populate the data in Oracle 8.1.5 table. If I upgrade my oracle version to 8.1.7 do i need to upgrade the plugin also, or the older version will work as it is.

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message History ----------------------------------------


From: ray.wurlod@Informix.Com on 27/07/2001 16:33 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.






--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

To the best of my knowledge, there are only 2 variants of version 2.3 (DataStage 3.6 onwards) of the ORAOCI8 plugin. One is for Oracle 8, the other for Oracle 8i, that is, Oracle 8.1.5 onwards.

It is my belief that you will not then require a different version of the plugin, provided you selected the 8i plugin when you installed DataStage. I am assuming that you did that, as you are currently using Oracle 8.1.5.

I dont think that there is anything visible in Manager which would show which variant of the plugin you are using. The only difference I could find is the date and size of the ORAOCI.DLL. You can trace this back to the 2 variants on the install CD.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Sudhir Mahendru [mailto:sudhir.mahendru@db.com]
Sent: Monday, 6 August 2001 7:12 PM
To: datastage-users@oliver.com
Cc: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage


I have datastage 3.6.3 installed and using Oracle OCI 8.0 plugin to populate the data in Oracle 8.1.5 table. If I upgrade my oracle version to 8.1.7 do i need to upgrade the plugin also, or the older version will work as it is.

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message
History ----------------------------------------


From: ray.wurlod@Informix.Com on 27/07/2001 16:33 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.






--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Hi I am using oraoci8.so file, I do not think it is Oracle 8i plug-in. pls advice

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message History ----------------------------------------


From: david@barham.hm on 06/08/2001 19:53 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


To the best of my knowledge, there are only 2 variants of version 2.3 (DataStage 3.6 onwards) of the ORAOCI8 plugin. One is for Oracle 8, the other for Oracle 8i, that is, Oracle 8.1.5 onwards.

It is my belief that you will not then require a different version of the plugin, provided you selected the 8i plugin when you installed DataStage. I am assuming that you did that, as you are currently using Oracle 8.1.5.

I dont think that there is anything visible in Manager which would show which variant of the plugin you are using. The only difference I could find is the date and size of the ORAOCI.DLL. You can trace this back to the 2 variants on the install CD.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Sudhir Mahendru [mailto:sudhir.mahendru@db.com]
Sent: Monday, 6 August 2001 7:12 PM
To: datastage-users@oliver.com
Cc: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage


I have datastage 3.6.3 installed and using Oracle OCI 8.0 plugin to populate the data in Oracle 8.1.5 table. If I upgrade my oracle version to 8.1.7 do i need to upgrade the plugin also, or the older version will work as it is.

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message
History ----------------------------------------


From: ray.wurlod@Informix.Com on 27/07/2001 16:33 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.






--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.







--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Ah, you are using Unix. The ORAOCI8.DLL is an NT file.

I am still of the opinion that you should be fine.


-----Original Message-----
From: Sudhir Mahendru [mailto:sudhir.mahendru@db.com]
Sent: Monday, 6 August 2001 8:06 PM
To: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage


Hi I am using oraoci8.so file, I do not think it is Oracle 8i plug-in. pls advice

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message
History ----------------------------------------


From: david@barham.hm on 06/08/2001 19:53 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


To the best of my knowledge, there are only 2 variants of version 2.3 (DataStage 3.6 onwards) of the ORAOCI8 plugin. One is for Oracle 8, the other for Oracle 8i, that is, Oracle 8.1.5 onwards.

It is my belief that you will not then require a different version of the plugin, provided you selected the 8i plugin when you installed DataStage. I am assuming that you did that, as you are currently using Oracle 8.1.5.

I dont think that there is anything visible in Manager which would show which variant of the plugin you are using. The only difference I could find is the date and size of the ORAOCI.DLL. You can trace this back to the 2 variants on the install CD.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Sudhir Mahendru [mailto:sudhir.mahendru@db.com]
Sent: Monday, 6 August 2001 7:12 PM
To: datastage-users@oliver.com
Cc: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage


I have datastage 3.6.3 installed and using Oracle OCI 8.0 plugin to populate the data in Oracle 8.1.5 table. If I upgrade my oracle version to 8.1.7 do i need to upgrade the plugin also, or the older version will work as it is.

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message
History ----------------------------------------


From: ray.wurlod@Informix.Com on 27/07/2001 16:33 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.






--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.







--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Hi
We are using Datastage 4.0 with Oracle 8.1.7. The plug-in we use is
oraoci8.sl (Oracle 8 OCI stage version 2.3, OCI 8.1.6 ). It has a dependency on oraoci8enu.sl. The Datastage user ids should still have ORACLE_HOME set to the 8.1.6 directory (DOnt get rid of this directory while upgrading). Datastage does does not work with 8.17 oci.

Naren


-----Original Message-----
From: Sudhir Mahendru [mailto:Sudhir.Mahendru@db.com]
Sent: Monday, August 06, 2001 5:06 AM
To: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage




Hi I am using oraoci8.so file, I do not think it is Oracle 8i plug-in. pls advice

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message History
----------------------------------------


From: david@barham.hm on 06/08/2001 19:53 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


To the best of my knowledge, there are only 2 variants of version 2.3 (DataStage 3.6 onwards) of the ORAOCI8 plugin. One is for Oracle 8, the other for Oracle 8i, that is, Oracle 8.1.5 onwards.

It is my belief that you will not then require a different version of the plugin, provided you selected the 8i plugin when you installed DataStage. I am assuming that you did that, as you are currently using Oracle 8.1.5.

I dont think that there is anything visible in Manager which would show which variant of the plugin you are using. The only difference I could find is the date and size of the ORAOCI.DLL. You can trace this back to the 2 variants on the install CD.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Sudhir Mahendru [mailto:sudhir.mahendru@db.com]
Sent: Monday, 6 August 2001 7:12 PM
To: datastage-users@oliver.com
Cc: datastage-users@oliver.com
Subject: RE: SQLCODE detection in Datastage


I have datastage 3.6.3 installed and using Oracle OCI 8.0 plugin to populate the data in Oracle 8.1.5 table. If I upgrade my oracle version to 8.1.7 do i need to upgrade the plugin also, or the older version will work as it is.

thanks and regards
Sudhir Mahendru
CTI-Banking Systems- IDMS/IDMS-DataWarehouse
(65) 423-7449


---------------------------------------- Message
History ----------------------------------------


From: ray.wurlod@Informix.Com on 27/07/2001 16:33 ZE10

Please respond to datastage-users@oliver.com

To: datastage-users@oliver.com
cc:
Subject: RE: SQLCODE detection in Datastage


SQL State is a phenomenon defined within the ODBC Standards. Therefore it is only reported when an ODBC driver is involved (either an ODBC stage or a "native" stage that uses ODBC under the covers). OCI does not fit in this class. About the best you can do is use DSGetNewestLogId() and
DSGetLogEvent() functions to retrieve the information from the job log, in a job control or after stage/job subroutine.

DBMS code *ought* to be captured, but the author(s) of the OCI stages might not have included updating the link variable. Check the web from time to time for new versions of the OCI stage types.

Or use an ODBC stage and an ODBC driver for Oracle.






--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in
error)
please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.







--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Locked