Planet i

IBM STG Lab Services

If you would like to tap into this wisdom, please reach out to our business manager, Mark Even (even AT us.ibm.com).

And if you have a comment or success story to share, please join the conversation on facebook, where the IBM i 25th anniversary celebration continues.

Read the original at DB2 for i.

____


In writing this, I now realize it's been more than 6 years since I first constructed the thought provoking and repeatable response to the situation I describe above.  You see, if the application can run on any hardware, why run it on Power?   If the application can run in any operating system, why run it in IBM i?  If the application can use any relational database management system, why use DB2?

My presentation to the somewhat skeptical and possibly hostile audience proceeds in this fashion... 

All business solutions (read applications) need a solid, yet flexible foundation. IBM definitely can provide that.  Check.



Illuminate and illustrate the features, functions and benefits of Power Systems (i.e. the hardware). Check.








 


And I slowly reveal the answer…

Every IT vendor, including IBM, continues to seek a level of deep and profound integration that results in efficiency and cost effectiveness. They search for integration that will hide complexity and time consuming administration. In most cases, they search in vain.

Integration is not an afterthought, it happens from the beginning, and is sustained throughout.

Integration is not a marketing magic trick, it’s real, and it’s sustainable.

Integration does not happen by accident, it happens on purpose through vision and foresight.

You SEE, it's all about the INTEGRATION. 


For more of the IBM i discussion and celebration look here.

If you are still a bit confused about DB2 for i, go back and read this.  And of course, feel free to reach out...

Read the original at DB2 for i.

____


In writing this, I now realize it's been more than 6 years since I first constructed the thought provoking and repeatable response to the situation I describe above.  You see, if the application can run on any hardware, why run it on Power?   If the application can run in any operating system, why run it in IBM i?  If the application can use any relational database management system, why use DB2?

My presentation to the somewhat skeptical and possibly hostile audience proceeds in this fashion... 

All business solutions (read applications) need a solid, yet flexible foundation. IBM definitely can provide that.  Check.



Illuminate and illustrate the features, functions and benefits of Power Systems (i.e. the hardware). Check.








 


And I slowly reveal the answer…

Every IT vendor, including IBM, continues to seek a level of deep and profound integration that results in efficiency and cost effectiveness. They search for integration that will hide complexity and time consuming administration. In most cases, they search in vain.

Integration is not an afterthought, it happens from the beginning, and is sustained throughout.

Integration is not a marketing magic trick, it’s real, and it’s sustainable.

Integration does not happen by accident, it happens on purpose through vision and foresight.

You SEE, it's all about the INTEGRATION. 


For more of the IBM i discussion and celebration look here.

If you are still a bit confused about DB2 for i, go back and read this.  And of course, feel free to reach out...

Read the original at DB2 for i.

788 400 000 seconds

13 140 000 minutes

219 000 hours

9 125 days

25 years

two and a half decades

a quarter century

• A computing platform that changed the game of information technology
• A business tool that allowed organizations large and small to provide products and services much more effectively
• A business solution that set an extremely high bar in terms of innovation and value
• A system that continues to be the very model of integration and simplicity

The legendary and world renowned IBM i  reaches a significant milestone this year!


This week the reflection, recognition and rejoicing kicked off at the COMMON conference.

I highly recommend you follow the conversation here.

Over the course of the next few weeks, look for deeper discussions here on my blog, as well these:

You and i

i Can

iDevelop


If you have been taking advantage of AS/400, iSeries, and/or IBM i for a couple decades, sit back and bask in the knowledge that you made a very wise choice.  If you haven't, then pay attention to what you've been missing, and more importantly, join the club!

Read the original at DB2 for i.

788 400 000 seconds

13 140 000 minutes

219 000 hours

9 125 days

25 years

two and a half decades

a quarter century

• A computing platform that changed the game of information technology
• A business tool that allowed organizations large and small to provide products and services much more effectively
• A business solution that set an extremely high bar in terms of innovation and value
• A system that continues to be the very model of integration and simplicity

The legendary and world renowned IBM i  reaches a significant milestone this year!


