decor
 

planetDB2 logo

Planet DB2 is an aggregator of blogs about the IBM DB2 database server. We combine and republish posts by bloggers around the world. Email us to have your blog included.

 

February 24, 2017


Craig Mullins

The DB2 12 for z/OS Blog Series - Part 5: Enhancements for Managing Dynamic and Static SQL

Most modern application development is done using dynamic SQL. But some features work only with static SQL and others only with dynamic SQL. DB2 12 for z/OS delivers functionality that minimizes the difference between static and dynamic SQL. Dynamic plan stability brings the plan stability feature of static SQL to dynamic SQL. With plan stability for static SQL, you can use the PLANMGMT parameter...

(Read more)

Henrik Loeser

Securing Workloads on IBM Cloud - Some Resources

Security Guides for IBM Cloud Recently, I provided you with an overview of security and compliance resouces for IBM dashDB and Cloudant. Today, I want to take a broader view and point you to some...

(Read more)
 

February 23, 2017


Adam Gartenberg

Connect 2017 - The Future of Domino Application Development

Here are my live notes from the Future of Domino Application Development session. I'll post a link to the team's slides, which should be available shortly, and Nate mentioned getting a video of the...

(Read more)

Henrik Loeser

Location and Intent Matter: Data Privacy vs. US Government

Some data is locked away Earlier this month and last Summer two interesting cases related to data privacy were decided. Both concern US search warrants for email data stored outside the United...

(Read more)
 

February 21, 2017


Adam Gartenberg

Mike Gotta (Gartner) – Workstream Collaboration

Mike Gotta, VP Research at Gartner, opened the IBM Watson Workspace and Watson Work Services Strategy and Roadmap session with his perspective on the dynamics we’re seeing today around the emerging...

(Read more)

Adam Gartenberg

Connect 2017 – Opening General Session (Part 2)

