This was not one of your conventional migrations. It came with all kinds of challenges for all the teams handling it. The application (a mission critical production application) had to be migrated to a different platform and hardware. The database had to be migrated to a higher version of DB2 from version 8.2 and to a better server and hardware.

These were some of the challenges that the migration teams faced:

1. To start with, the source server hardware (an old antique) was completely different from the target server( a P5) which meant tape incompatibility, so that meant we cannot use TSM tapes to restore anything.
2. Next, there was not sufficient disk space on the source server to pull the backup image to disk (the backups and logs went to TSM) and ftp the image from source to target. There was no easy way to add additional disks to the source because of hardware limitations ( I think I did mention this was an antique!)
3. While I was considering all this, the customer came up with another one. Since the application was to be moved to a different platform, it required a different code set on the target than the source. As all the DB2 DBAs know, there is no way you can change the code set at target when we use DB2 backup and restore.

This took out the backup/restore option from the table completely, which was perhaps a good thing!  The next obvious step was to create the database skeleton on the target using the db2look from source but with different code set as required. That was easily accomplished.

But the next step was pretty sticky! All of the production data needed to be migrated to the target server in the cutover window in one shot.

I was considering a couple of options to achieve this:

• Using db2move and ftp all the files across and load at target. But the database had hundreds of tables and some of them quite big.
• Using the DB2 federated server option at the target, creating a wrapper for the source and pulling data in.

I was inclining more towards the second option, which appeared less messy than the first. However I was still researching for a better solution that would be more cool to use and I was quite sure DB2 must have such an option!

Then I came across this new feature – DB2 remote load from cursor. It proved to be a beautiful tool as I discovered it later to be. I sincerely wish I had known it before, as it would have made my life a lot easier for most of my day to day tasks.

“Remote load from cursor” or “Load from cursor using remote fetch” is a new feature introduced with DB2 V9.1 and above, but the source database can be a lower version than the target and the codepage can be different which makes this as one of the “cool” migration tools to use.

Here is how we can achieve a pure data migration using the remote cursor:

Remote catalog the source db on the target instance.

• Define a cursor based on the remote connection like this:  db2 ” DECLARE C1 CURSOR DATABASE <sourcedbname>  USER <userid> USING <passwd> FOR <SELECT * FROM <source tablename>>”
• Last step is to use “load from cursor C1…..” like you would for any load from cursor.

I have used it to transfer even LOB data and its pretty fast. Ofcourse, the size of the tables matter and it is not a good fit for moving very large amounts of data within a short timeframe. But it proved to be a perfect fit in my case and I found so many uses for it afterwards too because it completely eliminates the export step whenever we need to migrate data. DBAs will find that this tool makes the dull and boring part of their tasks easy, cool and fun!

For more detailed information you can refer to the following link from the IBM DB2 info center:  http://bit.ly/pawUsT

I will expand the presentation further for you to include some situations where you can still employ HADR even though at first glance it might look like you cannot.

First, there are some situations and conditions where HADR can play a role as a migration tool:

1. The source and target operating systems are the same and at the same version and the source and target DB2 levels are the same as well.
    
2. Source and target are at different geographical locations and the condition one described above holds true.

With some minor twists you can achieve our objective even if the condition number one doesn’t hold exactly true.

To set up  HADR between any two servers requires that the servers have the same operating system and level and same DB2 version and level.

To overcome this obstacle and to migrate the data easily using HADR, there are some workarounds:

1. If you are migrating using the same platform but different versions , for example, from source AIX 5.3 to  AIX 6 on the target, you can still use HADR, but, here is the deal. You will leave the target at 5.3 until HADR is set up and the database is switched over and then you can break HADR and upgrade the target OS version.
2. For migrating to a different DB2 version, you can  follow the same work around . For example if you needed to move from source DB2 9.1 to target DB2 9.7, leave the target at 9.1 and at same version and fix pack level as source. Restore the database on the target as standby, establish HADR, swing the HADR to switch roles . Disconnect HADR and then upgrade target.

For both Operating System and DB2 upgrades the down time required is very little and can be done using the normal maintenance window once database migration is complete.

This work around for different versions of source and target will make it easier for large databases to be migrated without the usual fuss and pressure associated with such migrations. Setting up HADR involves most of the work to be done in the background without disturbing the production site.

All the work of sending the backup from source site to target, restore of the database at the target site in the roll forward pending mode, configuring the target with standby HADR configurations can all be done without in anyway affecting the source site.

The only time you will need to touch the source site will be to update the HADR configurations on the source database. And then once more to activate HADR once standby is ready.

Migration Test Run

Another added benefit with using HADR is, you can test run the HADR activation to see if it works without actually switching over the primary and standby.

All you need to do is to start the HADR on primary source and standby target and once the HADR reaches peer state, disconnect HADR, bring up standby as a normal standard database and verify if all your data from the primary was replicated on the standby.

Once the test yields satisfactory results, you can restore the original backup again on the target, as before, in roll forward pending state. Restart HADR to allow it to catch up, set up the cutover date and window at your own convenience and then swing the HADR to switch roles. After the switch you have a fully migrated database in minutes with no data loss and very little down time. This migration process, while being unconventional (and cool!), has very low risks and comes with a complete back out plan, all you need to do is swing back the HADR!

The best part of this migration is, it is also a solid Data Recovery solution if you keep HADR up and running. And believe me once you see how robust the solution is, you want to stay with it like my customer did.

The IBM information center has very useful information for setting up HADR from scratch: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0021056.htm This is the link for V9.1, the same info is available for 9.5 and 9.7 in the information center.

A replay of my HADR presentation is available on the DB2 Night Show website: http://www.dbisoftware.com/blog/db2nightshow.php?id=236

Have a wonderful day!

