Index creation problem

Mohammed Nayeem

Index creation problem
Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after dropping
index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
00000230, AND RESOURCE NAME 4K
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Even I tried after increasing space in secondary & primary allocations , but
still I got same error. Is my ID got screwed up ??? With my colleagues id ,
same job
is worked well.

Earlier I was'nt getting such messages whenever I create new index's.

I appreciate your input on this.

Thanks
Nayeem



Max Scarpa

Re: Index creation problem
(in response to Mohammed Nayeem)
HI.....

I think your index is too big and your workfiles are not big enough. Try
CREATE INDEX ........DEFER YES and use

RECOVER/REBUILD INDEX with enough sort files (SORTNUM > 12)

HTH

Max Scarpa



Linda Billings

Re: Index creation problem
(in response to Helio Pimpinato da Rocha)
Hello, Mohammed,

The 00000230 indicates that a temporary workfile is not available. The
SQLSTATE indicates "..Virtual storage or database resource is not
available.". Something is being dynamically allocated by your SQL, probably
in DSNDB07. Check to see if there is a problem there.


Linda Billings
Enterprise Systems Programmer
Info-Tech Services
Department of Administration
State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde



-----Original Message-----
From: Mohammed Nayeem [mailto:[login to unmask email]
Sent: Tuesday, January 02, 2001 10:48 AM
To: [login to unmask email]
Subject: Index creation problem


Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after
dropping
index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
00000230, AND RESOURCE NAME 4K
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Even I tried after increasing space in secondary & primary allocations ,
but
still I got same error. Is my ID got screwed up ??? With my colleagues id ,
same job
is worked well.

Earlier I was'nt getting such messages whenever I create new index's.

I appreciate your input on this.

Thanks
Nayeem








Edward Vassie

Re: Index creation problem
(in response to Linda Billings)
You may be running out of space in the sortwork database (DSNDB07).

Look in the JES log for the MSTR address space. If you have run out of sort
space you will see some messages.

There are two ways to get round this.

1) Increase the size or number of your sort work tablespaces.

This should only be done if normal DML is also reporting a shortage of
space.

If the only time you run out of space is during index creation there is a
better way to solve this problem.

2) When you create the index, use the DEFER option.

This will create the index definition, but will not populate the index.

To populate the index, run a batch job to REBUILD INDEX nnn

The advantage of using a batch job is that the sorting is done outside of
DB2. You have to specify the sort space you need in the JCL. Look in the
Utilities manual for details.

In this way, you can create very large indexes without impacting on other
users of DB2.

From Edward Vassie...


-----Original Message-----
From: Mohammed Nayeem [mailto:[login to unmask email]
Sent: 02 January 2001 16:48
To: [login to unmask email]
Subject: Index creation problem


Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after
dropping
index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
00000230, AND RESOURCE NAME 4K
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Even I tried after increasing space in secondary & primary allocations ,
but
still I got same error. Is my ID got screwed up ??? With my colleagues id ,
same job
is worked well.

Earlier I was'nt getting such messages whenever I create new index's.

I appreciate your input on this.

Thanks
Nayeem








Bruno CEI

Re: Index creation problem
(in response to Edward Vassie)
Mohammed,

I think that you are running out of space on your work database (DSNDB07).

You can choose either to increase the size of the work space available to
DB2 or, which is probably best, create the index with the DEFER option and
then use the REBUILD utility to populate it.

Bruno



-----Original Message-----
From: Mohammed Nayeem [mailto:[login to unmask email]
Sent: 02 January 2001 16:48
To: [login to unmask email]
Subject: Index creation problem


Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after
dropping
index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
00000230, AND RESOURCE NAME 4K
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Even I tried after increasing space in secondary & primary allocations ,
but
still I got same error. Is my ID got screwed up ??? With my colleagues id ,
same job
is worked well.

Earlier I was'nt getting such messages whenever I create new index's.

I appreciate your input on this.

Thanks
Nayeem







___________________________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
Sema Group.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.

If you have received this email in error please notify the Sema Group
Helpdesk by telephone on +44 (0) 121 627 5600.
___________________________________________________________________________



Mohammed Nayeem

Re: Index creation problem
(in response to Bruno CEI)
Thanks to all of you for responding quickly.

Yes I am using DEFER NO option .



Mohammed Nayeem

Re: Index creation problem
(in response to Mohammed Nayeem)
CREATE TYPE 2 UNIQUE INDEX INX1
ON tableX
(COL1 ASC, COL2 ASC, COL3 ASC, COL4 ASC)
USING STOGROUP XXXXXXXX
PRIQTY 200000
SECQTY 100000
ERASE NO
BUFFERPOOL BP0
CLOSE YES
DEFER NO;



Mohammed Nayeem

Re: Index creation problem
(in response to Mohammed Nayeem)
I am using db2 batch job for index creation and If I use sort files , will it
solve problem.
I am using DEFER NO option only.
Yep I need to run REBUILD INDEX once index is created for populating this index.



Helio Pimpinato da Rocha

Re: Index creation problem
(in response to Max Scarpa)
This occurs because the temporary store is small. Then create the index
wich "defer = yes ", after you'll make the recover of this index.

Ok

Helio




Mohammed Nayeem <[login to unmask email]>
Enviado Por: DB2 Data Base Discussion List <[login to unmask email]>
02/01/01 13:47
Responder a DB2 Data Base Discussion List


Para: [login to unmask email]
cc:
Assunto: Index creation problem

Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after
dropping
index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
00000230, AND RESOURCE NAME 4K
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Even I tried after increasing space in secondary & primary allocations ,
but
still I got same error. Is my ID got screwed up ??? With my colleagues id
,
same job
is worked well.

Earlier I was'nt getting such messages whenever I create new index's.

I appreciate your input on this.

Thanks
Nayeem



the