Best-effort live notes from the session. Synthesis/take-aways to follow. Ed Brill History of bringing community together over past several decades. Glad to be back part of it. [Buzzword Bingo...

(Read more)

Adam Gartenberg

Connect 2017 - Opening General Session (Part 1)

Best-effort live notes from the session. Synthesis/take-aways to follow. Connect 2017 Opening General Session - Part I Cognitive interactive musical opening (featuring lifetime IBM Collaboration...

(Read more)
Big Data University

This Week in Data Science (February 21, 2017)

Here’s this week’s news in Data Science and Big Data. mathmachinelearning

Don’t forget to subscribe if you find this useful!

Interesting Data Science Articles and News

Upcoming Data Science Events

Featured Courses From BDU

  • Big Data 101 – What Is Big Data? Take Our Free Big Data Course to Find Out.
  • Predictive Modeling Fundamentals I
    – Take this free course and learn the different mathematical algorithms used to detect patterns hidden in data.
  • Using R with Databases
    – Learn how to unleash the power of R when working with relational databases in our newest free course.

Cool Data Science Videos

The post This Week in Data Science (February 21, 2017) appeared first on BDU.


DB2utor

DB2 12 Webinar Series

I recently spent two weeks at the IBM Silicon Valley Lab (SVL) learning all about DB2 12 for z/OS. During the second week, I met with customers who participated in the DB2 12 Early Support Program (ESP). It was very exciting to learn first-hand from the developers who worked on DB2 12, and it was also nice to hear all the positive feedback from the customers. Having this level of access is one of the best things about my job with IBM. I feel very fortunate.
 

February 20, 2017


Henrik Loeser

Write Your Own CLI Plugins for Bluemix Cloud Foundry

README for my Plugin Last year I blogged about how I am using plugins to extend the Bluemix Cloud Foundry command line interface (CLI). The CLI has a set of commands to manage plugin repositories...

(Read more)
 

February 17, 2017


Henrik Loeser

Carnival: Even DB2 Wears a Mask (Database Security)

Data Privacy and Security Right now we are in the hot phase of the carnival season. Many people are wearing masks. Some move into other characters, some just hide their real identity. Did you know...

(Read more)
 

February 16, 2017


DB2utor

DB2 12 Plan Stability with APREUSESOURCE

During a migration, DB2 12 will automatically rebind any package bound in DB2 9 and earlier versions. The automatic rebind results in a new package, while the previous package copy is automatically discarded. However, to ensure plan stability, a BIND should be done manually, because auto rebind won't copy the original package to the previous one. So if a regression in access path occurs, the REBIND SWITCH option is not available.

Data and Technology

Time to Plan Your Trip to IBM InterConnect 2017

I am looking forward to attending this year’s IBM InterConnect conference in Las Vegas, NV the week of March 19-23, 2017. And after reading my blog post today I bet you will be interested in...

(Read more)
 

February 15, 2017

Big Data University

Learn how to use R with Databases

R is a powerful language for data analysis, data visualization, machine learning, statistics. Originally developed for statistical programming, it is now one of the most popular languages in data science.

If you are a database professional (Data Engineer, DBA, Database Developer, etc.) and looking to leverage the power of R to analyze and visualize data in relational databases (RDBMSes), Big Data U (BDU) has a free, self-paced online course to get you going quickly: Using R with Databases.

And if you are not yet familiar with R, there is also a free crash course in R to get you started: R 101.

If you are a Data Scientist or a Data Analyst, chances are that you are already familiar with the richness of the R programming languages and are already leveraging it for modeling, classification and clustering analysis, creating great graphs and visualizations, etc. But you may be hitting the memory limits of R when utilizing it for very large data sets.

Utilizing R with databases and data warehouses that are known for scalability and performance with large amounts of data is one mechanism to overcome the memory contraints of R. And the free course – Using R with Databases – will show you how.

This course starts with a comparison of R and Databases and discusses the benefits of using R with databases. It teaches you how to setup R for accessing databases and demonstrates how to connect to databases from R, specifically using interfaces like RJDBC and RODBC.

The course then goes on to show you how to query data from databases, get the results and visualize the analysis. It also covers some advanced topics like modifying and saving saving data in databases from R, as well as using database stored procedures from R.

Some databases also support in-database analytics with R, so you can benefit from the large amounts of memory and parallel processing features of databases while employing R for analysis. This course also helps you to learn about using in-database analytics with R.

Like other courses in BDU, each module inUsing R with Databases, comes with hands-on labs so you can practice what you learn in the course and try out your own variations.

More over, the hands-on lab environment, called BDU Labs, is free, cloud-based, ready to use, and integrated within BDU so you don’t have to register for a new account or worry about installing software.

The course consists of 5 learning modules and after each module there are review questions. At the end of the course there is final exam.

Successfully passing the course (by proving your proficiency with review questions and final exam), marks your achievement with a a course completion certificate.

This course is part of Data Science with R learning path on BDU, and when you complete all courses in this learning path, you also earn an IBM badge that can be shared on your social profiles.

Enroll now for free and start Using R with Databases!

The post Learn how to use R with Databases appeared first on BDU.

 

February 14, 2017

Jack Vamvas

TSAMP command - lsrg and awk

 Question: How can I list out information in a tabular format using the TSAMP lsrg command ? I’d like to parse the information.

This is part of setting up a monitoring tool

Answer: The lsrg command is useful for listing information about resource groups in IBM Tivoli System Automation for Multiplatforms (TSAMP).   There are different ways of using lsrg command.  

Combining lsrg and awk will list out the member resource information. With columns :

Class:Resource:Node[ManagedResource] Mandatory MemberOf OpState WinSource Location AutomationDetails[CompoundState]                  [ObservedState]                   [BindingState]

 

lsrg -m -D, -V | awk -F, '{print $1,$2,$3,$4,$5,$6,$7,$9,$10}'

Adam Gartenberg

Will I see you at Connect?

It looks like I'm long overdue for some updates here. For those who haven’t heard, I returned to IBM Collaboration Services mid-last year, after several fun and exciting years in IBM’s Analytics...

(Read more)
Big Data University

This Week in Data Science (February 14, 2017)

Here’s this week’s news in Data Science and Big Data. ibm-watson

Don’t forget to subscribe if you find this useful!

Interesting Data Science Articles and News

Featured Courses From BDU

  • Big Data 101 – What Is Big Data? Take Our Free Big Data Course to Find Out.
  • Predictive Modeling Fundamentals I
    – Take this free course and learn the different mathematical algorithms used to detect patterns hidden in data.
  • Using R with Databases
    – Learn how to unleash the power of R when working with relational databases in our newest free course.

Upcoming Data Science Events

The post This Week in Data Science (February 14, 2017) appeared first on BDU.

 

February 13, 2017


Craig Mullins

The DB2 12 for z/OS Blog Series - Part 4: Real Time Statistics History

Real Time Statistics (RTS) were introduced in DB2 for z/OS as part of the maintenance stream of Version 7 and IBM has been improving their functionality every release since. For those uninitiated to what RTS are, they are a series of statistics that are automatically maintained by DB2 and can be used by DBAs (and tools) to schedule maintenance tasks like REORG and COPY. Prior to the introduction...

(Read more)
 

February 10, 2017


DB2Night Replays

The DB2Night Show #189: DB2 LUW Optimized for SAP - The rest of the story!

@mhubel Special Guest: Martin HubelIBM DB2 GOLD Consultant DB2 LUW Optimized for SAP - The Rest of the Story! 76% of our audience learned something! For 24%, it was review and validation they were doing the right things to tune and manage SAP on DB2 LUW! You will learn about registry variables for SAP, dropping and adding indexes, and more best practices. What's more, Martin talks about how to defend your DB2 turf from Hana! Please learn...

(Read more)
 

February 09, 2017


Kim May

Thank you Governor Hogan!

When I started working for The Fillmore Group over ten years ago, the world of DB2 was far different.  The State of Maryland had probably over a dozen IBM mainframes running state...

(Read more)
 

February 08, 2017


Dave Beulke

DB2 12 and Other Technology Updates from the Silicon Valley Lab

Last week I was one of a few Gold Consultants who was invited to attend IBM’s System z Software Technical Sales Boot Camp at the Silicon Valley Lab. This event has many presentations highlighting IBM’s new technology and technology that is being developed. It is also a great time to catch up with...

(Read more)
 

February 07, 2017


Craig Mullins

The DB2 12 for z/OS Blog Series - Part 3: Temporal Improvements

Temporal tables and time-travel query support was added in DB2 10 for z/OS. With temporal tables a time period is attached to the data to indicate when it was valid or changed in the database. A traditional database stores data implied to be valid at the current point-in-time; it does not track the past or future states of the data. Temporal support makes it possible to store different database...

(Read more)
Big Data University

This Week in Data Science (February 7, 2017)

Here’s this week’s news in Data Science and Big Data. iotanalytics_embed

Don’t forget to subscribe if you find this useful!

Interesting Data Science Articles and News

Featured Courses From BDU

  • Big Data 101 – What Is Big Data? Take Our Free Big Data Course to Find Out.
  • Predictive Modeling Fundamentals I
    – Take this free course and learn the different mathematical algorithms used to detect patterns hidden in data.
  • Using R with Databases
    – Learn how to unleash the power of R when working with relational databases in our newest free course.

Upcoming Data Science Events

Cool Data Science Videos

The post This Week in Data Science (February 7, 2017) appeared first on BDU.


DB2utor

Administrative and Install Level Authorities

One big change in DB2 12 is the new install SYSOPR authority. Using install SYSOPR instead of SYSADM allows systems programmers to install or migrate DB2 without having access to user objects and user data.
 

February 06, 2017


DB2 Guys

Attention Developers! The upcoming IIUG conference is especially for you

by Rajesh Govindan, Portfolio Marketing Manager – IBM Informix If you’re an Informix developer, you’ll want to attend the upcoming IIUG Conference in Raleigh, North Carolina. It’s the largest gathering of Informix users in the world. And it’s a great place to improve your skills, learn […]
 

February 03, 2017


Henrik Loeser

Security and Compliance for IBM dashDB and Cloudant

Database Security & Compliance I often get asked about the security features of IBM dashDB and Cloudant. Both are database services ("DBaaS") offered on IBM Bluemix. Once the security topic is...

(Read more)
 

February 02, 2017


Frank Fillmore

Kim May Takes the Reins at Baltimore/Washington DB2 Users Group @IDUGDB2 @IBMzAnalytics

My colleague, Kim May – Vice President of Business Development at The Fillmore Group, has been elected Chairperson of the Board of Directors of the Baltimore/Washington DB2 Users Group.  Kim...

(Read more)
Modern SQL

with Clause: Performance Impacts

The non-recursive with clause, views, and derived tables (from clause subqueries) provide very similar functionality. The main difference is their visibility. Consequently, the principle of least astonishment suggests that refactoring a derived table into a with query should not have any side effects on performance. Databases generally obey this principle, although PostgreSQL represents a big exception.

The following sections outline the most important optimizations that interfere with the with clause and present an overview of which databases apply them to with queries, views, and derived tables.

Predicate Pushdown

Predicate pushdown is an optimization that applies conditions (predicates) as early as possible—ideally preventing any loading of unneeded rows.

The following example uses a derived table to demonstrate this:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
       ) derived_table
 WHERE c1 = ?

If the subquery is executed in isolation, it returns all rows of base_table. Even if there is an index on c1, the isolated subquery doesn't know about the restriction on c1 and so it cannot use the index.

Predicate pushdown passes the condition to the subquery so that the query effectively looks like this:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
         WHERE c1 = ?
       ) derived_table