There are many reasons why this is not possible. Most of them are technical, but sometimes as simple as: working for multiple customers as a freelance worker. So how to keep track of all these servers, user-ids, passwords and settings? It can drive you nuts! Writing down is not an option because sooner or later somebody will misuse your notes (please, no sticky notes on the screen!). A password manager is only half the solution because you still have to initiate the session yourself and then copy / paste the user-id and password. And no matter how smart this password manager is. You will curse it after you have dome a few hundred times a copy – paste or drag & drop.

A few years ago I bumped into a solution: Remote Desktop Manager is a software solution that keeps track of all your servers, protocols, special ways to connect and even user-id and password. The name is a bit misleading because it does do much, much more than "just" Microsoft’s Remote Desktop. It supports over 20 connection types from simple FTP, Telnet and X-Window up to LogMeIn, VPN and virtualization software (and more with a plugin). The software has two versions: a standard (free) version and a enterprise version. The enterprise version does focus a lot on security and has the ability to set-up a central database of connections.

The package does everything you want such a package to do: fire-up the right software, supply the credentials for the server, do scripting and much more. Pricing for the enterprise version starts at $35 per user and comes down rapidly for multiple users. Very reasonable for what the software offers and who knows, you might be happy with what the free standard version offers. I depend on the software every day and it has made my job much more productive. It is so relaxed… grab a coffee and with one mouse click your connected to the server you want to work with.

Check it out for yourself: http://remotedesktopmanager.com/ and do a trial.

I am not related in any way to the company that creates this little gem, I just think it is what every DBA or support person could use this.

This tip is provided by: Klaas Brant – KBCE b.v. Do you have a hot tip? Please mail it to info@kbce.com to share it with the DBBuzz community.