This week the reflection, recognition and rejoicing kicked off at the COMMON conference.

I highly recommend you follow the conversation here.

Over the course of the next few weeks, look for deeper discussions here on my blog, as well these:

You and i

i Can

iDevelop


If you have been taking advantage of AS/400, iSeries, and/or IBM i for a couple decades, sit back and bask in the knowledge that you made a very wise choice.  If you haven't, then pay attention to what you've been missing, and more importantly, join the club!

Read the original at DB2 for i.

With the arrival of IBM i TR6 we find the DB2 for i Query Optimizer enhanced to provide more index advice. In a previous post I discussed the philosophy behind generating index advice during the SQL query optimization process.  I also called out the limitations in the advice provided. One limitation of note involves no advice given for different columns ORed together, such as:

SELECT    *
FROM       NFL_TEAMS
WHERE    COLOR = 'PURPLE' OR EATS_PEOPLE = 'YES';

If you've attended our DB2 for i SQL Performance class, you know that multiple indexes over the same table can be used to assist the database engine with identifying rows of interest. This technique is known as "index ORing". But without advice from optimization, you are on your own to determine the appropriate indexes to create.

Keep in mind that when ORing the same column together, the optimizer has always been able to produce advice for queries such as:

SELECT    *
FROM        NFL_TEAMS
WHERE     COLOR = 'BLACK' OR COLOR = 'BLUE'


With the latest database group PTFs installed, the query optimizer will now advise indexes for up to five ORed predicates involving different columns. Additional details can be found here.

If your query has more than five columns ORed together, there will be no index advice produced (had to draw the line somewhere). Besides, ORing five different columns together is likely to result in most of rows in the table being selected anyway - in which case a full table scan is the best option, who knows...

There are additional limitations when mixing ANDed and ORed predicates in the query as well.

Some Examples

 

The following query now produces index advice (two separate indexes). Indexes that can be used to understand the data, as well as used for implementation:

select   *
from    orders
where  orderpriority = '5-LOW'
OR      shipmode = 'AIR';




Notice that the next query produces index advice consisting of one index (this is business as usual):

select    *
from     orders
where   orderpriority = '5-LOW'
AND    shipmode = 'AIR';






When more than five different columns are ORed together, no advice is produced:

select    count(*)
from     orders
where   orderpriority = '5-LOW'
OR       shipmode = 'AIR'
OR       quantity = 1
OR       year = 2013
OR       shippriority = 0
OR       orderkey = 1
OR       custkey = 10;






As mentioned previously, when mixing ANDed and ORed predicates in the query there are still limitations in the advice that can be generated. This is due in part to the number of key combinations that could be produced. In the following query, the optimizer only gives advice for the same column(s) appearing on both sides of the OR condition (i.e. YEAR). It is not going to advise two separate indexes.

select    *
from    orders
where  (year = 2013 and shipmode = 'AIR')
OR      (year = 2013 and orderpriority = '5-LOW');





I'll let you figure out why the optimizer only advises one index in the next case, and why it contains columns (YEAR, ORDERPRIORITY, SHIPMODE):

SELECT *
FROM   orders
WHERE  (year = 2013
        AND month = 1
        AND ((orderpriority = '5-LOW'
              AND shipmode = 'AIR')
             OR (orderpriority = '1-URGENT'
                 AND shipmode = 'TRUCK')))
       OR (year = 2012
           AND quarter = 4
           AND ((orderpriority = '5-LOW'
                 AND shipmode = 'AIR')
                OR (orderpriority = '1-URGENT'
                    AND shipmode = 'TRUCK')));

Join In

The new index advice for ORs also works with JOIN conditions (and of course, when mixing ANDs with ORs, the same limitations apply):

SELECT    o.*
FROM       orders AS o
INNER JOIN
                  dates AS d
ON           (o.shipdate = d.datekey OR o.receiptdate = d.datekey)
WHERE    d.year = 2013
AND         d.month = 1;

Words of Wisdom

Please reread my post: Index Advice - Take it or Leave it.  Read it again, and take it to heart. Analyzing SQL queries and tuning them via indexing techniques is both science and art. For example, should you create a radix index, or should you create an encoded vector index? I don't know, it depends. Obviously, seeking out the dependencies is important.

