A big thank you to everyone who attended the webinar last week. If you missed it, you can listen to the playback and download the presentation and resource materials. Just register at ibm.biz/Db2TT_Aug2018 to get access.
IBM Data Server Manager
PeterKohlmann 270000F5WX 5,688 Views
I will be presenting an update on the IBM Integrated Analytics System (IIAS) and the IBM Unified Data Management Console Strategy on August 9th at 9AM Pacific and noon Eastern time. Follow the link to register. The data management console for IIAS is leading the way in design and development across IBM database systems. The latest IIAS console provides an early view to what is coming to all the Hybrid Data Management consoles and Data Server Manager. See how our single console strategy for cloud, on-premises, and Db2-based offerings is evolving with amazing new features and designs.
PeterKohlmann 270000F5WX 5,876 Views
We have just posted new Jupyter (Python) example notebooks for Data Server Manager, the IBM Integrated Analytics System and for the whole Db2 Family. Have a look in https://github.com/DB2-Samples/minedsmhistory. The example notebooks are organized into four groups:
I hope these will inspire you to try and write some of your own notebooks.
PeterKohlmann 270000F5WX 5,827 Views
Have you heard about Docker Containers, wondered whether you could use them in your organization to deploy Db2 or Data Server Manager? Next week, on the 21st of May, Vikram Khatri will take the mystery out of using containers and show you how to use them to deploy either Db2 or Data Server Manager. You can sign up for this free virtual conference Learn how to build Db2 and Data Server Manager containers.
For Data Server Manager you can use these techniques to deploy to any container service, either on your desktop, in your private cloud or on the public cloud.
PeterKohlmann 270000F5WX 6,072 Views
If you are heading to Las Vegas next week to attend the Think Conference please drop by the demonstration Hybrid Data Managed Ped area. I will be around most of the week and would love to hear your feedback and you can see some of the exciting new things we are working on for Data Server Manager and our consoles for the Db2 family of products.
PeterKohlmann 270000F5WX 7,310 Views
An Interim Fix for Data Server Manager 2.1.5 was released today. It is available on developerWorks and Fix Central. It contains a number of fixes, including a fix to an issue you might encounter if you upgraded from 2.1.4 to 2.1.5. In some cases, you many not be receiving email notifications when alerts are generated.
The upgrade process is the same as moving from 2.1.4 to 2.1.5. If you have not already upgraded to 2.1.5 simply apply this Interim Fix directly onto 2.1.4. If you have already upgrade to 2.1.5 apply the interim fix onto 2.1.5. The downloadable image on developerWorks has also been updated to include this new interim fix.
PeterKohlmann 270000F5WX 10,999 Views
Data Server Manager 2.1.5 is now available for download from developerWorks. Click on ibm.biz/IWantMyDSM to find the latest download. You can also see the improvements listed in the DSM "What's New" technote.
One of the highlights for me is that you now have access to enhanced monitoring for Db2 Warehouse on Cloud and Db2 on Cloud. Until 2.1.5 there was a fixed and unchangeable monitoring profile for Db2 Warehouse on Cloud and Db2 on Cloud databases. We originally took a very conservative approach to the monitoring we allowed for these systems. As you can appreciate from my last article, the more detailed monitoring information you choose to collect, the more there is potential performance impact to that system.
We received numerous requests to open up the "On Cloud" profile and we listened. The default profile for the "On Cloud" databases still only turns on a minimal amount of monitoring. But now you can choose to change the "On Cloud" monitoring profile to do more detailed monitoring just like you can do with your Db2 on premises databases. Support is there today for Db2 on Cloud systems and Db2 Warehouse on Cloud SMP/MPP systems. Support for Flex is coming with the next Db2 Warehouse on Cloud Flex update.
If you are an "On Cloud" user and haven't used DSM before, have a look at some of my previous articles. You can now take advantage of additional monitoring like individual statement history. You can also choose to enable additional alerts, increase the frequency of the data collection and how long you keep it. Remember monitoring has a cost (usually small) so please look at my last article to make sure you know how to measure and manage the impact when you increase frequency or detail in the monitoring you do.
PeterKohlmann 270000F5WX 12,768 Views
I often get asked the question, "What is the overhead of using Data Server Manager to monitor my database?" I have two answers.
The first answer is: "Not much". Data Server Manager (DSM) makes almost exclusive use of in-memory metrics that are part of the IBM Hybrid Database Management Db2 family of products. Since the in-memory metrics are always collected by the database engine there is only a small overhead to the SQL that DSM runs in the background to collect that data. The default monitoring profile is designed to minimize overall database impact while still collecting key data.
The second answer is: "It depends". From working with database performance experts both inside and outside of IBM for many years I learned that almost every answer to a performance question begins with those words. The impact depends on a few things like how many monitoring options you have turned on in your monitoring profile. It depends on whether you are using realtime or also historical data collection. It depends on the monitoring data collection interval you set. It also depends on whether you are using advanced monitoring like individual statement history. It also depends on whether you are running millions of short transactions or just a few long running analytic queries.
The good news is that you can use DSM itself to measure the exact impact it is having on your system. With a single statement you can create a Workload definition that will identify each SQL statement that Data Server Manager issues against a monitored database:
CREATE WORKLOAD DSM_WORKLOAD APPLNAME ('DSMAu*','DSMRt*','DS_ConnMgt*','DSSNAP*','DSMOQT');
The Db2 Warehouse and On Cloud products as well as Db2 Advanced Editions allow you to create custom workload definitions like the one above. Workload definitions do not impact performance in any way, but they do allow you to see monitoring information unique to the applications included in that workload. In this case all the application connections associated with DSM are included in the statement above.
From the DSM Monitoring Overview page you should now be able to see the DSM_WORKLOAD as an option in the Database dropdown. By selecting DSM_WORKLOAD you can see the time spent, CPU used, number of transactions and other metrics just from the perspective of the DSM application connections. (Refresh your browser if for any reason you don't see it as an option.) If you select All workloads the key metrics in the overview page are display relative to each other. For example, you can see exactly what percentage of the available CPU DSM is using and compare that again the rest of the applications using the system.
So you can measure the impact that Data Server Manager has on each database you are monitoring. The answer doesn't have to be "Not much" or "It depends." It can be very accurate and help you to select the right level of monitoring by understanding its impact under your specific circumstances.
If you want to learn more about using Workload definitions to monitor your database, have a look at a previous blog on SQL Statement Monitoring and Historical Analysis.
PeterKohlmann 270000F5WX 11,087 Views
I just uploaded a new version of the Jupyter notebook that is used in my Blog posting on how to mine history data from the DSM repository. The new version uses Jupyter extensions (magic commands) for Db2 written by George Baklarz. Have a look, you don't have to hard code a userid and password into the notebook anymore. Running SQL is much easier and you can format multiline SQL. The new extensions make using a notebook much easier and faster with Db2. Have a look at https://github.com/DB2-Samples/db2jupyter for instructions and a tutorial on using the extensions. It includes instructions for setting up your own notebooks or you can download the DSX Desktop from IBM.
If you want to see DSM mining and the Jupyter notebook in action, I will be at IDUG EMEA in October and presenting as part of the free Developer Workshop on Thursday. To sign up follow the link: ibm.biz/db2wkshp.
PeterKohlmann 270000F5WX Tags:  server dsm warehouse tables manager db2 virtual federation data remote 13,629 Views
If you have ever had manage a data warehouse you know how much time involves unloading, transporting and reloading data into a central database. If you are working with data sets in the MB or GB range you have an alternative. Using Db2 Version 220.127.116.11 and Data Server Manager 2.1.4 you can easily access tables in remote databases from a wide variety of vendors just like local tables.
And you can use Materialize Query Tables or MQTs to store a local copy of the data, or the results of a query, against the remote data. A simple REFRESH command can update your local result set.
If you are a DBA or a Data Engineer you can use DSM to turn Db2 into a virtual warehouse combining locally stored data with data from numerous other sources. This means your Data Analysts, Developers or even Data Scientists can access key data from across your enterprise like simple tables, but you get to stay in control of managing the central resource.
Have a look at this new DSM video that walks you through federating data from multiple sources, using MQTs to accelerate performance, and visual explain to see exactly what is happening.
PeterKohlmann 270000F5WX Tags:  data notebook mining scientist repository pivot db2 history dsm 3 Comments 12,634 Views
Data Server Manager stores very useful information in its historical repository database that you can directly access through SQL. You can run the following statement against a DSM repository database to see the details of key metrics across all the databases monitored by DSM. (There is more information on accessing this information in the DSM Wiki.)
WEEK(COLLECTED) AS WEEK_COLLECTED,
SUBSTR(DBCONN_ID,1,14) AS DBCONN_ID,
SUBSTR(WORKLOAD_NAME,1,14) AS WORKLOAD_NAME,
DATE(MAX(COLLECTED)) AS END_DATE,
SUM(TOTAL_CPU_USEC_DELTA) / 1000000.0 AS CPU_SEC,
SUM(LOGICAL_READS_DELTA) AS LOGICAL_READS,
SUM(ACT_COMPLETED_TOTAL_DELTA) AS ACTIVITIES,
SUM(TOTAL_APP_COMMITS_DELTA) AS COMMITS
GROUP BY WEEK(COLLECTED), DBCONN_ID, WORKLOAD_NAME;
Try running it in the DSM SQL Editor
You can run this SQL directly in the DSM SQL Editor and export the results to an Excel file and easily pivot on the data.
Work like a Data Scientist
If you want to mine the data like a Data Scientist, try accessing the same data through a Data Scientist (Python) notebook.
The minedsmhistory notebook file I stored in Github does all the work for you. It retrieves data from the Data Server Manager monitoring history and maps out resource usages across your Db2 Enterprise. (You will need connection privileges to the Data Server Manager repository database.) It creates a view in the repository database that provides a list of databases and workloads in each database for each data point collected. The values collected are key resource metrics: CPU seconds, Transactions, and Logical Reads. It then maps out a pivot table for the total resources or throughput per database and workload by week for each database in your Data Server Manager monitoring enterprise.
Using with DSX Desktop
By using IBM DSX (Data Scientist Experience) desktop you can work like a Data Scientist in minutes. Start by downloading the DSX Desktop from IBM. Once you have installed the DSX desktop you can create a new notebook by selecting My Notebooks and New Notebook and From File. Download the MineRepository.ipynb file from this project and choose the file from in the Notebook File section of the Create Notebook form. Provide a new notebook and select Create Notebook. You can then run the notebook by selecting the run cell icon for each step in the notebook. (Make sure you update cell number three with your unique database connection information.)
Try it out and let me know what you think, especially if you create your own notebook that you would like to share.
PeterKohlmann 270000F5WX Tags:  monitoring manager server realtime history db2 data 12,802 ViewsModified on by PeterKohlmann
PeterKohlmann 270000F5WX 9,622 Views
PeterKohlmann 270000F5WX 10,000 Views
Data Server Manager 2.1.3 has been available for almost a week and you can see a full list of all this improvements in the What's new in Data Server Manager 2.1.3 document.
There are some great new features that should get your attention. There is a complete redesign of priviledge management that makes managing object privileges fast and easy. Now you can also manage database authorities from Administer->Database. You can now backup and restore to Cloud storage with DB2 for Linux, UNIX and Windows Version 11. And there is a new tuning wizard to makes it easy to capture SQL for workload optimization.
But there are lots of little things that always get my attention. The development team is very focused on continuous improvement and each release contains dozens of small but important enhancements that make Data Server Manager a pleasure to use.
While you may notice that the look and feel of the SQL Editor has improved, my favourite enhancement is the auto save. Before 2.1.3, if you were working on a script and went for lunch, when you came back your browser session may have timed out you may have lost your work. So instead of having to save your work each time, Data Server Manager automatically saves your last script and pulls it up the next time you log in. That is great, especially if like my you move between devices when using Data Server Manager.
Another thing I use all the time is the new information slider at the bottom right side of the screen. One click will take you to this community, the knowledge center or to our twitter account.
If you work across timezones you now have the choice to see add your data in the timezone of your browser or the timezone of the server you are managing.
There are also lots of new and updated realtime and historical monitoring views in this release. My favorite thing under Storage is the Table Performance view with an interactive heat map. If you turn on the heat map you can zoom in or out of the heat map view to quickly by rotating your mouse wheel to pinpoint tables that are running hot.
So when you try out the new Data Server Manager 2.1.3 and check out the new features, don't be surprised if you find a lot of small but very important improvements with each update. And if you haven't tried Data Server Manager before you will be pleasantly surprised with the power and the experience.
You can also follow us on Twitter@IBMDSM and YouTube.
PeterKohlmann 270000F5WX 9,271 Views
For many customers IBM Data Server Manager is becoming a critical service in their Enterprise architecture. It monitors critical DB2 HADR and DB2 pureScale systems and can alert you to system outages or potential problems with your highly available database infrastructure. If Data Server Manager isn't available they are running blind.
If you need Data Server Manager to stay up no matter what, there is a new best practice guide to help you: Setting up IBM Data Server Manager as a Highly Available Service.
This guide describes how to build, configure, and deploy DSM as a high availability (HA) service on a Linux system. DSM runs on an HA cluster that includes one master node and one backup node. If DSM stops running on one of the nodes, the other instance of DSM will start to ensure that the DSM service is always available. Critical configuration and user data for DSM are also continuously synchronized between the two DSM nodes.