Sybase OC VS ODBC Stage

[turash]

I have a job which moves data for two tables

Source DB2

Target Sybase

If i run the job with Sybase OC stage it returns the following error

Sybase Server warning 515 (severity 16)

While similar job with ODBC Stage does not capture this error in the error log.

[chulett]

What version of Sybase?

[turash]

Sybase 12.5

What version of Sybase?

[chulett]

Ok, just wanted to make sure you're using a supported version of Sybase. So... have you configured DataStage for Sybase? Meaning, have a Sybase client installed on the DataStage server and the proper environment variables included in your dsenv file?

[turash]

Yes

My main issue is this i am planning to do transaction grouping using ODBC.

ODBC stage does not captures some of the errors.

Ok, just wanted to make sure you're using a supported version of Sybase. So... have you configured DataStage for Sybase? Meaning, have a Sybase client installed on the DataStage server and the proper environment variables included in your dsenv file?

[chulett]

OK. That would have been something worth mentioning in your original post, we wouldn't have wasted time talking about trying to 'fix' a Sybase issue.

So... the error itself isn't the issue, it's the fact that when you use the ODBC stage it doesn't capture 'some of the errors'? Not really sure what that would mean - it completes successfully when it should have aborted? It logs these 'errors' as Info messages and doesn't abort the job? It aborts but doesn't log the appropriate error?

It seems to me we'd need a better explanation of your issue before anyone could help much.

[ray.wurlod]

Does the target end of your design look like this?

Code: Select all

       -----> Transformer  ----->  ODBC
                  |        Output
                  |Rejects
                  |
                  V
          SequentialFile

In the ODBC stage set rows/transaction to 1 and parameter array size to 1. Create new columns on the rejects output to capture SQLSTATE, DBMSCODE and LASTERR link variables from the Output link. VarChar(20) for the first two and VarChar(2000) for the LASTERR ought to do. Don't forget to set the Rejects check box in the Constraints area for the Rejects link.

[turash]

Yes My Test Job Looks like this

On my target there is a column which does not allow null values.

Job status as well as log shows the job ran successfully. no rejects

If check the table i don't see that row.

If I Use Sybase OC Stage instead of ODBC the same row gives me following

Error Message(Target Sybase OC)
GdbgStg_GD_openclient..xfmStg_GD: At row 1, link "loadGDTpolicy"
Sybase Server message 3621 (severity 10) from stored procedure 'Input_0', line 1: Command has been aborted.
At row 1, link "loadGDTpolicy"
Sybase Server warning 515 (severity 16) from stored procedure 'Input_0', line 1: Attempt to insert NULL value into column 'asm_insured_name', table 'GDNADPWEB.dbo.TPOLICY'; column does not allow nulls. Update fails.


ODBC Setting

Driver=/opt/dsadm/Ascential/DataStage/branded_odbc/lib/VMase19.so
Description=DataDirect Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=GDNADPWEB
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=
NetworkAddress=livdspsoft1,4600
OptimizePrepare=1
PacketSize=0
Password=
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=



My Final Job will have more than one table to transfer from Source to target. I need to use ODBC for its transaction grouping feature

Does the target end of your design look like this?

Code: Select all

       -----> Transformer  ----->  ODBC
                  |        Output
                  |Rejects
                  |
                  V
          SequentialFile

In the ODBC stage set rows/transaction to 1 and parameter array size to 1. Create new columns on the rejects output to capture SQLSTATE, DBMSCODE and LASTERR link variables from the Output link. VarChar(20) for the first two and VarChar(2000) for the LASTERR ought to do. Don't forget to set the Rejects check box in the Constraints area for the Rejects link.

[turash]

Sorry for any confusion caused


ODBC Stage captures errors if there is any key violation but does not capture error such as target column does not allow null values.

Error Message Captured though Sybase OC

GdbgStg_GD_openclient..xfmStg_GD: At row 1, link "loadGDTpolicy"
Sybase Server message 3621 (severity 10) from stored procedure 'Input_0', line 1: Command has been aborted.
At row 1, link "loadGDTpolicy"
Sybase Server warning 515 (severity 16) from stored procedure 'Input_0', line 1: Attempt to insert NULL value into column 'asm_insured_name', table 'GDNADPWEB.dbo.TPOLICY'; column does not allow nulls. Update fails.

ODBC Setting

Driver=/opt/dsadm/Ascential/DataStage/branded_odbc/lib/VMase19.so
Description=DataDirect Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=GDNADPWEB
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=
NetworkAddress=livdspsoft1,4600
OptimizePrepare=1
PacketSize=0
Password=
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=

OK. That would have been something worth mentioning in your original post, we wouldn't have wasted time talking about trying to 'fix' a Sybase issue.

So... the error itself isn't the issue, it's the fact that when you use the ODBC stage it doesn't capture 'some of the errors'? Not really sure what that would mean - it completes successfully when it should have aborted? It logs these 'errors' as Info messages and doesn't abort the job? It aborts but doesn't log the appropriate error?

It seems to me we'd need a better explanation of your issue before anyone could help much.

[jdmiceli]

As a suggestion, try replacing the ODBC stage with the RDBMS stage. We seem to have better luck with that. If I remember correctly, it provides better error messaging.

Please correct my if I am wrong, Ray and Craig...

Merry Christmas!