As of January 2008, the Pipelines will no longer publish monthly tips. However, we welcome and will post user-submitted tips on this page. Please send your tips to webmaster@quest-pipelines.com.

Our archive of monthly tips will be maintained, so feel free to browse them below.

2006 Tips		2005 Tips		2004 Tips
2003 Tips		2002 Tips		2001 Tips
2000 Tips

January's Tip of the Month

DB2 V8 and Unicode
By Willie Favero, Reprinted From IT Toolbox Blogs
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30701

Unicode is an interesting subject. For some reason there still seems to be a lot of confusion on the subject. Unicode is one of those things that looks new yet has been around for a long time. Although it has been an optional part of DB2 on the OS/390 and z/OS platforms since Version 6, it didn't get our attention (the DB2 for z/OS user community) until when we found out that parts of DB2 would start to use Unicode and the "optional" part was going away. As you know, during ENFM, the DB2 catalog is converted from EBCDIC to Unicode. There are other areas of DB2 that Unicode plays a significant role in that we will discuss in just a few minutes. These are the reasons that just bringing up the subject of Unicode during a discussion can get a groups heart rate racing. These are also the reasons why at every conference I have attended since V8's use of Unicode was announced, it can be difficult to get into one of Chris Crone's sessions on Unicode if you hadn't planned to arrive at least 20 minutes early (Chris, by the way, is IBM SVL's resident expert on Unicode). There is also way too much stuff around Unicode to discuss in this blog entry. So, today we will simply try to describe what Unicode is, why Unicode might cause you some concerns, and point you to a lot of resources so you can read more about Unicode should this entry spark you interest.

What is Unicode?

Before going into too much more detail about Unicode, I should stop and explain a few terms that you will start to see in Unicode discussions. You are probably used to dealing with EBCDIC and are quite familiar with that fact that a hexadecimal "F1" represents a numeric "1", a hex "C1" represents the upper case character "A", and the hex value "81" represents a lower case character "a". These hex representations of your data are called code points. When you group a bunch of these code points together, they are referred to as code pages. If you have been around z/OS for a while (even going back to the early releases of MVS), I'm sure you have run into code page issues. Code pages have been around since the dinosaur. BTW, code pages are NOT the same as CCSIDs. Do not interchange the two terms, they really are different. A CCSID (Coded Character Set Identifier) is just a numeric value that identifies a code page. CCSIDs are part of the metadata that describes the data in a DB2 subsystem and I believe they were first introduced to DB2 back in Version 4. For example, if you are using a US English code page, it will be represented by CCSID 037. There is also a special case US English code page that includes the euro symbol that has a CCSID 1140. Both CCSIDs 037 and 1140 will probably be valid for most US shops. A CCSID in DB2 is also specified for the entire DB2 subsystem and once specified, really should not be modified. That "should not be modified" phrase is what could cause you concern when you start your DB2 V8 planning & migration.

Of course, the big question is "What is Unicode?" The official definition from the Unicode web site is: "Unicode is the universal character encoding, maintained by the Unicode Consortium. This encoding standard provides the basis for processing, storage and interchange of text data in any language in all modern software and information technology protocols.. A quote from the Unicode website that I like even better is: "Unicode provides a unique number for every character, no matter what the platform, no matter what the program, no matter what the language". I think this quote really sums up why Unicode is important. Everyone gets to work with the same character representation, end of story. With Unicode standard 4.0, there are currently over 64,000 characters identified by unique numeric codes. When only code pages where in use, different languages would use different codes pages. In fact, there where often occasions where a single language might use multiple code pages. The result can be character translation errors. Unicode came into existence to minimize these problems. Unicode attempts to represent every possible character by its own Unicode code point. To accomplish this, Unicode uses Universal Transformation Formats (UTFs). UTF not only includes code points fro most languages, it also covers math and science symbols. This format also allows for easy inclusion of new characters.

Should Unicode be of concern?

To find out if you should be concerned, your first stop should be DSNHDECP to validate what CCSID you currently have DB2 set up to use. For customers that should be using a US English CCSID, the error most frequently discovered is CCSID 500 or, in some rare cases, CCSID 0.

The DSNHDECP module contains your application programming defaults, among them the CCSD that is in use. CCISD 500 is usually associated with Belgium, Switzerland, and international Latin. CCSID 037 is usually associated with countries using US English. In some instances CCSID 1140 may be specified in place of CCSID 037 if the euro symbol is being used. The other CCSID values specified in DSNHDECP are listed in the following table.

Could you have a Unicode concern? Maybe, maybe not. A determination cannot be made by simply running a query against the DB2 catalog. XXXXXXXX has two tasks that should now be performed. First, run job DSNTIJP8. This job is delivered via APAR PQ84421 (PTF UQ85439 on RSU 0406). It is the DB2 Version 8 flavor of DSNTIJPM. Do not run DSNTIJPM delivered with DB2 Version 7. This is the verification job for migrating from Version 6 to Version 7. The Version 7 flavor of DSNTIJPM is delivered on the Version 8 distribution tapes. This job should be run on your DB2 Version 7 at your earliest convenience to identify any concerns that need to be resolved before commencing on a migration to V8. Any CCSID issues should be completely resolved BEFORE you start your migration to DB2 V8 compatibility mode.

If you discover you may not be using the CCSID you should be using, should you change it? Absolutely not! After running DSNTIJP8, if you have CCSID concerns, should open an ETR with DB2 Level 2 Support specifying DB2 "CCSID 500 prior to migrating to Version 8" as the subject line. Level 2 will provide you with the instructions on how to proceed. You should not change any occurrences of CCSID 500 or CCSID 0 (if those are the CCSIDs you are concerned about) in the DB2 catalog or in DSNHDECP without guidance from DB2 Level 2 Support. I strongly recommend that an ETR be opened as soon as possible if you discover you are not using the CCSID you think you should be using. This action should be taken if your Version 8 migration is not planned for some time. This will give you adequate time to make changes should it be determined that changes are necessary.

The possible problem (emphasizing possible), that may arise when having a CCSID specified that does not match the data is the possibility of improper translation of some characters. Here is an example. DB2 thinks a piece of data is encoded using CCSID 500 although DB2 Version 7 is not really enforcing that CCSID and the data appears to really be using CCSID 037. When the Version 8 migration is complete, the DB2 catalog will be encoded using Unicode. If you were using "[" (left square bracket) it would have had the EBCDIC hex representation of "BA". DB2 would encode that character using the appropriate Unicode value. When that character is subsequently retrieved from DB2, and your DB2 is now Version 8 NFM or ENFM, the character will be converted from Unicode back to CCSID 500 because DB2 is now enforcing the CCISD. Now the character returned will be a right corner, a character not on my keyboard. A character translation error has just occurred. Using CCISD 037 and 500 as example and because CCSID 500 seems to be the CCSID mis-coded in most cases, there are actually 7 characters that have different hex values as demonstrated in the following table.

It should be pointed out that there is data that is unaffected by Unicode. For example, unaffected would be any column defined as "FOR BIT DATA", and numeric data stored as binary, packed, or float.

One concern often mentioned in a first time Unicode discussion, is the amount of space the catalog will take after encoding it in Unicode. Customers often express a concern over a significant increase in catalog size. When the catalog is converted to Unicode, how much the catalog might increase in size is dependent on the type of data being converted in the catalog. For example, if your current CCSID is 037 then each character will take up a single byte. Almost all of the characters in CCSID 037, with few exceptions, will still only take up a single byte when encoded in Unicode. So your increase would be minimal, if not zero. If you are using a lot of special characters and/or double byte characters, then the Unicode equivalents will probably use more space than the EBCDIC character.

Another area that may cause some concern is the collating sequence used once you complete the conversion to Unicode. The first 127 Unicode characters are the same as ASCII so the collating sequence will be different. The collating sequence for Unicode is numeric, uppercase characters, and then lower case characters (1, 2, 3, A, B, C, a, b, c). In EBCDIC, the collating sequence is lower case, upper case, and then numeric (a, b, c A, B, C, 1, 2, 3). This will affect both data ordering and the results from range predicates. Once your conversion of the catalog to Unicode is complete, you may need to validate any in-house queries that are used against the catalog, and any tools that process the information in the catalog, to insure you still get the same results.

The last thing that needs to be discussed before closing out this blog entry is who this affects. I have heard the comment that "Unicode only changes the catalog so the affect on me should be minimal". Not true. Beside have the catalog in Unicode and affecting collating sequence and range predicates, you need to be aware that predicate evaluation and SQL parsing is preformed in Unicode. Once you complete your migration of Version 8 to new function mode (NFM), all of your DBRMs will be created using Unicode, all SQL stored in SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT is stored in Unicode, literals may be in Unicode, the results from an SQL statement may be Unicode, and SQL included in IFCIDs "could" be in Unicode. This last item has a ZPARM that controls the format.

February's Tip of the Month

DB2 z/OS Index Creation Guidelines
By Craig Mullins for Knowledge Xpert for DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30702

Consider the following recommendations when creating indexes:

Create Indexes Before Loading Tables

The LOAD utility updates indexes efficiently. Usually, the LOAD utility is more efficient than building indexes for tables that already contain data. The data being loaded should be sorted into the order of the clustering index before execution.

Consider Deferring Index Creation

The DEFER option on the CREATE INDEX statement allows the index to be created but not populated. The RECOVER INDEX utility can then be executed to populate the index. This will speed the index creation process because REBUILD INDEX usually populates index entries faster than CREATE INDEX.

Creating a STOGROUP-defined index with DEFER YES causes the underlying VSAM data set for the index to be allocated.

Additionally, the DB2 catalog is updated to record that the index exists. However, if the table being indexed currently contains data, DB2 will turn on the recover pending flag for the index space and issue a +610 SQLCODE. Subsequent execution of RECOVER INDEX will turn off the recover pending flag and populate the index.

Consider Deferring Index Data Set Definition

The DEFINE parameter can be used to control when the underlying data set(s) for the index space are created. DEFINE YES, which is the default, indicates that the data sets are created when the index is created. DEFINE NO indicates that data set creation will not occur until data is inserted into the index. The DEFINE parameter should be used only with STOGROUP-defined indexes; it will be ignored if specified for VCAT-defined indexes.

Specifying DEFINE NO can be useful to minimize the number of data sets where indexes are being created on empty tables that will remain empty for some time.

Create a Unique Index for Each Primary Key

Every primary key explicitly defined for a table must be associated with a corresponding unique index. If you do not create a unique index for a primary key, an incomplete key is defined for the table, making the table inaccessible.

Use WHERE NOT NULL to Allow Multiple Nulls in a UNIQUE Index

Specify the UNIQUE WHERE NOT NULL clause to enable multiple nulls to exist in a unique index. This is useful when an index contains at least one nullable column, but all non-null entries must be unique.

Create Indexes for Foreign Keys

Unless an index already exists for access reasons or the table is too small to be indexed, create an index for each foreign key defined for a table. Because DB2's referential integrity feature accesses data defined as a foreign key "behind the scenes," it's a good idea to enhance the efficiency of this access by creating indexes.

Specify Columns Carefully in Multicolumn Indexes

If a table has only multicolumn indexes, try to specify the high-level column in the WHERE clause of your query. This action results in an index scan with at least one matching column.

A multicolumn index can be used to scan data to satisfy a query in which the high-level column is not specified (but another column in the index is specified). However, a non-matching index scan of this sort is not as efficient as a matching index scan.

Consider Several Indexes Instead of a Multicolumn Index

Because DB2 can utilize multiple indexes in an access path for a single SQL statement, multiple indexes can sometimes be more efficient (from a global perspective) than a single multicolumn index. If access to the columns varies from query to query, multiple indexes might provide better overall performance for all your queries, at the expense of an individual query.

If you feel that multiple indexes might be of benefit for your specific situation, test their effectiveness first in a test environment by:

• Dropping the multicolumn index
   
• Creating a single index for each of the columns in the multicolumn index
   
• Updating DB2 Catalog statistics to indicate production volume
   
• Running EXPLAIN on all the affected queries and analyzing the results

Here is an example where DB2 could use two indexes to satisfy a data retrieval request. For the following query:

SELECT   DEPTNO, DEPTNAME, MGRNO
FROM     DSN8810.DEPT
WHERE    DEPTNO > 'D00'
AND      ADMRDEPT = 'D01';

Only two indexes exist for this table, one index for DEPTNO and another index for ADMRDEPT. DB2 could use either, or both of these indexes to satisfy the request. If multi-index access is used, the index on DEPTNO is used to retrieve all departments with a DEPTNO greater than 'D00', and the index on ADMRDEPT is used to retrieve only rows containing 'D01'. Then these rows are intersected and the correct result is returned.

DB2 can use multiple indexes to satisfy OR conditions as well.

March's Tip of the Month

See How Many CPUs are on Your Server via SQL
By Chris Eaton, Reprinted From IT Toolbox Blogs
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30703

Similar to the new table functions for accessing the recovery history file via SQL, DB2 8.2.2 also added new UDFs for accessing environmental information via SQL.

There are three new table functions in 8.2.2

• ENV_GET_INST_INFO - returns information you get from the db2level command. It also tells you if you are running DPF and if so how many database partitions this database is running across.
   
• ENV_GET_PROD_INFO - returns the DB2 products you have installed, along with the license information and the release numbers of the products.
   
• ENV_GET_SYS_INFO - returns information about the system you are running on including the operating system name and version, the server hostname and the cpu and memory information.

Here are some scripts you may find useful. First let's create views on top of each of these table functions:

db2 create view db2$inst_info as select * from table(env_get_inst_info()) as ii
db2 create view db2$prod_info as select * from table(env_get_prod_info()) as pi 
db2 create view db2$sys_info as select * from table(env_get_sys_info()) as si

Here are a few pretty basic queries (and their results).

select substr(inst_name,1,10) as "Instance Name",
       num_dbpartitions as "Number Partitions",
       rtrim(char(inst_ptr_size)) ||'bit' as "Bitness",
       fixpack_num as Fixpack
  from db2$inst_info;

Instance Name Number Partitions Bitness        FIXPACK
------------- ----------------- -------------- -----------
DB2                           1 32bit                   10

select substr(os_name,1,15) as "Operating System",
       substr(rtrim(os_version) || '.' || rtrim(os_release),1,20) as "OS Version",
       substr(host_name, 1,15) as "Hostname",
       total_cpus,
       total_memory as "Memory (Meg)"
  from db2$sys_info;

Operating System OS Version           Hostname        TOTAL_CPUS  Memory (Meg)
---------------- -------------------- --------------- ----------- ------------
WIN32_NT         5.1.Service Pack 1   CEATON                    1         1535

select case(installed_prod)
         when 'ADCL' then    'DB2 Application Development Client'
         when 'ADMCL' then   'DB2 Administration Client'
         when 'CONEE' then   'DB2 Connect Enterprise Edition'
         when 'CONPE' then   'DB2 Connect Personal Edition'
         when 'CUBE' then    'DB2 Cube Views'
         when 'DLM' then     'DB2 Data Links Manager'
         when 'ESE' then     'DB2 Enterprise Server Edition'
         when 'EXP' then     'DB2 Express Edition'
         when 'GSE' then     'DB2 Spatial Extender'
         when 'PE' then      'DB2 Personal Edition'
         when 'QP' then      'DB2 Query Patroller'
         when 'RTCL' then    'DB2 Run-Time Client'
         when 'WM' then      'DB2 Warehouse Manager'
         when 'WSE' then     'DB2 Workgroup Server Edition'
         when 'WSUE' then    'DB2 Workgroup Server Unlimited Edition'
         else '?'
        end as "Product",
        case(is_licensed)
          when 1 then 'Yes'
          when 0 then 'NO'
        end as "Licensed",
        prod_release
   from db2$prod_info;

Product                                Licensed PROD_RELEASE
-------------------------------------- -------- --------------------------
DB2 Enterprise Server Edition          Yes      8.2

Well nothing too earth shattering there but let your imagination run wild for a minute. With this info you could build scripts to do some health checks on your system when you combine this info with other DBM or DB config information. For example, you could check to see if your default degree of parallelism is greater than the number of CPUs you have running on a server. Or check to see how much of the total server memory you have allocated to bufferpools. Or from a CLP session on your desktop go to all your database servers to check which ones are running something less than fixpak 7.

April's Tip of the Month

Using MDC and MQTs in DB2 Express-C
Reprinted From http://db2news.blogspot.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30704

If you are among the many who are utterly confused about what can and cannot be done with DB2 Express-C, you are apparently in good company, and are now in for some straight answers.

The biggest questions concern multi-dimensional clustering and materialized query tables, both of which can add tremendous analytical power to databases that are otherwise designed for an OLTP workload. The answer is yes, absolutely, you can exploit MDC and MQT in DB2 Express-C, even though it may sound too good to be true. Just in case you don't believe me, I am posting the proof here:

$ db2licm -l
Product name: "DB2 Express Edition"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2exp"
Version information: "9.1"
Max number of CPUs: "2"
Annotation: "6;(_uw)"

So we're using Express-C. Believe me?

$ db2level
DB21085I Instance "db29exc1" uses "64" bits and DB2 code release "SQL09010"
with level identifier "02010107".
Informational tokens are "DB2 v9.1.0.0", "s060629", "LINUXAMD64", and Fix Pack
"0".
Product is installed at "/opt/ibm/db2expressc/V9.1".

...the GA version - not an open or closed beta.

$ db2 "CREATE TABLE mdctest (store_id INTEGER NOT NULL PRIMARY KEY, 
area_region INTEGER NOT NULL, 
area_salesrep INTEGER NOT NULL) 
ORGANIZE BY DIMENSIONS (area_region,area_salesrep)"
DB20000I The SQL command completed successfully.

