Tuesday, November 8, 2016

The Data Model Matters

For a couple of years now, the major theme of our DB2 for i Technical Forums held around the world has been: design it, build it, secure it, tune it.

During the "design it" portion of the Forum, we focus extensively on the data model. No surprise. Our vast experience tells us that, when it comes to implementing a solution that is flexible, extensible, agile and scalable, the data model matters.

Historically speaking, the S/38, AS/400, iSeries and IBM i development communities have embarked on building data centric applications without the presence of a database administrator. While this has in some ways been an advantage, it has also resulted in a gap. Specifically, a gap in the understanding of the science and art of relational data modeling.

To fill this gap, we routinely recommend standing up an IBM i database engineer (DBE). One of the important responsibilities of the DBE, is to design, implement and support a proper data model.

When it comes to getting the most out of DB2 for i, the data model matters.  By telling DB2 about your data centric business rules, the data element attributes, and the relationships between sets, DB2 can do more work on your behalf. This results in higher productivity, greater efficiency and better performance.

Define your date column as a DATE!  By telling DB2 the column value represents a date, the database management system knows more and can do more. The SQL Reference is filled with date manipulation functions.  Why would you ever write one when it's already provided - assuming of course you define your data elements appropriately.

When it comes to formulating and executing SQL queries, the data model matters. SQL is set oriented. As such, the proper definition of sets and their various relationship(s) are fundamental to developing good set based operations. Sure, DB2 can overcome issues with a poorly defined and implemented data model; but at some point, the amount of work it takes to connect the dots to find the answer to your crazy procedural query will overwhelm everyone's patience and budget.

When it comes to handling very large data sets and over coming limits to growth, the data model matters.  Performance and scalability are two different things; and they do not necessarily go hand in hand. As the data set grows, it is not guaranteed that the performance and throughput will stay in the acceptable range. Relational data modeling best practices can mitigate the risk of growing data sets, as well as provide solutions to over coming database size limits and restrictions. Designing (the data model) for growth and scalability will also pay dividends when your business decides to makes an acquisition resulting in the inevitable large leap in data quantity and transaction processing volumes.

A sound data model also matters when it comes to data governance and control. In other words, properly securing your rows and columns is made much easier with a good data model. By applying relational concepts and normalization rules, the task of isolating and protecting personal identifiable information (PII) with DB2 RCAC becomes much easier. On the other hand, carelessly and needlessly propagating PII data throughout the database makes it virtually impossible to secure the sensitive elements in any effective way.


A pop quiz: is it easier to protect your tax ID stored in one table, or your tax ID stored in 1,000 different tables?


Best practices when defining and implementing your relational data model include:
  • Follow the normalization rules (3rd normal form is a good starting point)
  • All columns have an appropriate type, length, precision and scale
  • Use only one key column to represent the relationship between any two tables
  • Be consistent with column naming conventions
  • Define and use primary, unique, referential and check constraints
  • Define and implement an initial indexing strategy
  • Define and implement views to assist the database users
  • Document the model and keep it current (IBM Data Studio makes this relatively easy)

Now, this all sounds well and good when designing a new data model. But what if you are facing a legacy application with a poorly designed data model decades old?

This is where our targeted database modernization and re-engineering strategy comes into play!

Database modernization and re-engineering, if approached in a financially and technically responsible manner, will provide increased flexibility, extensibility, agility and scalability.

If you want to know more about designing and implementing a new database, or if you are faced with the task of enhancing an existing database to meet new requirements, please reach out. We invented the art of modernizing AS/400 databases, and we can help with reinventing yours.

Tuesday, October 11, 2016

Are You Falling Behind?

Increasingly, we are finding that IBM i installations are falling behind. In many cases, perilously so...

Recently, we engaged a client who only applies PTFs when there is an actual problem encountered. That means, almost never. In other words, no proactive or preventive maintenance. No periodic updates or upgrades. And yes, they do have several planned maintenance windows available per year.

Another set of clients have just migrated to IBM i 7.1. That, after waiting several years to finally get "current". Unfortunately, they are miles away from current.

More and more we see IBM i shops who are in some kind of analysis paralysis as they attempt to make a decision to stay with IBM i or move off to some "other" platform. All the while their business is changing, expanding and branching out in ways that require a non-linear approach to information management and data processing. My experience shows that virtually all of these shops have business requirements that can be met very nicely using current technology available via the combination of Power+IBM i+DB2. Technology that is readily available to them, I might add.

