International Business Machines Corporation (IBM) Earnings Call Transcript & Summary

Welcome and thank you for joining today's webcast. My name is Pat Zakhar, and I am the Db2 Analytics Accelerator Marketing Manager. I am pleased to be joined today by Cuneyt Goksu, an IBM Level 3 Certified IT Specialist who is a Db2 Analytics Accelerator and Db2 Tools Lab Advocate. Cuneyt will be presenting today's topic, the Perfect Trio: Db2 Analytics Accelerator, Temporal Tables and Transparent Archiving in Db2 for z/OS. During today's webcast, we have subject matter experts standing by to answer your questions. Please use the Q&A chat window if you do have any questions. And then at the end of our session, we will also open up for additional Q&A. Cuneyt, thank you very much for joining us. And now I am going to hand it over to you.

Thanks, Pat. Hello, everyone. So the name of the presentation is Perfect Trio. So why did I give this name? Because I will start with a business problem and with its challenges, and then I will propose 3 different technologies: temporal tables and transparent archiving, which are part of the Db2 for z/OS; and Db2 Analytics Accelerator. So I will go into details of those 3 functions and the features and the technologies, and then I will propose how we can combine 3 of them to resolve this business problem. So why companies are keeping data for long periods of time? I will try to give you 3 examples. First reason is because of the legal requirements, because of the compliance rule, because of the state rule, some companies, for instance, financial institutions, should keep the data for a certain period of time. Another reason is because of the application requirements. For instance, the help desk application. That application should reach out historical data of the customer for a proper answer in a short period of time. Another reason is analytics or warehouse applications. That applications generate insight from the data; that's why we should keep the historical data for long periods of time. So these are some fundamental reasons to keep the data for long periods of time. If you keep your data for a long period of time, there are certain impacts. One impact is the application performance. How? Let's assume you had Db2 table, and there is noncontinuously ascending clustering key, and that means the new rows get inserted throughout the table. And this will increase the CPU cost of the data access because the most recently inserted rows are often the most recently accessed rows. And they will be mixed -- the recently rows -- recently inserted rows will be mixed with the old and cold rows in the table. So the net result is the number of GETPAGEs to access the rows will increase, so it means the CPU costs. And you may have Db2 table -- another Db2 table, a large Db2 table with continuous ascending clustering key. So that means the new rows, the inserted rows, will go at the end of the table. So it may grow your indexes and you may have larger indexes. So it may also affect your CPU. How? A larger index means a bigger level, and it may affect your GETPAGEs on your indexes or Db2 utilities may process longer time. So these are just 2 examples that a bigger and larger table at the end of the day may increase your application performance. There is another impact to keep the data for a long period of time, which is data storage costs because storing years of historical data on a high-end disk subsystem on IBM Z may cost you a lot of dollars. Therefore, over the years, there are lots of cost-reducing alternatives that is produced. The first alternative is storing the historical data off-line in a print platform such as tape system [ prices ]. But it has its own problems such as if you load -- if you unload the data to a tape subsystem and then that means your application cannot access the data immediately, a dynamic query, for instance. So that data has to be restored back to the disk subsystem overnight or in case needed. And even if you do that, you cannot load all the data back to your Db2 or disk subsystem because you just load just a subset of the data back online. So is there any other alternative? Or is there a better way? Of course, the industry and IBM developed a lot of alternative ways. So let's see what are those alternative ways. So this is a common architecture chart that shows how the data is moving from one platform to another for archive increases. For instance, on the left-hand side, you have a production database. And you keep the data for a short period of time, online accessible from -- for instance, from your OLTP application. And then you have a different database, which is used for archiving purposes, maybe in the same platform or a different platform, and you copy data from your production database to archive database periodically, for instance, monthly. Okay? And you may have 2 different applications for 2 different purposes: one application access the production database and an application access to archive database, okay? And some companies have stretched this architecture and have another platform for off-line archive, and they keep copying the data to a tape or CD or a less expensive platform. But the common problem here in this architecture, if the data is sitting in an off-line archive, for instance, we need to restore this data back to an online platform to make it accessible. This is one aspect. And the other aspect is you need 2 -- at least 2 different applications to access the data on production system and to access the data on your archive system. So in the upcoming charts, I will address how we can solve this common issue. Db2 temporal tables can be used to keep your online and archive data in the same platform and to access them in a single application. Temporal tables as part of Db2 for z/OS is implemented since Db2 10. So this is not a new technology. This is mature and used by several customers around the world. And by keeping data in a Db2 temporal table, you don't need additional coding to implement to versioned data. That means with a single application, with a single interface, with a single scale, you can access both -- online both active data and the versioned historical data at the same time. And you can access the past and the current data in a single view, in a single scale, in a single application. That's one of the most powerful reasons to use the improved Db2 temporal tables. And of course, you have the ability to ask questions about your data, especially the history of the data because of the auditing reasons, for instance. So Db2 temporal tables are implemented in 2 ways: business temporal and system temporal. So there are 2 temporal table concepts. And the first concept is business time temporal tables. So in this architecture, every row has a pair of timestamp or a date column, which is set by the application. This is the responsibility of the application to fill those values. So you simply have a begin time and end time, and those 2 values represent the validity of the row. And you can ask questions in between them in your queries. And your query may bring values from the current or any rows from the prior time or that may represent the future time in the business time temporal table concept. The second concept is the system time. And in this architecture, again, every row has a pair of timestamp, which is set by the database management system. And again, you have the begin time and the end time. And the begin time is the time of when the row is inserted, and the end time is the time when the row is modified or even it is deleted. So every base row has a transaction start ID timestamp. So it is a unique timestamp for each base row. And when you have this architecture in system time, temporal table structure, you can ask questions for the current or you can ask questions for the previous time. So now let's see how the history is generated in Db2 temporal table structure. So there is a concept of periods in both architecture, in system time and business time. So that period represents the life of a row actually. So one column stores the start time, and the other column stores the end time of a specific row. In a system time period architecture, so just the creation or the deletion of the row from Db2 perspective. And Db2 system time versioning automatically keep the historic of that row -- of the changed row in this architecture. In a business time period, actually creates your own valid period. It is the responsibility of the application. It's the responsibility of the user, and user maintains the valid times for a specific record. So temporal tables are set as 2 different structures: system-period temporal table or application-period temporal table. Should the net business value if you create this architecture from a history perspective, you are aligned with the compliance rule because you keep the history data next to your active data, and it may perform better. And it is easier to integrate with your homegrown applications, homegrown solutions because you have a single table. And from the application perspective, it is transparent to access both active data and the historical data in a single query. So now let me explain how the system works, how the row maintenance works with system time. So when you create a temporal table in Db2, the base table and the history tables are created automatically. So initially, when you insert a row, it is in the base table, and there is no footprint in the history table yet. And when you do an update on that table, the version is created on the history table. So as you can see in the history table, the system time and -- in the history table and the system time begin in the base table are equal. And when you do an additional update, a new version of the row is created in the history table as well. So at the end of the day, when you delete a row from the base table, there are 3 versions of the same row in the history table, and there is no footprint in the base table. So at any time, you can ask the question via SQL, and you can get 3 different versions of this row from the history table. And at the end, when you do a new insert into the base table, the old rows or the old versions of the same row are in the history table and active row sits in the base table. Okay? So just keep in mind, at any time, if all of the above statements, okay, executed in the same unit of work, there would be no history table rows because they're in the same unit of work. So when you create this structure, the data maintenance is done by the Db2 automatically. When you do the insert update and the delete, the versions of the rows flow from the base table to the history table automatically. And when you access the row via select statement, the rows retreat from the base table or when you use a different SQL dialect with ASOF queries, the rows also retreat from the history table and the base table transparently and automatically by the Db2. So that creates an environment to have an automatic data maintenance and also transparent to the application for both base and the history table. So one of the main use cases of temporal table is the auditing. Basically, you create a history of the same row for auditing purposes, so you can track which SQL operation actually did the modification, update or delete, for instance, or also you can easily identify who modified the data. So to make that happen, which is actually not restricted to Db2 temporal table, which is also available for nontemporal tables as well, there's a specific column which we created as character 1 and which is called operation code, and it tells you what is the operation, which is an insert or which is an update or which is a delete. Okay? And there is another column called user, okay? And that keeps the SQL ID or the current user ID, and simply you can identify who changed the role and in which operation. So now let me give you an example about how it works. So let's assume you have a base table, and the user Joe inserts a new role into this table. And please focus on 2 columns: user column and the op code column. So initially, user column has the value of the user ID, which is Joe, and the operation column has the value of I because of the insert operation. And when a new user, Don, updates that row, now Db2 creates a history in the -- of the same row in the history table. So when you check the values in the base table, now the user ID is Don because he updated the row, and the operation code is U because of the update operation. And the previous version of the row is moved to the history table. And then the -- Laura deletes this row, a new user deletes this row from the base table. Simply, there is no footprint of this row in the base table, but a record is created on the history table, which has the user ID, Laura, as the modified user, and the operation code is the delete. So when we look at the final part at any time, you see how the row was created and when it is updated by whom and then who deleted the row at what time. Basically, you create the history of this row for audit versions. So let's assume you have created this structure and you have the Db2 Analytics Accelerator attached to your Db2 system. How those queries, how those temporal queries are routed to the IDAA? So first of all, both the active and the history tables with timestamp column can be loaded to the accelerator. So this is supported by the accelerator. And we have a ZPARM, which is called query accelerator options. And when you set the value to 5, so the queries against the system temporal tables are allowed to run accelerator, for instance. So basically, you can control the routing via a ZPARM value. And of course, from the IDAA perspective, these queries has the criteria to be offloaded to the accelerator because there are certain criterias that needs to be matched for the offload process. So the same rules applies for the temporal tables as well. And for certain query rewrite needs to be applied for the temporal table structures, as you see, there are certain SQL dialect to support the temporal table access. So those queries has to be rewritten, and actually, it is done transparently. But the net point here is those queries are eligible for accelerators. So if you create this architecture and if you load this data back into the accelerators, those queries can be routed to the accelerator as well. So if we go back to the first question for the archiving, we can ask this question: Can temporal table -- system time temporal tables specifically can be used for an archiving purpose? So the answer is yes because simply, we are creating a history of the data for some period. And we keep the data -- active data in the base table, and we create a versioning of the data in the history table. And simply overall, we have the full picture of the rows of -- the full picture of the data in a single form. And it is the decision of the business to decide what is the data retention period here because the before image of the changed row is still kept in the same platform and it is ready to be accessible by the applications. And the flow of the data between the base and the history is done automatically by Db2 itself. So the net point, the net message here is the maintenance is done automatically, you don't need additional maintenance procedures. The data is transparent to the application and also eligible by the accelerator. Now let me switch to Db2 archive transparency. Db2 archive transparency -- basically, Db2 transparent archiving is available since Db2 11. Hence, this address certain areas of the original business problem. First of all, if you have a large table and if you keep the active data and the archive data in the same table, query and data maintenance of this large table is a common business problem. And index maintenance, index design and even the maintenance of this large table from the performance perspective is a business problem, as we all know. So we developed in-house solutions for that problem over the years. Simply, we split the table into 2, and we moved the inactive or the cold data to a different table, maybe in the same Db2 subsystem or in a different Db2 subsystem. But that has its own challenges from the usage perspective and from the performance perspective. For instance, the main question is, if we have 2 different tables for the different purposes, for active and archive purposes, how am I going to access the data in a single query from both tables? This is a common question needs to be addressed. And the second question is how the data maintenance or how the data archiving process will happen. It should be transparent to the user, right? So in -- without the transparent archiving solutions, there are homegrown, manual process that basically unloads the data from the base table and loads it into the archive table or -- different in-house solutions are in the market, but they are all homegrown and needs to be maintained, and it requires some form of application change. So now let me explain how this system works. So let's assume we have a table called T1, and the DBA creates another table called T1 Archive, okay? So the logical structure of those 2 tables has to be same. We are not interested the physical structures of those tables. So they can sit in different type of table spaces, but the logical structures has to be the same. And then DBA couples those 2 tables, T1 and T1 Archive, with an alter table statement. And when this is issued, simply we enable the transparent archiving for the base table. And let's assume the program issues a deleted statement from the T1. Simply, the deleted row automatically moved from base table to archive table based on the value of a global variable. So we control this movement process or data maintenance process with the value of the Move to Archive global variable. So if it is set to yes in the same session, so this movement process works. And to access the archive table is controlled with a different global variable called Get Archive global variable. So in a dynamic SQL environment, if it is to yes, your queries against the base table are also executed on the archive table, and the net result is retrieved back to the application. So the point here is from the application perspective, they don't need to issue a specific SQL against archive table, all right? So everything is work on the base table, but the data on the archive table is fully transparent to the application. So when you implement Db2 transparent archiving, let me give you a short comparison between the temporal tables. First of all, this is not the same like temporal tables because in temporal tables, we create the versions of the same row in the history table and we keep the history based on the before and after image of that row. But in Db2 managed archiving, actually, we have one single row, which may be in the base table or the archive table, all right? So we don't keep the versions of the row -- of the same row in the transparent archiving architecture. So the most cases, as you see in the chart, you separate the rows of the active rows in the base table and the cold rows in the archive table. So simply, when you access the base table in this architecture, you know that all the pages are keeping the active rows and they are not mixed with the cold rows that are not required by the OLTP application maybe anymore. So it creates an environment for you and easier for the data maintenance of the base table and easier to design certain indexes for OLTP and certain indexes for the archive application because they are sitting in 2 separate tables right now. So how the system works in Db2 transparent archiving? On the left-hand side, you have the base table. On the right-hand side, you have the archive table. So first of all, from the data maintenance perspective, when you issue an insert, it is inserted into the base table, but when you do a delete operation for archiving purposes, okay, that row moves from left to the right, okay? So that movement is -- depends on the global variable, Get Archive global variable. So if it is set to yes, this archive process works for the delete operation. And this is also true for the REORG DISCARD, okay? So some customers use this approach instead of a delete, so they use a REORG DISCARD and then the rows moves from left to right. And from the SQL access perspective, depends on the value of the Get Archive global variable. So your select statements against the base table can also be retrieved from archived table transparently, okay? So that means in a single SQL, in a single web predicate, depends on the value of the Get Archive global variable. The rows may be retrieved from both tables, and behind the scene, they are unioned and sent back to the application as a single result there. So that provides level of transparency to the application. So what is new in Db2 transparent archiving? As I said, it is proposed in Db2 11. So as of Db2 12, now we can set the Move to Archive global variable as a subsystem parameter as a new ZPARM so it can be used on subsystem level. And additionally, row change timestamp column can be part of a partitioning key. So it helps us to create this archive table on partition base on the Db2 Analytics Accelerator for archiving purposes. So I will give an example in the upcoming chart, but this is supporting to use the temporal table structure own accelerator. And additionally, in Db2 12, there are optimizer improvements based on the Union All, and of course, it helps the performance of the access from the transparent archiving and temporal table structures. Now let me summarize the differences between the temporal and the archive structures. First of all, the temporal table supports and the archive table cannot be activated for the same table in the same Db2 subsystem. So we have to select either one of them. And there are main key differences between those 2. In system temporal table, we have the base table, and there is an associated history table automatically created. And when the rows are accumulated in the history table, they are not current rows. They are just the versions of the base row, which are created based on the delete or update operations. Actually, they are before images of the rows. And in Db2 managed archiving, again, there are 2 tables, which is a base table and that associated archive table. And the rows in the archive table, actually, they're current as opposed to the temporal tables. They are just older tables and they are still current. So now let me switch to the Db2 Analytics Accelerator because that's the third component in our presentation. And then later, I will show you how we can combine those 3. So Db2 Analytics Accelerator is a logical expansion of the Db2 for z/OS. So simply, when you use Db2 Analytics Accelerator and then the query sent to the Db2 optimizers, Db2 checks if the query is eligible for Db2 for z/OS. And if it decides if the access that is much better to execute in Db2, the query is executed and then the result is sent back to the application. And if the application sends a different query, which is eligible for analytic purposes, it is a complex query, and optimizer sends this query back to the Db2 Accelerator, so it is executed and then the result is sent back to the Db2 itself. So let's assume you have Db2 Analytics Accelerator and Db2 for z/OS in the same architecture. So you have 4 different type of tables in this architecture. The first one is the normal Db2 table, which only sits on the Db2 for z/OS and there is no footprint in the accelerator. And the second form is, as seen the chart, is Table 2, the table is accelerated. That means there is a copy of the data in the accelerator, and the queries coming to this table are eligible for accelerator as well. In the third form is similar to Table 2, but we can archive this data to accelerator. That means the data on the Table 3 will be in accelerator, so there is no data in Table 3, but the queries coming to Table 3 will be routed to the accelerator, okay? And in the fourth form, which is the accelerator on the table, when we create this table, the data can only be in the accelerator. So there is just a footprint, just table definition in the Db2, but actually, the data is always sitting on the accelerator. So how we can combine those 2 solutions, Db2 managed transparent archiving and Db2 Analytics Accelerator? So simply, your base table and the associated archive table can be in both places, Db2 for z/OS and the Db2 Analytics Accelerator. And when the archive table is partitioned, regardless of whether the base table is partitioned or not, you can simply create partitions on your archive data, okay? And if the archive table is partitioned on a date basis and if the older rows are not updated, simply you can utilize each pieces of the Db2 Analytics Accelerator. HPSS stands for high-performance storage saver. So simply, in that case, the large majority of the archive table's data will physically exist only, only on the accelerator. And the timestamp column can be added in case needed to facilitate the date-based partitioning on the archive table. So let me explain this combination in a picture. On the left-hand side is our Db2 system. On the right-hand side, it's the Db2 Analytics Accelerator. So we have a table, base table, which is T1, okay? And it is an accelerator shadow table. That means the table has also reflected into accelerator. So it just keeps the most recent 3 months of data, okay? And we have the archive table as well, which is called T1 archive, okay? And as you can see, it is partitioned on weekly basis, okay? So the archive table is also reflected to the accelerator, and the incremental update is enabled in this architecture as well. So that means all the changes coming to the base table and the archive table, they are reflected to the accelerator. And remember, for the archive table, the most recent partitions, let's assume those 2 partitions, are reflected both in Db2 and IDAA, and the rest of the partitions are archived and by using the HPSS feature, those older partitions are just in IDAA, okay? So let's assume there is a SQL activity on Table 1. Let's assume there is an update operation, okay? So when there is an update, so that update happens on T1, and the data changes are also reflected to the accelerator because of the incremental update, okay? So if a query hits Table 1 and the results that is just part of the Table 1, they can be retrieved from Db2 or the accelerator. That's fine. So let's assume we have an archiving process happened. That means there is a delete operation. So under the delete operation, okay, that row is deleted from T1 and moved to the archive table, right? So because of the incremental update, those changes are also reflected to the IDAA. So that means the IDAA and Db2 will be in sync even when the archiving process works, okay? So at any time, if a complex query comes and it requires the data from the older partitions, which means from the archive table, okay, that query might be eligible for IDAA. So that query rerouted to IDAA, and it will be executed from there. So the data will be in sync with Db2, and the archive SQL will be executed from the IDAA. So if you combine history on the accelerator, you can simply enable temporal table structure for some tables and you can enable transparent archiving for another set of tables. But at the end of the day, you can start creating a history in the accelerator, and you can use both technologies at the same time. And how we can use those 3 to modernize our ETL process? In today, ETL means moving data from the original source to a different platform and do some transformation and load the transformed data to a target warehouse or a data market. But that requires several versions of the same data in staging areas, and that staging areas may be in different places, in different platforms. And the main purpose of ETL is to make the data consumable by the end users and to prepare the data for an optimized platform or merging and cleaning the data coming from different data sources and making data consistent. Actually, this last chart summarizes everything that I have mentioned so far. For instance, when we look at this chart, on the left-hand side, we have 2 tables, which is in blue, stored in the Db2. And maybe those tables are also enabled for archiving purposes, okay? So we -- that means we have a copy of this data sitting on an archive table in the same Db2 subsystem, and those copies of the data is also accelerated on the accelerator, okay? So in such an architecture, if you want to run an ETL process based on this data, basically, you can offload this ETL process into the accelerator. Simply, your ETL process takes the input, okay, takes the input from the data, which is sitting on the accelerator and which is also populated with the archiving process and creates the staging area through the accelerator on the tables, okay, and prepares the data for the target data warehouse. So simply, you are maintaining your archive process in the Db2 and the accelerator for -- maybe for OLTP applications, but at the same time, you are aggregating data for the ETL process and you are basically loading this generated aggregated data back to your warehouse. And we have reached to the end of the presentation. Thanks for listening, and I hope it's useful and you can use it in your own environment. And now I will hand it back to Pat so we can open up for the questions. Thank you very much.

Well, thank you so much, Cuneyt. That was just great information. I -- so before we go ahead and open up for the Q&A, I do want to mention that I've posted the presentation from today's webcast and a couple of useful links in the Learn More section that you'll see on your page. And after today's webcast, you can access that information and a replay of this webcast using the same link that you used to join us today. So Cuneyt, I guess with that, let's go ahead and move over to the Q&A.

So the first question I have is can we combine external data or non-Db2 data into this architecture?

Yes. Actually, that's a good question. So yes, we can do that. Simply, if we copy or move the external data into the IDAA through the IDAA loader, for instance, we can join, we can combine, we can aggregate both data sitting in IDAA Db2 data and external data coming from Oracle, coming from VSAM, IMS and consume this data back in IDAA. So the short answer, yes, we can do that with IDAA loaders.

Okay. Very good. Let's see. The next question, is this solution applicable with the latest release of Db2 Analytics Accelerator or version 7.5?

Yes, it is very applicable, even better with 7.5. We have integrated synchronization capability. So simply, with this integrated synchronization capability, we can use HTAP capability as well. That means the queries, the results, that will be synced with the Db2. So even 7.5 will bring additional capabilities to implement this architecture.