Knowledge, understanding and experience are required to really make sense of optimization and SQL query behavior. Getting educated and trained is the best and most important first step.

And rest assured that DB2 for i is working hard to optimize your queries to allow the best possible performance with your data, in your environment. This includes taking its own advice and creating the indexes for you...

Read the original at DB2 for i.

With the arrival of IBM i TR6 we find the DB2 for i Query Optimizer enhanced to provide more index advice. In a previous post I discussed the philosophy behind generating index advice during the SQL query optimization process.  I also called out the limitations in the advice provided. One limitation of note involves no advice given for different columns ORed together, such as:

SELECT    *
FROM       NFL_TEAMS
WHERE    COLOR = 'PURPLE' OR EATS_PEOPLE = 'YES';

If you've attended our DB2 for i SQL Performance class, you know that multiple indexes over the same table can be used to assist the database engine with identifying rows of interest. This technique is known as "index ORing". But without advice from optimization, you are on your own to determine the appropriate indexes to create.

Keep in mind that when ORing the same column together, the optimizer has always been able to produce advice for queries such as:

SELECT    *
FROM        NFL_TEAMS
WHERE     COLOR = 'BLACK' OR COLOR = 'BLUE'


With the latest database group PTFs installed, the query optimizer will now advise indexes for up to five ORed predicates involving different columns. Additional details can be found here.

If your query has more than five columns ORed together, there will be no index advice produced (had to draw the line somewhere). Besides, ORing five different columns together is likely to result in most of rows in the table being selected anyway - in which case a full table scan is the best option, who knows...

There are additional limitations when mixing ANDed and ORed predicates in the query as well.

Some Examples

 

The following query now produces index advice (two separate indexes). Indexes that can be used to understand the data, as well as used for implementation:

select   *
from    orders
where  orderpriority = '5-LOW'
OR      shipmode = 'AIR';




Notice that the next query produces index advice consisting of one index (this is business as usual):

select    *
from     orders
where   orderpriority = '5-LOW'
AND    shipmode = 'AIR';






When more than five different columns are ORed together, no advice is produced:

select    count(*)
from     orders
where   orderpriority = '5-LOW'
OR       shipmode = 'AIR'
OR       quantity = 1
OR       year = 2013
OR       shippriority = 0
OR       orderkey = 1
OR       custkey = 10;






As mentioned previously, when mixing ANDed and ORed predicates in the query there are still limitations in the advice that can be generated. This is due in part to the number of key combinations that could be produced. In the following query, the optimizer only gives advice for the same column(s) appearing on both sides of the OR condition (i.e. YEAR). It is not going to advise two separate indexes.

select    *
from    orders
where  (year = 2013 and shipmode = 'AIR')
OR      (year = 2013 and orderpriority = '5-LOW');





I'll let you figure out why the optimizer only advises one index in the next case, and why it contains columns (YEAR, ORDERPRIORITY, SHIPMODE):

SELECT *
FROM   orders
WHERE  (year = 2013
        AND month = 1
        AND ((orderpriority = '5-LOW'
              AND shipmode = 'AIR')
             OR (orderpriority = '1-URGENT'
                 AND shipmode = 'TRUCK')))
       OR (year = 2012
           AND quarter = 4
           AND ((orderpriority = '5-LOW'
                 AND shipmode = 'AIR')
                OR (orderpriority = '1-URGENT'
                    AND shipmode = 'TRUCK')));

Join In

The new index advice for ORs also works with JOIN conditions (and of course, when mixing ANDs with ORs, the same limitations apply):

SELECT    o.*
FROM       orders AS o
INNER JOIN
                  dates AS d
ON           (o.shipdate = d.datekey OR o.receiptdate = d.datekey)
WHERE    d.year = 2013
AND         d.month = 1;

Words of Wisdom

Please reread my post: Index Advice - Take it or Leave it.  Read it again, and take it to heart. Analyzing SQL queries and tuning them via indexing techniques is both science and art. For example, should you create a radix index, or should you create an encoded vector index? I don't know, it depends. Obviously, seeking out the dependencies is important.

