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.

 

April 29, 2016


Robert Catterall

DB2 for z/OS: Should You Do Some Buffer Pool Consolidation?

19, 19, 23, 26. Know what these numbers have in common? They indicate the number of 4K buffer pools allocated for four different production DB2 for z/OS subsystems that I analyzed over the past couple of years. I believe that there are quite a few sites where a plethora of 4K buffer pools have been defined for a given DB2 subsystem. That's not illegal or anything (DB2 allows you to have as many as 50 different 4K pools for a single subsystem), but it does make the task of managing and...

(Read more)

Leons Petrazickis

Datathon For Diabetes in Boston

This weekend I'm at the Datathon for Diabetes in Boston. The goal is to use publicly available data to generate an insightful analysis of diabetes. The post Datathon For Diabetes in Boston appeared...

(Read more)
 

April 28, 2016

Jack Vamvas

How to redirect zip to another folder

Question: When attempting to use gzip on Linux and zip the file - I tried by going to another folder and completing the gzip command as normal. As an example:

Move to another folder , the intended target for the zipped file

Execute code as similar

gzip /myfolder/file.txt

 I  discovered it was gzip in the same folder  as the source folder. Not zipping the file in the target folder.

Is it possible to gzip and redirect to another folder?

Answer:  You need to use the  -c switch

So if you need to redirect the zip file to the intended target folder you would execute code similar to the below.

gzip -c filename > /target/location/filename1.Zip

 

Read More

How to generate db2support files (DBA DB2)

Real world experience is the DBA king

 

 

April 27, 2016


Willie Favero

The last word on the status of SYNONYMS in DB2 for z/OS

(Posted Saturday, April 27, 2016) It turns out that my posting on April 16, 2016 discussing SYNONYMS being un-deprecated was a bit premature. Please ignore the contents of that post. Instead, here's the most up-to-date information about SYNONYMS presented by today?s guest blogger Patrick...

(Read more)
Big Data University

This Week in Data Science (April 26, 2016)

Here’s this week’s news in Data Science and Big Data.Game of Thrones

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

Interesting Data Science Articles and News

Upcoming Data Science Events

The post This Week in Data Science (April 26, 2016) appeared first on Big Data University.

 

April 26, 2016


Kim May

*FREE* IBM DB2 Analytics Accelerator (IDAA) Hands-on Training

DB2 Gold Consultant Frank Fillmore will deliver a free, hands-on IBM DB2 Analytics Accelerator (IDAA) class at the IBM offices in Chicago at 71 South Wacker Drive on Monday and Tuesday, June 6th and...

(Read more)

DB2utor

IDUG NA Conference Reminder

I'm very excited about the upcoming IDUG DB2 Tech Conference for North America. Though DB2 for z/OS 12 doesn't have an official release date, it will be prominent of discussion at the conference.
 

April 25, 2016


Willie Favero

"HOUSTON, WE HAVE A PROBLEM"

(Posted Monday, April 25, 2016) I think that misquote from the Apollo 13 movie is perfect for today?s blog post. You see, here in Houston, we have a DB2 User Group that has its attendance dropping off. The solution of course, is to get more folk from around the Houston area to attend our...

(Read more)

DB2Night Replays

The DB2Night Show z68: DB2 for z/OS Trusted Contexts and Roles

Presented by: Cristian Molaro IBM Champion and Gold Consultant "The DB2Night Show z68: DB2 for z/OS Trusted Contexts and Roles: What you must know" Replays available in WMV and M4V formats! 100% of our studio audience learned something!Cristian provided valuable tips and insight into trusted contexts and roles, including their definition and configuration. Watch the replay...

(Read more)
 

April 24, 2016

Manohar Visnawatha

How do you CronJob a script to mail it to your INBOX

Many time we come across a situation where in we need to schedule a script and mail that report to our INBOX
If that is the case here is how we do it
Read more »
Manohar Visnawatha

DB2 Partitioned Tables - Life made easy - Part 2

Continuing from my first post of this series DB2 Partitioned Tables - Life made easy - Part 1

Go through that post to get an idea of the table structure created and partitions used in this exercise .

We now go through some workout examples of the following categories

  1. Attaching a partition
  2. Detaching a partition
  3. Roll in a partition
  4. Roll out a partition 
Detaching / Roll out a partition : 
Read more »
Manohar Visnawatha

DB2 Partitioned Tables - Life made easy - Part 3

So all we are going to see in this post is some left-outs from the last 2 posts from this series

