Network buffer underflow issue

pradkumar · Post by **pradkumar** » Mon Nov 17, 2008 9:01 pm

Hi Everyone,

Before posting this question i have searched the forum and couldn't find the similar posts.

I have a job design ,source as db2 database and target sql server. some records are getting rejected and the job log is has warning

SQLFetch: Error retrieving results from server.
Warning: : [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Network buffer underflow
Unable to set output decimal field value from argument string '$"'5??' in function setOutputFieldAsDecimal() for field '2' in output dataset '0'.
Message:: sql_F4201,0: Warning:: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__F4201__382F5661'. Cannot insert duplicate key in object 'dbo.F4201'.

Message: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

The option i set for loading is truncate and insert the rows. however i have similar jobs and they are working fine.. iam getting error for this job only.

Any suggestions on this warning and how to resolve this warning are highly appreciated.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Tue Nov 18, 2008 1:19 am

Worry about

of PRIMARY KEY constraint 'PK__F4201__382F5661'. Cannot insert duplicate key in object 'dbo.F4201'.

first. This may have caused the statement truncation and consequent network buffer underflow.

pradkumar · Post by **pradkumar** » Wed Nov 19, 2008 5:19 pm

When i checked with the developer he has said that there is no such primary key violation (he is not inserting any duplicate records) and same job runs fine say for the first time and if i run the same job again it is throwing an error Network buffer underflow.

Can any one please suggest an approach how to solve this issue.

tobypanzer · Post by **tobypanzer** » Thu Nov 20, 2008 1:37 pm

pradkumar wrote:When i checked with the developer he has said that there is no such primary key violation (he is not inserting any duplicate records)
and same job runs fine say for the first time.

So in the first run, you are inserting some records...

pradkumar wrote:if i run the same job again it is throwing an error Network buffer underflow.

and here, in the second run, are you inserting the same records?

When you say "No such primary key violation", do you mean the key constraint doesn't exist in the database, or the developer is sure that he doesn't have any duplicate records? Either way, I would use a computer to compare his statements with reality.

pradkumar · Post by **pradkumar** » Thu Dec 04, 2008 11:35 am

Hi,

Iam posting the same issue i got with the network underflow buffer problem. i have searched the forum but still iam lost on this.

I have a job source from db2 database .... flows through copy stage .. from copy stage one link to target sql server database and one more link from copy stage to aggregator stage and takes total source count and loads the count to audit table. when i run this job most of the times the job will run successfully but sometimes on day time the job will finish with warnings and rows will get dropped . The log for this job is

sql_tgt,0: Info: Job name: Using NLS map ISO-8859-1
sql_tgt,0: Info: Job name.sql_F4201: Executing SQL statement 'INSERT INTO sql_tgt coumn names VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,

sql_tgt,0: Logging delayed metadata.
sql_tgt,0: Requesting delayed metadata.
_tgt,0: Info: jobname.tgt: Connected to UDB DB2 for iSeries and AS/400, version 05.40.0000
tgt,0: Info: jobname.tgt: Executing SQL statement 'SELECT column names FROM source '
bx5_tgt,0: Logging delayed metadata.
bx5_tgt,0: Warning: jobname.tgt: SQLFetch: Error retrieving results from server.
bx5_tgt,0: Warning: jobname.tgt: [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Network buffer underflow.
bx5_tgt,0: Warning: jobname.tgt: [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT. REASON BAXPRDDTA.F4201 TYPE TABLE.

Even i modified the job to pull the key columns and say, the 1st 30 of the 66 columns in one DRS stage and the key columns and the remaining columns in another DRS stage, then do a join stage using the key columns, before sending the data to the target. This job has been running successfully most of the time but sometimes the job is aborting .
the messages from log are

Dynamic_RDBMS_9,0: Warning: jobname_Join.Dynamic_RDBMS_9: [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Network buffer underflow.
Dynamic_RDBMS_9,0: Failure during execution of operator logic.
Dynamic_RDBMS_9,0: Fatal Error: Fatal: SQLFetch: Error retrieving results from server.
node_node1: Player 1 terminated unexpectedly.
Join_7,1: Failure during execution of operator logic.
Join_7,1: Fatal Error: waitForWriteSignal(): Premature EOF on node server name No such file or directory
main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.

I dont understand the two same jobs ran succussfully and sometimes these two jobs throwing the above errors.

Any light on this issue is highly appreciated.

Thanks in Advance

pradkumar · Post by **pradkumar** » Thu Dec 18, 2008 9:49 am

Hi Everyone,

The issue got resolved by changing the transaction isolation level to Read Uncommitted in the source DRS stage (DB2 database on AS400).

Thanks to everyone for sharing the knowledge..