IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQLCODE=-904 with SQLSTATE=57011


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sujesh_prasannan

New User


Joined: 01 Aug 2007
Posts: 6
Location: chennai

PostPosted: Sat Feb 02, 2008 3:07 pm
Reply with quote

Hi all,
i have codded a Db2 program to fetch few records. But while running this it is giving me a sql code = -904 with SQLSTATE = 57011

Can any one help me in this. I have no clue on what this is.

Spool details:

Code:
DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN   
         UNAVAILABLE RESOURCE. REASON 00E70082, TYPE OF RESOURCE 3002,  AND RESOURCE NAME NUMBER OF  STORED PROCEDURES                 
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                       
DSNT415I SQLERRP    = DSNXERT2 SQL PROCEDURE DETECTING ERROR
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Sat Feb 02, 2008 7:46 pm
Reply with quote

SQLCODE of -904 with SQLSTATE of 57011 typically indicates a "Resource Unavailable" or "Resource Busy" error. This error occurs when a database object, such as a table or index, is temporarily unavailable or in a state that prevents the requested operation from being performed. Here's a breakdown of what these codes generally signify:

SQLCODE = -904
Meaning: Resource Unavailable
Description: The requested resource (often a database object like a table, index, or buffer) is temporarily unavailable or is being used by another process, preventing the requested SQL operation from completing successfully.

SQLSTATE = 57011
Meaning: Resource Busy
Description: Indicates that a resource required for the execution of a SQL statement is busy or unavailable. This SQLSTATE code corresponds to the SQLCODE -904 and provides more specific information about the nature of the resource being unavailable.

Causes of SQLCODE = -904 / SQLSTATE = 57011
Locking Conflicts: Another transaction or process has locked the resource (table, index, etc.) that your SQL statement is trying to access or modify.
Resource Utilization: The resource is currently being used extensively, such as during a large data operation or maintenance activity.
Temporary Unavailability: The resource may be temporarily unavailable due to system maintenance, backups, or other system-related activities.

Resolving SQLCODE = -904 / SQLSTATE = 57011
To resolve this error, consider the following steps:

Retry Logic: Implement retry logic in your application or SQL script to handle temporary resource unavailability. You can retry the SQL operation after a short delay or after detecting that the resource is no longer busy.
Check Locks: Review the locking behavior of your SQL statements and ensure that they are not conflicting with locks held by other transactions. Use appropriate isolation levels and locking strategies.
Monitor System Activity: Check for ongoing system activities or maintenance tasks that may be causing resource unavailability. Coordinate with system administrators if necessary.
Optimize SQL Statements: Optimize your SQL statements and queries to minimize resource usage and reduce the likelihood of encountering resource unavailable errors.
Review Database Configuration: Review database configuration settings related to concurrency, locking, and resource utilization to ensure they are appropriately configured for your application's requirements.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Sun Feb 03, 2008 6:40 pm
Reply with quote

If you have QuickRef, do QW 00E70082 (the reason code)
If you don't have QW, check the manual

Explanation: The user has reached the maximum number of outstanding
stored procedures or opened cursors for the current thread.
Back to top
View user's profile Send private message
Santoshdorge

New User


Joined: 27 Jun 2006
Posts: 48
Location: Pune

PostPosted: Mon Feb 04, 2008 5:42 pm
Reply with quote

Hi sujesh ,

Please try checking the database status?
If it is not in RW status then all is related to DBA you need to contact him in order to solve -904.


thanks,
Santosh.
Back to top
View user's profile Send private message
rakesh17684

New User


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

PostPosted: Fri Feb 08, 2008 10:43 am
Reply with quote

this mostly occurs , due to index or the table space getting into issues, you can get the exact reasons if can see the status of the database using any database monitoring tools like platinum etc.,Also this can be resolved by executing Db2 repair commands either in DB2 or a JCL.

comments or concerns????
Back to top
View user's profile Send private message
rakesh17684

New User


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

PostPosted: Fri Feb 08, 2008 10:47 am
Reply with quote

Db2 commands

for status display
-DISPLAY DATABASE (table-name) SPACE(*)

for repair
If the TABLESPACE needs correcting use the
REPAIR OBJECT LOG NO SET TABLESPACE command with Database, Tablespace and parameter.
If the INDEX needs correcting use the REPAIR OBJECT LOG NO SET INDEX command with Database, Index and parameter. (this depends on the type of repair needed)
Back to top
View user's profile Send private message
shekharmax

New User


Joined: 26 Dec 2007
Posts: 3
Location: hyderabad

PostPosted: Fri Feb 08, 2008 11:18 am
Reply with quote

hi sujesh,

Expalnation of your code is :

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'.
Verify the identity of the resource that was not available.

Common Causes of sqlcode=-904, sqlstate=57011
Resource Unavailability:
The tablespace, index, or database is offline or in a restricted state (e.g., STOPEO status).

Capacity Issues:
The tablespace or index may be full, or a maximum extent has been reached.

Configuration Problems:
There could be configuration issues such as incorrect definitions or allocations.

Pending Utilities:
A utility might be running on the resource, making it temporarily unavailable.

Locks and Contention:
Resource is locked by another process or in a contention state.

Steps to Resolve SQLCODE -904
Check DB2 Messages and Logs:
Examine the DB2 master log (DSN1PRNT output) and job logs to identify the exact resource causing the issue and the associated reason code.

Identify the Resource:
The error message will provide the resource type and resource name. For example:
Code:
UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096,
TYPE OF RESOURCE 00000200, AND RESOURCE NAME DBNAME.SPACENAME


Review Reason Codes:
Consult DB2 documentation for the specific reason code provided in the error message. Common reason codes include:
00C90081: Resource unavailable due to an error.
00C90096: Tablespace or index in restricted status.
00C90097: Dataset or tablespace is full.

Resolve Resource Unavailability:
Bring Resource Online: If the resource is offline, bring it back online using appropriate DB2 commands (e.g., START DATABASE).
Resolve Restricted Status: If the resource is in a restricted state, resolve the issue causing the restriction.
Address Capacity Issues: If the tablespace or index is full, extend it or add additional datasets.
Check for Pending Utilities: If a utility is running, wait for it to complete or take appropriate action to complete or terminate the utility.

DB2 Commands to Address Issues:

Start Database:
Code:
START DATABASE DBNAME SPACENAME


Check Tablespace Status:
Code:
DISPLAY DATABASE(DBNAME) SPACENAM(SPACENAME) RESTRICT


Re-run the Job:
After addressing the issue, re-run the job or SQL statement to ensure the problem is resolved.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SQLCODE=-311 in Cobol SP-DB2. COBOL Programming 2
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts SQLCODE=-204 SQLSTATE=42704 DB2 4
Search our Forums:

Back to Top