IBM iSeries (AS/400) DB2 UDB: Top 10 expert questions

Kent Milligan, IBM iSeries/i5 DB2 UDB expert, has been answering Search400.com's member questions for more than five years. iSeries users have asked Kent the same questions again and again. In an effort to save you time, we've compiled the top 10 questions asked about IBM iSeries/i5 DB2 for you. Do you have a question that's not listed here? Ask Kent your IBM DB2 questions.

TABLE OF CONTENTS
  [IMAGE] 1. Recommended index not used
  [IMAGE] 2. Setting up a default library in IBM DB2
  [IMAGE] 3. CHAR fields vs. VARCHAR
  [IMAGE] 4. The real difference between IBM DB2 databases
  [IMAGE] 5. Change the length of an existing field
  [IMAGE] 6. Accessing an explicit file member using SQL
  [IMAGE] 7. Date formats & creating a table with SQL
  [IMAGE] 8. What is the difference between DB2/400 and IBM DB2 UDB for the iSeries?
  [IMAGE] 9. Access data in SQL Server from the iSeries
  [IMAGE] 10. How can I tell which indexes are being used by IBM DB2 and the query optimizer to improve performance?

1. Recommended ...

To continue reading for free, register below or login

Requires Membership to View

To gain access to this and all member only content, please provide the following information:

Email Address:

By joining SearchDataManagement.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

To read more you must become a member of SearchDataManagement.com

As an existing member of the TechTarget network please activate your SearchDataManagement.com account 

By joining SearchDataManagement.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT DB2 Advisor DB2 security: The starting point Information Server products touted by IBM at conference EDA software: Event-driven architecture and DB2 Content management software: Who will leverage semi-structured and unstructured data? XQuery and XML data: DB2 helps manage the era of unstructured data "Low IT" databases: DB2 for the low IT user Enterprise information integration (EII) delivers information on demand IBM DB2 and Master Data Management Mainframe Micro-quiz: DB2 V8 IBM fellow on DB2 V8 for zSeries IBM DB2 management How to select an MPP database: DB2 vs. Teradata What are the top database management systems (DBMS)? Are there benefits to using both Teradata and a DB2 database? Tips for evaluating top database management systems and choosing a small DBMS Exec explains IBM's Information On Demand (IOD) initiative IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 7 IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 6 IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 5 DB2 basics IBM DB2 basics

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

index not used

Question: Operation Navigator recommends using an index for SQL performance monitoring. I created the recommended index, but it looks like the system doesn't use it. I find it strange that the index is recommended and then not used. Is this normal?

Milligan: Even though the query optimizer advises creating an index, it doesn't always choose to use it when creating its access plan. There are some reasons for this:

The query optimizer does not always advise the perfect index. The reason for this is that currently, only local selection is used when determining what index to advise -- it does not yet consider join, order by, or group by columns. As a result, after you create that advised index, it may not choose to use it, as it is still not the perfect index. You may need to manually intervene -- examine the query and consider the join, group by, and order by columns to determine what the perfect index is and create that index.

Alternatively, it may be recommending that index so that it can extract stats from the index to further evaluate the optimal access method. For example, once the index has been created, it may be using statistics from the index to determine that query will return many rows, thus a table scan is the least expensive access method.

To better understand the concept of a perfect index and how indexes help the query optimizer with statistics, you can read the white paper Indexing and statistics strategies for DB2 for i5/OS.

2. Setting up a default library in IBM DB2

Question: How can I set up a default library in IBM DB2 in order to not change our SQL statements? In fact, we are migrating from IBM B2 UDB Windows Server to the iSeries. Actually, in Windows environment, our SQL statements run well and with the SET CURRENT SCHEMAS = "EMPORIUM", we define default schemas so that we don't have to explicitly specify it in each of our SQL statements. How can I do this in an iSeries environment? I need to define a library such as SELECT ... FROM MCMLIB.COMMANDES WHERE. Can you help me?

Milligan: It depends on the application programming interface that you are using. IBM DB2 UDB for iSeries added support for the SET CURRENT SCHEMA statement in V5R2. The iSeries Access ODBC and JDBC drivers support connection keywords and properties to allow specifying a default library. Here's an example of the ODBC connection keyword:

DefaultLibraries=Schema1,Schema2

3. CHAR fields vs. VARCHAR

Question: What are the best practice guidelines with regard to using CHAR fields vs. VARCHAR fields on a IBM DB2 database for the iSeries?

