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.
IBM Data Server Manager
PeterKohlmann 270000F5WX 115 Views
PeterKohlmann 270000F5WX 271 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 1,751 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 1,532 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 2,868 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:  data db2 remote warehouse federation virtual server dsm tables manager 4,469 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:  dsm data notebook scientist history db2 mining repository pivot 3 Comments 4,483 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:  manager data db2 realtime monitoring server history 4,267 ViewsModified on by PeterKohlmann
PeterKohlmann 270000F5WX 3,106 Views
PeterKohlmann 270000F5WX 3,434 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 2,905 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.
PeterKohlmann 270000F5WX 3,279 Views
SQL statements are at the core of a relational database. A history of the when and how those statements were executed can be the key solving problems and improving performance. Data Sever Manager can give you insight into those statements but it is important to understand which tool to apply to which problem. Data Server Manager can help you see and understand:
Each kind of monitoring can provide a different insight and some require specific setup or configuration to suit your needs.
Have a read through the Data Server Manager SQL Statement Monitoring and Historical Analysis Guide posted today. I am sure I will continue to update and revise the document based on your feedback. Let me know what you think and how you are using SQL history in Data Server Manager today.
Interesting video on DSM and Big SQL:
marichu 120000M9XV 4,002 Views
Peter Kohlmann has created a blog post in the International DB2 Users Group (IDUG) site on Data Server Manager. Here is the link: http://www.idug.org/p/bl/et/blogaid=401
The blog post gives an overview of the Data Server Manager's features. What I think is interesting is the section: Base and Enterprise Editions of Data Server Manager. Here, Peter discusses the various licensing options for Data Server Manager, both Base Edition and Enterprise Editions, when the environment contains a mix of DB2 data sources, e.g. DB2 Express, DB2 Workgroup, DB2 AESE and DB2 ESE.
If you have further questions on the licensing of Data Server Manager, please contact any of the following folks:
- Anson Kokkat, firstname.lastname@example.org
- Peter Kohlmann, email@example.com
- Marichu Scanlon, firstname.lastname@example.org
marichu 120000M9XV 3,644 Views
The next DB2 Tech Talk is on 28th Jan at 12:30 PM ET
The webcast runs from 12:30 PM ET until 2PM.