In this example, one could ague that the where clause should be written in the subquery anyway. However, this argument doesn't apply when querying a view instead of a derived table.

Predicate pushdown is subject to some logical constraints. For example, join predicates cannot be pushed below the first join they affect. On the other hand, predicates can be pushed through group by and window functions if they are among the grouping- or partitioning keys.

Predicate pushdown is particularly important to make better use of indexes.

Projection Pushdown

Projection pushdown or column elimination aim to eliminate unneeded columns as early as possible—ideally not fetching them at all.

Consider the following example, which uses a view for demonstration:

CREATE VIEW view_name AS
SELECT *
  FROM base_table
 WHERE deleted = 0
SELECT count(*)
  FROM view_name

Note that count(*) does not need any table columns.0 If the database can push this information down, i.e. the columns that are actually needed, the database can avoid fetching unneded columns.

In the above example, projection pushdown is particularly important if there is an index on the deleted column: in that case that database can satisfy the query from the index alone (the so-called Index-Only Scan) and does not need to fetch the other columns from the table itself. This can improve query speed by an order of magnitude. Learn more about the Index-Only Scan on Use The Index, Luke!.

Sort Elimination

Sort elimination removes redundant sort operations. Redundant sort operations can arise out of multiple order by clauses:

WITH cte
  AS (SELECT id
        FROM feature_with
       ORDER BY id
     )
