I am Application developer, so not aware of answer to below query:
1. I have observed that at my current organisation, a single query (long running) can use entire temporary table space. Due to which other queries were not able to perform their operation and were getting resource not available error.
2. Whereas, in my earlier organisation, I have observed that entire team, was running multiple sql queries against production database to extract various statistics information without any issue. The size of database also was very large. We use to perform daily extract and there was no issue reported.
I wonder, what can be the difference in configuration at two sites ? Is there a chance that site 2 is using db2 data sharing ?
I talked to DBA, they were saying, this has been setup like this from so many years and they were not able to provide right answer.
I don't think anyone can help you specifically with this info. However, you got to atleast share what and how is your set up to us.
Something to refresh.
General database considerations
DB2 for z/OS requires the addition of two buffer pools. The following 32K buffer pools must be created by the database administrator before running the database scripts: •BP32K
DB2 for z/OS requires a TEMP database for storing declared temporary tables.•Create a dedicated STOGROUP to contain the IBM Business Monitor data.
•Create a TEMP database and a TEMP table space to contain the declared temporary tables for processing scrollable cursors. Examples are shown below.
For DB2 for z/OS version 9 and version 10 in a non-data-sharing environment, the TEMP database is DSNDB07 and is created during database installation. Temporary table spaces are added to the existing TEMP database. The following code is a representative example of a temporary table space: CREATE TABLESPACE WBITEMP IN DSNDB07
USING STOGROUP SYSDEFLT
For DB2 for z/OS version 9 and version 10 in a data-sharing environment, a WORKFILE database must be created. Only one WORKFILE database can be created per subsystem. The following code is a representative example for creating a WORKFILE database and temporary table space: CREATE DATABASE WORKTEMP AS WORKFILE STOGROUP SYSDEFLT;
CREATE TABLESPACE WBITEMP IN WORKTEMP
USING STOGROUP SYSDEFLT
For detailed information about how the TEMP database and TEMP table spaces are set up, refer to DB2 for z/OS information center. See the related link.
Note: If you are using DB2 for z/OS and you intend to use SPUFI for running the database scripts, use FTP to transfer the files to the z/OS database server. The IBM Business Monitor database scripts end with a line-feed character. The FTP server on z/OS will correctly map the line feed to an end-of-line character for the database script.
In DB2 for z/OS version 9 and 10, the work file database and TEMP databases are combined. See the DB2 for z/OS information center for the procedures and sizing recommendations for creating work file databases.
Set the RRULOCK subsystem parameter to YES for greater concurrency.
If data movement service is to be enabled, increase the number of locks per user, NUMLKUS, to at least 100,000.