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
 

 

What error will be thrown by DB2 when tablspace becomes Full

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

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Fri Jul 21, 2006 8:17 pm    Post subject: What error will be thrown by DB2 when tablspace becomes Full
Reply with quote

Dear Members,

Hello !!

I have a query in DB2. Suppose we have a table and while we are doing a mass load using a program what error will be thrown by DB2 when the tablspace becomes full.

While doing the same i got the following error

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON
00D70027, TYPE OF RESOURCE 00000220, AND RESOURCE NAME DBTEST.DSNDBC.DB101.I1010311.J0001-.A001
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -110 13172746 0 13820609 -740941815 14090279 SQL DIAGGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF92' X'00C9000A' X'00000000' X'00D2E2C1' X'D3D62009' X'00D70027'
SQL DIAGNOSTIC INFORMATION


I concluded this could be due to tablespace full. Is my conculsion correct ??
Back to top
View user's profile Send private message

ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Fri Jul 21, 2006 9:00 pm    Post subject: Re: What error will be thrown by DB2 when tablspace becomes
Reply with quote

Jagan,

This is the SQLCODE you get when the tablespace is full.

SQL0289N Unable to allocate new pages in table space ?<TABLESPACE-NAME>?.

From your Scenario -904 may be caused by index problem
INDEX NEEDED to be resized TO HANDLE THE large DATA.

Contact your DBA for more Information........

Thanks,
Ravi...
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Tue Jul 25, 2006 12:31 pm    Post subject:
Reply with quote

If you have appropriate levels of authority-

Try out these queries-

Select STOGROUP from SYSIBM.SYSDATABASE
where NAME=DSNDBC ; DSNDBC is the name of the database that is mentioned in the error message above.

Having got the storegroup name try the next quesry below:

Select VOLID from SYSIBM.SYSVOLUMESwhere name='stogrp';

'stogrp' is the result you got from previous query.

if one or more rows of asterisks are returned then it means SMS is used.make sure that at least 2 rows are returned.

Is only one row is returned , run alter command to add volumes to this group

ALTER STOGROUP 'strgrp' ADD VOLUMES(*);
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Tue Jul 25, 2006 2:37 pm    Post subject: Re: What error will be thrown by DB2 when tablspace becomes
Reply with quote

Hi Ravi and Anamika,

When i check with my DBA he went straight to the dataset using 3.4 option
and the following is displayed:

DBTEST.DSNDBC.DB101.I1010311.J0001.A001 *VSAM*
DBTEST.DSNDBD.DB101.I1010311.J0001.A001 AT2B01


and looked at the data set information

which displayes as below

Data Set Information
Command ===>
More: +
Data Set Name . . . . : DBTEST.DSNDBD.DB101.I1010311.J0001.A001

General Data Current Allocation
Management class . . : **None** Allocated tracks . : 6
Storage class . . . : **None** Allocated extents . : 1
Volume serial . . . : AT2B01
Device type . . . . : 3390
Data class . . . . . : **None** Current Utilization
Organization . . . : VS Used tracks . . . . : ?
Record format . . . : ? Used extents . . . : ?
Record length . . . : ?
Block size . . . . : ?
1st extent tracks . : 6
Secondary tracks . : 0
Data set name type :

Creation date . . . : 2004/04/27 Referenced date . . : 2006/07/21
Expiration date . . : ***None***

Then he told the secondary track is 0 which should actually be increased and this would resolve the problem. I am waiting for his action.

But I am inquisite to know what is this file and how is it linked to the DB2 table which i am referring. What i am aware is I1010311 is an index to the table that becames full.

Pls let me know more in depth.
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 26, 2006 9:25 am    Post subject: Re: What error will be thrown by DB2 when tablspace becomes
Reply with quote

Jagan,

You want to know about the DB2 Behind the Scenes....................
The following Link is angood article by Craig Mullin's about it please read it......

http://www.dbazine.com/db2/db2-mfarticles/mullins-scenes


Thanks,
Ravi...
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Wed Jul 26, 2006 11:56 am    Post subject:
Reply with quote

A couple of things here-


1. DB2 does not store table and index data within the same dataset.It allocates atleast one dataset for each index that you create.

2. The VSAM file system used by Db2 does not extend infinitely and therefore the space gets used up when you have heavy insert/load activities.Therefore you must specify adequate Primary and secondary(increment) for each table and index space by specifying PRIQTY and SECQTY within your create index statement.If secondary quantity is omitted , than a a very small default size is allocated which obviously is not enough.

I think in your case for your indexspace/dataspace the secondary quantity has been omitted.
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 Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts Full health checks of IMS databases ashek15 IMS DB/DC 1 Sat Apr 01, 2017 8:34 am
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm


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