What error will be thrown by DB2 when tablspace becomes Full

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

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 ??

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

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...

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

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(*);

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

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.

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

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......

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

Thanks,
Ravi...

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

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.