Presently, the IBM i user community enjoys significant stability, as well as tremendous flexibility when it comes to OS and RDBMS features and functions. Not only are there three OS releases available and supported (7.1, 7.2 and 7.3), there are also periodic enhancements available in the form of a technology refresh or update. While a given update is typically delivered as a robust set of PTFs, you certainly should not view a Technology Refresh (TR) as a simple set of "fixes".


Hey Look, Another Technology Refresh!


Today, IBM is announcing yet another technology refresh, specifically 7.3 TR1 and 7.2 TR5.

Alas, if you are using 7.1, you are falling behind. There is no TR available for the n-2 release.

The general availability for the 7.3 and 7.2 updates should be in about a month, so you have time to begin your testing and installation planning process.

From the database point of view, what are some of the items we can expect from this TR?

As usual, the focus is on increasing capabilities around data centric processing and enhancing database engineer productively...

JSON_TABLE
A new table function which works very much like XML_TABLE, allowing for JavaScript Object Notation data to be returned as rows and columns.

INCLUDE SQL
Provides a way to include (pull source code into) another SQL procedure/function.


PERCENT_RANK as an ordered OLAP specification (7.3 only)
A distribution function that returns a relative percentile rank of a row within an OLAP window.


Additional Services (via SQL)

HISTORY_LOG_INFO
Retrieve data from QHST

JOB_INFO
Retrieve a list of jobs by user(s)

PARSE_STATEMENT
Analyze an SQL statement and extract information about the DB object(s),  column(s) and UDF(s)

Misc. Performance Improvements

LPAD/RPAD functions
QSYS2.OBJECT_LOCK_INFO view
QSYS2.SYSPROGRAMSTAT view
QSYS2.SYSPROGRAMSTMTSTAT view
QSYS2.OUTPUT_QUEUE_ENTRIES view
QSYS2.OUTPUT_QUEUE_INFO view
UDF in-lining (more complex UDFs can now be inline)
STATEMENT DETERMINISTIC (a new form of DETERMINISTIC for functions)

IBM Access Client Solutions - Database GUI
Enhancements to SQL Performance Center, Visual Explain and Run SQL Scripts

_____________


For more details on the new DB2 for i Technology Refresh, the illustrious Mr. Scott Forstie has what you need here.

For more information on all various TRs, go here.

_____________


What Should You Do Now?


Regardless of any future and far off "migration plans", get current with PTFs and TRs. That includes HIPER, Group and the latest Technology Refresh for your particular release, even 7.1.

While the various IT "leaders" are off trying to identify your future platform, you can get on with solving current business problems and providing immediate value with IBM i.

To facilitate keeping current (and relevant), stand up a DB2 for i sandbox. This can be a small but balanced LPAR or stand alone system running 7.3 plus TR1.

Take some dedicated time every week to discover, experiment, and try new database features and functions.  Show off your work to the higher ups.  Be a hero.

Please secure the personal identifiable information (PII) stored in your files and tables.

Start by asking, and answering the following question:  

Who has *ALLOBJ authority on any system where PII is stored?

After you get over the shock and awe of finding out just how exposed you are, embark on the serious journey of securing the at risk data. Begin with the correct application of IBM i object based security, then continue with separating roles and responsibilities (the security officer and the database engineer should have different levels of authority based on their respective responsibilities).

Take a good look at using DB2 Row and Column Access Control. It will allow you to apply a more granular level of security on the sensitive data. DB2 RCAC is available in 7.2 and 7.3, so yet another reason to get current.

Identify, train, and support an IBM i database engineer. Ensure this person has not only the responsibility, but also the authority to do the job.

The database engineer is the font of knowledge and skill around DB2 for i features, functions and benefits. The database engineer will not only keep your organization current, but also be the trusted advisor for when and how DB2 technology can be applied to solving data centric business problems!

Start using modern tools and modern techniques immediately.

Go get IBM Data Studio and learn to use it.

Go get Access Client Solutions and try it out.

And finally, do more with your data, right now.

Go get DB2 Web Query, create some compelling visualizations and demonstrate the value to your business leaders.  You can use that IBM i 7.3 sandbox to check out the advanced aggregation functions and OLAP specifications. Again, be a hero.

If you need more information or assistance with DB2 for i features and/or data centric programming technique, please do not hesitate to reach out.



Monday, September 19, 2016

Doug Mack joins the Blogosphere

I am excited to see that my long time colleague and good friend Doug Mack has joined the blogging community!

Doug and I began working together some time back in 1994. That was the period during which the idea of warehousing data and performing analysis on consolidated information began hitting the market.

Also back then, Doug was instrumental in creating and leading the AS/400 Data Warehousing movement, telling audiences all over the world that, "yes, you can use AS/400 for business intelligence". It should come as no surprise, that today Doug continues to provide insight through his discussion of query modernization and report re-engineering, all in pursuit of delivering more value to your business.

