4K RESOURCE UNAVAILABLE

Grace Chen

4K RESOURCE UNAVAILABLE
Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform
a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can anyone
explain this for me?
Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67
cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.


Grace Chen





---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Stephen Vagnier

Re: 4K RESOURCE UNAVAILABLE
(in response to Grace Chen)
Grace,
you may have a DB2 maintenance issue. Check your
maintenance level and see if you get any hits on IBM's web site.
I would also define more 4K tablespaces and spread them
out over several volumes.

Hope this helps...

****************************************
Steve Vagnier
American Electric Power
Data Services
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677




Grace Chen
<[login to unmask email] To: [login to unmask email]
O.CA> cc:
Sent by: DB2 Data Subject: 4K RESOURCE UNAVAILABLE
Base Discussion
List
<[login to unmask email]
ORG>


12/05/2003 10:51
AM
Please respond to
DB2 Database
Discussion list
at IDUG






Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which
perform a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can
anyone explain this for me?


Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and
67 cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.





Grace Chen





---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rob Johns

Re: 4K RESOURCE UNAVAILABLE
(in response to Stephen Vagnier)
If I understand the problem correctly, this is a sort work tablespace. That
being the case, I wouldn't specify a secondary allocation for the sort
tablespaces. If there is no secondary, it should use the next available
sort tablespace rather than attempting to extend the dataset.


-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:51 AM
To: [login to unmask email]
Subject: 4K RESOURCE UNAVAILABLE



Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform
a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can anyone
explain this for me?

Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67
cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.



Grace Chen




----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: 4K RESOURCE UNAVAILABLE
(in response to Rob Johns)
You might try a delete/define of the underlying dataset. Maybe it was
messed up when it filled up. You should have more than one defined anyway.
I've never heard of having just one.

Tina


-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 10:51 AM
To: [login to unmask email]
Subject: 4K RESOURCE UNAVAILABLE



Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform
a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can anyone
explain this for me?

Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67
cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.



Grace Chen




----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: 4K RESOURCE UNAVAILABLE
(in response to Tina Hilton)
I just took a look at the other responses you have already recieved. Did you do a -dis thread(*) and ensure that the thread is no longer running? Is there some other query running that is eating up your sortwork space? If the queries are not running your sortwork dataset should have been freed up. Also, as some of the others stated, its not a good idea to define secondary allocation for your sort tablespaces, and you should have a few. The size should be based on what is reasonably the largest query you think that performs a sort.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/5/03 10:51:24 AM >>>

Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform a big table scan.
This user found what he did was wrong and stop it right away. However, the query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate and release the 4K space so that other short query can use it. Can anyone explain this for me?
Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67 cylinders scondary. When the problem happened, the volumn which this tablespace defined was full. No more space for extension.

Grace Chen



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Grace Chen

Re: 4K RESOURCE UNAVAILABLE
(in response to Dave Nance)
Dave,
I did -dis thread(*) and that thread no longer running. At that moment, we
only had some LDAPSRV threads which did nothing and 2 CICS thread which idle
there.
My question is if a thread couldn't get 4K temp space anymore, does it fail
automatically? And if it is, does it release 4K temp space? In this case,
it should not cause the DB2 subsystem stuck there. Is there any tool that
I can see 4K temp space usage by each thread?
I got information about don't specify secondary extension to temp space.
But I don't know why?
What's the difference between define two big temp space datasets without
secondary extension and two small temp space datasets with secondary
extension?

Thanks,
Grace



-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:24 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


I just took a look at the other responses you have already recieved. Did
you do a -dis thread(*) and ensure that the thread is no longer running? Is
there some other query running that is eating up your sortwork space? If the
queries are not running your sortwork dataset should have been freed up.
Also, as some of the others stated, its not a good idea to define secondary
allocation for your sort tablespaces, and you should have a few. The size
should be based on what is reasonably the largest query you think that
performs a sort.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/5/03 10:51:24 AM >>>


Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform
a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can anyone
explain this for me?

Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67
cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.



Grace Chen