Knowledge, understanding and experience are required to really make sense of optimization and SQL query behavior. Getting educated and trained is the best and most important first step.

And rest assured that DB2 for i is working hard to optimize your queries to allow the best possible performance with your data, in your environment. This includes taking its own advice and creating the indexes for you...

Read the original at DB2 for i.

Or he has a very special set of glasses.


As some of you know, IT Jungle publishes an electronic newsletter called The Four Hundred. I read one author's articles religiously. That author is Victor Rozek.

I believe (from reading) that Victor has a fantastic ability to connect the dots, and illuminate the connections in an insightful way.

Instead of just shining a bright white light on his subject of choice, he also points beams consisting of a single color, just to see what might be revealed. More often than not, he gives the reader a glimpse into a totally unexpected or otherwise unknown aspect. Thus it will come as no surprise that his column is entitled: "As I See It:".  HA!  more like "do YOU see it!".

His most recent article is no exception. Because the article is about data proliferation and the related pursuits of piling it up and sifting through it, I'm calling it out.

Whether you do data or not, you should take a moment to read Victor's article here.

Read the original at DB2 for i.

Or he has a very special set of glasses.


As some of you know, IT Jungle publishes an electronic newsletter called The Four Hundred. I read one author's articles religiously. That author is Victor Rozek.

I believe (from reading) that Victor has a fantastic ability to connect the dots, and illuminate the connections in an insightful way.

Instead of just shining a bright white light on his subject of choice, he also points beams consisting of a single color, just to see what might be revealed. More often than not, he gives the reader a glimpse into a totally unexpected or otherwise unknown aspect. Thus it will come as no surprise that his column is entitled: "As I See It:".  HA!  more like "do YOU see it!".

His most recent article is no exception. Because the article is about data proliferation and the related pursuits of piling it up and sifting through it, I'm calling it out.

Whether you do data or not, you should take a moment to read Victor's article here.

Read the original at DB2 for i.

There is a difference - a BIG difference - between “doing database” and doing “very large database”.

Some History

What is VLDB, and Do I Have It?

 

Limit! What Limit?

What Not To Do

What To Do

Get the latest version of IBM i Navigator (aka IBM i Access - System i Navigator) – this would be the IBM i 7.1 Windows client. Install it, along with the latest service pack. Connect to your DB2 for i system and invoke Health Center.


The DB2 for i Health Center can show you a lot of things, but we are interested in the Size Limits for objects in a particular schema.



If you want to change the schema of interest, hit the button and do so:



When selecting the Size Limits tab, we get the opportunity to view the table and index current size compared to the maximum size or limit:




Take a look at both the table size and number of rows. If either of these are approaching the limit, you'll see a warning indicating a critical state. Please do be aware that if the schema has a lot of tables in it, this operation might take a few minutes.




If you are interested in where the index size is at, select this object type:



If you want a programmatic approach to reviewing your table and index sizes as compared to the limits, there is a stored procedure called QSYS2.Health_Size_Limits () and the description can be found here.

Do More

5: build and implement a plan to modernize and re-engineer database objects and data access 

_____


The IBM DB2 for i Center of Excellence is the foremost (and dare I say only) team in the world fully qualified to assist you with very large database in the IBM i environment. As a matter of fact, the table limits and VLDB capabilities are what they are today because of the past research and leadership of the AS/400 Teraplex Center and the expertise of the men and women in the DB2 for i Development Laboratory.

If you need help with understanding the current state of your growing databases, or with meeting requirements for large data and scalability, contact me. We have a DB2 for i Very Large Database Consulting Workshop available that can identify solutions for overcoming limits to growth and ensure you are getting the most out of IBM i. We can also do remote database and application assessments to help identify any looming issues sooner than later – that is to say, before your business hits a wall. 

At the end of the day, rest assured that DB2 for i can handle your data whether you count rows in the millions or in the billions, assuming you embark on proper planning and implementation of the very large database practices and procedures for IBM i.

Read the original at DB2 for i.