Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts FUSE - IBM MQ - CICS integration error Kevin Vaz CICS 1 Mon Sep 18, 2017 4:31 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us