----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the latest
on IDUG conferences at http://conferences.idug.org/index.cfm This message,
including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: 4K RESOURCE UNAVAILABLE
(in response to Grace Chen)
If you have 2 with secondary allocations, and they always take the secondary
extents, then why not just use the total space for the primary and have a
secondary of 0? Unless you routinely delete/define them to free up the
extents, you're using the space anyway. At my current job we don't use
secondary extents. At my previous job we had a client/server application
that generated the SQL and often generated poor performing SQL that required
a lot of space. They insisted I use secondary extents to give it more of a
chance to grow when these "bad" sql statements were executed. I only did
this on the last one, though.

-----Original Message-----
From: Grace Chen [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 12:56 PM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


Dave,
I did -dis thread(*) and that thread no longer running. At that moment, we
only had some LDAPSRV threads which did nothing and 2 CICS thread which idle
there.
My question is if a thread couldn't get 4K temp space anymore, does it fail
automatically? And if it is, does it release 4K temp space? In this case,
it should not cause the DB2 subsystem stuck there. Is there any tool that
I can see 4K temp space usage by each thread?
I got information about don't specify secondary extension to temp space.
But I don't know why?
What's the difference between define two big temp space datasets without
secondary extension and two small temp space datasets with secondary
extension?

Thanks,
Grace



-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:24 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


I just took a look at the other responses you have already recieved. Did
you do a -dis thread(*) and ensure that the thread is no longer running? Is
there some other query running that is eating up your sortwork space? If the
queries are not running your sortwork dataset should have been freed up.
Also, as some of the others stated, its not a good idea to define secondary
allocation for your sort tablespaces, and you should have a few. The size
should be based on what is reasonably the largest query you think that
performs a sort.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/5/03 10:51:24 AM >>>


Hi List,
We are running DB2 V7 on Z/OS 1.4.
We had 4K RESOURCE UNAVAILABLE yesterday and no one can do anything.
We found that it was caused by a user who ran a Crystal Report which perform
a big table scan.
This user found what he did was wrong and stop it right away. However, the
query in DB2 was still running.
Then we had this message:
12.22.59 STC03614 DSNT501I -DBP1 DSNIXWKF RESOURCE UNAVAILABLE 680
680 CORRELATION-ID=CRW32.EXE
680 CONNECTION-ID=SERVER
680 LUW-ID=GA0902DE.F4B3.031203185350=5271
680 REASON 00C900A5
680 TYPE 00000230
680 NAME 4K
12.22.59 STC03614 DSNT501I -DBP1 DSNIWKFL RESOURCE UNAVAILABLE 681
681 CORRELATION-ID=CRW32.EXE
681 CONNECTION-ID=SERVER
681 LUW-ID=GA0902DE.F4B3.031203185350=5271
681 REASON 00C90084
681 TYPE 00000230
681 NAME 4K
Since then no one can get into DB2 anymore. They all got DSNT501I message
like above.
My question is :
This BIG query already got 4K resource unavailable and it should terminate
and release the 4K space so that other short query can use it. Can anyone
explain this for me?

Another thing is how can I prevent this happened again?
We only had one 4K tablespace defined and it has 67 cylinders primary and 67
cylinders scondary. When the problem happened, the volumn which this
tablespace defined was full. No more space for extension.



Grace Chen




----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the latest
on IDUG conferences at http://conferences.idug.org/index.cfm This message,
including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cathy Taddei

Re: 4K RESOURCE UNAVAILABLE
(in response to Tina Hilton)
I do not know why DB2 would fail to release sortwork/temp resources after a
thread has terminated. Before anyone abended, you probably got return code
00D70027 in the ssidMSTR log for your 4K tablespace. Were there any other
messages around that time, besides those for RC's 00D70027, 00C900A5, and
00C90084? Were DSNDB07 and your 4K tablespace still in R/W status? DB2
should not get "stuck" for the lack of sortwork space. I can stop all
tablespaces in DSNDB07, and still run queries that don't require sorting.
However, if one did require sorting, it would definitely abend.

There are two difference between defining two small tablespaces with
extents, and two large ones without:
1) the small tablespaces take up less room initially, and
2) the small ones may not be able to grow because of lack of space or
fragmentation on the volume, so your total space may turn out to be lower
than what you forecast.

Personally, I do not put extents on 4K tablespaces, and I always have more
than one of them, even for my smallest subsystem. Having more than one can
be a minor performance boost, and I like to avoid having a single point of
failure.
HTH,
Cathy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Grace Chen
Sent: Friday, December 05, 2003 9:56 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


