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
 

 

SQLCODE -904

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

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 11:27 am    Post subject: SQLCODE -904
Reply with quote

Hi,

Yesterday, while going testing for programmatic load,my job failed with sqlcode -904. Resource unavailable.


Code:


SQL CODE:         -904                                                         
SQL OPERATION:                                                                 
SQL ERROR VARS:   00D70014.00000220.XXXX.DSNDBC.DATA000D.TS00001S.I0001.A019   
SQL NUMBER ROWS:  0000000000                                                   
SQL MESSAGES:                                                                   
DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE
                                                                               
         00D70014, TYPE OF RESOURCE 00000220, AND RESOURCE NAME XXXX.DSNDBC.DATA000D.TS00001S.I0001.A019   
                                                                               
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                               
                                                                               
DSNT415I SQLERRP    = DSNXRINS SQL PROCEDURE DETECTING ERROR                   
                                                                               
DSNT416I SQLERRD    = -110  13172746  0  -1  0  14090260 SQL DIAGNOSTIC INFORMAT
                                                                               
DSNT416I SQLERRD    = X'FFFFFF92'  X'00C9000A'  X'00000000'  X'FFFFFFFF'  X'0000
                                                                               
         SQL DIAGNOSTIC INFORMATION                                             
                                                                               
                                                                               
  NO SQL WARNING FLAGS HAVE BEEN SET                                           


It says unable to extend the dataset. but when i checked the number of extends i see only 69.


Code:


Total Tracks:       8955 non-x:       8955 Data Sets:        2 non-x:        2
-------------------------------------------------------------------------------
Command - Enter "/" to select action                        Tracks %Used   XT 
-------------------------------------------------------------------------------
         XXXX.DSNDBC.DATA000D.TS00001S.I0001.A019                             
         XXXX.DSNDBD.DATA000D.TS00001S.I0001.A019            8955    ?    69 
         


Also I checked how many volumes this dataset spans.


Code:


Vol
COMMAND ===>                                                  SCROLL ===> PAGE
Objects associated with -  XXXX.DSNDBC.DATA000D.TS00001S.I0001.A019           
        Type    ---------------- Object name -----------------                 
       DATA     XXXX.DSNDBC.DATA000D.TS00001S.I0001.A019                     
       VOLUMES  VO0265 VO0267 VO0272 VO0279 VO0274 VO0262                     
                VO0276 VO0266 VO0273 VO0259 VO0268 VO0271                     
                VO0269 VO0277 VO0282 VO0246 VO0270 VO0215                     
                VO0284 VO0297                                                 


I see the number of volume are - 20.

Is there any limit to number of volumes a dataset can span?
Or there is some other cause of the error?

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

senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Wed Dec 14, 2011 12:40 pm    Post subject: Reply to: SQLCODE -904
Reply with quote

Check all the Database/Tablespace Status is START or STOP.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 12:42 pm    Post subject:
Reply with quote

I checked the Database and Tablespaces. All were in RW mode.
Back to top
View user's profile Send private message
senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Wed Dec 14, 2011 1:01 pm    Post subject: Reply to: SQLCODE -904
Reply with quote

May be the Problem will occur the following reasons:
•Console output from the system on which the job was run, and a listing of the SYSLOG data set for the period of time spanning the failure.
•SVC dump (or system dump), taken to SYS1.DUMPxx data set, as result of an operator-initiated dump command or SLIP trap exit.
•SYSABEND, SYSUDUMP, or SYSMDUMP output.
•Listing of SYS1.LOGREC data set, obtained by executing IFCEREP1.
•IDCAMS LISTCAT output for the ICF (VSAM) catalog that contains the DB2 subsystem catalog that defined the failing index or table.
•VTOC listing of the disk volume on which the failing operation occurred.
•Listing of the results produced by the SQL statements.
•SYSPRINT output, including JCL, for the application program or batch job, and system messages that were issued. Make sure that MSGLEVEL=(1,1) on the JOB statement so that all diagnostic information is sent to SYSPRINT.
•Definitions of views and tables involved in the error.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 1:25 pm    Post subject: Re: Reply to: SQLCODE -904
Reply with quote

senthilnathanj wrote:
May be the Problem will occur the following reasons:
•Console output from the system on which the job was run, and a listing of the SYSLOG data set for the period of time spanning the failure.
•SVC dump (or system dump), taken to SYS1.DUMPxx data set, as result of an operator-initiated dump command or SLIP trap exit.
•SYSABEND, SYSUDUMP, or SYSMDUMP output.
•Listing of SYS1.LOGREC data set, obtained by executing IFCEREP1.
•IDCAMS LISTCAT output for the ICF (VSAM) catalog that contains the DB2 subsystem catalog that defined the failing index or table.
•VTOC listing of the disk volume on which the failing operation occurred.
•Listing of the results produced by the SQL statements.
•SYSPRINT output, including JCL, for the application program or batch job, and system messages that were issued. Make sure that MSGLEVEL=(1,1) on the JOB statement so that all diagnostic information is sent to SYSPRINT.
•Definitions of views and tables involved in the error.


