Submission of Questions Knowledge Base Computer Education Techniques

Longstanding Expertise in Evolutionary IT

Common Questions - DB2

HomeDB2 Courses

Submit DB2 Questions

Recognized DB2 Experts

Knowledge Base

Advanced search
     

Interrelated Information Technology
Java COBOL
WebSphere MQ CICS

The Computer Education Techniques knowledge base is a service for answering questions, inclusive of the research and validation of the accuracy of information in the public domain. Citation of source documentation and examples are used to provide answers to the questions. Utilization of the information of this service and reliance on the answers, information or other materials received through this web site is done at your own risk.

Q I am creating a new CICS/DB2 program to be added to an existing system. The code in the old programs used to create a queue name is not working for me. Is there a standard way to create good TSQ names? There doesn't seem to be a standard here at our organization. Does each application area have their own standard.
A The TSQ naming techniques used by most programmers is to combine the transaction ID (EIBTRNID) and the terminal ID (EIBTRMID). They are both four character fields and they should create a unique ID for a transaction at a terminal. There may be some issues with this technique but it usually works.
Q We have DB2 version 8. There are issues with tables being dropped when it takes a long time for the transaction to complete. Do you have any recommendations for addressing this problem?
A In DB2 version 8, "dropped table recovery" is enabled by default for newly created data tablespaces. If the "dropped table recovery" is enabled, it will do additional logging which over time could slow down the drop operation.

In order to change this, it will be necessary to 'ALTER' the tablespace.

In DB2 version 7, this was turned off by default.
Q The DB2 UDB version 8 JDBC driver returns an error for ResultSet.next() method call when it should return FALSE on reaching the end of the result set. The DB2 UDB version 7 driver returns FALSE. Is this a defect in the DB2 UDB version 8 driver?  Does the IBM DB2 UDB version 8.1 JDBC 2.0 driver support connection pooling?
A No, the behavior of DB2 UDB version 8 JDBC driver conforms to the JDBC 3.0 specification which mandates this. 

Yes, it does support connection pooling.
Q How can ASCII data be loaded using the LOAD utility?
A There is a CCSID option on the LOAD statement which allows you to load input data into ASCII, EBCDIC, or Unicode tables. The ASCII, EBCDIC, and UNICODE options on the LOAD utility statement provide the capability for specifying whether the format of the data in the input file is ASCII, EBCDIC, or Unicode.

If the CCSID of the input data does not match the CCSID of the table space, the input fields are converted to the CCSID of the table space before they are loaded.
Q What is the difference between Created temporary tables and Declared temporary tables?
A
Created temporary tables

CREATE GLOBAL TEMPORARY TABLE statement puts a description of the table in catalog table SYSTABLES. The table description is persistent and is shareable across application processes CREATE GLOBAL TEMPORARY TABLE statement does not create an instance of the table. The first implicit or explicit reference to the table in an OPEN, SELECT, INSERT, or DELETE operation executed by any program in the application process creates an empty instance of the given table Indexes, UPDATE (searched or positioned), and DELETE (positioned only) are not supported.

Locking, logging, and recovery do not apply.

Work files are used as the space for the table.
 
Declared temporary tables

DECLARE GLOBAL TEMPORARY TABLE statement does not put a description of the table in catalog table SYSTABLES. The table description is not persistent beyond the life of the application process that issued the DECLARE statement and the description is known only to that application process DECLARE GLOBAL TEMPORARY TABLE statement creates an empty instance of the table for the application process. Each application process has its own unique instance of the table, and the instance is not persistent beyond the life of the application process. Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported.

Some locking, logging, and limited recovery do apply. No row or table locks are acquired. Share-level locks on the table space and DBD are acquired.
Q How do you convert an index-controlled partitioned table space to a table-controlled partitioned table space?
A There are several techniques including:

Use CREATE INDEX with the PARTITIONED clause to create a partitioned index on an index-controlled partitioned table space.

Use CREATE INDEX with a PART VALUES clause and without a CLUSTER clause to create a partitioning index.
Q What is a NPSI: Nonpartitioned secondary index?
A A nonpartitioned secondary index is any index that is not defined as a partitioning index or a partitioned index.

A nonpartitioned secondary index on a table can be created that resides in a partitioned table space or a nonpartitioned table space.
Q Can a VARCHAR efficiently be used on an index?
A If the NOT PADDED clause is specified on a CREATE INDEX statement, any varying-length columns in the index key are not padded to their maximum length.

If an existing index key includes varying-length columns, the index can be altered to use the NOT PADDED clause
Q How is star join optimization implemented on DB2 for z/OS and OS/390?
A

DB2 for z/OS utilizes the following strategy for star join optimization:

1. Detect star join.
2. Join from the “outside in” (dimension to fact table) using one of the available fact table indexes. The index that is used and dimension tables that are chosen to be joined at this step are determined based on the cost estimation. By using this approach, the fact table or the selected fact table index is scanned no more than once.
3. Sort the qualified dimension table rows from the previous step and stored them in work files.
4. Join the work files to the fact table.
5 Join back to the dimension tables that are ignored in Step 2.
Q Where can I find IBM's DB2 documentation?
A

The documentation in PDF format can be downloaded from:

DB2 Version 9 http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc/db29lib.htm
DB2 Version 8 http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc/db28lib.htm
DB2 Version 7 http://www.ibm.com/software/data/db2/zos/v7books.html
Q When will online REORG support 32K pages?
A In DB2 Version 8, the 32K page restriction for online REORG has been eliminated.
Q What performance enhancements are in DB2 Version 8?
A Many of the performance enhancements come from the enhanced index capabilities. Comparing a decimal column to a floating point number could not be done in the past. Being able to compare these values can mean choosing a better index or even an index access instead of a table space scan. An index can be used for a backward scan, so some indexes may not be required, reducing the overhead for inserts and deletes.

Support for varying length indexes can save space and can use indexonly access with a varchar. Having distribution statistics for columns which are not part of the index can give the optimizer better information in order that can perform better optimization. Gathering these statistics was a separate DSTATS program, and is now part of RUNSTATS.

Materialized query tables can provide a one or two order of magnitude performance improvement by rewriting queries to use the precalculated information.

The utility enhancements for DB2 UDB for z/OS V8 include:

1. New BACKUP SYSTEM and RESTORE SYSTEM Utilities.
2. Delimited data support in LOAD and UNLOAD.
3. Improved Distribution Statistics in RUNSTATS.
4. Rebalance partitions during REORG.
5 Usability improvements.
Q I have been informed that I will be responsible for implementing and managing multiple versions of products under DB2. What does this exactly mean?
A DB2 Release 8 utilizes a Version ID for packages. DB2 now has the ability to support multiple versions of the same package in a single system by using the new version option for packages. This will allow multiple packages that share both the schema and package-id to co-exist in the system catalogs. A major advantage will be to allow a new version of a package to be tested on the system without affecting users of the existing version of the package.
Q I am Database Administrator and I need to know which new tools are available for monitoring DB2 UDB?
A There are two new features which are currently available: Health Monitor and the Health Center.
  • The Health Monitor is a server-side tool that monitors the health of the instance, even without user interaction.
  • The Health Center provides the graphical interface to the Health Monitor.

IBM states that is intention with these tools was to add a management by exception capability to DB2 Universal Database.
Q I attended your DB2 Systems Performance course in 2003. I require some assistance on tablespaces. More specifically, does the tablespace get locked when an online table load is run? And are you allowed to update table columns that are part of a DB2 Index?
A When loading data into a table in Version 8, the table space in which the table resides will no longer be locked. Users will have read and write access to all the tables in the table space. For the table being loaded, the existing data in the table will be available for read access if the load is appending data to the table.

Columns can be updated that are part of a DB2 Index; the requirement is DB2/OS390 V6+ or higher.
Q I took a DB2 Database Administration course with SYS-ED in the late 1990’s and I currently have a problem that I need assistance in resolving. Can RUNSTATS Utility be executed against the DSNDB01 Database?
A No,RUNSTATS Utility can not be executed against the DSNDB01 database.

The error message will be:

DSNU064I, UTILITY NOT ALLOWED AGAINST SYSTEM DATABASE
Q We are a large z/OS IBM mainframe shop, we have been getting the following intermittent error message:

904 is a "Resource Not Available"

Is there a single underlying cause for this error message and how can we correct it?
A The source of this error message is typically caused by one of the following three conditions:
  1. An image copy needs to be taken. An image copy gives DB2 a starting point in case recovery needs to be run at later time.
  2. There is contention with another program. DB2 allows concurrency; however a long running update programs can cause problem. DB2 only has a certain amount of locks. Once this maximum threshold of locks is used, DB2 escalates to a tablespace lock. Only the process that caused the escalation can use the table. All other processes will receive a -904.
  3. A DB2 utility is being executed. Certain DB2 utilities require exclusive control of the table. If a process is started while one of these is executing, a -904 is issued.
Q Educational Consulting: A Challenge

We are testing a zSeries mainframe with z/VM and VSE guests and Linux guests running under z/VM. The initial benchmarks indicate performance problems with DB2/DRDA between VSE/ESA and Linux/UDB. It would appear that IBM’s DRDA performance enhancements have fallen well short of their stated objectives.
A A couple of our clients are evaluating similar implementations and SYS-ED Curriculum Managers/systems consultants in the United States and Europe are in the process of gathering and analyzing the data and implementing different scenarios. Naturally, a tailored DB2 training solution will be the by product of our work. From the client’s perspective, success of the test decides whether they will continue running a zSeries mainframe or not! FYI, we are in the process of compiling the information for a first-rate course.

Schedule