$ db2 "DESCRIBE INDEXES FOR TABLE mdctest SHOW DETAIL"
Index  Index              Unique  Number of
schema name               rule    columns   Column names
------ ------------------ ------- --------- -------------------------- 
SYSIBM SQL060814215526560 D       2         +AREA_REGION+AREA_SALESREP 
SYSIBM SQL060814215526680 D       1         +AREA_SALESREP 
SYSIBM SQL060814215526700 D       1         +AREA_REGION 
SYSIBM SQL060814215526720 P       1         +STORE_ID 

4 record(s) selected.

The Ds in the DESCRIBE INDEXES output show that there really are multi-dimensional block indexes on our silly example table, proving that MDC is really being used here.

Now, on to MQTs:

$ db2 "CREATE TABLE mqttest AS (SELECT area_region, area_salesrep, 
COUNT(*) AS store_count FROM mdctest 
GROUP BY ROLLUP(area_region, area_salesrep)) 
DATA INITIALLY DEFERRED REFRESH IMMEDIATE"
DB20000I The SQL command completed successfully.

$ db2 set integrity for mqttest immediate checked not incremental
DB20000I The SQL command completed successfully.

May's Tip of the Month

Migrate UNION ALL views to a Range Partitioned Table
By George Baklarz for the upcoming release of Knowledge Xpert for DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30705