Please refer to my previous posts
DB2 Partitioned Tables - Life made easy - Part 1 &
DB2 Partitioned Tables - Life made easy - Part 2 to catch up this series .

Nevertheless of our discussions in previous posts we have seen the partition's being attached and detached from the base partitioned table (i.e., DB2INST1.PARTBLE), You can also do this Roll-In of data from the other tables also .

ALTER TABLE DB2INST1.PARTBLE
ATTACH PARTITION PART JAN15
STARTING '1/1/2015'
ENDING '3/31/2015' IN TBSP2
FROM TABLE DB2INST1.EMPLOYEE


Read more »
Manohar Visnawatha

Basic Shell command to make ourselves comfortable at all times - Part 2


Welcome to the Part 2 of this series , We have discussed about grep,ls,awk,find in my last post

Basic Shell command to make ourselves comfortable at all times


In this post we are gonna see some crontab and vi editor options.

Crontab :


Crontab is a utility provided by linux to schedule jobs or scripts , Like if you want to run a job every morning 8:00 am , you can do this with the crontab utility
dbadm@linux122:~> crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXX7mwgtU installed on Tue Nov 12 17:53:36 2013)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

#### BACKUPS

01 01 * * * /home/DB_BKPS/scripts/sample1_backup.sh
01 02 * * * /home/DB_BKPS/scripts/sample2_backup.sh

Read more »
Manohar Visnawatha

Friendship between REORG and Temp Tablespace's - Part2

In my previous post we have discussed about REORG utility and some monitoring utilities for REORG

Friendship between REORG and Temp Tablespace's

Normally when you issue a REORG on a table like one below

REORG TABLE DB2INST1.EMPLOYEE

db2 does the reorganization of the table in the same tablespace it is residing on ,which is not a good thing always , especially when your data is growing there is no sense in doing reorg in the same tablespace and disturbing the other physical objects like tables and MQT's performance

That is where system temporary tablespaces save us from the performance degradations while REORG

So how do we achieve this ? , we create separate bufferpools for all different page sizes i.e., 4K, 8K, 16K, 32K

Read more »
Manohar Visnawatha

Accidents happen - Recovering a dropped table

A must quality of a DBA is to know the recovery strategy first before entering the battlefield .That is the reason I have started the Accidents happen series so that we can discuss further recovery strategies in this series of posts.

I have not found many search results where somebody who could show in detail how to recover an accidentally dropped table . So I thought of giving an example scenario as it will help at least some people who are dealing with it for the first time.

The first precaution you can take is to add WITH RESTRICT ON DROP clause to the CREATE TABLE

Say you created a table as shown below

CREATE TABLE "DB2INST1"."EMPLOYEE" ( "EMPNO" CHAR(6 OCTETS) NOT NULL , "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , "MIDINIT" CHAR(1 OCTETS) , "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , "WORKDEPT" CHAR(3 OCTETS) , "PHONENO" CHAR(4 OCTETS) , "HIREDATE" DATE , "JOB" CHAR(8 OCTETS) , "EDLEVEL" SMALLINT NOT NULL , "SEX" CHAR(1 OCTETS) , "BIRTHDATE" DATE , "SALARY" DECIMAL(9,2) , "BONUS" DECIMAL(9,2) , "COMM" DECIMAL(9,2) )  
IN "USERSPACE1"
WITH RESTRICT ON DROP
ORGANIZE BY ROW ;
Read more »
Manohar Visnawatha

Pro's and Con's of default variable $_ in Perl

In this post we will see some good practice while you write your Perl scripts . If you see my previous post Getting started with Awesome Perl I have discussed usage of default variable $_ while what we see in this post how good for a programmer is to use the default variable

It's for sure that the default variable ($_) does the work but when you have a large chunk of code , readability of the code will become a problem . Lets take a piece of code and analyze it .

Let's pass some sql data into an array like one shown below

my @data = `db2 "list tables for schema DB2INST1"`

Now the array @data is populated with some data , lets go and do some manipulations

Read more »
Manohar Visnawatha

Handy Perl script to check the cost of STORED PROCEDURES

In my previous 2 posts we have discussed some basics of Perl coding namely

Keeping these basics on mind and adding to them here we develop our first Perl script .

Although it's the DBA who deploy STORED PROCEDURE and FUNCTIONS , some times you want to quickly check the PROCEDURES deployed from last week or so and check the cost(timerons) against them to find the culprit.

