B-Advised DB2 Blog

DB2's Got Talent Finals

2011-03-27T14:47:00.000-07:00

Hi All, Recently I have been taking part in the DB2's Got Talent competition on the DB2 Night Show. On last Friday, the last round of finals took place and I'm proud to say that I am one of the 4 who made it all the way through. If anyone wishes to see the replays, they are available at www.db2nightshow.com. If you liked my presentations, and wish to support me (the only finalist who talked

Getting More From The REPORT Utility

2011-03-19T17:58:00.000-07:00

Hi All, It has been way too long since my last post. In the past weeks I have been taking part in the DB2 Got Talent contest on the DB2 Night Show (www.db2nightshow.com). During the finals (which are still going on, so you are more than welcomed to vote for me) I talked about the REPORT utility and I wanted to add a blog post about that topic. The REPORT utility might seem a bit less important

Table Partitioning in DB2 Express - Part II

2010-07-14T07:22:00.000-07:00

Hi As a direct continuation to the previous post on that subject, I decided to post here the solution that I have developed for one of my clients, so you all can enjoy it. I do ask that if anyone improves the implementation, please send me a copy so I can post it here for everyone. All the scripts that create the objects required for this solution can be found in the resources page on our

IOD 2010 Global Conference Promotion Code Discount

2010-06-23T10:24:00.000-07:00

Hi All, It's been a real busy time for me lately, so this will be brief. I was elected as an IBM Champion for 2010, and as a result, I get to provide you folks with a promotion code that can get you a discount in registration. All you have to do is follow these instructions: IOD 2010 Global Conference Promotion Code Registration Instructions:Visit the IBM Information On Demand 2010 Global

Converting Number to Words Using SQL - Once Again

2010-05-31T00:11:00.000-07:00

Hi This post is related to http://blog.badvised.com/2009/12/converting-number-to-words-using-sql.html For some reason, one can not post a comment that is too long, so I have opened this post in order to share with you an alternative solution that was sent to me by Sriram Lakshminarasimhan. His solution does not require additional tables as I used in my implementation. Sriram, thank you for

Table Partitioning in DB2 Express

2010-05-17T10:37:00.000-07:00

Hi, It's been a while since my last post and I want to apologize, this one is about DB2 LUW ... (although everything here behaves the same on zOS if I'm not mistaken). A customer I was helping today had a problem with one of his tables. He has a really big table that keeps getting records inserted into, and he needs to periodically delete old records (just a plain log table), and the delete

Where am I?

2010-04-13T05:46:00.000-07:00

Hi I know it has been a long time since my last post, but in the last couple of months there were two significant changes in my life which took up all my time. The first change was buying my first apartment, which had me running around a bit more than I had expected. The second change which is probably the most important thing that happened to me yet was the birth of my first child. Her name

Index Usage For Foreign Key Enforcement by DB2

2010-01-24T13:42:00.000-08:00

In this post I would like to check how much flexibility DB2 provides regarding using an existing index for foreign key enforcement. A foreign key, is a type of constraint which tells DB2 to enforce that every value in a "child / details" table exists in a "parent / master" table. As a result, when making updates to the parent table, DB2 must perform some tests against the child table so no

Identity Column Insert Performance

2010-01-12T22:59:00.000-08:00

Hi It has been a while since my last post, but since it was the holiday season, I assumed that nobody was reading blog posts anyway... :-) This time I want to write about Identity Column insert performance, or to be more precise, the effect of the CACHE parameter of identity columns on insert performance. The identity column provided by DB2 is a special type of column. You can create only a

Space Reuse Affected By Commit?

2010-01-04T10:35:00.000-08:00

Hi In this post I would like to present to you a strange scenario involving DB2 space reuse which I was not able to reconstruct in a test system. One day a programmer told me he added a tiny change to one of his batch processes which caused the CPU consumption to increase from 1 minute (1 hour elapsed time) to 22 minutes. The batch process receives a file with client records (usually around 30

Converting Number to Words Using SQL

2009-12-17T02:42:00.000-08:00

Two posts in one week? Christmas came early this year… :-) There is a web site called experts exchange where people ask questions and other people answer. I spend some time answering questions there and today someone asked for a solution for the following problem: He has a number and he needs to convert it to text, for example, convert 10 to ten. This is required for some check handling

Copy DB2 Statistics – Modeling Production Systems

2009-12-14T13:01:00.000-08:00

This post is somewhat related to a previous post about comparing access path selection between test and production systems. In the previous post I talked about the problem that verifying a package has optimal access paths in the test system, will not guarantee it will have the same access paths in the production system. The post also had a link to a REXX script that compares plan_tables between

Converting RBA to Timestamp

2009-12-08T00:33:00.000-08:00

Hi This time I want to address an issue I see many people ask about in forums – how can I convert a RBA value to Timestamp. Well, first we should note that DB2 uses two methods to keep log records sequence. In non Data Sharing systems, each log record is associated with an RBA (Relative Byte Address) which is the 6 byte offset of the log record, from the beginning of the DB2 logs. In a Data