SELECT id
  FROM cte
 ORDER BY id

There is no reason to sort the result twice. Even if the order by clauses were different, only the outermost order by clause determines the row order in the result. With some exceptions,1 any order by clause except the outermost one can be eliminated.

Redundant sort operations are less obvious when they arise out of various order by, group by (potentially sort-based), over (partition by… order by…), and join (sort-merge) operations. Additionally, indexes might yield rows in a useful order and might thus make a sort operation obsolete.

In the following example, a database might sort the rows on (c1, c2) to resolve the row_number() window function.2 In that case, the order produced by the with query already satisfies the outer order by clause so that the database doesn't need to run another sort operation.

WITH cte
  AS (SELECT c1
           , c2
           , ROW_NUMBER()
             OVER(PARTITION BY c1 ORDER BY c2) rn
        FROM table_name
     )
SELECT c1, rn
  FROM cte
 ORDER BY c1, c2

Note that the outer order by clause is still required because the over clause only defines the relevant order for the window function. If the database executes the window function differently, e.g., distributing the partitions to parallel threads, then the database cannot eliminate the last sort operation. Omitting the outer order by clause would then return the rows in undefined order.

Comparison of Products

Besides PostgreSQL, all tested databases optimize with clauses in the same way that they optimize views or derived tables.

The PostgreSQL query planner considers each with query and the main statement separately. This makes the with clause an optimization fence (as of 9.63): meta data, such as the order in which a with query returns rows, is not available during optimization of the outer query.

Before 5.7, MySQL did not optimize derived tables as well as it optimizes views.4

Optimizations: WITH vs. derived tables vs. views

with Clause: Performance Impacts” by Markus Winand was originally published at modern SQL.

 

February 01, 2017


Craig Mullins

The DB2 12 for z/OS Blog Series - Part 2: Advanced Triggers

As we continue our series on new functionality in DB2 12 for z/OS, today we take a look at the improvements delivered for triggers. Before we dive into the new capabilities, let's briefly review what triggers are. Triggers are event-driven specialized procedures that are stored in, and executed by, the DBMS. Each trigger is attached to a single, specified table. Triggers can be thought...

(Read more)
 

January 31, 2017


Robert Catterall

Are You Using System Profile Monitoring to Manage Your DB2 for z/OS DDF Workload? Perhaps You Should

Here's a scenario that might sound familiar to you: you have a particular transaction, which I'll call TRNX, that is the source of quite a lot of deadlock activity in a DB2 for z/OS system. It seems that whenever more than one instance of TRNX is executing at the same time, a deadlock situation is highly likely. You went with row-level locking for the table spaces accessed by TRNX, but the trouble persisted. It is conceivable that rewriting the program code associated with TRNX might eliminate...

(Read more)

Henrik Loeser

Improve Security for your Domains on IBM Bluemix

Secure Your Apps Do you use your own domain names with IBM Bluemix? Then you probably know that you can secure access by adding SSL certificates for your domain. During my recent my work with...

(Read more)
Big Data University

This Week in Data Science (January 31, 2017)

Here’s this week’s news in Data Science and Big Data.IBM

Don’t forget to subscribe if you find this useful!

Interesting Data Science Articles and News

Upcoming Data Science Events

Cool Data Science Videos

The post This Week in Data Science (January 31, 2017) appeared first on BDU.

Subscribe by email

 

About

planetDB2 is an aggregator of blogs about the IBM DB2 database server. We combine and republish posts by bloggers around the world. Email us to have your blog included.
 

Bloggers

decor