Prior to range partitions, many applications were developed that used the UNION ALL view capability of DB2 to create a form of range partitioning. A separate table would be created for each range and then all tables combined into a view. In order for this to work properly, each table needed to be defined with an appropriate constraint that limited the rows that would be placed into it:

CREATE TABLE Q1_2006
  (
  TX_NUMBER  INT NOT NULL,
  TX_ITEM    CHAR(10) NOT NULL,
  TX_QUANTITY INT NOT NULL,
  TX_DATE    DATE NOT NULL,
     CHECK
       (TX_DATE BETWEEN '2006-01-01' AND '2006-03-31')
  )

The final check constraint would give DB2 information on which rows could be placed into this table. A view would be created that combines all four quarters into a single view of the year:

CREATE VIEW FOURQUARTERS AS
  (
  SELECT * FROM Q1_2006
  UNION ALL
  SELECT * FROM Q2_2006
  UNION ALL
  SELECT * FROM Q3_2006
  UNION ALL
  SELECT * FROM Q4_2006
  )

Adding new partitions to this view requires that the view be dropped and this may cause the loss of some dependent objects. Range Partitioning solves this problem and simplifies the roll-in and roll-out procedure. Only three steps are required in order to migrate existing UNION ALL views to a range partitioned table.

1. Create a partitioned table

   A partitioned table with only one partition in it needs to be defined. The first partition should be used to capture records with null values and for dates that are below the starting range. The use of the LIKE command simplifies the creation of the partitioned table since it can use the design of one of the existing tables in the UNION ALL view:

   CREATE TABLE YEAR2006 LIKE Q1_2006
     PARTITION BY (TX_DATE NULLS FIRST)
      (
       PARTITION EXCEPTION STARTING MINVALUE
         ENDING '2006-01-01' EXCLUSIVE
      )

