-905 SQL code

rajesh1183 · New User Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad

Hi,

I have a select query where for a praticular instance, it has to retreive around 2.36 million records in a single select. I was receving -905 sql code after the SELECT query has retreived 2.28 million records in a single shot. Can you please assist me in avoiding the -905 error... I can't split the query and retreive the data in parts.. I have to retreive all the 2.36 millions of data in single select query.

Thanks...

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 3:48 pm

The manual has Programmer Responses for the SQLCODE.

Not knowing what environment (foreground, background, online)
in which you are executing this SQL,
nor your processing requirements,
makes giving suggestions rather useless at this point.

rajesh1183 · New User Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad

Well, I was using this SQL query in a batch SAS program. I have proper indexes for the fields used in WHERE clause. I did an EXPLAIN and I have all the columns matching to the columns in the index(criteria of MATCHCOLS satisfied)..

dick scherrer · Posted: Wed Jul 01, 2009 8:30 pm

Hello,

This may be out of your control. It you have used the maximum allowed and it is not enough, you would be best to use an alternate approach.

rajesh1183 · New User Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad

Thanks for the reply....

And there is nothing to post the SQL query as it is a SELECT... anyhow the query wuold look like...

SELECT * from INS_CLAIMS where year=2008 and quarter=12;

There was around 26 million records in the table and I need to retreive all the data every month and do some calculation for generating the insurance reserves... I was looping the query by splitting it based on the year and quarter... For the year 2008 and last quarter there was around 2.3 million records which was causing the -905 error. For rest of the years and quarters the amount of records are less and it was ruinning fine. For the past years and quarters there will not be any change in the no.of records...

dick scherrer · Posted: Thu Jul 02, 2009 10:34 am

Hello,

Is there a compound key made up of year and quarter?

If, suggest you add this additional key and run some tests of the problem process(es) and some regression tests to ensure that acceptably working processes do not see a negative impact.

If this was mine to work with, i'd probably add the new y/q key and reorg the data and then run my performance measurement tests.

ashimer · Posted: Thu Jul 02, 2009 4:14 pm

Ask your DBA to increase the resource limit in RLST table ...

dbzTHEdinosauer · Posted: Thu Jul 02, 2009 4:39 pm

As Dick indicated, as more data is added, the query will become larger.

Have you though of 'Unloading the table' and then processing the qsam file?
Though this would involve a rewrite of the SAS application
(which is very quick and easy to do, so they say),
you would never have the problem of size again.