I use this script more than seldom, but every time I use this , it fulfills my tasks

This script helps you check the ESTIMATED COSTS of the deployed STORED PROCEDURES from the last n no. of days on the go.

Read more »
 

April 21, 2016


Willie Favero

April 2016 (RSU1603) service package has been tested and is now available

(Posted Thursday, April 21, 2016) Testing for RSU service package RSU1603 is now complete. This April 1, 2016 "1st Quarter Quarterly Report" (108 KB PDF file) contains ALL service through the end of December 2015 not already marked RSU. This service also includes PE resolution and...

(Read more)

Xtivia

Analyzing Lock Escalation

What is Lock Escalation?

When LOCKLIST and MAXLOCKS are not set to AUTOMATIC, or total system memory is constrained, lock escalation can occur. When there is not enough room in the lock list to store additional locks that an application needs to continue processing, lock escalation occurs. DB2 will attempt to satisfy queries and perform updates, inserts, and deletes by locking at the row level. Each row level lock requires a certain amount of memory that varies by the version of DB2. The IBM DB2 Knowledge Center page for the LOCKLIST parameter spells out how much memory each row lock takes. When there is no more memory available in the lock list for additional locks, or when a single application reaches the percentage of the lock list defined by MAXLOCKS, DB2 performs what is called lock escalation. This means that applications with row-level locks instead try to acquire table-level locks.

The Problem

The table-level locks acquired with lock escalation are much worse for concurrency. Now instead of only individual rows being unavailable for reads, updates, and deletes (depending on the isolation levels of the applications involved and the work being performed), the entire table may be unavailable. This can lead to other negative locking phenomena, including longer lock-wait times, lock timeouts, and even deadlocks.

Analyzing the Problem

Lock escalation is one of the more negative things that can happen with DB2’s concurrency control. It is something DB2 databases should be monitored for and that should be addressed if it occurs on an ongoing basis. Lock escalation is documented in the DB2 diagnostic log, and this is one of the better places to look for it. Once my diagnostic log parser alerts me that there is lock escalation occurring, I spend some time analyzing to see which databases (if more than one on the instance) and which times it is occurring at. The db2diag tool is a powerful tool in this analysis. The following syntax will list out occurrences of lock escalation, including the database name and time stamp:

$ db2diag -g message:=scalation -fmt '%ts %db %errname'
2016-03-28-01.13.34.680662 PRODM
2016-03-28-01.13.34.681123 PRODM
2016-03-28-01.14.41.746583 PRODM
2016-03-28-01.14.41.747016 PRODM
2016-03-28-01.16.28.127806 PRODM
2016-03-28-01.16.28.128327 PRODM
2016-03-28-01.17.20.249458 PRODM
2016-03-28-01.17.20.250037 PRODM
2016-03-28-02.45.10.337993 PRODM
2016-03-28-02.45.10.338500 PRODM
2016-03-28-02.45.46.461853 PRODM
2016-03-28-02.45.46.462300 PRODM
...

This is a bit messier than I would like it to be, but when using db2diag, for some reason, the errono field is not populated for lock escalations. You can get the same info from SYSIBMADM.PDLOGMSGS_LAST24HOURS or the table function PD_GET_LOG_MSGS, where oddly enough the msgnum field IS populated:

select timestamp
    , substr(dbname,1,12) as dbname 
from sysibmadm.PDLOGMSGS_LAST24HOURS 
where msgnum=5502 
with ur

TIMESTAMP                  DBNAME
-------------------------- ------------
2016-03-28-12.20.25.549646 PRODM
2016-03-28-12.20.24.804685 PRODM
2016-03-28-12.20.14.725929 PRODM
2016-03-28-12.20.02.290882 PRODM
...

Analyzing the timing of lock escalation events can be quite useful to determine if perhaps there is an application that is using a higher isolation level and also if there may be missing indexes for the workload. There is also a lot more detailed information in the MSG field of SYSIBMADM.PDLOGMSGS_LAST24HOURS or PD_GET_LOG_MSGS – which may include the application name, the specific SQL being executed, and other details.

Resolving the Problem

The most obvious solution here is to increase the size of LOCKLIST in the db cfg using syntax like this:

db2 update db cfg for PRODM using LOCKLIST 30000

It is also possible that the MAXLOCKS parameter may need to be adjusted. Both of these parameters can be set to AUTOMATIC and tuned by STMM(Self Tuning Memory Manager). In fact, these are the two parameters I’m most likely to include in STMM tuning because the impact of having them too small can be so high, and because from what I’ve seen, DB2 seems to do a good job of tuning them.

