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 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 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...

...

DB2utor

DB2 11 Block Fetch Capabilities

Last week I briefly went through the history of block fetch. Today I look at the block fetch enhancements delivered in DB2 11 for z/OS, known as package-based continuous block fetch, or blasting.
 

April 11, 2016


Craig Mullins

The Most Misunderstood Features of DB2 - Part 8: Do I have to pick just one?

There are so many misunderstood and misused aspects of DB2 for z/OS that it would be hard to choose only one. This series of blog posts, which concludes today with this one, has covered a wide variety of topics over 7 installments. So it would seem that there is no lack of misunderstanding out there! So, if I had to pick just one thing as the most misunderstood thing, I think I’m going to have to...

(Read more)
 

April 08, 2016


Craig Mullins

Happy Birthday Mainframe!

Fifty Two Years Ago This Week On April 7, 1964, IBM announced "a new generation of electronic computing equipment" called the IBM System/360... or as it, and subsequent generations of the machine have become known, the mainframe! If you'd like to take a walk down memory lane you can read the original press release on the IBM web site. The System/360, unquestionably, was one of the most...

(Read more)

DB2Night Replays

The DB2Night Show #Z67: SQL Performance Tuning in 2016

Presented by: Sheryl Larsen Sr. DB2 Product – Specialist, BMC Software "The DB2Night Show z67: SQL Performance Tuning in 2016" Replays available in WMV and M4V formats! 100% of our studio audience learned something!Sheryl discussed SQL features and performance in the most recent versions of DB2. Watch the replay...

(Read more)

Kim May

DB2 101 Event – 8 Seats Still Available!

Frank and I attended an IBM Business Partner event in Philadelphia earlier this week where we met with several IBM’ers supporting Business Partner initiatives in 2016.  Each of the specialists...

(Read more)
 

April 07, 2016


Willie Favero

The IBM System/360: It all happened 52 years ago... and the world has never been the same.

(Posted Sunday, April 7, 2016) Tuesday, April 7, 1964. That is the day it all became reality.

(Read more)

Xtivia

Dropping a DB2 Schema and Objects it Contains

It may be something that’s not terribly common to have to do, but that makes it all the more important that we document how to do it. Dropping a schema and all of the objects in that schema used to be tedious and time-consuming. We used to have to look for a large number of different types of objects and individually drop each one before we were unable to drop the schema itself. In DB2 9.5, IBM introduced ADMIN_DROP_SCHEMA to help with this.

Identifying Objects in a Schema

It is best to first list out the objects in a schema so you can communciate with others precisely what is being dropped, or at least record it before the schema is dropped. Also in DB2 9.5, the administrative view SYSIBMADM.OBJECTOWNERS was introduced. This joins together all the various system tables that list the various kinds of objects so we have one location to list the various kinds of objects. It is easy to query to find the objects in a particular schema, and lists the schema itself as an object:

select substr(OWNER,1,12) as OWNER
    , OWNERTYPE
    , substr(OBJECTNAME,1,30) as OBJECTNAME
    , OBJECTTYPE 
from SYSIBMADM.OBJECTOWNERS 
where OBJECTSCHEMA='SSIRS_AGENCY' 
with ur;

OWNER     OWNERTYPE OBJECTNAME               OBJECTTYPE              
--------- --------- ------------------------ ------------------------
SYSIBM    S         SSIR_DMART               SCHEMA                  
DB2BCUP   U         SQL150114132019140       TABLE CONSTRAINT        
DB2BCUP   U         INDV_AID_CD_DM           TABLE                   

  3 record(s) selected.

In the above SQL, you would obviously have to replace the schema name with the name of the schema you are working with.

Backout Planning

Like any good DBA, I first have a back out plan for every change I perform, and this is no different. Here is the data to collect before dropping a schema:

  • db2look with syntax for the whole database
  • List of objects in the schema from SYSIBMADM.OBJECTOWNERS
  • Count of rows in all tables in the schema from SYSCAT.TABLES
  • Exported data from the tables in the schema in del and/or ixf formats

My many-layered back out options are:

  1. Re-create the objects from the db2look ddl
  2. Import/load the data into the tables from the del files
  3. Create the tables and import data in from the ixf files
  4. Last Resort: restore a backup somewhere else and export out what I might have missed

Actually Dropping the Objects and Schema

Like many DBA tasks, there is more effort in planning and preparing the backout plan than there is in the actual work. Actually dropping the schema and all objects it contains is accomplished like this:

$ db2 "call ADMIN_DROP_SCHEMA('SSIRS_AGENCY',NULL,'DBA','DRP_TAB_ERROR')"

  Value of output parameters
  --------------------------
  Parameter Name  : ERRORTABSCHEMA
  Parameter Value : -

  Parameter Name  : ERRORTAB
  Parameter Value : -

  Return Status = 0
$ db2 "select * from DBA.DRP_TAB_ERROR"
SQL0204N  "DBA.DRP_TAB_ERROR" is an undefined name.  SQLSTATE=42704


The final select is done to ensure that no errors were generated.
I also verify there is noting left in the schema like this:

$ db2 "select * from SYSIBMADM.OBJECTOWNERS where OBJECTSCHEMA='SSIRS_AGENCY' with ur"

OWNER                                                                                                                            OWNERTYPE OBJECTNAME                                                                                                                       OBJECTSCHEMA                                                                                                                     OBJECTTYPE              
-------------------------------------------------------------------------------------------------------------------------------- --------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------

  0 record(s) selected.

The post Dropping a DB2 Schema and Objects it Contains appeared first on Xtivia.

 

April 06, 2016


Dave Beulke

Data Management – 5 Data Management Fundamentals

I continue to be amazed by high ranking executives, directors, managers and team leads that have only a minimal understanding of data management fundamentals and principles. I know I come from the old school, having gone through the industry and career transitions from flat files (VSAM), to...

(Read more)
 

April 05, 2016

Big Data University

This Week in Data Science (April 5, 2016)

IBM and Coursera IoT

Once again, here’s this week’s news in Data Science and Big Data.

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 (April 5, 2016) appeared first on Big Data University.


Data and Technology

What Does the Latest Salary Survey Say About Data Professionals?

In the latest Computerworld IT Salary Survey (2016) 71% of IT workers who took the survey reported that they received a raise in the past year. That is a nice healthy number. For those of us who...

(Read more)

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