2. Attach existing tables

   Each table that is part of the view needs to be attached to the partitioned table. The range partitioned table must have the same table space type as the tables that are being attached. The ALTER command will issue an error message if they are of different types:

   ALTER TABLE YEAR2006 ATTACH PARTITION Q1
       STARTING '2006-01-01' ENDING '2006-03-31'
       FROM TABLE Q1_2006;
   ALTER TABLE YEAR2006 ATTACH PARTITION Q2
       STARTING '2006-04-01' ENDING '2006-06-30'
       FROM TABLE Q2_2006;
   ALTER TABLE YEAR2006 ATTACH PARTITION Q3
       STARTING '2006-07-01' ENDING '2006-09-30'
       FROM TABLE Q3_2006;
   ALTER TABLE YEAR2006  ATTACH PARTITION Q4
       STARTING '2006-10-01' ENDING '2006-12-31'
       FROM TABLE Q4_2006;

3. Run SET INTEGRITY

   The SET INTEGRITY statement needs to be issued to bring the data back online. After the SET INTEGRITY completes, the YEAR2006 table contains the same contents are the original FOURQUARTERS view. The following SET INTEGRITY command does not have exception tables defined since the base tables would not have allowed records outside of the constraint ranges.

   SET INTEGRITY FOR YEAR2006 ALLOW WRITE ACCESS
     IMMEDIATE CHECKED INCREMENTAL