The post Analyzing Lock Escalation appeared first on Xtivia.


Data and Technology

Database Access Auditing: Who Did What to Which Data When?

As just about anyone in business these days knows there is a growing list of government and industry regulations that organizations must understand and comply with. This increasing compliance...

(Read more)
 

April 20, 2016


Dave Beulke

DB2 LUW Version 11- 5 Great New Features and Many More to Come

DB2 LUW Version 11 was unveiled last Tuesday, April 12th. The announcement described the many tremendous items which are included in the new DB2 LUW 11. Being part of the DB2 LUW 11 Beta program has been exciting. Now that IBM has announced it, I’ve highlighted some of the new features in DB2 LUW...

(Read more)

Craig Mullins

IDUG is Coming to the Lone Star State

The weather is improving, Spring has sprung and that means it is, once again, time to start planning your trip to this year's IDUG North American DB2 Tech Conference. This year it is being held in Austin, TX at the Renaissance Austin Hotel. This is a very nice area and the hotel is great – I’ve stayed there numerous times in the past. If you’ve ever attended an IDUG conference before then...

(Read more)
 

April 19, 2016

Big Data University

This Week in Data Science (April 19, 2016)

Here’s this week’s news in Data Science and Big Data. Kobe Bryant Shots

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

Interesting Data Science Articles and News

Upcoming Data Science Events

The post This Week in Data Science (April 19, 2016) appeared first on Big Data University.


Antonio Cangiano

Big Data University: Educating One Million Data Scientists

In last week’s post, I discussed one of the projects I’m currently working on at IBM. My colleagues and I have another interesting project that I want to introduce you to as well. It’s called Big...

...

DB2utor

How Data Server Manager Fits with z/OS

I’ve written a lot about Data Server Manager (DSM) since its release in October and the announcement of DB2 for z/OS support in January.
 

April 18, 2016


Craig Mullins

Don’t let ICIs put your DB2 application in the ICU!

Those of you who have been paying close attention have probably already noticed, or indeed encountered, incompatibility issues with how DB2 for z/OS behaves from version to version. Oh, sure, we all know that there have been deprecated features, and we deal with those over the long periods of time it takes for the features to be completely removed from DB2. Sure, we don't like it, but it is not...

(Read more)
 

April 16, 2016


DB2Night Replays

The DB2Night Show #177: Sneak Peek Overview of What's New in DB2 LUW V11.1

@ibm_db2 ::: @db2guy Learn more about IBM DB2 V11.1 on IBM.com What's New in DB2 LUW V11.1 - Overview Special Guests:Matt Huras,Chief Architect DB2 LUWBerni Schiefer,IBM Fellow 97% of our audience learned something! A new version of DB2 LUW is born! V11.1 will be generally available mid-June 2016. Almost 75% of our HUGE live studio audience indicated that they were very excited about the new features and capabilities becoming available...

(Read more)

Willie Favero

Synonyms go UN-deprecated in DB2 11

(Posted Saturday, April 16, 2016) It turns out that my posting about SYNONYMS being un-deprecated was a bit premature. Please ignore the contents of this post. Instead, please click through to my latest blog post where Patrick Bossman, STSM with DB2 for z/OS Development, present the REAL story...

(Read more)
 

April 13, 2016


Henrik Loeser

CeBIT: Goldsmith in the Hybrid Cloud - How to Create Value from Enterprise Data

Gold Nuggets - Data as Gold Data, data, data. There is a lot of data, data already stored or archived, and data about to be produced, generated, measured, or even missing data. But there is not...

(Read more)

Henrik Loeser

Data Protection, Privacy, Security and the Cloud

Protecting your bits (This is the first post in a planned series on data protection, security, and privacy related to DB2/dashDB in the cloud and IBM Bluemix) As a data/database guy from Germany,...

(Read more)
 

April 12, 2016

Big Data University

This Week in Data Science (April 12, 2016)

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

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

Interesting Data Science Articles and News

Upcoming Data Science Events

The post This Week in Data Science (April 12, 2016) appeared first on Big Data University.


Antonio Cangiano

Data Scientist Workbench: IBM’s All-in-One Tool for Data Scientists

For the past ten years, I’ve been working on a multitude of projects within IBM. Being part of the Emerging Technologies team, in the IBM Analytics group, I get to use (and often implement) some...

...

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