Friday, March 7, 2014

DB2 for i Technical Forums

Hola! My teammate and amigo Hernando Bedoya (he is actually familia) and I are just wrapping up two very successful DB2 for i Technical Forums in Bogota, Colombia (week of Feb 24) and Bilbao, Spain (week of Mar 3). Hernando was an excellent guide and translator. (you see, my Spanish is virtually nonexistent, and based in part on watching Speedy Gonzales cartoons on the TV as a child).

First and foremost I want to extend my sincere thanks and appreciation to our event partners...

In Bogota, Mr. Octavio Bustos of Redsis arranged and hosted the Forum for his clients in Colombia.

In Bilbao, Mr. Igor Izaguirre of Trentinort arranged and hosted the Forum for his clients in Spain.

The opportunity, and our success is due in large measure to these good folks.  Muchas gracias!

___


The history of the DB2 for i Technical Forum begins 8 years ago on a long and scenic drive from the small village of Agordo in the Italian Alps to the port city of Venice.

You see, the famous and world wide ITSO AS/400 Technical Forums originally conceived by the one and only Ian Jarman were no more, and the IBM conduits to share accurate and current technical guidance about all things OS/400 were shriveling up.

During the drive out of the mountains, my long time IBM colleague and dear friend Simona Pacchiarini was wondering aloud how we continue to spread the word about database. In a moment of inspiration (or maybe lunacy) we hit upon the idea of creating our own "Technical Forum" - expressly for the purpose of sharing and discussing database topics. Given that some of the philosophical and architectural concepts of "forum" can be traced back to Italy, we felt compelled to initiate this idea in Milan, and we have continue the tradition on an annual basis ever since.
(and what is the real reason my DB2 for i Technical Forums are in Italy every year? Simona is a bulldog when it comes to getting good things done!)

___


I have used the Forums in Italy as a proving ground when developing techniques that serve to illuminate the DB2 for i features, functions and unique advantages. During last year's event I tested a new theme entitled "Design It, Build It, Tune It". Of course, this topic is in addition to the standard update on what's new in DB2. Given the success of the presentations and lab exercises, we are continuing the theme in 2014.

The simple notion behind the sessions on design / build / tune involves the importance of sharing knowledge, teaching skills and discussing best practices for data centric applications, in a holistic way.

"Design It" focuses on the science and art of data modeling - something that is universal, but sorely lacking in IBM i shops. We present an overview of conceptual models, logical models and physical models which serve as the blueprint for communicating the database architecture and design.

"Build It" focuses on implementing a physical model, thinking in sets (instead of records), and the elements of set-at-a-time processing. The SQL components of DDL, DML and PSM are used to demonstrate the concepts and used to construct the data centric application (i.e. tables, views, indexes, queries, procedures).

"Tune It" focuses on techniques for increasing efficiency, performance and scalability. We present an overview and discuss the IBM i integrated toolset for monitoring, analyzing and tuning SQL requests.

___


I believe the biggest value of the DB2 Forum comes from the final presentation whereby all of the features and functions are woven into a realistic business problem/solution scenario. The practical demonstration, the putting into action all of the concepts and best practices, really shows the audience a clear path forward in terms of data centric development technique.

In the spirit of a forum, our 3 day event always includes lively dialogs, discussions and debates on all things database.

If you are interested in bringing the DB2 for i Technical Forum to your clients or organization, please reach out. Or if you are in need of specialized / focused assistance with your data centric project, we are here to help.

Adios


Friday, February 21, 2014

The ABC of Social

Analytics
Big Data
Cloud

Social

Are you wondering why constant and measured Social interaction via the web (and soon simple text messages) are such a big deal to business?

Hint: it's not necessarily about sharing your latest vacation picture with friends and family.

For a simple answer (ok, maybe not so simple) I encourage you to check out a behind the scenes look at how Analytics, Big Data and Cloud are used to drive and exploit Social behavior; watch the recent PBS FRONTLINE documentary "Generation Like".

As a fifty-something who grew up with one rotary phone in the house, I found it amazing.
As a data centric professional, I found it interesting.
As a business person, I found it profound.
As a parent, I found it disturbing.

I pose this rhetorical question: what do you think of the ABC S?

Of  course, multifaceted and "all of the above" answers permitted.

Monday, January 27, 2014

You're a DBE, now what?

In between client engagements this past week, I had the opportunity to sit down with a long time member of my Center of Excellence team and all around super consultant Jim Denton.  We talked about the progress made in getting more and more of our IBM i users to embrace the idea of a DB2 database engineer. The notion of having a person (or persons) in the building who really get, and truly focus on "DB2 for i" is taking hold, and it's paying dividends for those companies that are forward thinking.

Jim was relaying an interesting question he received from one of our favorite clients over in Dayton, Ohio.  This particular company has fully embraced the wholesale and winning combination of acquiring data centric knowledge and skills, as well as embarking on a targeted and well managed database modernization and re-engineering project.  Good for them!

The question posed goes something like this:

"Hey Jim, now that I'm a database engineer, what should I be doing day to day?"

First, this is a really insightful question. It means that the person is thinking strategically and is ahead of the curve.  Well done!