June's Tip of the Month

DB2 LUW SQL Scripts
Reprinted from Systems Engineering and RDBMS
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30706

You can use this SQL to generate the SQL statements for truncating the tables in a schema:

SELECT ‘ALTER TABLE ‘ || TABNAME || ‘ ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;’
FROM SYSCAT.TABLES A
WHERE A.TABSCHEMA = CURRENT SERVER
AND A.TYPE = ‘T’

You can use this SQL to generate the RUNSTATS commands for all tables or sub-set of the tables - you can make modifications as needed for the criteria for the RUNSTATS command:

SELECT ‘RUNSTATS ON TABLE ‘ || ‘MYSCH’ || ‘.’ || TABNAME || 
‘ WITH DISTRIBUTION AND DETAILED INDEXES ALL;’
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘MYSCH’
AND TYPE = ‘T’;

For generating the enable/disable for the tables:

Enable_constraint:

select ‘alter table ‘ || current server || ‘.’ || tabname ||  
‘ alter foreign key ‘ || constname || ‘ ENFORCED;’
from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

Disable constraint:

select ‘alter table ‘ || current server || ‘.’ || tabname ||  
‘ alter foreign key ‘ || constname || ‘ NOT ENFORCED;’
from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

For Identity Resets:

SELECT ‘SELECT ”ALTER TABLE ‘ || A.TABNAME || ‘ ALTER ‘ || 
A.COLNAME || ‘ RESTART WITH ” ||  ‘ || ‘RTRIM(CHAR(MAX(’ || A.COLNAME || ‘)+1)) ‘ ||
‘ FROM ‘ || A.TABNAME ||’;’
FROM SYSCAT.COLUMNS A
WHERE A.TABSCHEMA = CURRENT SCHEMA
AND A.IDENTITY = ‘Y’
ORDER BY A.TABNAME;

