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.

 

August 30, 2016


Robert Catterall

DB2 for z/OS: Clearing Up Some Matters Pertaining to Database Access Threads

I have recently received a number of questions pertaining to DB2 for z/OS database access threads, or DBATs. DBATs are threads used in the execution of SQL statements that are sent to DB2 from network-attached applications (i.e., from DRDA requesters that access DB2 for z/OS by way of DB2's distributed data facility, also known as DDF). Thinking that these questions (and associated answers) might be of interest to a good many people in the DB2 for z/OS community, I'm packaging them in this blog...

(Read more)
 

August 26, 2016


ChannelDB2 Videos

DB2 Tips n Tricks Part 94 - How To Find Tablespaces included inside Tablespace Level Backup Image


Thumbnail

How To Find Tablespaces included inside Tablespace Level Backup Image db2ckbkp -T imgname Happy Learning & Sharing

ChannelDB2 Videos

DB2 Tips n Tricks Part 93 - How LOGARCHMETH2 is not alternative for LOGARCHMETH1


Thumbnail

How LOGARCHMETH2 is not alternative or backup for LOGARCHMETH1 Configure failarchlog DB CFG Parameter Happy Learning & Sharing
Jack Vamvas

How to write a DB2 loop with INSERT

Question: I’d like write a sql statement to loop through an INSERT statement and increment with a  count. The purpose is to create some test tables for load testing.

Answer: It is possible to create a loop in DB2 which loops through an incremental INSERT. This is a basic example, which can be customised for your purposes.Note the use of ATOMIC. The purpose of ATOMIC is to rollback before the call is passed back to the requestor, if there is a problem.

In this example the CNT variable increments at every INSERT, up until it is under 100000.

 

db2 “CREATE TABLE mytble (ID INT)”
db2 "BEGIN ATOMIC DECLARE CNT INT DEFAULT 5; WHILE CNT < 100000 DO INSERT INTO mytbl (ID) VALUES('16'); SET CNT = CNT + 1; END WHILE;END"

 Read More

Software unit testing and DB2 sql loop test code (DBA DB2)

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

 

 

 

 

 

 

 

 

August 25, 2016


Data and Technology

A Dozen SQL Rules of Thumb, Part 2

Today’s blog post picks up where we left off in our three-part series of rules of thumb (ROTs) that apply generally to SQL development regardless of the underlying DBMS. These are the general guiding...

(Read more)
 

August 24, 2016


Dave Beulke

3 Consideration for Enjoying the Data Lake

With all the outside activities with friends and family, summer vacations are always wonderful. Being outside at the lake enjoying the warm weather and cooling off in the lake are a wonderfully relaxing great times. This is the safe, content image that everyone thinks about when discussing the new...

(Read more)
 

August 23, 2016

Big Data University

This Week in Data Science (August 23, 2016)

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

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

Interesting Data Science Articles and News

Upcoming Data Science Events

  • Constant Contact: Using IBM BigInsights to Create Business Insight – Join this session on August 25th to learn how Constant Contact, a leader in email marketing, is using IBM BigInsights to create useful insights for their clients in a way that scales.
  • IBM i2 Summit – Join the IBM i2 Summit on August 30-31 to hear directly from experts who are using all forms of data, including “dark data,” to outthink threats.
  • Combining IBM SPSS Statistics and R for competitive advantage – This Data Science Central Webinar event on September 1st, will show you how SPSS Statistics can help you keep up with the influx of new data and make faster, better business decisions without coding.
  • Big Data and Health presented by IBM Canada – Join We Are Wearables Toronto and IBM Canada on September 16th for a look at how wearables and sensors are changing healthcare.
  • How Data Can Help in the Fight Against Sexual Assault – Join the Center for Data Innovation and Rise, a civil rights nonprofit, on October 6th in Washington D.C., for a panel discussion on how policymakers and law enforcement can develop data-driven policies and practices to help in the fight against sexual assault and improve the lives of survivors.

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


DB2utor

Setting Special Registers Using Profiles

DB2 11 introduced a powerful feature that allows you to set special registers for remote applications by creating a profile in the profile tables. Information on the install jobs used to create these tables, as well as rules for inserting rows into the tables, can be found in the DB2 Administration Guide or the IBM Knowledge Center.
 

August 22, 2016


Craig Mullins

The Tao of DB2 - Part 6: That Storage Stuff

When we last looked in on our DBAs (the soon-to-retire mentor and his intern) the intern was learning about backup and recovery and had put together a comprehensive backup plan for the new system when it would move to production.  As the intern reclined in his chair viewing the magnificent backup plan with pride, his mentor glanced at it and admitted that the intern had done well. Just...

(Read more)

Henrik Loeser

Notes on Notebooks, Data, DB2, and Bluemix

Weather Graph in Jupyter Notebook Some time ago I shared with you how I used open data like weather data and CeBIT statistics to have some fun with Jupyter Notebooks. Notebooks are an old, but now -...

(Read more)
 

August 17, 2016


Dave Beulke

Cubicle Humor

Over the years I have seen some great posters and interesting items in client cubicles. Below is an example of one. Enjoy! Download this as a PDF     Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high...

(Read more)
Jack Vamvas

How to set up Avamar and DB2 backup object retention

I was configuring a DB2 LUW database to work with Avamar as the backup system. An issue arose about managing the DB2 database backup retention period. Does DB2 LUW or Avamar control the retention period for a DB2 LUW database?

For some comparison , I’ll outline briefly how TSM is configured with DB2 LUW.

If I set the NUM_DB_BACKUPS, REC_HIS_RETENTN, AUTO_DEL_REC configurations and values in a DB2 database and use TSM , then these values have a direct impact on the TSM retention. In a typical TSM configuration , the usage  AUTO_DEL_REC_OBJ will delete the objects from TSM once the DB2 history file is pruned based on values in NUM_DB_BACKUPS and REC_HIS_RETENTN

Number of database backups to retain   (NUM_DB_BACKUPS) = 12
Recovery history retention (days)     (REC_HIS_RETENTN) = 14
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON

 Read more about these parameters at Manage DB2 backups retention policy

These values influence the db2 history file.

In comparison how does Avamar interact with the history file?

The short answer is that pruning of the DB2 history file does not sync up with the Avamar catalogue.  DB2 history pruning effects only the local DB2 history file .   Avamar will expire based on the retention in the Avamar group.

 Let’s look a bit closer at the two scenarios in retention and expiration in the DB2 LUW and Avamar set up.

1) Archive logs and adhoc backup sent from the DB2 client. In this situation typically you’d have the LOGARCHMETH1 configured for the Archival logging management and then use ad-hoc backups. For example , you'd configure LOGARCHMETH1 for archival logging and issue a backup referencing the relevant Avtar libraries:

 

 db2 update db cfg for MYDB using LOGARCHMETH1 VENDOR:/usr/local/avamar/lib/libdb2_avamar.so logarchopt1 @/usr/local/avamar/avdb2.flg