I invite you to visit and follow Doug's new DB2 Web Query blog, where he will be sharing knowledge and know how on getting the most value out of your IBM i data. 

And yes, you can use IBM i for business intelligence.

Welcome Doug!

Wednesday, May 11, 2016

Reinvigorating IBM i Application Development

The first thing you will notice about the conversation below is that it is presented in Spanish.

Colombian Spanish to be exact.  This is due to the fact that the dialog is between two wonderful people from the enchanting country of Colombia; namely Hernando of IBM and Didier of Redsis.  

The occasion for this dialog was our recent DB2 for i Technical Forum held in Bogota during the same week in April when IBM i 7.3 was announced.

For those of us who are unable to read Spanish, please use your favorite translation tool to convert to your language of choice.  Señor Bedoya ensures me that the meaning and main points will shine through.

Before I turn over this invigorating post to Hernando, let me share a simple but profound piece of wisdom with you...

          When it comes to acquiring knowledge and skill,
          both the teacher and the student must be ready.

_________


En Abril 12, 2016, IBM hizo el anuncio de la Version 7.3 del sistema operativo IBM i (para algunos OS/400). Precisamente ese día Michael Cain y yo estábamos dictando el tercer Foro Técnico de DB2 for i que Redsis, socio de negocios de IBM en Colombia patrocinaba. Se aprovechó esta oportunidad para hacer el anuncio de las bondades de esta nueva versión que entre otras incluye muchas novedades en DB2 for i. (Pueden ver el BLOG de Michael Cain que publicó recientemente de este tema).

El foco del Foro técnico de DB2 for i se centró en Modernización. El evento era de 3 días donde no solo se cubría las novedades de 7.3 pero se cubría temas de modernización desde la base de datos, aplicaciones e interface gráfica. Nosotros (Michael Cain y yo) cubrimos la modernización de la base de datos y Redsis cubrió la modernización de las aplicaciones e interface gráfica. Redsis utilizo un enfoque práctico para ilustrar los conceptos y nos llamó la atención ver que la persona que conducía la demostración era un joven aparentemente recién graduado de la Universidad. Didier lleva un año trabajando en Redsis y nos parecía increíble ver la naturalidad con la que usaba: Rational Developer for i, programación en Free RPG (si escucharon bien....RPG!!) y entendía perfectamente el concepto de DDS.

Por esta razón decidimos hacerle una entrevista al final del evento.
 

Hernando: Didier como ingresaste a Redsis
 

Didier: Ingrese a Redsis antes de graduarme de la Universidad con la expectativa de programar en tecnologías Web y en lenguaje Java. Pero no fue así...
 

Hernando: ¿Cómo así? ¿Qué paso?
 

Didier: Conocí a mi jefe (Octavio Bustos) y me asigno a conocer unas herramientas de modernización que Redsis representa de la compañía Profund Logic para aplicaciones desarrolladas en IBM i . Y rápidamente estaba sentado ante el reto de modernizar una pantalla verde.
 

Hernando: ¿Y cuál fue su primera impresión o reacción?
 

Didier: Honestamente pensé que me había equivocado de empresa. Todo me parecía muy complicado:

•    Archivos fuentes
•    DDS
•    Sin la posibilidad de copy y paste y mucho menos drag and drop
•    Un lenguaje de programación críptico que arroja muchos errores si ubicas un carácter en la posición que no es.
•    Un lenguaje de procesos que llaman CL que parece ingles abreviado


Hernando: ¿Y cómo lograste salir de este shock cultural?
 

Didier: Octavio me instalo Rational Developer for i (herramienta grafica para editar, programar y hacerle pruebas a los programas) y empecé a ver las cosas diferentes. El también me explico los conceptos básicos del sistema operativo, ahora lo veo como una gran amigo donde no me tengo que aprender las cosas de memoria solo pensar que verbos (acciones) y objetos sobre los cuales estas se realizan. Yo que recibí formación en pantalla  negro con blanco (linux), ahora me siento más cómodo en las pocas ocasiones que debo usar la pantalla negro con verde.  Por otro lado estamos usando Free RPG que es un lenguaje moderno con mucha similitud al javascript. Los dos son clave para las iniciativas de modernización que estamos adelantando.  Hoy Rational Developer for i es mi mano derecha y me entusiasma mucho la modernización Web en IBM i.  La curva de aprendizaje del lenguaje se hace tan fácil cuando se usan herramientas gráficas y se usa Free RPG. 
 

La Conclusión


