Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Running queries against OLTP Database

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 6
Location: USA

PostPosted: Tue Jul 14, 2015 4:51 pm    Post subject: Running queries against OLTP Database
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Jul 14, 2015 8:29 pm    Post subject:
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: 6
Location: USA

PostPosted: Tue Jul 21, 2015 12:06 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Jul 21, 2015 8:56 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Syntax for running batch history repo... polymathtarun CA Products 1 Tue Jun 21, 2016 1:51 pm
No new posts Database access from COBOL amitmahalkar DB2 1 Wed Jan 27, 2016 11:33 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us