The What and Why


What you should be doing day-to-day as a DB2 for i engineer basically falls into two categories:

  1. Reactive
  2. Proactive

Both reactive and proactive activities generally involve: monitoring, analysis and something else.

The something else will depend on your focus and your client (i.e. who are you providing value to).

Why you should be doing this seems self explanatory, but if it's not, consider this:

Partnering with leaders on solving data centric business problems and getting more value out of data is of the utmost importance. Doing it proactively makes YOU more valuable and your company or organization more viable.

In terms of monitoring, assume you are an investigator.  You need to peek behind the curtain, look inside the black box, ask the probing questions. In short, you need to learn more about your data, the usage of data and the life cycle of data. You need to discover who is using the data and for what purpose.

Here are a few ideas to get you started. If you need more, please reach out and we can help you identify and prioritize activities that provide a good return on investment. We can also help you get better at identifying and partnering with your colleagues.

The Ideas


Narrow your scope to the top 10. Once you have a repeatable process, open the aperture and expand.

Profile your data. Learn more about the properties and trends of tables and indexes. How large are they. How fast are they growing. Who is using them.  How are they used. Who has access to the data. How is the data accessed. When is the data accessed.  Get the idea?  Know your data.

Take periodic and consistent snapshots of the the query plans. Store this data and use it to develop trends. What are the longest running queries. Who is running the queries. What are the attributes of the query requests (do you see simple statements, or complex statements).  Who is reading the three million results from the query run twice a day, every day (I can tell you... no one - this is an extract!).

The DB2 for i catalog is your friend. Get to know it. Use it.

Make a blueprint of your current data model. Pick a subject area or application and model the database objects, such as they are. Use reverse engineering tools and methods to be more productive. While you're at it, take this opportunity to learn a proper database modeling interface. Something that has more colors than just green.

Use the blueprint to identify gaps in your organization's relational database best practices. SQL queries are based on and driven by the data model. A bad data model means more work for the SQL query. A good data model means less work for the SQL query. If the model behaves well (i.e. well defined sets), then the SQL will behave well (i.e. good set operations).

Using the current data model and your gap analysis, define a future data model, build a new blueprint. Formulate a plan to modernize and re-engineer the database to incorporate the foundational elements that support data centric processing.

Watch and wait for an opportunity (i.e. business requirements) to implement the modernization and re-engineering plan. We like to do this on a targeted and cost justified basis. Better yet, don't wait; engage the business to understand pain points and new initiatives.

Get out of your office or cubical. Go meet people. Talk to your colleagues and clients at every level. Find out what they are struggling with. Find out what they are using the data for. Find out what the data is NOT providing them.

The information seekers in your organization are requesting data from IT. The data is extracted and downloaded to a PC. Once on the desktop, the real magic occurs. Peek behind the curtain, look inside the black box, ask the probing questions about what the data is used for and why. Better yet, ask the information seeker "what problem are you trying to solve?". This is how you get in a position to provide value on a proactive basis.

This is how you become relevant and stay relevant.

What else?

Well... what about you?

I recommend you strive to learn something new every week. Take an hour (or two) each and every week for yourself. Crack open the SQL Reference manual. Initiate a project for yourself. Do a proof of concept or proof of technology. Develop and hone your soft skills.

All work and no play makes Jack a dull boy.

Thursday, January 16, 2014

RESOLUTION


As we pass AND approach the various new year thresholds, this is a time when most of us symbolically reflect on the past, and plan for the future. This has been practiced for centuries in cultures all around the world.

While crossing the seasonal threshold and moving from one year to the next, there is a long standing tradition of making (and breaking) a resolution. Many of these annual resolutions involve a promise to do less, do more, or do better.

According to Merriam-Webster the definition reads...

res·o·lu·tion    noun \ˌre-zə-ˈlü-shən\

: the act of finding an answer or solution to a conflict, problem, etc.

: the act of resolving something

: an answer or solution to something

: the ability of a device to show an image clearly and with a lot of detail


I really like all of these explanations, and believe they apply very well to what we need to do as IT professionals (your personal resolutions are your own business).

When I reflect back on what my team has witnessed in countless IT organizations around the world, a few clear and distinct things come to mind.

Every business leader wants to have (IT) solutions that are:

  • Flexible
  • Extensible
  • Agile
  • Scalable
  • Timely

To meet these requirements, new tools, new techniques and new approaches need to be embraced. And if you ask me, doing so without throwing the baby out with the bath water. In other words, keeping what works and re-engineering what doesn't. Rarely do we find that it is cost effective or advantageous to start over from scratch. Some might call this approach "evolution, not revolution". The pace at which you evolve is a function of time, energy and funding. I would also throw in will power.

From a data perspective, business leaders want more relevant information. They want to move from data being the static and benign byproduct of a transaction or event, to data being the raw material that is refined into insightful information; something that provides a unique and otherwise hidden perspective on what's happening, what's coming, what's needed.

For many (most?) of my clients, it seems that data is dragging them down like some kind of massive anchor. A huge bucket of bits that is increasing in weight and volume, forever tugging on the organization's precious resources.