I'm building a new database for a .Net application and I was considering VARCHAR for some longer fields to save storage. I had hoped that the VARCHAR field would also remove the trailing blanks from the string type fields. However, in my initial tests, it does not appear that VARCHAR has any affect on these trailing blanks. Is there any setting on the iSeries that can be changed to automatically trim them?

Milligan: In general on IBM DB2 UDB for iSeries, VARCHAR data types should be used for columns containing long descriptions or memos that are not referenced very often. 50 bytes is probably the smallest length you would want to use for a VARCHAR column. DB2 does have to allocate some additional bytes for each row with variable length columns to keep track of the varying length data, so some of the space savings will be consumed by these "extra tracking" bytes. Due to this overhead, the space savings would only be noticeable on tables with a large number of rows.

4. The real difference between IBM DB2 databases

Question: One of our customers is starting a project with Portable Applications Solution Environment (PASE) under V5R3. The programmers tried to work from that with C-program's to exchange information with the native database and have some major problems with things such as host variables and others. Information that can help to solve these problems is rarely available -- especially in Germany. The German IBM Software Service seems to be a group of "we are iSeries; don't know much about the rest of the IT world." Can you help with information about the REAL difference between the UDB databases in the UNIX/Windows and iSeries versions?

Milligan: Click here to learn the real differences between the UDB databases in the UNIX/Windows and iSeries versions.

5. Change the length of an existing field

Question: Can I change the length of a field in a physical file without recompiling it?

Milligan: The best way to change the length of an existing field is the CHGPF CL command.

6. Accessing an explicit file member using SQL

Question: Can you access an explicit file member using interactive SQL? I want to access a multi-member *PF.

Milligan: Yes, the easiest way is to create an alias for each secondary member that you want to access, and then use the ALIAS name on your SQL request. Creating an alias is just a one-time setup operation.

Here's an example:
CREATE ALIAS mylib/a1 FOR mylib/myfile(mbr1)

SELECT * FROM mylib/a1 WHERE f1>0

7. Date formats & creating a table with SQL

Question: I'm trying to create a table using SQL. I would like the dates to be of the *USA (mm/dd/yyyy) format. All of our files have been created with DDS and all of our dates are DATFMT *USA. I would like to create some new files with SQL, but I can't get the dates in the format that we use. How can I do this?

Milligan: The DATFMT cannot be specified on SQL Create Table. All date values, however, are stored by DB2 in the same internal format regardless of the DATFMT that is specified on the column or field definition. When that data is read from the SQL Table it will be converted to the output date format specified by the application -- in your case the *USA format.

8. What is the difference between DB2/400 and IBM DB2 UDB for the iSeries?

Question: What is the difference between DB2/400 and IBM DB2 UDB for the iSeries?

Milligan: DB2/400 and DB2 UDB for iSeries are essentially the same thing. iSeries (AS/400) was able to adopt the IBM DB2 UDB branding when it delivered a lot of new database functionality in V4R4 of OS/400, so the database was renamed from DB2/400 to DB2 UDB for iSeries in that release.

You can find more information on IBM iSeries and the DB2 UDB family here.

9. Access data in SQL Server from the iSeries

Question: Can you tell me what I need to access data in a MS SQL database from the iSeries?

Milligan: The white paper you are looking for is Heterogeneous Data Access for iSeries Applications.

To access a non-DB2 data source (including Microsoft SQL Server) from an iSeries program written in any high-level programming language, use WebSphere Information Integrator (Formerly DB2 Information Integrator - DB2 II). This product requires that the data access to be performed with an SQL-based interface such as CLI, embedded SQL or JDBC.

10. How can I tell which indexes are being used by IBM DB2 and the query optimizer to improve performance?

Question: How can I tell which indexes are being used by IBM DB2 and the query optimizer to improve performance?

Answer: In V5R3, a new tool was added to iSeries Navigator known as Index Evaluator. This new function makes it easy to analyze which indexes (and keyed logical files) are helping your SQL and query performance and which indexes are not pulling their weight. You cannot rely on the object timestamp "last used date" because that value is not always updated by the query optimizer.

For each index defined over the table, the evaluator returns data about the last time an index was used in the implementation of a query and/or used to provide statistics about the table data to the query optimizer. In addition, it returns a count for the number of times the query optimizer used an index object.

Below, you see the output of the Index Evaluator, which is initiated by right-clicking on a table object in iSeries Navigator and selecting the Show Indexes task -- the task name is Indexes on V5R3).