Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

-905 SQL code

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Wed Jul 01, 2009 3:14 pm    Post subject: -905 SQL code
Reply with quote

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...
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 01, 2009 3:48 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Wed Jul 01, 2009 4:03 pm    Post subject:
Reply with quote

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)..
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 01, 2009 8:30 pm    Post subject:
Reply with quote

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.

Quote:
I can't split the query and retreive the data in parts..
I suspect not completely true. . . As long as the correct output is generated, it should not matter it it is "split" one way or anoter prior to generating the final output.

Depending on the select query (which has not yet been posted, so no one could even have a chance at making a suggewstion), you might be able to simply unload the data and process the qsam file. There is no rule that every process must be run directly and only "in db2".

Also consider, if a way is found to handle <3million with the current system limit, what happens when the volume grows to 3.5million rows. . .
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jul 02, 2009 10:22 am    Post subject: Reply to: -905 SQL code
Reply with quote

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...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 02, 2009 10:34 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 02, 2009 4:14 pm    Post subject:
Reply with quote

Ask your DBA to increase the resource limit in RLST table ...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jul 02, 2009 4:39 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am
No new posts MAX-RC 88 and Return code 606 in DFH0... lind sh CICS 2 Tue Jan 24, 2017 1:23 pm
No new posts How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 2 Thu Jan 12, 2017 12:02 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us