db2  backup db MYDB online load /usr/local/avamar/lib/libdb2_avamarloader.so  open 2 sessions options @/usr/local/avamar/avdb2.flg

 

To set the Avamar expiration add      --expires=10  to the flag file.  I’ve used 10 as an example, set it according to the Recovery Point Objective and Backup Retention Policy for this database server.

The key to this configuration is ensuring all the file are available to perform a Restore and Rollforward. Setting the correct expiries is critical.

The other reason to set the expiry is to ensure the backup copy is not kept forever. Avamar doesn’t assign a retention when there is a CLI backup

2) Avamar backups. In this scenario backups are scheduled and managed through Avamar. The retention set at the group level defines the retention period for the backup objects.

A reminder  that pruning of the DB2 history file does not sync up with the Avamar catalogue. DB2 history pruning effects only the local DB2 history file .   Avamar will expire based on the retention in the group.

Ensure the retention period set at the flag file is co-ordinated with the Avamar group retention.So that means that it is necessary to coordinate the retention between the avtar flag set on the database server and Avamar groups very carefully.

This command will list the backup objects stored

../avtar --flagfile=/usr/local/avamar/avdb2.flg --list

Read More on Avtar , Avamar and DB2 backups

 Avamar avtar command option cheat sheet

SQL2161N A damaged recovery history file could not be fixed. (DBA ...

How to terminate a DB2 backup (DBA DB2)

 

 

 

 

August 16, 2016

Big Data University

This Week in Data Science (August 16, 2016)

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

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


DB2utor

How DSN1COPY Improves Data Validation

DSN1COPY is a high-performance copy utility that should have a place in every DBA's toolbox.
DBAs love using DSN1COPY because it quickly copies files from one system to another. You just need to be careful about how to use it. Test out the process and make sure you understand the impact database changes have on your test system and production systems.
 

August 15, 2016


Data and Technology

A Dozen SQL Rules of Thumb, Part 1

today’s blog post we will examine some rules of thumb that apply generally to SQL development regardless of the underlying DBMS. These are the general guiding principles by which your SQL...

(Read more)

Craig Mullins

The Tao of DB2 - Part 5: Build a Backup and Recovery Plan

We join our intern DBA and his mentor where we left them last week... The intern is feeling good about himself. He has worked with the application team on the big new project designing the database structures, protecting the data using constraints and implementing proper security protocols, and assisting the development team on a daily basis. The intern was beginning to think, that perhaps, just...

(Read more)

Kim May

IBM Authorized Training – “Using Queue Replication” Starting August 23rd

We have seats available in the “Using Queue Replication” class scheduled to run Tuesday through Friday, August 23-26.  Students may attend class in our Baltimore classroom or via distance...

(Read more)
 

August 13, 2016


ChannelDB2 Videos

Overview of Transactional Logging in DB2 Database


Thumbnail

"One of the primary mechanisms used by DB2 to ensure data consistency and recoverability is transactional logging. This article outlines key concepts, helps ...

ChannelDB2 Videos

Filesystems for DB2 on Unix and Linux Systems


Thumbnail

DB2 doesn’t have any defaults for filesystems because that is an OS-level thing. However, there are a few sanity checks and some recommended separation when ...
 

August 10, 2016


Dave Beulke

Helping Millennials Understand the Mainframe

Being a consultant specializing on performance issues exposes me to a wide variety and types of systems and platforms. Focusing on performance issues on a variety of platforms, cloud, distributed, and mainframe, I’ve realized they all have wonderful uniqueness and plenty of issues to keep me...

(Read more)
 

August 09, 2016

Big Data University

This Week in Data Science (August 09, 2016)

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

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

Interesting Data Science Articles and News

Upcoming Data Science Events

The New Big Data University Platform

  • Welcome to the new BDU! – Big Data University has been updated and massively improved. Find details about the changes here.
  • Courses – Courses now have improved structure and offer a more user-friendly experience. Check out the many new courses offered.
  • Learning Paths – The new learning paths make it easy to get started on your path to expertise in your field of choice.
  • Badges – The new badges offered are distinguished by levels. Level 1 badges can now be claimed immediately.

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


DB2utor

Data Studio Tools Included in Data Server Manager 2.1

In January I wrote about IBM Data Server Manager, noting that the packaging and delivery of features is different for z/OS than it is for other supported platforms. Now that the latest version, Data Server Manager 2.1, is available, I want to get back to this.
 

August 08, 2016


Craig Mullins

The Tao of DB2 - Part 4: Protect the Data!

When we last checked in on our intern DBA and his mentor the intern was assigned to help out on the new project. He worked with the development team and created the DDL to implement the needed database structures. Today... a developer shows up in the intern's cube and tells him that the data looks wrong. The intern rubs his forehead and brings up the data in his table editor to see what the...

(Read more)
 

August 05, 2016


Frank Fillmore

#IBM Q Replication – “Frank’s Field Notes Summer 2016” Materials @IBMIIG

One of our best-attended webinars ever!  The recording of the webinar is found here.  There presentation materials for download: IBM Q Replication – Franks Field Notes Summer 16

(Read more)
 

August 04, 2016


Craig Mullins

The Tao of DB2 - Part 3: The New Big Project

When last we left our protagonists, the intern had been sent away to learn more about tools and documentation, while his mentor planned for the new database and application that was in the works. The DBA summoned his intern to his cubicle and told him, “Today I want you to work on implementing the new database for Project C that everybody here has been talking about for some time now. I’m sure...

(Read more)
 

August 02, 2016

Big Data University

This Week in Data Science (August 02, 2016)

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

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

Interesting Data Science Articles and News

Upcoming Data Science Events

  • Big Data & Analytics for Pharma Summit – Join some of the world’s top analytics professionals within the pharmaceutical industry on November 3 & 4 in Philidelphia for kenote speeches, workshops, and more.
  • eMetrics Summit – Marketing analytics practitioners, experts and visionaries discuss capturing and applying insights from data on October 23 – 27 in New York.
  • Big Data University on 2016 Hadoop Summit – Join Big Data University at the Hadoop Summit in Australia on September 1st as they discuss spatial-temporal trajectory analysis with Spark.

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


DB2utor

Controlling DDF Threads using Profiles

Recently I wrote about IBM's strategy to move customers from DB2 Connect Gateway to DB2 Connect Client. The customers I've heard from aren't happy with this development.
 

August 01, 2016


Craig Mullins

The Tao of DB2 - Part 2: Beyond the Manuals

When last we left our intern DBA his mentor, the experienced DBA, had sent him off to read the DB2 manuals. What a thankless task! The intern was busily reading manuals on line while he downloaded the PDF versions to read later. But he was not gaining a lot of insight from the task. Although he was used to technical jargon, the manuals were difficult to consume and understand. He threw his hands...

(Read more)
 

July 28, 2016


Robert Catterall

DB2 for z/OS: Clearing the Air Regarding CLOSE YES for Table Spaces and Indexes

This is another of my DB2 for z/OS blog entries motivated by a desire to clear up a matter about which there is some misunderstanding in the DB2 community. In this case, the misunderstanding concerns the CLOSE option of the CREATE and ALTER statements for DB2 for z/OS table spaces and indexes. The confusion here, as I've encountered it, is mainly related to differences between "soft close" and "hard close" of DB2 data sets (table spaces or indexes, or partitions of same if the objects are...

(Read more)

ChannelDB2 Videos

IBM Q Replication Frank’s Field Notes, Summer ‘16 2016 07 28


Thumbnail

Having worked with Q Replication customers since 2006, DB2 Gold Consultant and IBM Champion Frank Fillmore has collected useful tips and techniques for Q Rep...
 

July 27, 2016


Craig Mullins

The Tao of DB2 - Part 1: Achieving Balance and Understanding with DB2

This blog post begins a multi-part series of posts as we look in on a new DB2 DBA learning from a long-time DB2 DBA who is preparing to retire... Our expert DB2 DBA sure has his work cut out for himself, but he understand the Tao of DB2 and is armed with database Te... He recognizes the Li of the application programs and how they work with DB2... He can remove tweaks that work against the...

(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