Dave,
I did -dis thread(*) and that thread no longer running. At that moment, we
only had some LDAPSRV threads which did nothing and 2 CICS thread which idle
there.
My question is if a thread couldn't get 4K temp space anymore, does it fail
automatically? And if it is, does it release 4K temp space? In this case,
it should not cause the DB2 subsystem stuck there. Is there any tool that
I can see 4K temp space usage by each thread?
I got information about don't specify secondary extension to temp space.
But I don't know why?
What's the difference between define two big temp space datasets without
secondary extension and two small temp space datasets with secondary
extension?

Thanks,
Grace

-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:24 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


I just took a look at the other responses you have already recieved. Did
you do a -dis thread(*) and ensure that the thread is no longer running? Is
there some other query running that is eating up your sortwork space? If the
queries are not running your sortwork dataset should have been freed up.
Also, as some of the others stated, its not a good idea to define secondary
allocation for your sort tablespaces, and you should have a few. The size
should be based on what is reasonably the largest query you think that
performs a sort.

Dave Nance
First Health Services, Corp.
(804)527-6841



------------------------------------------------------------------------------

This email is confidential and may be legally privileged.

It is intended solely for the addressee. Access to this email by anyone else,
unless expressly approved by the sender or an authorized addressee, is
unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution
or any action omitted or taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error, please
contact the sender, delete this e-mail and destroy all copies.

=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Grace Chen

Re: 4K RESOURCE UNAVAILABLE
(in response to Cathy Taddei)

Thanks for all of you who replied my question.
I found a PTF UQ78946 for DB2 V7 which fix the problem that DB2 doesn't
release 4K temp work space after the space extension fail. I believe
UQ78945 is for DB2 V6.

Grace


-----Original Message-----
From: Taddei, Cathy [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 3:48 PM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


I do not know why DB2 would fail to release sortwork/temp resources after a
thread has terminated. Before anyone abended, you probably got return code
00D70027 in the ssidMSTR log for your 4K tablespace. Were there any other
messages around that time, besides those for RC's 00D70027, 00C900A5, and
00C90084? Were DSNDB07 and your 4K tablespace still in R/W status? DB2
should not get "stuck" for the lack of sortwork space. I can stop all
tablespaces in DSNDB07, and still run queries that don't require sorting.
However, if one did require sorting, it would definitely abend.

There are two difference between defining two small tablespaces with
extents, and two large ones without:
1) the small tablespaces take up less room initially, and
2) the small ones may not be able to grow because of lack of space or
fragmentation on the volume, so your total space may turn out to be lower
than what you forecast.

Personally, I do not put extents on 4K tablespaces, and I always have more
than one of them, even for my smallest subsystem. Having more than one can
be a minor performance boost, and I like to avoid having a single point of
failure.
HTH,
Cathy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Grace Chen
Sent: Friday, December 05, 2003 9:56 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


Dave,
I did -dis thread(*) and that thread no longer running. At that moment, we
only had some LDAPSRV threads which did nothing and 2 CICS thread which idle
there.
My question is if a thread couldn't get 4K temp space anymore, does it fail
automatically? And if it is, does it release 4K temp space? In this case,
it should not cause the DB2 subsystem stuck there. Is there any tool that
I can see 4K temp space usage by each thread?
I got information about don't specify secondary extension to temp space.
But I don't know why?
What's the difference between define two big temp space datasets without
secondary extension and two small temp space datasets with secondary
extension?

Thanks,
Grace

-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:24 AM
To: [login to unmask email]
Subject: Re: 4K RESOURCE UNAVAILABLE


I just took a look at the other responses you have already recieved. Did
you do a -dis thread(*) and ensure that the thread is no longer running? Is
there some other query running that is eating up your sortwork space? If the
queries are not running your sortwork dataset should have been freed up.
Also, as some of the others stated, its not a good idea to define secondary
allocation for your sort tablespaces, and you should have a few. The size
should be based on what is reasonably the largest query you think that
performs a sort.

Dave Nance
First Health Services, Corp.
(804)527-6841


----------------------------------------------------------------------------
--

This email is confidential and may be legally privileged.

It is intended solely for the addressee. Access to this email by anyone
else, unless expressly approved by the sender or an authorized addressee, is
unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution
or any action omitted or taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error, please
contact the sender, delete this e-mail and destroy all copies.

===
==


----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm