DB2 Training Services Longstanding Expertise in Evolutionary IT

Submit Db2 / DB2 Questions

HomeDB2 Courses

Submit Db2 Questions

Recognized Db2 / DB2 Experts

Knowledge Base

Advanced search

Interrelated Information Technology

IBM MQ (WebSphere MQ) CICS Transaction Server

The SYS-ED 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 and reliance on the answers, information, or other materials received through this website is done at your own risk.

IBM has changed the branding of its software from DB2 to Db2.  SYS-ED is transitioning urls to reflect this decision.

Q What is the DB2 XML data type?


The XML data type is used to define columns of a table that store XML values; where all stored XML values must be well-formed XML documents. It provides the ability to store well-formed XML documents in their native hierarchical format in the database alongside other relational data. XML values are processed in an internal representation that is not a string and not directly comparable to string values. An XML value can be transformed into a serialized string value representing the XML document using the XMLSERIALIZE function or by binding the value to an application variable of an XML, string, or binary type. Similarly, a string value that represents an XML document can be transformed to an XML value using the XMLPARSE function or by binding an application string, binary, or XML application type to an XML value.
Q Can there be an index on DB2 XML data?
A Yes, there can be an index on DB2 XML data. Indexes over XML data can improve the performance of queries on XML columns. XML elements or attributes that are frequently used in predicates and cross-document joins should be indexed. An index over XML data indexes part of a column. The parts of an XML column to index are indicated by specifying an XML pattern, which is a limited XPath expression.

As with relational indexes, the recommended practice is that XML elements or attributes which are frequently used in predicates and cross-document joins be indexed.
Q Is the DB2 INSERT/UPDATE statement used to insert and modify values in XML data?
A Yes. Well-formed XML documents are inserted into XML typed columns using the INSERT SQL statement. Although XML data can be inserted by using XML, binary or character types, in order to avoid code page conversion issues, XML or binary types can be used.

In INSERT, UPDATE, or DELETE operations, string data can be assigned directly to XML columns without an explicit call to the XMLPARSE function.

If the UPDATE statement is used without an XQuery updating expression, a full-document update must be performed.

XML data is queried by using SQL, XQuery with XQuery expressions, or a combination of both.
Q How is a table created which can store XML data in DB2 version 9?
A Table creation is demonstrated with this example:

In order to create a table named Student which contains an XML column called Courses:

              (StudentID         INTEGER NOT NULL         PRIMARY KEY,
                                      Courses XML)  

A primary key is not required to store or index XML data. It also is possible to add one or more XML columns to a table by using the ALTER TABLE SQL statement. By default, databases use the UTF-8: Unicode code set. If the decision is to made to store XML data in a database with a code set other than UTF-8, the recommended practice is to insert this data in a form that does not undergo code page conversion, such as BIT DATA, BLOB, or XML.
Q What is meant by the attachment facility?
A The attachment facility is an interface between DB2 and TSO, IMS, CICS, or batch address spaces. It allows application programs to access DB2.
Q Our organization is a large DB2 installation, we have recently acquired several businesses that use Oracle as their database. Will we have to recode all the applications under DB2? Can Oracle SQL and PL/SQL run under IBM DB2?
A IBM DB2 9.7 for Linux, UNIX, and Windows supports Oracle SQL and PL/SQL. This allows many applications developed on the Oracle database to run against DB2. However, the compatibility is not one hundred percent. For example, DB2 supports PL/SQL triggers, but does not allow trigger actions to combined. The degree of compatibility is highly dependent on the features being used by the applications. DB2 supports facilities from Oracle 11g; while some constructs from Oracle 8 are not. CETi technology partners have been researching claims made by IBM that over 90% of Oracle code can be moved to DB2 without change and that many of the remaining adjustments can be automated. The initial feedback on the performance of applications written against PL/SQL on DB2 are favorable. However, Oracle licensing terms prohibit the publishing of benchmarks results without written consent.

What is a DB2 buffer pool?

A A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more table spaces or indexes, and is made up of 4K, 8K, 16K or 32K pages.

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 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 version 7 driver returns FALSE. Is this a defect in the DB2 version 8 driver? Does the IBM DB2 version 8.1 JDBC 2.0 driver support connection pooling?

No, the behavior of DB2 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?

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 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?

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.


Where can I find IBM's DB2 documentation?


The documentation in PDF format for version DB2 12, 11 and 10 is at http://www-01.ibm.com/support/docview.wss?uid=swg27047206.

The documentation in PDF format can be downloaded from http://www-01.ibm.com/support/docview.wss?uid=swg27009474 for versions DB2 11, 10 and 9.

The documentation in PDF format for DB2 version can be downloaded from http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc/db28lib.htm

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?

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 for z/OS V8 include:

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?

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.


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?


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?

No, the RUNSTATS utility can not be executed against the DSNDB01 database.

The error message will be:



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?


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.


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/DB2. It would appear that IBM’s DRDA performance enhancements have fallen well short of their stated objectives.

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.