Fue una experiencia interesante el poder ver como la juventud de IT con las herramientas adecuadas pueden aprender RPG moderno de la misma manera que aprenden JAVA, PHP, o cualquier otro lenguaje moderno.

La realidad es que el desarrollo en IBM i puede ser tan moderno como ustedes quieran hacerlo. 

Existe una entrevista que le hicieron a Michael Cain en el último DB2 and RPG Summit con respecto a este mismo tema como obtener personal adiestrado en RPG.  La entrevista la pueden ver en el siguiente aqui.


Tuesday, April 12, 2016

Significant Upgrades to the Database Engine!


We quite often refer to DB2 for IBM i as the "database engine". On this particular day, I ruminate on why the word "engine" always seems to fit, and more importantly why it actually describes what you have sitting inside your IBM i system.

The basic definition of an "engine" goes something like this:

   A machine with moving parts that converts power into motion.

In computer programming, an engine is a program that performs an essential function or functions.

An engine can be a central focal point in a system, subsystem, or application. "Engine" can also be used to describe special-purpose algorithms. 

Hmm... this sounds a lot like the integrated relational database management system we call DB2 for i.

And for an even more interesting connection, consider that in 1844, Charles Babbage named his stored-program computer the "Analytical Engine"!

Why do we build and employ engines?

Engines provide us with many positive advantages:
  • Strength
  • Speed
  • Power
  • Efficiency
  • Repeatability
  • Predictability
  • Progress
All of which allows us to move forward in a direction and manner that is likely impossible without the engine.

Simply put, engines allow us to accomplish amazing things. Literally and figuratively speaking, engines push us forward by increasing our capabilities.

Personally, I'd like to add to the list of advantages, "lazy and easy".  As in, I can be lazy, because it's easy!


Your Data-Centric Development Engine


Whether we take the traditional definition or the computer science definition, DB2 for i surely represents an engine for innovation. But this benefit is manifested only when the engine is actually used.

When we talk about "data-centric" development, we are referring to designing, programming and implementing solutions in which the database engine plays a major role.

Technically speaking, data-centric development relies on using the advanced features and functions incorporated within DB2 for i, and generally made available via SQL.

Financially speaking, data-centric development means being more efficient and productive by making use of algorithms and capabilities that you already possess.

Assuming you could, why would you spend any time, energy and money reinventing advanced technology, when that technology is sitting in front of you, ready to use?

But wait! I don't see the database capability needed to help solve my business problem.

Look again...


Significant Updates to DB2 for i


With the April 12, 2016 announcement of IBM i 7.3, we have another set of exciting database features and functions at our fingertips. The enhanced IBM i 7.3 capabilities extend the benefits of data-centric development via SQL, DB2 for i, and IBM i running on Power.

For the past few years, my friends and family have been illuminating the importance of expanding your data-centric applications in 3 fundamental areas:
  • Security
  • Analytics
  • Mobility
No surprise, these areas represent common and high priority business requirements. They also represent "low hanging fruit" with regards to providing more value to your clients, customers and partners. And IBM i provides significant assistance in the pursuit of strategic initiatives involving the securing of data, analyzing of data and delivering that data to mobile consumers.

Keeping with the theme of our "database engine", I want to call out a couple of the areas that IBM i 7.3 can power your business forward.


Time travel with DB2 for i Temporal Support


The business requirement is straightforward:

Maintain all current transaction states, retain all previous (i.e. historical) transaction states, and provide the capability to easily retrieve the appropriate current and/or historical transaction state for a particular time period. Oh, and don't mess it up!  

Traditionally we have addressed the business requirement (i.e. the need for point in time data management) by implementing some sort of homegrown solution. These solutions require crafting time aware data processing logic in application code or customized procedures and triggers. This application-centric technique has many disadvantages:
  • Implementing time-aware processing logic with integrity and accuracy is very complicated.
  •  Application development and on going maintenance is costly and time consuming.
  •  Application performance and scalability is often not optimal.
  •  Due to a lack of standards, not to mention developer nuance, different applications will have different implementations of the time based processing logic.
Sounds like a great job for our database engine!

With 7.3, DB2 for i provides the following temporal capabilities:
  • Transparently maintain a system history of all data changes with system-generated time stamps, which are managed as system time periods
  • Allow applications or end users to formulate simple queries that access data as of any desired point in the past, with very high precision
Why Design and Implement a Temporal DB?

The coding of complex temporal operations via SQL is much simpler, which increases developer productivity and reduces the cost to implement and maintain time aware applications by up to 45 times, as shown by an IBM study. DB2 temporal data management helps improve data consistency and data quality across the enterprise.

Why Use a Temporal DB?