For a few keen companies, data is an enabler. This byproduct of their transactions or events will serve an important purpose. That is to become a lens to focus the past, and for looking into the future. Their data becomes an asset; something to be preserved, treasured and made useful. Something that becomes a unique advantage to their business, their partners and their customers.

Approaching Information Management Differently


Another major trend we see is that increasingly, the formal IT organization is being bypassed. Or at best, relegated to being just the conduit for data. This means that the effectiveness and more importantly, the value of traditional IT organizations is being eroded.

We see more and more business users acquiring information technology solutions directly - deploying and using them without the assistance or oversight of the IT organization. Obviously there are many concerns with this trend. The one I want you to focus on is: irrelevance.  As in, your knowledge, skill and expertise are irrelevant, no longer required.

Increasingly, users have access to the data. The various lines of business have growing requirements for information. If IT cannot provide the information, then they are asked only to provide the data. Sooner than later, the "value add" of IT will dry up. To be sure, IT should have a vital part to play in the acquisition, storage, management, and productive / safe use of data. But given the current trend, how do you stay relevant?  How do you continue to provide value?

One idea I have been sharing with my clients involves a change in philosophy and approach. In terms of engaging your business leaders, users and colleagues, move towards a consulting oriented discussion instead of service oriented discussion. As a consultant, you are in a position to guide and influence the community around you. And don't wait for them to come to you.  Be proactive. Go to them.  This is what makes you valuable and keeps you on the list of critical success factors.

Find a way to help, not hinder.

_________


For the coming year, let me suggest that you persevere to:

  • Stay relevant
  • Make and maintain a connection with your users clients
  • Provide real value

To accomplish these things (from an information management perspective), I recommend you personally resolve to:

  1. Learn more about the science and art of information management
  2. Gain control and governance of data and data access (don't be a Target)
  3. Get better at the design, architecture and modeling of database solutions

Happy New Year!


Wednesday, December 11, 2013

DB2 for i SQL Classes for 2014

The DB2 for i Center of Excellence team has scheduled some high powered classes in Rochester, Minnesota (home of IBM i and DB2 for i) for 2014.  Make your plans now to take advantage of THE experts and in some cases, the folks who wrote the code.

For the data centric application architect and SQL programmer we have the DB2 for i Advanced SQL Workshop. This event is scheduled for February and September.

To view the description, dates and to enroll go  here  and be sure to check out the video overview at one of these sites:

English on YouTube

Spanish on YouTube

English on YouKu  (I have no control over the commercials!)

For the DB2 for i Engineer, or anyone interested in becoming more adept at understanding and solving SQL performance and scalability issues we have the DB2 for i SQL Performance Workshop. This event is scheduled for March and September.

To view the description and to enroll go  here  and check out the video overview at one of these locations:

English on YouTube

Spanish on YouTube

English on YouKu  (again, I have no control over the commercials!)

If you have any questions about the value of attending these workshops, please reach out to me.

If you are interested in bringing these knowledge and skills transfer workshops directly to your organization on a private basis, we can do that too.

Wednesday, December 4, 2013

Hey, Let's Check the Catalog!

My long time friend and teammate Kent Milligan has some great insight and advice for an often overlooked feature of DB2 for i - the catalog.

If you are not familiar with Kent and his knack for illuminating the functions and benefits of all things DB2, open up a browser and instigate a search using "Kent Milligan IBM".

Might want to get a caffeinated beverage as it's going to be a long night of reading.

Enjoy!

-------- 


The DB2 Catalog is probably one of the most overlooked resources on your IBM i system by both programmers and database engineers.  The DB2 catalog is comprised of a set of catalog views and tables that contain information about all of the DB2 objects on your system. Metadata is another term used to describe data about your database.  As DB2 objects are created and deleted on the system – either with SQL or DDS via IBM i commands  – the catalog objects are automatically updated to reflect the latest state of your databases. The catalog objects sit behind the scenes, helping DB2 manage your databases while at the same time storing valuable metadata about your databases.

The information stored in the DB2 Catalog is a valuable resource that make it easy to analyze your database and to develop utilities to manage your database.  The following IBM i Navigator screen shows some of the catalog views that reside in the QSYS2 schema.
 

 


The SYSCOLUMNS view shown in this window is a great resource to leverage for understanding the lengths and types of data stored in your DB2 for i databases.

Let’s say you’re interested in finding all of the “long” columns in your database – that’s easy as running the following SQL SELECT statement against the SYSCOLUMNS view. 
 
SELECT      column_name,
                   table_name,
                   length,
                   data_type
FROM         qsys2.syscolumns
WHERE       length > 2500
ORDER BY length DESC;

In this post, I obviously don’t have room to explain all of the DB2 catalog objects and the ways that they can be exploited to simplify your job, so I’ve included some links to more detailed information. 

The first link is to an article that I published 2 years ago. The second link takes you to the Catalog section of the DB2 for i SQL Reference.

·         DB2 for i Catalog Views

Hopefully, this introduction to the DB2 Catalog will motivate you to find a way to leverage the DB2 catalog to make your job easier because I’m confident that is the case.

Merry Christmas and Happy Cataloging!
 
- Kent