Your complete guide to reader questions and author responses.
New This Month
DB2 DBA columnist Robert Catterall answers a reader's question about tuning DB2 for z/OS buffer pools. Read more expert Q&As in the archives.
Tuning Up for OLTP and Data Warehousing
What's the impact of changing the locksize for tables from the default of ROW to
TABLE in a read-only data warehouse?
with Scott Hayes
When is intra-parallelism appropriate in a DSS environment?
with Scott Hayes
Simplifying Warehouse
Management
Is DB2 v.7.2 compatible with the SQL interface and drivers of DB2 OLAP Server?
What about the integration with DB2 OLAP Server?
with Eric Michiels
Certification
How many DB2 certifications are there? Where
can I find a course that prepares me for the first certification?
with Susan Visser
DB2 DBA
In a DB2 for z/OS system that comprises tens of thousands of tables (and even more indexes), how can I find out which database tables require maintenance without running a lot of reports?
with Uri Lifshitz
When using COBOL DB2 stored procedures on z/OS running under the WLM address space, would you recommend a plain COBOL sub-routine call or a SQL CALL?
with Robert Catterall
When a program contains a static SQL statement that references an unqualified table name, how can you know which table will be accessed when your database includes several different tables (same structure, different data) that have the same unqualified name but different high-level qualifiers (such as different table owner IDs)?
with Robert Catterall
How do we submit a requirement for a technical change to DB2 for z/OS?
with Roger Miller
Where can I find a code snippet for accessing DB2 for z/OS stored procedures in Java using input parameters?
with Robert Catterall
What's your recommendation for performing DB2 v.7 traces running under z/OS V1R4 in a production environment? Our tracing, implemented via zPARMs, consumes precious CPU cycles and real memory and also fills up the SYS1.MANx datasets with type 100-102 records in the SMF infrastructure.
with Robert Catterall
Could you provide some recommendations for conserving disk space on a mainframe?
with Robert Catterall
What's the best way to implement stored procedures as the communication mechanism to a mainframe?
with Robert Catterall
Do you know of any products that use DRDA without a local DB2 in an MVS (OS/390, Z/OS) environment?
I'm looking for a way to isolate DB2 into its own LPAR to take advantage of IBM's Workload License
Charges (WLC) pricing.
with Robert Catterall
How do you determine whether to use a materialized
query table (MQT) using DB2 Cube Views or DB2 OLAP Server for a particular analytic need?
with Robert Catterall
Your article in the DB2 Magazine about DB2 platform differences was very good. The one
thing missing was how the DBA roles differ between Linux, Unix, and Windows (LUW) and the mainframe.
In the LUW platform, the DBA does everything; in the mainframe environment, the roles are separated
into an application DBA and a system DBA.
I was hoping you might have some paper that talked about how these roles are different between LUW and the mainframe.
with Robert Catterall
Why does PROGRAM TYPE SUB minimize the number of moduel reloads in in the
WLM-managed stored procedures address spaces, and are there any drawbacks to
that approach?
with Robert Catterall
Our Web transactions aren't yet ready for the concepts discussed in your column
"Do You MQ." Would the DB2 Governor work as an interim solution that would let
us assign higher priority for online requests and lower priority for batch?
with Robert Catterall
Do you have to use WLM-managed stored procedure execution environments if you
want to use one DB2 for z/OS stored procedure program for multiple different
schemas in a database — or are there other options?
with Robert Catterall
What steps can we take to achieve faster group restart for a four-member data sharing group?
with Robert Catterall
Do the DB2 v.7 for z/OS ZPARMS SRTPOOL and MAXRBLK refer to the global respective sort spaces or to the SORT and RID SORT pools for each individual user?
with Robert Catterall
Would you recommend using STOGROUPs with SMS by using the VOLUME('*') parameter in the STOGROUP definition and letting SMS do the actual placing?
with Robert Catterall
I'm the backup DBA in a DB2 shop where no one has changed a DB2 option in 18 years. We're having problems with a
Nomad program and a CICS online program butting heads. Do you have any recommendations? Do I need professional
help?
with Robert Catterall
Can you define DB2 catalog and directory data sets to be under SMS control?
with Robert Catterall
How do I determine whether or not stored procedures marked as STAY RESIDENT are actually reloading? All of our modules so marked were created as PROGRAM TYPE MAIN,
which is the default.
with Robert Catterall
Can the Distributed Data Facility handle a project that uses dynamic SQL and produces
3,000,000 logs daily with 1,000 concurrent users?
with Robert Catterall
What are the maximum limits for segmented and partitioned tablespaces and,
how can I convert a nonpartitioned tablespace to a partitioned one without a
down time window?
with Robert Catterall
I've been wrestling with a performance problem for several weeks (high class 3
wait times in an accounting report). Should I increase the size of our virtual
buffer pools and add hiperpools?
with Robert Catterall
With DB2 Query Parallelism, I've heard that you shouldn't set MAX DEGREE greater than the number of CPs. I don't agree, but I wonder whether setting MAX DEGREE high will have an adverse effect on other users. Any
advice?
with Robert Catterall
How do you generate random numbers in DB2?
with Robert Catterall
I have a good
understanding of DB2 as an application programmer, but I'm not up on the latest with UDB. Can you suggest a good book that will bring me up to date?
with Robert Catterall
Do you know of any way to detect unused indexes in DB2 (for z/OS and OS/390)? Are there tools or techniques to facilitate identification of such indexes?
with Robert Catterall
Why do 4GB DB2 objects max out at
5,826 cylinders of 3390 storage?
with Robert Catterall
Does DASD striping work with DB2 for z/OS and OS/390?
with Robert Catterall
A SQL trace of a packaged application that had been experiencing contention
errors indicates that it's still trying to acquire a tablespace lock despite changes I
made to (LOCKSIZE ROW, LOCKMAX 0). Should this be happening?
with Robert Catterall
I'm new to DB2 and have some questions about DB2 and Websphere MQ.Is the queue
integrated within DB2 or is it a separate operating system-specific type of
queue like a global section held in memory? Also, can you explain what CICS is?
with Robert Catterall
How do I query databases located on two different servers?
with Robert Catterall
Have you really moved small domain tables to CICS?
with Robert
Catterall
When are locks released after package and plan execution?
with Robert Catterall
I've been told I must use DB2 Connect from a Java application to extract data from DB2 tables.
Do I need to worry about the Comp-3 data and how it will be interpreted by an ASCII machine?
with Robert Catterall
Is DDF an option for data sharing between four subsystems? The database will be
used
for read-only batch jobs.
with Robert Catterall
In your column "When
Less is More," why not place the table in question in its own tablespace?
with Robert Catterall
Should you use partitions to separate newer (more frequently accessed) data from
older data in a large table?
with Robert Catterall
What are your recommendations for setting
policies governing ad hoc reporting using QMF, Crystal Reports, and so on?
with Robert Catterall
Non-partitioned indexes (NPIs) are
making inserts to one of my large tables time consuming. How would you deal with
NPIs in this situation?
with Robert Catterall
Where can I find an example of using stored procedures
with DB2 and JDBC?
with Robert Catterall
Two questions about DB2 accounting
reports
with Robert Catterall
Is there a rhyme, reason, formula, or
theory that can be used to determine the optimal number of tablespace partitions and
their optimal size?
with Robert Catterall
You write that there's no good reason
to use the simple tablespace. Why not?
with Robert Catterall
How do you leverage 160 I/O channels in relationship to
partitioning?
with Robert Catterall
How do I control which schema I want to use when I am using more than one in a program?
with Robert Catterall
We are already at the 2G limit for the DBM1 address
space and our machine is only an R95. Do you really have the same need to separate data sets
by volume as much with the new DASD technologies?
with Robert Catterall
How can I refer a global temporary table
defined in the calling stored procedure in the called stored procedure?
with Robert Catterall
Can you have too many DB2 package collections?
with Robert Catterall
IMS vs. DDF
with Robert Catterall
Alternatives to Batch Processing
with Robert Catterall
The Database Security
Blanket
BDC Brain Teasers
with Paul Zikopoulos
Top 10 Performance Tips
Is there a rule of thumb for determining the optimal number of actions to perform before doing a commit?
with Scott Hayes
What is a "high water mark" and what does it tell you? & more
with Scott Hayes
How can you speed up database connections?
with Scott Hayes
Ever wonder what prefetch size you should use with RAID-5?
with Scott Hayes
Programmers Only
What kind of a word is "sargable?"
with Bonnie Baker
Miscellaneous
We're implementing a Web services-based application that uses the Distributed Data Facility (DDF). Initial monitoring analysis shows very high CPU enclave consumption, which limits the number of users we can support without degrading the performance of other critical online applications. IBM recommended caching dynamic SQL statements, but that approach didn't provide enough relief to support the number of users required. What else can we try to reduce the impact of DDF enclave CPU activity?
with Robert Catterall
We're considering going to DB2 data sharing to reduce planned outages. In your
DB2 Magazine article, "Staying Power," you write that "an
SQL statement executed in our data sharing group will consume about 7.3 percent
more in-DB2 CPU time than it would if it were executed in a stand-alone DB2 for
z/OS subsystem." What SMF and/or TMON fields should I apply the 7.3 percent to
in order to estimate my increase in CPU utilization?
with Roger E. Sanders
In your recent column on split mirroring [Distributed DBA, Quarter 4, 2005], you write that the SET WRITE RESUME command must be executed from the same database connection that has executed the SET SUSPEND command. What do you do when this session has abended? (The same problem exists with the QUIESCE command.) Also, what are the "appropriate system-level commands" to split the mirror? What has to be split — the instance profile directory, database directory, or tablespace file systems?
with Roger E. Sanders
In DB2 UDB for Linux, Unix, and Windows v.8, how do you calculate table size for a DMS tablespace
(assuming the table has all data types)?
with Roger E. Sanders
How (or when) does DB2 decide to change from row-level to table-level locking,
and how can I avoid deadlocks in my environment without increasing application
run time?
with Roger E. Sanders
What exactly do the terms plan and package mean in a DB2 UDB for Linux,
Unix, and Windows context? Is it possible to run an application with only a package?
with Roger Sanders
What exactly do the terms plan and package mean in a DB2 UDB for Linux,
Unix, and Windows context? Is it possible to run an application with only a package?
with Roger Sanders
Where can I find documentation that will help me understand all the information
I get when I take a snapshot?
with Roger Sanders
Would you recommend turning on
intra-parallelism on a DB2 Enterprise-Extended Edition (EEE) data warehouse database?
with Scott Hayes
More details about "The OLAP-Aware Database," please.
with Jeff Jones
Why change the memory optimization setting on Windows
2000 if you've already set the DB2 registry
variable DB2NTNOCACHE to ON ?
with Chris Fierros
Two questions about DB2 and Linux
with Paul Zikopoulos
How do I inquire about crash recovery status?
with IBM
We're upgrading from V4R4 to V5R1 of the operating system. We currently use triggers but wonder
if there are any major concerns with triggers on
V5R1.
with Michael Pavlak
I tried entering the RUNSQLSTM , CRTSQLCI , and CRTSQLRPG commands and got messages saying the
commands are not found. What library are these commands located in, or what feature of OS/400 do I
need to use SQL?
with Michael Pavlak
Will combining two servers into one and increasing the number of instances on a
server create performance problems with two large
databases?
with Paul Zikopoulos