July's Tip of the Month

DB2 V8 Rebind FAQ
By Willie Favero, Reprinted From IT Toolbox Blogs
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30707

This discussion continually keeps popping up from time to time. Every month we have more and more folks moving to DB2 Version 8 which means we have people who had no interest in the subject of binding stuff last year suddenly needing the information this year. The really nice part of a blog is I can keep posting this stuff as long as there someone left with a question about it. The following is a rebind summary based on a series of questions and answers in a recent thread on the DB2-L list.

When should I rebind?

In compatibility mode (CM)

What about new function mode (NFM)?

You really only need to rebind once when you move to Version 8 although you may bind/rebind more often than that. I would suggest you do your rebind in compatibility mode (CM) for a couple of reasons:

• Get the latest access paths. Running the newer access paths could potentially save you some CPU while you are in CM. You also want to "test" the optimization enhancements while still in CM on the off chance something horrible happens (and it shouldn't) and you need to fall back to V7. If a rebind goes south on you while you're in new function mode (NFM), your option is to wait until it can be fixed. It is our INTENTION to make optimizer code available so that any surprises are caught while you have an opportunity to fallback.
   
• Get rid of the 31 bit code and upgrade your package structures to 64 bit. This will also save you CPU. However, the amount of saving you gain for just this reason is slight. If the package is 31 bit, the package needs to be "puffed up" to 64 bit so it can get to stuff above the bar. When the package is bumped out of the EDM pool, it's tossed. Next to you use it, it has to be "puffed" again. If you rebind the package it's in 64 bit mode permanently, no more puffing. If you should fall back to Version 7, DB2 will auto-rebind the 64 bit packages for you on first access.
   
• Re-enable the SPROCs (SELECT procedures, fast column read, or what ever else you call it). The procedure in your package is 31 bit so it's disabled. Rebind will enable the SPROC for you. This will help performance and could possible save you a few CPU cycles also.

What about NFM?

What about rebind in NFM. If you have already rebound the package in CM, it's not necessary. However, I would hope you went to NFM to use some of the new SQL like multi-row fetch to help reduce your CPU consumption even more. To use the new SQL, you are going to have to bind the package.

What else should I be aware of?

• Statistics. The more accurate your statistics are, the better your chance at DB2 picking the optimal access paths. I would take a serious look at Statistics Advisor, a no-charge option of V8 Visual Explain. Stats Advisor will tell exactly what RUNSTATS control cards should be used to gather the best statistics for an SQL statement. At a minimum, if you are going to pay the price of a rebind, the least you can do is run a few RUNSTATS jobs.
   
• If rebinds make you nervous, rebind into a test collection that is listed first in your package list. If you don't care for the access path, just drop the package from the test collection and DB2 will fall through the package list to the original package. Of course, this does require you to rebind the plan with the new package list.
   
• Also, remember to save you EXPLAIN output from Version 7, your accounting data from V7, and your statistical data for the same interval as the accounting data from V7 to verify any poor access paths are caused by V8 and not just carried over from V7.

Bottom line (IMHO), rebind it all in CM, bind for new function in NFM.

And my last comment on this subject. Do not rebind everything with a single rebind statement. Break the task up into multiple rebinds steps. And remember to validate your results.

August's Tip of the Month

Setting up DB2 v9 as a Coldfusion Data Source
By Victor Rubba, Reprinted From http://viconflex.blogspot.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30708

In the last few weeks I've had the opportunity to get to know Coldfusion just a little bit better. One of the first challenges I faced is getting CF talking to DB2, so I could take advantage of the CF Extensions for Flexbuilder and that funky Wizard. To be precise I used ColdFusion MX 7.02 and DB2 Community Express Edition v9.1.

Keep in mind I have only tested this in Windows:

1. Install the DB2 client or get access to a machine that does. I installed DB2 in the default folder on my dev machine to get this working.

2. Copy C:\Program Files\IBM\SQLLIB\java\db2java.zip to C:\CFusionMX7\lib. Important: Make sure db2jdbc.dll is in the path somewhere or copy it to same folder!

3. Restart Coldfusion MX. Login to CF Admin. Go to Datasources.

4. Add new Datasource:

   • driver type Other
   • CF DataSource Name: [sourcename]
   • JDBC url: jdbc:db2:[dbname]
   • Driver Class: COM.ibm.db2.jdbc.app.DB2Driver
   • Driver Name: DB2Driver
   • username: [name]
   • password: [password]
   • [Submit]

Couple things to note. Out of the blue the other day suddenly I started getting this db2jdbc not found in java path error... I figure something I installed hooped my system path. Instead of messing around with why, I figured out that just copying the dll into my CF lib folder fixed the problem. The ...app.DB2Driver is specific to Windows OS I believe. The username and password are usually a Windows account... probably best to use the account that has admin rights to db2 at least until you get it working.

I also did spend a good chunk of time trying to get the built in driver to talk to db2... I personally had no luck whatsoever, and I have read in other places that the drivers generally are only backwards compatible... the driver that ships with CF is definitely not v9, I think it might be v7... not sure though. I did test the backward compatibility claim by using the v9.1 jdbc driver against a db2 v8.x data source and it worked fine.

September's Tip of the Month

Working with Data in a DGTT
Extract from Knowledge Xpert for DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30709

Declared Global Temporary Tables (DGTTs) support data manipulation language (DML) constructs. However, you can only retrieve data from a DGTT table using the SELECT SQL statement (or a stored procedure that issues a SELECT statement). Since the schema definition for a DGTT is always SESSION, you must refer to your DGTTs using the SESSION schema qualifier either explicitly or by setting the default schema to resolve it.

For example, you can retrieve data from the sample DGTT used in this section using the SELECT * FROM SESSION.TEMPSTAFFTABLE shown in the following figure:

select * from tempstafftable
SQL0204N "PAULZ.TEMPSTAFFTABLE" is an undefined name. SQLSTATE=42704
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     12  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
  3 record(s) selected.

Note the error in the first transaction. It was returned because the SESSION schema qualifier was specified and it wasn't the default for the system in this example.

SELECT statements operate on DGTTs like any other tables. For example, you can perform projections (shown in the first example in the following figure), restrictions (shown in the second example), and other operations (shown in the remaining examples) on them:

select job, salary, comm from session.tempstafftable
JOB   YEARS  SALARY    COMM
----- ------ --------- ---------
Sales     12  30000.00    200.00
Mgr       24  31000.00   2000.00
ID        13  39999.00   3000.00
 3 record(s) selected
 
"select name from session.tempstafftable where salary > 31500"
NAME
---------
Melnyk
  1 record(s) selected.
  
select avg(salary) from session.tempstafftable
1
----------------------------------
 33666.333333333333333333333333333
 
  1 record(s) selected.
  
select salary+comm as TOTALCOMP from session.tempstafftable
TOTALCOMP
----------
  30200.00
  33000.00
  42999.00
  
  3 record(s) selected.

In addition to a SELECT statement, INSERT, UPDATE, and DELETE operations can also be performed on the data that resides in a DGTT. Don't forget that you must use the SESSION qualifier to perform the operation on the DGTT. This requirement sometimes eludes application developers when performing operations on specific columns of a DGTT (see the following example). An example of INSERT, UPDATE, and DELETE operations on a DGTT is shown below:

update session.tempstafftable set "Years" = 11 where
   session.tempstafftable.id=10
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
 3 record(s) selected.
 
insert into session.tempstafftable values (40,'Doyle',20,'ID',
15,23000,230)
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   40 Doyle         20 ID        15  23000.00    230.00
   
 4 record(s) selected.
 
delete from session.tempstafftable where id=40
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
 3 record(s) selected.

If you attempt to modify the contents of a DGTT using an INSERT, UPDATE, or DELETE statement, and the statement fails, what happens depends on how the DGTT was defined. . If the DGTT was created with the NOT LOGGED option, DB2 deletes all of the rows in the DGTT table, but the table still exists. If the DGTT was defined without the NOT LOGGED option, only the change made by the failed INSERT, UPDATE, or DELETE statement is rolled back.

If you modify the contents of a DGTT using an INSERT, UPDATE, or DELETE statement within the scope of a transaction, and subsequently roll back that transaction, DB2 deletes all of the rows in the DGTT.

October's Tip of the Month

Replicating DB2 Tables into CICS and CPU Usage
Reprinted from http://searchdatacenter.techtarget.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30710

Question:

We replicated one of our DB2 tables into a CICS maintained data table with 700,000 records. Calls to DB2 were replaced with calls to the data table. CPU usage went up. Our system is Threadsafe. Could the TCB switches caused by the data table calls be more expensive than DB2 calls? The data table is in an FOR accessed by multiple AORs.

Answer (from Robert Crawford):

If you're running CICS/TS 2.2 or earlier, the time spent in DB2 subtasks doesn't get collected in the CICS Monitoring Facility (CMF) bucket USRCPUT so the application won't get charged the true cost of running. When you went to data tables CICS could account for all the CPU time which is reflected in your results.

If you're running CICS/TS 2.2 or later the CPU in the open (L8) TCB's is included in USRCPUT. However, there is a great deal of overhead involved in function shipping to a file-owning region (FOR) depending on the type of file processing your application performs. Not only is there additional CPU in the AOR as it packages up the request, the FOR has to spend time attaching tasks and returning the requested record.

I have the following suggestions:

• I'm not aware of any task switching requirements for data tables. In CICS/TS 2.3 or earlier CICS tasks won't swap to an L8 TCB until the first DB2 call. For CICS/TS 3.1 you have to tell CICS to start the transaction on an open TCB (I don't know the parameter) from the start. You ought to look at an auxiliary trace to see if indeed you're transactions are moving between TCB's.
   
• If your application only reads the data table there's no reason you have to use an FOR. If possible, run a performance test of your application with the data table local to the AOR and measure that against the current performance.
   
• If the file requests must be function shipped try tuning the application to avoid browses and updates. You may also want to take advantage of MROLRM which keeps the mirror tasks alive on the FOR until the calling transaction takes a syncpoint.

November's Tip of the Month

Coding Efficient SQL
By Roger Sanders for Knowledge Xpert for DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30711

There are several steps to consider in coding efficient SQL statements.

The First Step: The first step, and the goal, is to simply code SQL to achieve the required result. Let the optimizer determine the required processing to achieve the result. This gives good performance in the majority of the cases and has a number of advantages:

It results in a high level of data independence and minimizes the need to change application programs when the structure of the data changes, new columns are added or deleted, a column is increased in length, a new index is added, new access strategies are introduced into DB2, enhancements to the optimizer are made, or the computer model is changed.

It reduces the amount and complexity of host-language code that must be developed, tested, and maintained.

It also enhances performance by reducing the path length. The more work that can be pushed down to DB2's level, the fewer instructions that must be executed in the host program.

The Second Step: If the first step does not produce acceptable performance, the second step is to rewrite the SQL so that a better access path can be chosen by the optimizer. A number of hints, tips, guidelines, and alternatives are included in this section of topics and throughout the product that should help you with this task.

The Third Step: If the required performance is still not achieved, the third step and last resort is to break the SQL into parts and introduce more procedural code. However, it does lose the advantages of the first step and should be avoided in a distributed environment because that means more data must be transmitted in most cases.

December's Tip of the Month

Simplify Object Management with Toad's Object Templates
From Knowledge Xpert for DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30712

Toad for DB2 lets you save the current settings on a Create or Create Like window as a template for creating other objects of the same type. As you create other objects, you can load this template to populate fields automatically in the Create or Create Like window with values from the template. Using templates can save you time in creating objects that have similar attributes.

To save the current settings as template:

1. In the Create or Create Like window for a specific object type, provide the field values that you want to save in the template.

2. Click on the toolbar in the Create or Create Like window.

3. In the Save As window, enter a name for the template file.

4. Click Save.

   The template is saved to the storage location defined in Tools | Options | Databases | DB2 and is available for selection when you define an object of the same type.

   Note: Toad appends the DB2 platform (DB2LUW or DB2ZOS) and the object type to the name you specify.

Create a Default Template for a Database Object Type

Toad lets you create a default template that is automatically loaded each time you open the Create window for a specific database object type. (The fields in the window are pre-filled with values from the template.)

Depending on how you set up global options for default templates, you can create the following default templates:

• One default template per object type on each DB2 platform (LUW or z/OS)

  Or

• One default template per object type for each database on each DB2 platform

  Or

• One default template per object type for each user connection to each database on each DB2 platform.

Note: To set global options for default templates, use Tools | Options | Database | DB2.

To create a default template:

1. In the Create or Create Like window for a specific object type, provide the field values that you want to save in the template.

2. Click on the toolbar in the Create or Create Like window.

3. On the confirmation window, click Yes to save the current settings as the default template for this object type.

   The template is saved to the template location defined in Tools | Options | Databases | DB2 and is automatically loaded the next time you define an object of the same type.

   Note: Toad generates the name for the default template. This name identifies the object type, the DB2 platform (DB2LUW or DB2ZOS), and optionally the database name or the user ID connected to the database (if default templates are globally defined as database- or connection-specific in Tools | Options | Databases | DB2).

Load a Template

From the Create or Create Like window in an object editor, you can open a user-defined template to fill in fields automatically with values from the template. Using a template can save you time in creating objects that have similar attributes.

To load a template:

1. In the Create or Create Like window for the object you are creating, click on the toolbar.
    
2. In the Open window, select the file name for the template you want to open.
    
3. Click Open. The Create or Create Like window fields are populated with the values from the template.
    
4. Edit the values as needed before executing the script to create the object.

For questions or comments on this site: webmaster@quest-pipelines.com
All content Copyright © Quest Software, Inc. All rights reserved.