jconn driver return 500 records instead of a lot more sometimes

[Former Member]

Hi all; I need some help and I am pretty out of ideas. I am using Jboss 5.1 with apache commons dbcp over sybase jconn driver to communicate with SQL Anywhere 12.0.1 I have some stored procs I execute that need to return about 10k records but for some reason return exactly 500 most of the time. sometimes on first use of a "clean" connection from the pool I get the proper result size. I cant really put my hands over this. does the number 500 ring a bell to anyone? is this some kind of a known cache size somewhere in the DB side or the driver side?

up until now I have tried com.sybase.jdbc3.jdbc.SybDataSource and com.sybase.jdbc4.jdbc.SybDataSource as my drivers and got the same result. I dont use any special connection params. I am sorry that I dont have any more info but I though this might ring a bell. I think the problems started with the upgrade from SQL Anywhere 10.0 to 12 but I am not sure.

any help would be greatly appreciated.

[MCMartin]

same question as this: SA12 - unexpected resut set limit to 500

[Breck_Carter]

Martin suggested request logging and it's a good idea: http://dcx.sybase.com/index.html#1201/en/dbusage/performance-s-5753386.html

[Former Member]

yeh sorry for the double post it was some guy from the DB team (i am on the Java side). below is a link of a rar file with 2 TDS captures, translated to txt via Ribo Sybase tool. Each capture has 2 stored procedure calls inside. Both captures SP should produce the same result as they “sit” over the same static data and do the same logic every time. the file Bad.tds contains a call to the stored procedures and returns ~500 records per SP. It is bad because we have about 10K records total. This is the problem that we are trying to understand. the file Good.tds contains a call to the the stored procedures and returns all the records as we expect.

can someone please see the capture files and help me figure out what is the difference. I am not sure how to interpret all the the text of the TDS protocol there. to me it looks like the SPs are ok on both files but return different results. - Are all procedures (from both files) seem to finish correctly with no disturbance? - Do you see anything suspicious in the captured files? Thanks. link to TDS capture

BTW: SP names begin with SP_ so its searchable in the captures. thanks very much for any help.

[Breck_Carter]

The parameter values passed to SP_MOBILE_TOP_PROTO_SDR are different... is it possible the stored procedure is returning the correct number of rows in each case? ...just asking.

[Breck_Carter]

See http://sqlanywhere-forum.sap.com/questions/8069/sa12-unexpected-resut-set-limit-to-500/8103