Seven

When I meet with business leaders around the world, the same general set of requirements always seem to pop to the top. This set of requirements also happens to represent the set of four attributes that must manifest within your data centric architecture and applications.

What is this set of four requirements?

• Flexibility

• Agility

• Scalability

• Timeliness

In other words, business leaders want their IT solutions to support and propel them forward, even when the terminus is unknown, or the objective multifaceted. The engines that run business must be flexible, agile and scalable.  As new opportunities, markets and clients present themselves, the solution must not only meet the need, but must show up ready to go in a timely fashion. Waiting years to have the application up and running is just not acceptable anymore. 

Whether the set is expressed as business requirements, or expressed as (positive) attributes of the "new and improved" architecture, a critical success factor is modern tools and modern techniques.

To this end, IBM i Technology Refreshes appear periodically to assist you.  In other words, IBM i is keeping up, are you?

If you are not keeping up with the technology at your disposal, I doubt you are meeting all the business requirements.  If you are not meeting business requirements...  well, I'll let you think about the consequences of that.

 

Refresh Seven

Looking specifically across the list of database enhancements and improvements just announced in TR7, I see more features and functions in DB2 for i that help get the job done in an efficient and effective manner. This means more productivity for you, and a higher return on investment for the business.

If I may categorize a few of the items...

In the scalability corner we have a new upper limit for the size of an index object. An index can now be larger than 1TB, with a maximum size of 1.7TB!  Why?  Two reasons:

1) many users of DB2 for i find themselves in a very large database environment with their tables and indexes growing rapidly (very few people throw away data). If they hit a limit, overcoming it might require serious and disruptive changes to their applications.

2) even with relatively small database objects, we all sleep better at night knowing that there is a lot of room for growth in the database we bet our business on. Scale up, not out - keep it simple.

If you're curious, all of the database limits can be found in the SQL Reference - Appendix A.

And don't forget, while you are creating those larger than average indexes, IBM i Navigator can help you keep an eye on the build progress with the database maintenance view. This is really useful while building a 1.7TB index - trust me!   (by the way, I used DB2 SMP to get it done in hours, not days HA!)






In the agility corner we have relational database name ALIAS. One of my previous posts talked about three part naming whereby the RDB name can be provided along with the schema and table name; DB2 will implicitly establish the connection to the remote database associated with that name. Now the RDB name can be associated with an alias, allowing for something generic like TARGETRDB to be used in place of all of the remote database names. The SQL statement referencing the RDB name (i.e. RDB alias) can then be generic too.  By changing the RDB entry to use the same alias, you effectively change the target of the SQL statement to reference the object(s) in the different RDB as well.

In the flexibility corner we have an enhancement to the Generate SQL (DDL) tooling.

A physical file is a table.
A keyed logical file is an index.
A keyed logical file is also a view.

So when reverse engineering a DDS created LF, which SQL DDL statement do we get - CREATE INDEX or CREATE VIEW?  Now you can have either one as easy as clicking a button (provided you are running with the latest client code). It will also be possible to generate the optional WHERE clause as part of the CREATE INDEX (or CREATE VIEW) from a select/omit logical file. This gives you the ability to reverse engineer a sparse index too.

Another flexible tool in the kit is the ability to control the I/O blocking factor for a table using a specific SQL procedure instead of the CL command OVRDBF. The new QSYS2/OVERRIDE_TABLE() procedure allows the knowledgeable engineer to influence the blocking factor in a straight forward manner.

For example...

/* Increase the blocking factor on both sides from the default 64K to 256K */

-- Override the BIG source table to use a 256K blocking factor during read
CALL QSYS2.OVERRIDE_TABLE('MYSCHEMA', 'BIG_SOURCE_TABLE', '*BUF256KB');

-- Override the BIG target table to use a 256K blocking factor during write
CALL QSYS2.OVERRIDE_TABLE('MYSCHEMA', 'BIG_TARGET_TABLE', '*BUF256KB');

-- Move the data in BIGGER blocks
INSERT INTO MYSCHEMA.BIG_TARGET_TABLE
    SELECT    *
    FROM    MYSCHEMA.BIG_SOURCE_TABLE
    WHERE    YEAR = 2013;

-- Remove the override
CALL QSYS2.OVERRIDE_TABLE('MYSCHEMA', 'BIG_SOURCE_TABLE', 0);

-- Remove the override
CALL QSYS2.OVERRIDE_TABLE('MYSCHEMA', 'BIG_TARGET_TABLE', 0);



A complete list and a lot more details on the TR7 enhancements can be found at the DB2 for i Updates web site, compliments of Mr. Forstie and Co.

 

Focus on Three

If you or your organization need help with understanding the value of DB2 for i, and/or how to meet requirements, please let me know.  If you are wondering where you should be focusing your time and energy, let me share three vitally important foundational areas:

• The Science and Art of Information Management

• Control and Governance of Data and Data Access

• Proper and Adequate Architecture and Design

There you have it, four plus three equals seven; with a technology refresh to match.