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
 

 

SQL Code -904 when query with distinct on a non-index field

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

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Mon Feb 16, 2009 2:52 pm    Post subject: SQL Code -904 when query with distinct on a non-index field
Reply with quote

hi,

hi am getting sql code -904 when i run query like
Code:

select distinct (creator_tbl) from DB00.date_tb fetch first 10 rows only with ur
SQL0904N  Unsuccessful execution caused by an unavailable resource.  Reason
code: "00C90084", type of resource: "00000230", and resource name: "4K". 
SQLSTATE=57011

SQL0904N  Unsuccessful execution caused by an unavailable resource.  Reason code: "00C90084", type of resource: "00000230", and resource name: "4K".

Explanation:

The SQL statement could not be executed because resource
"<resource-name>" of type "<resource-type>" was not
available at the time for the reason indicated by
"<reason-code>".  Refer to the Problem Determination
documentation of DB2 for MVS for an explanation of resource type
codes. 

User Response:

Verify the identity of the resource that was not available.  To
determine why the resource was unavailable, refer to the
specified "<reason-code>". 

 sqlcode :  -904

 sqlstate :  57011

SQLCODE: -904


but the query on the same table works when distinct is on the index key(date) ...
Code:

select distinct (date) from DB00.date_tbl fetch first 10 rows only with ur;
Back to top
View user's profile Send private message

CHANDAN KAPOOR

New User


Joined: 12 Apr 2008
Posts: 36
Location: NOIDA

PostPosted: Mon Feb 16, 2009 2:59 pm    Post subject: Reply to: SQL Code -904 when query with distinct on a non-in
Reply with quote

Are the table are in the same region as the name of the table is different ie date_tb and date_tb1.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Mon Feb 16, 2009 3:01 pm    Post subject:
Reply with quote

Quote:
select distinct (creator_tbl) from DB00.date_tb fetch first 10 rows only with ur

Quote:
select distinct (date) from DB00.date_tbl fetch first 10 rows only with ur;

Are you referring the same table?
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Mon Feb 16, 2009 3:02 pm    Post subject:
Reply with quote

yes they are in the same region ...sorry for the typo
it should be

Code:

select distinct (creator_tbl) from DB00.date_tb fetch first 10 rows only with ur
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Feb 16, 2009 6:19 pm    Post subject:
Reply with quote

Resource type 230 is temporary file .... this file is not available in your region ...it could have been archived ... this file is required to sort and remove duplicates .... if you index is unique this file is not required and hence you are getting data for query on date ....
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Tue Feb 17, 2009 12:08 am    Post subject:
Reply with quote

Hi ,

Error is due to workfile tablespaces ( DSNDB07.DSN4kxx ) are not suuficient to sort the non-indexed column for DISTINCT function .

You may have to ask your DBA to increase the number of DSN4K tabelspace for the DB2 subsystem .

Generally , performing DISTINCT function on non-indexed column would not recommended because it would take more cost in sorting .


Thanks
Senthil
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Feb 17, 2009 9:10 am    Post subject:
Reply with quote

Involved the DBA and sorted out the issue, the workfile was reallocated and now we are up again.

Thanks for the information it was a good learning experience
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 SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
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 DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 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 outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am


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