The use cases for DB2 temporal data management include the ability to track and analyze changes in your business by easily comparing data from two periods in time. It also represents increased accuracy in time based reporting allowing for tracing when a row was modified, as well as who modified it. This can provide a cost-effective means to address auditing and compliance issues. And with the appropriate data modeling, DB2 for i can also drive the capture of row history in an environment with slowly changing dimensions.

How Does a Temporal DB Work?

After creating a correctly defined "history" table like the base "current" table, there are 3 functions of the DB2 temporal engine:

  1. Maintain the existence, linkage and integrity of the history table
  2. Capture and store all of the before and after row states, over time
  3. Provide access to the current row and/or historical row(s), based on time period

A simple illustration...

After four transactions to a row (t1 insert, t2 update, t3 update, t4 delete) only the current state is available.


After four transactions to a row (t1 insert, t2 update, t3 update, t4 delete) both the current state and the previous states are available.



An important note!

Please do not confuse temporal database support with database logging (i.e. journaling).  They are two different things, with very different requirements and restrictions.  Simply put, database logging does not represent relational data that can or should be queried as a normal part of the business process. Rather, database logging primarily supports transaction isolation and recovery, and secondarily support change data capture in support of transaction replication. The difference seems subtle to be sure, especially given all of the various and historical uses of IBM i journals.



More Data Analysis with DB2 for i OLAP


It's all about the data.  Specifically, it's all about analyzing the data quickly and efficiently to provide information and insight that drive decisions and provide direction. Having a database engine that supports the ability analyze data quickly and efficiently is an advantage. DB2 for i provides such an advantage.

New OLAP Specification Extensions

In IBM i 7.3, the SQL OLAP specification is extended with support for a new class of calculations called moving aggregates. These specifications support important OLAP functionality in the database engine such as cumulative sums and moving averages by using a logical set of rows within a "window". A window specifies a partitioning of rows, an ordering of rows within partitions and an aggregation group (of rows). The aggregation group tells the database engine which rows of a partition set, relative to the current row, should participate in the particular calculation.

If you are not familiar with, or don't remember OLAP specification functions that have been available since 5.4 (RANK, DENSE RANK, ROW NUMBER), recall that OLAP specification functions compute a single value for current row based on some or all the rows in a defined group.

In DB2 for i, OLAP specifications provide the ability to return ranking, row numbering, and other aggregate function information as a scalar value in a query result. It's the "other aggregate function" piece that is new and improved with 7.3

Technically speaking...

An Ordered OLAP specification specifies operations that require a window-order-clause. The operations are...

Lag or Lead function
RANK ( )
DENSE_RANK ( )
NTILE ( expression )
CUME_DIST ( )

A Numbering specification specifies an operation that returns sequential numbers for each row. The operation is...

ROW_NUMBER

An Aggregation specification specifies a function that will compute a single value from the window. The normal functions are...

AVG, CORRELATION, COUNT, COUNT_BIG, COVARIANCE, COVARIANCE_SAMP, MAX, MEDIAN, MIN, PERCENTILE_CONT, PERCENTILE_DISC, Regression, STDDEV, STDDEV_SAMP, SUM, VARIANCE, VARIANCE_SAMP

The OLAP aggregate functions are...

FIRST_VALUE, LAST_VALUE, NTH_VALUE, RATIO_TO_REPORT

The OLAP window specification includes the following components:

  • Partitioning
  • Ordering
  • Aggregation group

The window-partition-clause defines the partition within which the OLAP operation is applied.

The window-order-clause defines the ordering of rows within a partition that is used to determine the value of the OLAP specification.

The window-aggregation-group-clause defines a set of rows that are used to perform the aggregation.

An important note!

In addition to the extended and enhanced OLAP specification, several new aggregation functions are available in DB2 for i 7.3. Combining these aggregation functions with OLAP specifications provides a very powerful set of capabilities all delivered by the database engine - without writing your own program. See the DB2 for i 7.3 SQL Reference for details.

A simple example using one SQL statement...

Problem: Show the difference in sales between the current store and the store with the best sales, the second best sales, and the worst sales.

Solution: An SQL statement with OLAP specifications to define the window and moving calculations.





In Conclusion...


Once again, the integrated database engine known as DB2 for IBM i provides powerful new capabilities that will allow you to extend and enhance your data-centric applications in an efficient and timely manner.  Take advantage of it!

You can find more details and examples from the subject matter experts using the following links:

DB2 for i Version 7.3 Overview
DB2 for i Wiki
DB2 for i - Technology Updates

And if you need assistance with getting more value out of your data sitting idle in IBM i, please do not hesitate to contact me.