Hi,

What you have mentioned is the QW 00D70014
Code:

Collect the following diagnostic items listed in Appendix C, "Problem       
determination" in item -PROBLEM_DETERMI : 1, 2, 3, 4, 5, 10, 12, 14, 32, 35.


QW -PROBLEM_DETERMI
Code:

Resource types                                                             
                                                                           
1.  Console output from the system on which the job was run, and a listing 
    of the SYSLOG data set for the period of time spanning the failure.     
2.  Dynamic dump, taken to SYS1.DUMPxx data set, by DB2 (04E/04F abends).   
3.  SVC dump (or system dump), taken to SYS1.DUMPxx data set, as result of 
    an operator initiated dump command or SLIP trap exit.                   
4.  SYSABEND, SYSUDUMP, or SYSMDUMP output.                                 
5.  Listing of SYS1.LOGREC data set, obtained by executing IFCEREP1.       
.
.
.
.


I don't know how to capture all the diagnostics items. Please tell me how to capture them and analyse those values to the get a reason of abend.

Thanks in advance
Gylbharat.
Back to top
View user's profile Send private message
rakesha.hg

Active User


Joined: 21 Mar 2008
Posts: 161
Location: bangalore

PostPosted: Wed Dec 14, 2011 2:01 pm    Post subject:
Reply with quote

gylbharat wrote:
I checked the Database and Tablespaces. All were in RW mode.


indexes??
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 14, 2011 2:16 pm    Post subject:
Reply with quote

Maybe there is just not enough space available. Check with your SMS guy.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 2:41 pm    Post subject:
Reply with quote

GuyC wrote:
Maybe there is just not enough space available. Check with your SMS guy.


Where the space is not available --> LDS dataset?

Indexes are also in RW.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 14, 2011 2:52 pm    Post subject:
Reply with quote

I'm no space person.
I do know that depending on SMS definitions certain VSAMs can only use certain volumes (=SMS storage group) and sometimes they have to add volumes or tell us to clean up.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 2:59 pm    Post subject:
Reply with quote

ok... Thanks GuyC...
Back to top
View user's profile Send private message
db2sysdba.zos

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Wed Dec 14, 2011 3:55 pm    Post subject: Hi gylbharat,
Reply with quote

Tell me whether your database is SMS managed or not?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 5:17 pm    Post subject:
Reply with quote

Yes... The database is SMS managed.
Back to top
View user's profile Send private message
db2sysdba.zos

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Wed Dec 14, 2011 5:28 pm    Post subject:
Reply with quote

Fire this command in the console

DD CLEAR,DSN=ALL

Then check the target tablespace status,which you are going to load. ensure that RW mode.

then execute the LOAD.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 5:43 pm    Post subject:
Reply with quote

db2sysdba.zos wrote:
Fire this command in the console

DD CLEAR,DSN=ALL

Then check the target tablespace status,which you are going to load. ensure that RW mode.

then execute the LOAD.


TSO console??
Back to top
View user's profile Send private message
db2sysdba.zos

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Wed Dec 14, 2011 5:48 pm    Post subject:
Reply with quote

DD CLEAR,DSN=ALL

It's a MVS Command. Get your zO/S system administrator help, before firing this command in zO/S console.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 5:50 pm    Post subject:
Reply with quote

Hi,

I will not be able to get the system admin help on this... As he would be asking too many questions... And also he if from client side.
Back to top
View user's profile Send private message
db2sysdba.zos

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Wed Dec 14, 2011 6:07 pm    Post subject:
Reply with quote

I will clear you why I asked you to fire that command.

you are getting this error code. 00D70014

Since your database is SMS managed and its extends is below 251 there is nothing wrong with your extends or space( assumed that your storage group is SMS managed and there eonugh volumes for spanning).

So, conclusion is since you had the error code 00D70014 ,

Problem is with SYS1.DUMPxx.

If it's a testing region and if you have the authority to fire the MVS command, you can fire the above mentioned command there by you cal clear the DUMP DSN.

Then make sure your TS & Indexspace are in RW mode, before go ahead with LOAD Utility.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Dec 14, 2011 6:47 pm    Post subject:
Reply with quote

Thanks... I will check if i am able to run that command.

Before firing the command, how can we check if actual issue is with the SYS1.DUMPxx?
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 SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
No new posts Need to understand SQLCODE = 12 rohanthengal DB2 3 Thu Nov 05, 2015 11:22 am
No new posts SQLCODE -922 in Batch-Cics program tr... kishpra CICS 2 Thu Sep 10, 2015 12:36 pm
No new posts SQLCODE -450 for stored procedure spoorni DB2 3 Fri Jan 16, 2015 2:45 am
No new posts -81 SQLCODE returned when Remote Stor... kameswaramanoj.Akella DB2 1 Wed Dec 03, 2014 5:36 am


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