1. Sometimes there are many download links and you want most or all of them (e.g. all the PDF DB2 manuals for DB2 10 on page https://www.ibm.com/support/docview.wss?uid=swg27019288#manuals).
     
2. The file you have to download is a file that is huge (e.g. a DB2 LUW Fix-Pack on page https://www.ibm.com/support/docview.wss?uid=swg27007053) and with a single threaded http download this will take a long time. Many servers allow a multi-threaded download where each thread grabs part of the file. For example when you split a file in four piece and all components cooperate then the download will finish in 25% of the time. It is of course possible that one of the components will block you or limit you in such an attempt.

None of the existing browsers are going to help you with these two issues. Downloads are single threaded and you have to select the links one by one. Solution? A browser extension! I use Firefox as my default browser and it has a perfect plugin for my problem: DownThemAll.

DownThemAll is an open source and powerful, yet easy to use, Firefox extension that adds new advanced download capabilities to Firefox on windows, Linux and mac platforms. DownThemAll lets you download all the links contained in a webpage and much more: you can refine your downloads by fully customizable criteria to get only what you really want. Download all your manuals with a single mouse click! DownThemAll is also an advanced accelerator that increases speed up to 400%, it allows you to pause and resume downloads at any time. It fully integrated into Firefox and with each download you have can opt for regular download or DownThemAll. This plugin has been downloaded more than 40 million times! After you installed DownThemAll a webpage opens with some nice instruction videos. Like many open source projects

For those of you thinking "Great! But I use Internet Explorer or Chrome. Is there a version for my browser?" I have sad news: The answer is “No”, mainly due to technical limitations of your browser.

Want to read more before installing it in your browser? You can read all about this little gem on http://www.downthemall.net/. Happy downloading!

This tip is provided by: Klaas Brant – KBCE b.v. Do you have a hot tip? Please mail it to info@kbce.com to share it with the DBBuzz community.

I have learned that to get the best value out of any IDUG conference, planning is very important. So this is planning time!

I start by defining my learning objectives for this conference: I am looking forward to get most of the DB2 for z/OS sessions, with a particular interest in DB2 10 for z/OS. I have been working recently with this new DB2 and this seems to me a very interesting edition. I am eager for any additional education related to 10.

I also promised myself to take the opportunity to attend sessions that are not directly related to my work or preferences. IDUG is all about DB2 anyway, and it will not harm anybody to expand his or her IT world perception, right?

The first step for me is to get the Conference Event Grid from http://bit.ly/1d4X3R. This is a colourful pdf containing the conference sessions schedule and other events. If you are used to the European version of the grid, you will notice that the sessions’ colours have not the same meaning: they identify at a glance the type of session rather than the speaker category:

• BLUE = Hands-on Lab
• ORANGE = DB2 for Linux, UNIX, Windows
• PURPLE = DB2 for z/OS
• GREEN = Cross Platform
• RED = Special Interest Groups (SIGs)

Below figure shows a portion of the grid while I was selecting what sessions to attend:

Also visit the Online Conference Schedule at http://bit.ly/g7DnTQ. This is a great source of information and complements very well the grid; here you can see the sessions’ details, abstracts and objectives. It helped me a lot during my selection exercise.

But there is a lot more educational and networking value to get advantage from. Susan Visser has a very interesting blog post that exposes all the learning and networking potential of the conference, a blog post not to be missed: “All about IDUG DB2 Tech Conference – Anaheim” at http://ibm.co/eaAWXZ.

Problems started soon: because the quality of the offering is so appealing to me, I am having a hard time deciding which sessions not to attend! (I wish I could be at two places at the same time, but bi-temporal support is not intended to solve this problem). Fortunately we will have access to the conference proceedings and I will be able to study the presentations that I will be missing anyway.

So my travel plan looks like:

Tuesday, May 3, 2011

• S01: DB2 10 Technical Overview / Jeff Josten
• B01: How I Learned to Stop Worrying and Love Plan Management and Access Path Stability / Gerald Hodge
• B02: Mastering Distributed Access to DB2 for z/OS / Cristian Molaro. I must attend this one, otherwise I would take G02: DB2 LUW Security –What If Your Firewall Catches Fire? / Rebecca Bond
• B03: DB2 for z/OS: Availability Enhancements / Haakon Roberts
• F04: Database Performance Discoveries and Recommendations / Dave Beulke

Wednesday, May 4, 2011

• B05: The DB2 V10 Catalog – A Revolution / Steen Rasmussen
• B06: DB2 for z/OS Migration Planning Survival Guide / Linda Claussen
• F07: DB2 10 for z/OS – John Deere’s Beta Experience / Bryan Paulsen
• B08: Why Did the DB2 for z/OS Optimizer Choose that Access Path? / Terry Purcell

Thursday, May 5, 2011

• F09: The Importance of Being Consistent – DB2 for z/OS and Copy Services for IBM System z / Florence Dubois
• B10: DB2 10 for z/OS Planning and Very Early Experiences / John Campbell
• A11: A DB2 Hitchhikers Guide to RMF and SMF / Frank Petersen
• B12: Omegamon Performance Database (z/OS) – Exploit the Power of the PDB to Solve Performance Issues / Billy Sundarrajan

Friday, May 6, 2011

• B13: DB2 for z/OS Optimizer: What Have You Done for Me Lately? / Terry Purcell
• C14: Stuffed with Great Enhancements – New Features of DB2 9.7 Fix Packs / Melanie Stopfer
• G15: Monitoring and Troubleshooting Distributed Access to DB2 for z/OS / Cristian Molaro. I must attend this one, otherwise I would take B15: DB2 Design – Selected (Controversial?) Topics / Suresh Sane

This is my plan. Yours will vary of course based on your personal preferences and objectives. One of the greatest things about the IDUG Tech Conference is that there is value for everybody waiting to be enjoyed. Have a look at Rebecca Bond’s excellent and refreshing blog posts “IDUG NA – The Geeky Vacation!” at http://ibm.co/fbmAag and “Budgeting for IDUG North America conference as viewed by the DB2 Locksmith” at http://ibm.co/g3Yozr.

Bottom line: I strongly recommend that you take the time to prepare your IDUG DB2 Tech Conference experience in advance, there is a great DB2 offering waiting for you there.

Not attending Anaheim? Keep tuned for the IDUG DB2 Tech Conference in Prague, Czech Republic, 14-18 November 2011. We are working hard preparing a great EMEA Conference!

See you there! – Cristian Molaro

At regular intervals ABIS publishes sets of articles relating to DB2. This series of publications is titled Exploring DB2. Unfortunately for most readers, these publications are written entirely in the Dutch language.

In the Januari 2010 issue Peter Vanroose challenged us – the readers – to send in a query for presenting information from the extended explain tables. Of course, I could not resist such a challenge. Here are the results of my research on extended explain tables. This document contains the following parts:

1. The Query
2. Explain with Range Information
3. Explain with Range and Sort Information.

1. The Query

For a bit of variety, I wanted to create a query using a Common Table Expression and at least one partitioned table. Therefore a catalog query seemed less appropriate. So I created a query on the IVP database in stead. After all, the IVP database contains a partitioned table and most installations do have the IVP database installed. Hopefully therefore everybody will be able to use the queries below.

It is entirely evident that this query is not very efficient. This time we’re looking for explain data, not performance. Query optimization is outside this article’s scope. The same holds true for creating the extended explain tables. These are described in the Performance Monitoring and Tuning Guide and will be created for you by OSC. Alternatively, you can use member DSNTIJOS in the SDSNSAMP library.

--
   with     managers
           (mgrno,
            mgrname
            )
   as      (select   distinct
                     mgr.empno
                   , strip(coalesce(firstnme, '')) CONCAT
                     ' ' CONCAT
                     strip(coalesce(lastname, '???')) as mgrname
            from     DSN8810.DEPT dept
            join     DSN8810.EMP  mgr
                 on  mgr.empno = dept.mgrno
            )
   select   mgr.mgrname
          , dept.deptname
          , emp.lastname
          , emp.firstnme
   from     DSN8810.EMP emp
   left outer join
            DSN8810.DEPT dept
        on  dept.deptno = emp.workdept
   left outer join
            managers mgr
        on  mgr.mgrno = dept.mgrno
   where    emp.empno between '000000' and '150000'
        and emp.workdept between 'A' and 'EEE'
   order by case when dept.deptno IS NULL
                 then '*No dept'
                 when mgr.mgrno IS NULL
                 then '*No mgr'
                 else mgr.mgrname
            end,
            emp.empno
  ;

The result:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
MGRNAME                       DEPTNAME                              LASTNAME         FIRSTNME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
CHRISTINE HAAS                SPIFFY COMPUTER SERVICE DIV.          HAAS             CHRISTINE
CHRISTINE HAAS                SPIFFY COMPUTER SERVICE DIV.          LUCCHESI         VINCENZO
CHRISTINE HAAS                SPIFFY COMPUTER SERVICE DIV.          O'CONNELL        SEAN
EVA PULASKI                   ADMINISTRATION SYSTEMS                PULASKI          EVA
EVA PULASKI                   ADMINISTRATION SYSTEMS                JEFFERSON        JAMES
EVA PULASKI                   ADMINISTRATION SYSTEMS                MARINO           SALVATORE
EVA PULASKI                   ADMINISTRATION SYSTEMS                SMITH            DANIEL
EVA PULASKI                   ADMINISTRATION SYSTEMS                JOHNSON          SYBIL
EVA PULASKI                   ADMINISTRATION SYSTEMS                PEREZ            MARIA
IRVING STERN                  MANUFACTURING SYSTEMS                 STERN            IRVING
IRVING STERN                  MANUFACTURING SYSTEMS                 ADAMSON          BRUCE
IRVING STERN                  MANUFACTURING SYSTEMS                 PIANKA           ELIZABETH
IRVING STERN                  MANUFACTURING SYSTEMS                 YOSHIMURA        MASATOSHI
IRVING STERN                  MANUFACTURING SYSTEMS                 SCOUTTEN         MARILYN
IRVING STERN                  MANUFACTURING SYSTEMS                 WALKER           JAMES
IRVING STERN                  MANUFACTURING SYSTEMS                 BROWN            DAVID
IRVING STERN                  MANUFACTURING SYSTEMS                 JONES            WILLIAM
IRVING STERN                  MANUFACTURING SYSTEMS                 LUTZ             JENNIFER
MICHAEL THOMPSON              PLANNING                              THOMPSON         MICHAEL
SALLY KWAN                    INFORMATION CENTER                    KWAN             SALLY
SALLY KWAN                    INFORMATION CENTER                    QUINTANA         DOLORES
SALLY KWAN                    INFORMATION CENTER                    NICHOLLS         HEATHER
DSNE610I NUMBER OF ROWS DISPLAYED IS 22
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

2. Explain with Range information

After executing

   EXPLAIN ALL SET QUERYNO=114 FOR

with the above query the various explain tables will be supplied with appropriate data.

A basic query on just PLAN_TABLE tells us there are 7 steps (table rows) required for executing this query. From DSN_SORT_TABLE we learn there are 4 sorts:
two for the order-by clause, 1 for the distinct clause in the Common Table Expression, and 1 for executing the join with the CTE’s result-set.

As a first step I wanted to extend my standard query for PLAN_TABLE with data on a potential page-range scan. After all, that would make a tablespace scan (accesstype=R) slightly less expensive… To show this, we need to use data from the DSN_PGRANGE_TABLE. I have decided to add the number of partitions to the access column.

Some points of note regarding the below query and its results:

• Formatting is not ideal – especially the leftmost 5 columns could use some leading spaces. However, presentation was not the primary purpose of this exercise.
• Result rows are sorted – within each query – descending on Parent Qblockno. I started out sorting them ascending, but that resulted in a – to my taste – illogical sequence. With descending sort the result more closely matches the actual order of processing by DB2.
• In column Access you may note the I(2) occurrence – accompanied by LST in the PREF column indicating List Prefetch – which means
  that DB2 will perform an Index Scan on 2 partitions. Quite a bit better than a full Index Scan which would have had to process all 5 partitions.
• In the very same Access column you may also note the occurrence of T/R. This means the result set of the CTE will be materialized and subsequently accessed using a sparse index. This is the meaning of the T. Should this fail to work out, DB2 will perform a full scan of the CTE’s result set; this alternative being indicated by the R.
• Information on locking is not shown. Anyone interested can easily add the relevant colums.

The explain Query:

--
-- Query on PLAN_TABLE with Page-Range info
--
   SELECT   SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
          , CASE PLAN.PARENT_QBLOCKNO
                 WHEN 0 THEN ' '
                        ELSE
                        SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
            END AS PBLK
          , SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
          , SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
          , CASE PLAN.MIXOPSEQ
                 WHEN 0 THEN ' '
                        ELSE SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
            END AS OPSQ
          , PLAN.QBLOCK_TYPE AS TYPE
          , CASE PLAN.JOIN_TYPE
                 WHEN 'F' THEN 'Full'
                 WHEN 'P' THEN 'Pair'
                 WHEN 'S' THEN 'Star'
                 WHEN 'L' THEN CASE PLAN.METHOD
                                    WHEN 1 THEN 'L/R'
                                    WHEN 2 THEN 'L/R'
                                    WHEN 4 THEN 'L/R'
                                           ELSE '?'
                                    END
                 WHEN ' ' THEN CASE PLAN.METHOD
                                    WHEN 1 THEN 'Innr'
                                    WHEN 2 THEN 'Innr'
                                    WHEN 4 THEN 'Innr'
                                           ELSE ' '
                                    END
                          ELSE '*ERR*'
            END AS JOIN
          , CASE PLAN.METHOD
                 WHEN 0 THEN 'First'
                 WHEN 1 THEN 'NLjoin'
                 WHEN 2 THEN 'MSjoin'
                 WHEN 3 THEN 'Sort'
                 WHEN 4 THEN 'Hybrid'
                        ELSE 'UNKNWN'
            END AS METHOD
          , CASE PLAN.TABLE_TYPE
                 WHEN 'B' THEN 'Buffer'
                 WHEN 'C' THEN 'CTE'
                 WHEN 'F' THEN 'TabFun'
                 WHEN 'M' THEN 'MQT'
                 WHEN 'Q' THEN 'Temp'
                 WHEN 'R' THEN 'Recurs'
                 WHEN 'T' THEN 'Table'
                 WHEN 'W' THEN 'Work'
                          ELSE ' '
            END AS TYPE
          , SUBSTR(PLAN.CREATOR, 1, 8) AS CREATOR
          , SUBSTR(PLAN.TNAME, 1, 18) AS TABLE
          , SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
          , CASE PLAN.PRIMARY_ACCESSTYPE
                 WHEN 'D' THEN 'D/'
                 WHEN 'T' THEN 'T/'
                          ELSE ''
            END CONCAT
            STRIP(PLAN.ACCESSTYPE) CONCAT
            CASE WHEN PLAN.PAGE_RANGE = 'Y'
                 THEN '(' CONCAT
                      STRIP(SUBSTR(STRIP(CHAR(RANGE.NUMPARTS)), 1, 4))
                      CONCAT ')'
                 ELSE ''
            END AS ACCESS
          , CASE PLAN.PREFETCH
                 WHEN 'S' THEN 'SEQ'
                 WHEN 'L' THEN 'LST'
                 WHEN 'D' THEN 'DYN'
                          ELSE ' '
            END AS PREF
          , CASE PLAN.INDEXONLY
                 WHEN 'Y' THEN 'XO'
                          ELSE ' '
            END AS XO
          , CASE PLAN.MATCHCOLS
                 WHEN 0 THEN ''
                        ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
            END AS MC
          , CASE PLAN.SORTC_GROUPBY
                 WHEN 'Y' THEN 'G'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_JOIN
                 WHEN 'Y' THEN 'J'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_ORDERBY
                 WHEN 'Y' THEN 'O'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_UNIQ
                 WHEN 'Y' THEN 'U'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_GROUPBY
                 WHEN 'Y' THEN 'G'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_JOIN
                 WHEN 'Y' THEN 'J'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_ORDERBY
                 WHEN 'Y' THEN 'O'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_UNIQ
                 WHEN 'Y' THEN 'U'
                          ELSE ' '
            END AS GJOUGJOU
          , CASE PLAN.PARALLELISM_MODE
                 WHEN 'I' THEN 'I/O'
                 WHEN 'C' THEN 'CPU'
                 WHEN 'X' THEN 'SYS'
                          ELSE ' '
            END AS PAR
          , CASE PLAN.CTEREF
                 WHEN 0 THEN ' '
                 ELSE STRIP(CHAR(PLAN.CTEREF))
            END AS CTEREF
   FROM     PLAN_TABLE PLAN
   LEFT OUTER JOIN
            DSN_PGRANGE_TABLE RANGE
        ON  RANGE.QUERYNO  = PLAN.QUERYNO
        AND RANGE.QBLOCKNO = PLAN.QBLOCKNO
        AND RANGE.TABNO    = PLAN.TABNO
   WHERE    PLAN.QUERYNO = 114
   ORDER BY PLAN.QUERYNO
          , PLAN.PARENT_QBLOCKNO DESC
          , PLAN.QBLOCKNO
          , PLAN.PLANNO
          , PLAN.MIXOPSEQ
  ;

The result:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
QUERY  PBLK  QBLK  PLNO  OPSQ  TYPE    JOIN   METHOD  TYPE    CREATOR   TABLE               NDXNAME   ACCESS      PREF  XO  MC  GJOUGJOU  PAR  CTEREF
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
114    1     3     1           TABLEX         First   Table   DSN8810   DEPT                XDEPT2    I                 XO
114    1     3     2           TABLEX  Innr   NLjoin  Table   DSN8810   EMP                 XEMP1     I                     1
114    1     3     3           TABLEX         Sort                                                                                 U
114          1     1           SELECT         First   Table   DSN8810   EMP                 XEMP2     I(2)        LST       1
114          1     2           SELECT  L/R    NLjoin  Table   DSN8810   DEPT                XDEPT1    I                     1
114          1     3           SELECT  L/R    NLjoin  Work    TU00001   MANAGERS                      T/R                            J
114          1     4           SELECT         Sort                                                                                O
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

3. Explain with Range and Sort information

Next I wished to see whether I could add information from DSN_SORT_TABLE to this query in order to get a more complete overview of explained queries. This means that rows from a different table will have to be added which implies a UNION ALL will have to be used. Not at all a problem in itself, but it does have a few complicating consequences:

First of all the ORDER BY clause needs to be changed. Column names can no longer be used, in stead we need to specify column numbers. This reduces the query’s legibility and understandability, but so be it.

At the same time we now have two subqueries each having a WHERE clause that needs to select the correct query for explaining. I don’t like such solutions; not just because it’s redundant, but mainly because it happens all too often that these WHERE clauses get out of sync, rendering the end result of the complete query useless. Therefore I have added a CTE named QUERIES to hold the number(s) of the query/queries to be explained.

As it turned out I needed two more CTEs in order to find first the numbers and subsequently the names of the tables to be sorted. In order to limit the size of the result sets of these new CTE’s, I reused the QUERIES CTE, which holds the relevant query numbers. Thus the QUERIES CTE now replaces a total of 4 WHERE clauses.

Thus I added three CTEs in order to extend my query. This, however, entailed a risk: when using three or more CTEs a CONCAT function in the SELECT clause may result in column values being prefixed with an extraneous byte containing a value of X’00′, which can be quite ugly. Luckily, this query does not suffer from this problem because it does not comply with the “requirement” that a specific relation exist between the CTEs involved.

After verifying this I could start modifying the actual query. The first part remained almost the same.Only the WHERE clause needed to be changed. Selection on query-number had to be replaced by an inner join with relevant query numbers, as supplied by the CTE QUERIES. And – since data on sorting now will be obtained from DSN_SORT_TABLE – rows with METHOD=3 should now be skipped.

In order to add the data on sorting a second query is added to the existing one. Of course this second query has to create a result set with the same columns, although it obtains its data from DSN_SORT_TABLE in stead of PLAN_TABLE.

With this query and its result set, as with the previous one, some points of note are:

• Columns PAR and CTEREF have been removed to make room. These columns were empty in this example anyway…
• Compared with the prior version two rows have disappeared. These are the rows indicating a sort (method = 3). I might have let them remain, but they’d have been redundant. It’s a matter of preference.
• For the join-with-sort some of the information does appear double. I might have suppressed it, but I didn’t feel it was worth the effort. Again: a matter of taste.
• 4 new rows ahve been added, based on the content of DSN_SORT_TABLE. The net result being an increase of two rows. Thus we now have more information available than with the prior query.
• Results are sorted slightly awkward. For a JOIN requiring a sort, the JOIN is shown before the SORT, while of course DB2 will perform the SORT before the JOIN, rather than afterwards. A solution for this sorting problem falls outside this article’s scope.
• Column OPSQ (or MIXOPSEQ) now contains numbers for the rows originating from the PLAN_TABLE (not visible in our example) and characters for the various sort-steps (SORTNO) from the sorting table.This was done primarily to prevent numbers from being assigned and appearing more than once. As a secondary advantage we can now readily distinguish between the two types of rows.
• We can now more easily see what the Access T/R entails: the CTE’s result-set is being sorted, creating an intermediate result set, which can be accessed efficiently using a sparse index. Should DB2, for whatever reason, be unable to perform the sort, DB2 will use a “normal” tablespace scan in stead.
• It looks like the Order by clause is being implemented using two sorts. If you look more closely, however, you’ll notice that the first of these sorts is exactly identical to the sort for DISTINCT in the CTE. Further investigation by IBM-Belgium’s Bart Steegmans has revealed that the first sort is not actually executed. The pertinent rows in DSN_SORT_TABLE and DSN_SORTKEY_TABLE have been created in error by explain. To amend this situation IBM have opened APAR PM16586.

The Explain Query:

--
-- Query on PLAN_TABLE with Page-Range info
--                      and sorting info
--
   WITH     QUERIES
           (QUERYNO)
   AS      (SELECT   114
            FROM     SYSIBM.SYSDUMMY1
            )
          , SORTKEYS
   AS      (SELECT DISTINCT
                     SKEY.QUERYNO
                   , SKEY.QBLOCKNO
                   , SKEY.PLANNO
                   , SKEY.SORTNO
                   , SKEY.TABNO
            FROM     DSN_SORTKEY_TABLE SKEY
            JOIN     QUERIES QRYS
                 ON  QRYS.QUERYNO = SKEY.QUERYNO
            WHERE    SKEY.TABNO <> 0
            )
          , TABLES
   AS      (SELECT DISTINCT
                     PLAN.QUERYNO
                   , PLAN.TABNO
                   , PLAN.TABLE_TYPE
                   , PLAN.CREATOR
                   , PLAN.TNAME
            FROM     PLAN_TABLE PLAN
            JOIN     QUERIES QRYS
                 ON  QRYS.QUERYNO = PLAN.QUERYNO
            WHERE    PLAN.TABNO <> 0
            )
   SELECT   SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
          , CASE PLAN.PARENT_QBLOCKNO
                 WHEN 0 THEN ' '
                        ELSE
                        SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
            END AS PBLK
          , SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
          , SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
          , CASE PLAN.MIXOPSEQ
                 WHEN 0 THEN ' '
                        ELSE SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
            END AS OPSQ
          , PLAN.QBLOCK_TYPE AS TYPE
          , CASE PLAN.JOIN_TYPE
                 WHEN 'F' THEN 'Full'
                 WHEN 'P' THEN 'Pair'
                 WHEN 'S' THEN 'Star'
                 WHEN 'L' THEN CASE PLAN.METHOD
                                    WHEN 1 THEN 'L/R'
                                    WHEN 2 THEN 'L/R'
                                    WHEN 4 THEN 'L/R'
                                           ELSE '?'
                                    END
                 WHEN ' ' THEN CASE PLAN.METHOD
                                    WHEN 1 THEN 'Innr'
                                    WHEN 2 THEN 'Innr'
                                    WHEN 4 THEN 'Innr'
                                           ELSE ' '
                                    END
                          ELSE '*ERR*'
            END AS JOIN
          , CASE PLAN.METHOD
                 WHEN 0 THEN 'First'
                 WHEN 1 THEN 'NLjoin'
                 WHEN 2 THEN 'MSjoin'
                 WHEN 3 THEN 'Sort'
                 WHEN 4 THEN 'Hybrid'
                        ELSE 'UNKNWN'
            END AS METHOD
          , CASE PLAN.TABLE_TYPE
                 WHEN 'B' THEN 'Buffer'
                 WHEN 'C' THEN 'CTE'
                 WHEN 'F' THEN 'TabFun'
                 WHEN 'M' THEN 'MQT'
                 WHEN 'Q' THEN 'Temp'
                 WHEN 'R' THEN 'Recurs'
                 WHEN 'T' THEN 'Table'
                 WHEN 'W' THEN 'Work'
                          ELSE ' '
            END AS TYPE
          , SUBSTR(PLAN.CREATOR, 1, 8) AS CREATOR
          , SUBSTR(PLAN.TNAME, 1, 18) AS TABLE
          , SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
          , CASE PLAN.PRIMARY_ACCESSTYPE
                 WHEN 'D' THEN 'D/'
                 WHEN 'T' THEN 'T/'
                          ELSE ''
            END CONCAT
            STRIP(PLAN.ACCESSTYPE) CONCAT
            CASE WHEN PLAN.PAGE_RANGE = 'Y'
                 THEN '(' CONCAT
                      STRIP(SUBSTR(STRIP(CHAR(RANGE.NUMPARTS)), 1, 4))
                      CONCAT ')'
                 ELSE ''
            END AS ACCESS
          , CASE PLAN.PREFETCH
                 WHEN 'S' THEN 'SEQ'
                 WHEN 'L' THEN 'LST'
                 WHEN 'D' THEN 'DYN'
                          ELSE ' '
            END AS PREF
          , CASE PLAN.INDEXONLY
                 WHEN 'Y' THEN 'XO'
                          ELSE ' '
            END AS XO
          , CASE PLAN.MATCHCOLS
                 WHEN 0 THEN ''
                        ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
            END AS MC
          , CASE PLAN.SORTC_GROUPBY
                 WHEN 'Y' THEN 'G'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_JOIN
                 WHEN 'Y' THEN 'J'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_ORDERBY
                 WHEN 'Y' THEN 'O'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTC_UNIQ
                 WHEN 'Y' THEN 'U'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_GROUPBY
                 WHEN 'Y' THEN 'G'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_JOIN
                 WHEN 'Y' THEN 'J'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_ORDERBY
                 WHEN 'Y' THEN 'O'
                          ELSE ' '
            END CONCAT
            CASE PLAN.SORTN_UNIQ
                 WHEN 'Y' THEN 'U'
                          ELSE ' '
            END AS GJOUGJOU
   FROM     PLAN_TABLE PLAN
   JOIN     QUERIES QRYS
        ON  QRYS.QUERYNO = PLAN.QUERYNO
   LEFT OUTER JOIN
            DSN_PGRANGE_TABLE RANGE
        ON  RANGE.QUERYNO  = PLAN.QUERYNO
        AND RANGE.QBLOCKNO = PLAN.QBLOCKNO
        AND RANGE.TABNO    = PLAN.TABNO
   WHERE    PLAN.METHOD <> 3
  UNION ALL
   SELECT   SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
          , CASE PLAN.PARENT_QBLOCKNO
                 WHEN 0 THEN ' '
                        ELSE
                        SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
            END AS PBLK
          , SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
          , SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
          , CASE WHEN PLAN.MIXOPSEQ <> 0
                 THEN SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
                 ELSE ''
            END CONCAT
            CASE WHEN SORT.SORTNO > 0
                 THEN SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                             SORT.SORTNO, 1)
                 ELSE ''
            END AS OPSQ
          , PLAN.QBLOCK_TYPE AS TYPE
          , '' AS JOIN
          , 'Sort' AS METHOD
          , CASE TBLS.TABLE_TYPE
                 WHEN 'B' THEN 'Buffer'
                 WHEN 'C' THEN 'CTE'
                 WHEN 'F' THEN 'TabFun'
                 WHEN 'M' THEN 'MQT'
                 WHEN 'Q' THEN 'Temp'
                 WHEN 'R' THEN 'Recurs'
                 WHEN 'T' THEN 'Table'
                 WHEN 'W' THEN 'Work'
                          ELSE ' '
            END AS TYPE
          , SUBSTR(TBLS.CREATOR, 1, 8) AS CREATOR
          , SUBSTR(TBLS.TNAME, 1, 18) AS TABLE
          , SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
          , CASE PLAN.PRIMARY_ACCESSTYPE
                 WHEN 'D' THEN 'D/'
                 WHEN 'T' THEN 'T/'
                          ELSE ''
            END CONCAT
            STRIP(PLAN.ACCESSTYPE)
            AS ACCESS
          , CASE PLAN.PREFETCH
                 WHEN 'S' THEN 'SEQ'
                 WHEN 'L' THEN 'LST'
                 WHEN 'D' THEN 'DYN'
                          ELSE ' '
            END AS PREF
          , CASE PLAN.INDEXONLY
                 WHEN 'Y' THEN 'XO'
                          ELSE ' '
            END AS XO
          , CASE PLAN.MATCHCOLS
                 WHEN 0 THEN ''
                        ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
            END AS MC
          , SUBSTR(SORT.SORTC, 1, 4) CONCAT
            SUBSTR(SORT.SORTN, 1, 4) AS GJOUGJOU
   FROM     DSN_SORT_TABLE SORT
   JOIN     QUERIES QRYS
        ON  QRYS.QUERYNO = SORT.QUERYNO
   LEFT OUTER JOIN -- Obtain nr of table being sorted
            SORTKEYS SKEY
        ON  SKEY.QUERYNO  = SORT.QUERYNO
        AND SKEY.QBLOCKNO = SORT.QBLOCKNO
        AND SKEY.PLANNO   = SORT.PLANNO
        AND SKEY.SORTNO   = SORT.SORTNO
   LEFT OUTER JOIN -- Obtain name of table being sorted
            TABLES TBLS
        ON  TBLS.QUERYNO = SKEY.QUERYNO
        AND TBLS.TABNO   = SKEY.TABNO
   LEFT OUTER JOIN -- Join back to relevant PLAN_TABLE row
            PLAN_TABLE PLAN
        ON  PLAN.QUERYNO  = SORT.QUERYNO
        AND PLAN.QBLOCKNO = SORT.QBLOCKNO
        AND PLAN.PLANNO   = SORT.PLANNO
        AND(   PLAN.METHOD   = 3
            OR(    PLAN.SORTC_JOIN = 'Y'
               AND SUBSTR(SORT.SORTC, 2, 1) = 'J'
               )
            OR(    PLAN.SORTN_JOIN = 'Y'
               AND SUBSTR(SORT.SORTN, 2, 1) = 'J'
            )  )
   ORDER BY 1, 2 DESC, 3, 4, 5
  ;

The result:

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
QUERY  PBLK  QBLK  PLNO  OPSQ   TYPE    JOIN   METHOD  TYPE    CREATOR   TABLE               NDXNAME   ACCESS      PREF  XO  MC  GJOUGJOU
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
114    1     3     1            TABLEX         First   Table   DSN8810   DEPT                XDEPT2    I                 XO
114    1     3     2            TABLEX  Innr   NLjoin  Table   DSN8810   EMP                 XEMP1     I                     1
114    1     3     3     A      TABLEX         Sort    Table   DSN8810   EMP                                                        U
114          1     1            SELECT         First   Table   DSN8810   EMP                 XEMP2     I(2)        LST       1
114          1     2            SELECT  L/R    NLjoin  Table   DSN8810   DEPT                XDEPT1    I                     1
114          1     3            SELECT  L/R    NLjoin  Work    TU00001   MANAGERS                      T/R                            J
114          1     3     A      SELECT         Sort    Work    TU00001   MANAGERS                      T/R                            J
114          1     4     A      SELECT         Sort    Table   DSN8810   EMP                                                       O
114          1     4     B      SELECT         Sort    Table   DSN8810   EMP                                                       O
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Concluding remark: I only had a very limited set of explain data at my disposal. Therefore I cannot guarantee these queries will show explain data for “real” SQL from your development or production environments reliably. Should you find any defect or shortcoming, I would gladly hear from you. Any other remarks or shortcomings are equally welcome.

Remarks? Questions? More information? e-mail us with your questions.

The package PKG1 makes reference to an alias, NL.EMP, that resolves to a remote DB2 object at LOC2. For clarity I have included in this figure the SQL code involved in the alias creation, but of course the Alias is created once and before the program execution.

The alias resolution processing consist in the substitution of objects names, following the alias definition, in a SQL statement before the SQL is sent to a remote server. As represented there is no object named NL.EMP in LOC2 but the query will work. This is the way in which PP works, always.

When you migrate from PP to DRDA  you will need to BIND packages at each remote location, typically you will use the BIND COPY DBPROTOCOL(DRDA) SQLERROR(CONTINUE) bind options for this. Additionally, if the application uses three-part name aliases, and because DB2 V8 and V9 do not have the capability to perform alias resolution processing for DRDA requests, you need to create aliases in the remote location.

Consider how the picture will look like after migrating this application from PP to DRDA. In this case, LOC2 will contain a copy of package PKG1 as required by DRDA and this package contains the same reference to NL.EMP as the original package. In order to run this package successfully you need to create an alias in LOC2 to resolve NL.EMP into PRD.EMP.

This situation has changed recently by the introduction of the zParm DRDA_RESOLVE_ALIAS. This subsystem parameter, available in V8 and V9, allows you to activate alias resolution processing for DRDA so you do not need to create an alias at the remote location anymore. This is the way in which DB2 10 works and you will not have an option for deactivating this behavior.

Our scenario may look like the following figure after activation of DRDA_RESOLVE_ALIAS:

Now the scenario looks a lot simpler and this new zParm has the potential to make the migration from PP to DRDA a lot simpler.

DRDA_RESOLVE_ALIAS is set to NO by default and you need to set it to YES in order to activate this functionality. So please have a look into your current maintenance, maybe you have this zParm available but not activated.

PK64045: PREPARATION FOR ELIMINATION OF PRIVATE PROTOCOL IN DB2 10 FOR Z/OS (http://www-01.ibm.com/support/docview.wss?uid=swg1PK64045 ) documents this and many other features and tools that are made available to you in order to support the migration from PP to DRDA; this APAR should be your starting point in the migration process.

You should be considering to eliminate PP now even if your plans for DB2 10 are for a distant future. By using PP today you are missing many advantages for your distributed processing that are available only to DRDA; to describe some of them is reason enough for a new post…

Happy migration!
#db2 #dbbuzz

Graeme Birchall is the author of the DB2 SQL Cookbook which you can download from his website. His book is easy to read and he keeps it very up-to-date. In fact he recently published another update and has now some new 9.7 features in there. Graeme’s focus is DB2 LUW which does not mean his book will be useless for DB2 for z/OS people, just be careful. Compared to DB2 LUW some features are different or missing in z/OS (and the other way around).

His book is stacked with many examples and you can download the DDL and SQL examples from the website so you can experiment with it. “Don’t try this at home” is something that doesn’t apply to DB2 Express-C and Graeme’s book. So go download (here) and install DB2 Express-C and become a SQL Guru!

While you are on Graeme’s site also check out his stunning view from this living room over the Hudson River and the binding instructions for the book. Of course you can ship the pdf to the copy shop but this is much more fun. Thanks Graeme for all your support to the DB2 community!

BTW, thanks to @DB2locksmith & her blog, all of the security questions on the DB2 9.7 DBA & Advanced DBA exams were cake.

Ian’s tweet made me very happy.  It’s good to know that folks are reading my blogs, articles, tweets and posts and using them to good advantage.  Whether you are planning to sit for the certification exams or are just interested in deploying some (or all) of the security features that are incorporated in DB2 9.7 (and DB2 9.5), I  hope to help you discover that DB2 Security is “cake”.

Did you know that Separation of Duties is now very easy to set up?  DBAs no longer need to “automagically” get the ability to read data.  I mean, realistically, how many DBAs even have time to read data (or books or newspapers or…)?  I certainly don’t!

My article on Separation of Duties can be found at:

@Database Journal: IBM DB2 9.7, DBADM and my Rubik’s Cube

Want to learn about auditing?  Significant auditing changes were made with DB2 LUW 9.5 that make using DB2 native auditing a strong security benefit.  Prior to DB2 9.5, setting up auditing meant having very limited choices.  Now Security Administrators (SECADM) can fine tune their DB2 auditing approach to a highly granular level.  Want to learn more about DB2 auditing?  Check out these posts:

@DBI Software: DB2 LUW Security — Did they, or Didn’t they?

@DBI Software: DB2 LUW Security – DB2Audit Part 2 of (Infinity and perhaps beyond)

@DBI Software: DB2 LUW Security — DB2 Audit Log Olympics

@Database Journal: Database Auditing in IBM DB2 9.5 & 9.7: How do I know…

Have you discovered DB2 Roles yet?  If not, you are missing out on some easy ways to enhance your security approach.   I’ve written about DB2 Roles in a couple of articles.  You can find out more at:

@Database Journal: Database Security Puzzle Solving with IBM DB2 LUW Roles

@Database Journal: Putting IBM DB2 Database Security Setup Work on a Diet

Of course Label Based Access Controls (LBAC) are yet another awesome DB2 security feature.  If you haven’t tried using LBAC yet because you’ve heard it is a challenge to set up, I have an article to help you with that.  You can read it here:

@Database Journal: IBM DB2 LUW Database Hardening with Data Classification and LBAC

I hope you will read, share, comment and even argue about my articles.  But mostly, I just hope they convince you to take a strong approach to your DB2 database security.

If you would like to contact me, my email is open 24×7:  db2locksmith@securedb2.com.  Now go lock those databases down!

Netezza builds data warehouse appliances which bring analytics directly into the hands of business users within every department of an organization. At the IOD conference Las Vegas I watched a demonstration of a Netezza machine. The simplicity of deploying Netezza appliances makes the technology ideal for the needs of high-performance analytics. The machine requires minimal administration and IT skills, it is a true plug and play. After uploading your data you are ready to deploy the solution. SQL Queries are processed using a massive parallel method. Of course you still need SQL skills and understand your data but the hassle of creating a data markt is gone. The Netezza website gives you a good idea what I am talking about. The capacity of the machines varies from 1 TB up to 100 PB data! The Netezza is a remarkable machine and a great asset in IBM’s effort to provide the best business analytics. According to IBM Netezza will be integrated into IBM’s Information Management software portfolio and the many industry offerings already available.

What is a bit confusing are the many analytics solutions now offers: Next to the Smart Analytics Optimizer for DB2 on the z-series machines which exploits the zBX extension, IBM also offers the Smart Analytics Solution which is a prepackaged hardware / software solution. But both of these two are much more complex than the beautiful Netezza machine. That is what you expect from an appliance. Netezza does extremely fast query processing like your toaster does… toast!