Solve DB2 “stuck” shared quiesce problems

Posted by David Yahalom under DB2 LUW

I knew this day was coming and I was just too afraid to admit it. After years of experiencing all kinds of strange Oracle problems (ORA-600 this, Ora-600 that) I thought moving on to a simpler architecture (DB2) would prove more weirdness-resistant. Actually, I knew all long this is not true but I liked the false sense of hope it gave me. -sigh- Anyway, I’m giving DB2 an official “pain in the ass” medal for the problems it caused me today. Oracle earned its fair share of these medals in the past too, so DB2 should feel just at home.

Today, one of our developers came to me saying that a DML operating she wrote is “hanging” and the table on which this operation was issued was non-responsive. Just as she walked into my room the “hang” released and we were able to query the problematic table again. We wrote some quick queries to verify all is well. Ten seconds after that I received a phone call from another developer saying that he is received a “SQL0290N Table space access is not allowed. SQLSTATE=55039″.

Okay, so something went bad with the Quiesce process in DB2?

Relaxed, I thought the problem is probably caused because the table is in load pending state or a quiesce state. So I quickly did a list tablespaces command and saw the tablespace #3 is indeed in quiesced shared mode.

The handbook procedure in such cases is to do a quiesce reset on any table from the problematic tablespace. Well, I tried that and guess what? Nada. While the command itself executed okay, the result was more of the same. Table space access is not allowed errors.

This seems like a phantom quiesce mode in DB2 (a wonderful architectural “feature” DB2 probably learned from Satan himself, nonetheless). In such a case, there are a few more trick to try like to try and reacquire the quiesce by the same user that caused it and then to reset it or to try a load terminate operation. Neither of these worked. Tablespace is still in quiesce mode. Maybe putting the tablespace in backup pending mode should do the trick? No such luck. You simply can’t because the table is already Quiesced shared.

The solution that worked for me, after doing a lot of guesswork (and I’ll put money that it will probably work for you in such situations as well) is:

1) Do a list tablespaces show detail command to find out the tablespace that is in QUIESCE SHARED mode.

Tablespace ID = 3
Name = PROD_TBS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0001
Detailed explanation:
Quiesced: SHARE
Total pages = 2723840
Useable pages = 2723808
Used pages = 2181056
Free pages = 542752
High water mark (pages) = 2708288
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Number of quiescers = 1
Quiescer 1:
Tablespace ID = 3 <<--- This is the tablespace id that is in Quiece mode
Object ID = 12 <<--- This is the table inside the tablespace that is causing the QUIESCED SHARED

2) Run this query to find out which object is loking the tablespace in QUIECE SHARED mode:

SELECT SUBSTR(TAB1.DEFINER,1,15) AS DEFINER, SUBSTR(TAB2.TABSCHEMA,1,15) AS TABSCHEMA,SUBSTR(TAB1.TABNAME,1,15) AS TABNAME, SUBSTR(TAB2.TBSPACE,1,15) AS TBSPACE_NAME FROM SYSCAT.TABLES TAB1, SYSCAT.TABLESPACES TAB2 WHERE TABLEID=OBJECT_ID AND TAB2.TBSPACEID=TABLESPACE_ID AND TAB1.TBSPACEID=TAB2.TBSPACEID;

This is the result you will receive:

DEFINER TABSCHEMA TABNAME TBSPACE_NAME
--------------- --------------- --------------- ---------------
DB2INST9 TABSCM TABLE1 PROD_TBS1

There we have it. The problematic table name.

3) Now, lets try what we tried before, but this time, instead of just using a “quiesce table” command on a random table from the problematic tablespace, lets use it on the same table that is causing the problems.

db2 quiesce tablespaces for table TABSCM.TABLE1 share

Now we have re-quiesced the table back with the same locking level as it was originally quiesced with (shared).

3) And in order to get rid of the annoying quiesce do a:

db2 quiesce tablespaces for table TABSCM.TABLE1 reset

After this all is back to normal.

The reason I’m annoyed is because there is nothing online that suggests doing this. There are bits and bytes of info here and there but nothing concrete. Even IBMs solution for this is to simply to Re-Quiesce the tablespace (and not the table itself):

To remove a phantom quiesce:

1. 1 Connect to the database with the same user ID used when the quiesce mode was set.
2. Use the LIST TABLESPACES command to determine which table space is quiesced.
3. Re-quiesce the table space using the current quiesce

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002003.htm

Monitor locks in DB2

Posted by David Yahalom under DB2 LUW

Since locking in DB2 can be a real problem if not managed correctly, it is very important to keep a close eye on row and table locks.

I know of two easy ways to monitor locks.

The easiest one is simply to execute the following query from the DB2 CLI:

SELECT SUBSTR(appl_name, 1,15) as APPLICATION_WAITING,
SMALLINT(agent_id) as USER_WAITING_ID,
SUBSTR(authid,1,15) AS USER_WAITING,
SMALLINT(agent_id_holding_lk) AS USER_HOLDING_ID,
lock_mode AS HOLDING,
lock_object_type AS TYPE,
lock_mode_requested AS REQUESTED_LOCK_MODE
FROM SYSIBMADM.LOCKWAITS

Then use this to drill down to a specific lock for more details:

SELECT appl_status,
SUBSTR(PRIMARY_AUTH_ID,1,10) AS USER_HOLDING_LOCK,
SUBSTR(appl_name, 1,15) AS APP_HOLDING_LOCK,
SUBSTR(client_nname,1,20) AS CLIENT_HOLDING_LOCK
FROM SYSIBMADM.SNAPAPPL_INFO
WHERE AGENT_ID = <>>

Another way to monitor locks which will result in more detail displayed is:

1) Allow DB2 to monitor locks in the database:

db2 update monitor switches using LOCK on

2) Run the following command which will output the monitoring statistics to a text file of your choice:

db2 get snapshot for locks on database_name > /tmp/locks.txt

3) Keep in mind that there is a certain amount of overhead involved while using the monitor locks option, this is why I always turn the locking monitor off when it is not needed.

db2 reset monitor all

IBM migration toolkit

Posted by David Yahalom under DB2 LUW

I want to recommend a great free tool from IBM called the “IBM migration toolkit” (now avail. in V2 beta).

This GUI tool allows you to migrate data from Oracle, SQL and a varaity of other databases to IBM DB2 (both LUW and Z/OS). I found this very useful with my DB2 deployment and if you are working in a place where you need to transfer data from Oracle/SQL server to DB2, try this tool fist before going to higher-end (pricey) solutions as it might fit your needs.

“The IBM Migration Toolkit is an easy-to-use tool that allows you to migrate your data from a wide variety of source databases to either DB2 or Informix Dynamic Server, regardless of platform.”

Great Oracle RLS article

Posted by David Yahalom under Security, Oracle

Yesterday I stumbled across a great article about Oracle Row Level Security (RLS) with a great and detailed beginners tutorial.

RLS is a great way to implement application security where many users must connect to the same data but only see different parts of the data based on their role or position in the organization.

In many cases RLS is easier to implement and maintain compared to using views, tirggers or using different schema for data access as business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code.

Another great benefit of RLS is tighter security than what views or triggers provide because RLS can prevent a user from circumventing the security policy by accessing the database directly and query the tables underlying the views.

Oracle Row Level Security: Part 1

Is my DB2 optimizer a tad drunk?

Posted by David Yahalom under DB2 LUW

Today I have noticed a strange phenomena in one of our DB2 databases. When doing a low zero-selectivity query (that is, query without a WHERE clause), the DB2 optimizer preferred to use an index scan rather than do a table scan.

That seemed a bit strange so I tried running the same query twice. Once without any indexes on the underlying table (to force a table scan) and then again with (to allow the optimizer use an index).

The results really surprised me.

According to the optimizer, the table scan query will perform slower than the index scan query.

I know that with Oracle systems, the rule of the thumb is that using a full table scan would perform faster than an index scan when the total number of rows retrieved from a table is between 1% and 15% of table data.

It is very logical. If the database has to read the entire table wouldn’t it be faster just to read the blocks directly from the table container rather than doing an INDEX READ + ROWID lookup for the entire table data?

The way I see it:

SELECT * FROM TABLE

Performing a TABLE SCAN to get the data: X I/O blocks read from the table.
Performing an INDEX SCAN to get the data: X I/O blocks read from the table + Y I/O blocks read from the index.

Or am I wrong?

Any of you can shed some light on why the DB2 optimizer reports the table scan as slower than an index scan in this case?

Here is the real world example of what I mean:

SELECT COUNT *
FROM TABLE1;

Using an INDEX:

Access Plan:
-----------
Total Cost: 72284.4
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
FETCH
( 2)
72284.4
26250.8
/---+---
95047 95047
RIDSCN TABLE: USER
( 3) TABLE1
556.668
275.886
|
95047
SORT
( 4)
556.667
275.886
|
95047
IXSCAN
( 5)
427.788
275.886
|
95047
INDEX: USER
TABLE1_IDX1

Without an INDEX:

Access Plan:
-----------
Total Cost: 73526
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
TBSCAN
( 2)
73526
27725
|
95047
TABLE: USER
TABLE1

Leverage your Oracle 10g skills to learn DB2 9.1 for Linux, UNIX and Windows

Posted by David Yahalom under DB2 LUW, Oracle

Leverage your Oracle10g skills to learn DB2 9.1 for Linux, UNIX and Windows.

This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2.

Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2.

I do have some comments I would like to add:

1) IBM says:

“DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine.”

This compares best to Oracle tnsnames.ora file. I don’t understand why IBM didn’t draw this comparison.

2) IBM says:

“Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the database has been created, and a user needs to be assigned to these table spaces before they can be used.”

While I get IBMs point (that you have to create tablespaces manually after the create database command) This is not technically true. Frist, when you issue a create database command in Oracle you explicitly specify the creation of a temp tablesapce. Second, after the database creation all other tablespaces you have created can be used without being “assigned to users” simply by using them in the storage clause of the object creation statement.

3) IBM Says:

“As indicated earlier, Oracle’s data buffer concept is equivalent to DB2’s bufferpool; however, DB2 allows for multiple bufferpools to exist. There is no predefined number of bufferpools that you can create, and they can have any name.”

Again, not entirely true. While less flexible, Oracle 9i/10g allows for multiple db buffers with different block size. The limitation is one buffer per one block size while in DB2 it is one buffer per tablespace.

Using DBMS_FLASHBACK in Oracle to set your entire session back in time

Posted by David Yahalom under Oracle

Last week I’ve talked to you about using Flashback query, Flashback table and FlashBack database in Oracle 10g to help you improve productivity and perhaps save you time in doing complete or partial database recovery.

Today I want to talk to you about a less known feature of Flashback, the DBMS_FLASHBACK package available since Oracle 9iR1.

The DBMS_FLASHBACK package provides almost the same functionality as FlashBack query but with one big difference. Instead of being able to “go back in time” one query at a time, the DBMS_FLASHBACK package acts as a time machine, or a time tunnel, allowing you to “turn back the clock” and carry out normal SQL queries or PL/SQL code, without change, but as if you were running them from a time in the past.

Flashback query in Oracle 9iR2 (using the as of clause in your SQL statement) is actually based and internally using the capabilities of DBMS_FLASHBACK package.

To use DBMS_FLASHBACK you simply call DBMS_FLASHBACK.ENABLE_AT_TIME (and specify a specific timestamp in the database life cycle) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (and specify an SCN number in the database life cycle) to “set back the clock” of your current session as a whole.

Following that you can run you queries and PL/SQL code normally without any modifications but instead of getting the results you normally would (which represent to current state of the data in your database) the results you receive represent the state of the database as it was at the timestamp you provided with the DBMS_FLASH.ENABLE_AT_TIME command.

To return to the “present” simply cancel the “time tunnel” using the DFBMS_FLASHBACK.DISABLE command.

Let’s write an example that will show us just how easy it is to use the DBMS_FLASHBACK package.

SQL> conn admin/*********@daviddb
Connected.
SQL> create table employees (ename char(20), sal number(6));

Table created.

SQL> insert into employees values ('Linus Torvalds', 5000);

1 row created.

SQL> insert into employees values ('Tom Kyte', 4000);

1 row created.

SQL> insert into employees values ('Steve Ballmer', 9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000
This is our base table of employees. At this time the table holds "good" data.

SQL> var sysscn number
SQL> EXEC :sysscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

PL/SQL procedure successfully completed.

I’m creating a sysscn variable that I will use to store the current SCN of the system. In this example, using a system SCN number as the reference to which we will later Flashback to is easier to demonstrate than using a timestamp.

SQL> update employees set sal = 9999;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * From employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             9999
Tom Kyte                   9999
Steve Ballmer              9999

An employee from the HR department has accidentally updated all employee salaries in the company to 9999. When he notices this mistake he notifies the DBA immediately that something is wrong with employee salaries. Normally, this would require us to recover that table from a backup. We can avoid this by using the DBMS_FLASHBACK package.

SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:sysscn);

PL/SQL procedure successfully completed.

SQL> select * from employees;

ENAME                       SAL
-------------------- ----------
Linus Torvalds             5000
Tom Kyte                   4000
Steve Ballmer              9000

SQL> exec dbms_flashback.disable

PL/SQL procedure successfully completed.

Remember that while you are in a “time tunnel” you cannot use DML or DDL statements and that you cannot open a “time tunnel” from within a “time tunnel”. That is, you must use

Also keep in mind that you must have the execute privilege on the DBMS_FLASHBACK package in order to use this wonderful feature.

Using DBMS_FLASHBACK will sometimes make your life easier than using a regular Flashback query. Because it sets your entire session “back in time” you can run multiple database-wide queries or execute PL/SQL code freely from your session without restriction or the need to re-write existing code with the as of clause.

Oracle 10g FlashBack Database

Posted by David Yahalom under Oracle

A few days ago I ran into a problem with one of our development DB2 servers. A programmer in my team accidentally activated a Java Hibernate class with an option that truncates tables before loading them into memory. This resulted in most of our tables becoming empty (and I’m taking about something like 300 tables here).

I had to do a tape restore in order for us to get our database running again. This wasn’t hard, but was a hassle nonetheless since I had to mount the tape, restore the image with the backup software and then do a restore and rollforward of the database. This is a manual process that must be done by the DBA.

During my late night restore session I wished it was an Oracle server I was working on. I remember a few months ago I had a smiler situation with an Oracle 10g server we had but that time I was able to use the “Flashback database” feature to restore my db to a working state in less time with allot fewer hassle.

Not many DBAs utilize Oracle’s 10g new “Flashback Database” feature which is a shame. Maybe its because we were all brought up on Oracle during simpler days when Flashbacking an entire database was only a dream.

Since Oracle 9i we have a “flashback query” option which allows us to restore the state of tables to an X minutes ago timestamp. This ability is based on the UNDO Tablespace data retention mechanism. When you modify a table, given a large enough UNDO tablespace, Oracle keeps copies of the old table blocks in its UNDO tablespace.

Since Oracle 10g we also have something new called “Flashback Database”. This allows us to rollback the database, either in full or in parts, back to a specific point in time. This is most useful when you run into a problem and want a quick way to restore your database to a previous “snapshot” without the hassle and time of doing a tape restore.

This feature utilizes 10Gs new “Flashback Area” which stores changes to the database in files dedicated solely for this purpose.

Doing a database flashback is actually simpler than most DBAs believe.

Let me give you a quick example:

1) First, lets put the database in Flashback mode:

SQL> shutdown immediate;

Database closed.
Database dismounted.
Oracle Instance shut down.

SQL> startup mount;

ORACLE instance started.

SQL> alter database archivelog;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=9600;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1073741824;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/storage/flashback/';

System altered.

SQL> alter database open;

Database altered.

Note that this will only allow you to flashback your database to a point-in-time which is from now and forward.

To test flashbacking the db, let’s create a small test table:

SQL> create table hello_world (emp_id number(4), emp_name varchar2(30));

Table created.

SQL> insert into hello_world values (1, 'Bill Gates');

1 row created.

SQL> insert into hello_world values (2, 'Steve Jobs');

1 row created.

SQL> insert into hello_world values (3, 'Chris Eaton');

1 row created.

SQL> commit;

Commit complete;

Now lets get to the interesting stuff. Lets DROP THE TABLE and restore it from the flashback area:

SQL> drop table hello_world;

Table dropped.

SQL> flashback table "HELLO_WORLD" to before drop;

Flashback complete;

Pretty cool, right?
But this isn’t everything… Lets try to push the “Flashback Database” feature to its limits and try restoring the ENTIRE DATABASE to an earlier point in time.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount exclusive;

ORACLE instance started.

Database mounted.

SQL> FLASHBACK DATABASE to timestamp to_timestamp('01-01-2007 10:00', 'DD-MM-YYYY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Great, simple and free db2 monitor software

Posted by David Yahalom under DB2 LUW

Today I have stumbled upon this great little free tool called “DB2 Monitor”.

This is a fast windows GUI application for performance monitoring. Shows active connections with lock information, SQLs in db cache, database snapshots, database hit ratios, database objects information, list history. Can help resolve locking problems and identify worst performing queries.

Its fast, has a very simple user interface and just works every time! (something I can’t really say about most of IBMs JAVA monitoring applications for DB2).

Recommended.

You can get DB2 Monitor from here.

Update all database statistics with a single command

Posted by David Yahalom under DB2 LUW

As with all RDBMS implementation, DB2 UDB uses the statistics information in the catalog table to derive the best execution plan. We, as DBAs, should regularly run the RUNSTATS command to keep our database statistics updated for optimal query performance.

RUNSTATS ON TABLE SCHEMA_NAME.TABLE_NAME

Runstats works as advertised but what happen when you need to quickly update statistics for a large group of tables at once? Allot of DBAs (including myself) would write a script that creates another script, a “RUNSTATS ON TABLE_NAME” script, with the table name concated. This will, of course, work, but there is a simpler and better way to do this in DB2 using the reorgchk command.

To update stats for the entire database use:

REORGCHK UPDATE STATISTICS on TABLE ALL

To update all the tables of a particular schema use:

REORGCHK UPDATE STATISTICS on SCHEMA schema_name

You should note, however, that the RUNSTATS command generated by the REORGCHK UPDATES STATISTICS command collects statistic on the table only without distribution. To have distributed statistics on your tables you should use the RUNSTATS command instead.