IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Running queries against OLTP Database


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 19
Location: USA

PostPosted: Tue Jul 14, 2015 4:51 pm
Reply with quote

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 ?

Thanks
A programmer
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 14, 2015 8:29 pm
Reply with quote

I would suggest, please talk to DBA or get the required stats to investigate further.
Back to top
View user's profile Send private message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 19
Location: USA

PostPosted: Tue Jul 21, 2015 12:06 pm
Reply with quote

Rohit,

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 21, 2015 8:56 pm
Reply with quote

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

Code:
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
 •TMPBP32
 
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
BUFFERPOOL BP32K
SEGSIZE 32;
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
BUFFERPOOL BP32K
SEGSIZE 32;
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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Running REXX through JOB CLIST & REXX 13
No new posts What database does Jobtrac use CA Products 4
No new posts Running a Job with the Default User ID JCL & VSAM 2
No new posts Error while running web tool kit REXX... CLIST & REXX 5
No new posts Ca7 long running jobs report All Other Mainframe Topics 1
Search our Forums:

Back to Top