The Shy Objects

2009-11-29T00:00:00.000-08:00

Hi This post will be a bit different from the previous ones. No big experiments or useful tips, just an issue you will probably never encounter. I stumbled across it by accident while I was writing a script (that will be published in the near future). I have some experience with SQL Server, and there you can find many strange things you would not expect, stuff that seems like the QA team did

UNION ALL Performance and Behavior

2009-11-23T06:50:00.000-08:00

This time I am going to talk about UNION ALL. I am sure numerous posts were already dedicated to the UNION vs. UNION ALL issue, but just to make sure everyone knows the difference, UNION ALL returns all rows while UNION returns only distinct rows (which will require DB2 to sort the result set and remove duplicate rows before it is returned). What I wanted to do is to check how DB2 process the

REORG SHRLEVEL CHANGE – aiming at 24x7 – Part II

2009-11-15T14:13:00.000-08:00

In the previous post I started talking about the REORG utility with the shrlevel change option. I told you about two problems that one would probably encounter when running online REORG with the default parameter values: 1. The default value of MAXRO is 300 seconds, while the default timeout interval (IRLMRWT) is 60 seconds which means, once DB2 will get to the last log iteration, most likely

REORG SHRLEVEL CHANGE – aiming at 24x7 – Part I

2009-11-08T07:52:00.000-08:00

As I have seen in sites where I worked, there is some confusion regarding the parameters of the REORG utility. Everybody would like to have true 24x7 systems using online REORG SHRLEVEL CHANGE, but in order to be on the safe side, they used SHRLEVEL REFERENCE instead. When DB2 performs a REORG with SHRLEVEL CHANGE, it builds the shadow files the same way as it does with REORG SHRLEVEL REFERENCE,

DB2 V7 Restart Identity Column

2009-11-01T13:11:00.000-08:00

DB2 V7? is this for real? Well, as sad as it sounds, some people still use DB2 v7, or DB2 v8 compatibility mode. It's true; I even have a client that use it. That client had a problem; a programmer copied a table with an identity column from the production system to the test system using dsn1copy. Since the production table was bigger, when trying to insert records in the test system he received

Load Resume Shrlevel Change Behavior - The Results

2009-10-26T15:01:00.000-07:00

Hi As promised, here are the answers to the questions from the previous post. For my experiment I have created the following tables: CREATE TABLE MOMI.MOMITEST ( ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 10000 ), TMSTAMP TIMESTAMP NOT NULL WITH DEFAULT , -- DMY1 CHAR(250) NOT NULL WITH DEFAULT 'MOMI',

When Exactly is Current Timestamp ???

2009-10-21T10:14:00.000-07:00

Well, I know I still owe some answers regarding load resume yes shrlevel change post, but this post will be about an issue I faced when conducting the load utility testing. Assuming we have this scenario: table1 with column named tmstamp with a default value of current timestamp table2 with 2 columns - rec_tmstamp and max_tmstamp Some process is constantly inserting rows into table1 (with

Load Resume Shrlevel Change Behavior

2009-10-20T14:17:00.000-07:00

Hi Usually, I find it very hard to complain about the DB2 manuals, since they are rather detailed, but as for the load utility, I could not find in the manuals any description of the actual behavior when using resume yes shrlevel change. Some questions I could not find answers to in the manual were 1) Which part of the data is available for concurrent access? only the data that existed in the

DB2CFIMP / Configuration Assistant hangs when importing

2009-10-18T09:09:00.001-07:00

Hi Just a little problem with the DB2 LUW client I faced today. I needed to copy the database manager configuration from one server to another. I used DB2CFEXP in order to export the configuration at the source instance, and then tried to import it using DB2CFIMP on the target instance. The DB2CFIMP utility just froze and I was getting no reply for several minutes. I tried to perform the same

Compare Static SQL Access Paths

2009-10-14T15:17:00.000-07:00

What I am going to talk about this time is one of the most critical factors (in my opinion) of application performance - the access paths that were selected for your SQL statements. There are two types of SQL statements - dynamic and static. This post will be devoted for static SQL statements, that is, statements for which an access path is chosen at bind time. As a part of project I did for a

Job generation utility / a smarter Listdef

2009-10-14T15:14:00.000-07:00

As a DBA, sometimes I find myself in need of running jobs on a list of objects which I can only identify using a SQL statement. In these cases, the IBM Listdef utility is not sophisticated enough and a massive amount of manual labor is needed. In order to shorten the time it takes me to prepare such jobs, I wrote a simple REXX script that receives the following parameters (description of

Debugging with The Dynamic Statement Cache

2009-10-07T14:31:00.000-07:00

Hi In DB2 version 8 one of my favorite features was introduced and that is, the explain stmt cache statement. This statement provides information regarding the content of the dynamic statement cache (which statements are cached and what is the cached access path), and if you have ifcid 318 turned on, you also receive statistics such as number of executions, cpu time etc. Using that information