The DB2 Optimizer has the full use of all the non-uniform distribution statistics, and the various domain range values for any column statistics provided when no host variables are detected in a predicate, (WHERE COL5 > ‘X’). The purpose of a host variable is to make a transaction adaptable to a changing variable; this is most often used when a user is required to enter this value. (more…)
Archive for September, 2009
Use constants and literals if the values will not change in the next 3 years (for static queries)
Wednesday, September 30th, 2009The Many Uses of Coalesce in SQL Server
Monday, September 21st, 2009Problem
Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed. (more…)
How to create Custom Database Role and Manage its permissions
Wednesday, September 16th, 2009This tip comes from Namwar Rizvi
Custom database roles is the best way to manage object permission for the given database in a structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it. (more…)
Show currently executing SQL
Wednesday, September 16th, 2009
Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.
set pagesize 24 set newpage 1 set linesize 125 column sql_text format a100 column user_name format a12 select u.sid, substr(u.username,1,12) user_name, s.sql_text from v$sql s, v$session u where s.hash_value = u.sql_hash_value and sql_text not like ‘%from v$sql s, v$session u%’ order by u.sid;
Here is the output from this script. As we see, this is extremely useful because a runaway SQL statement could cause degradation of your whole system. If you identify a runaway query, you can issue the ALTER SYSTEM KILL SESSION ‘SID, SER#’; command to kill the query:
SQL> @cur_sql
5 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an
d t.bitmapped=0
6 select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, state, status, he
uristic_dflt, session_vector, reco_vector, 3600*24*(sysdate-reco_time), 3600*24*(sysda
te-nvl(heuristic_time,fail_time)), global_commit#, type# from pending_trans$ where session_vector
!= ’00000000′
7 BEGIN sys.dbms_ijob.remove(:job); END;
9 READER SELECT TO_CHAR(page_unique_id), page_seq_nbr, book_unique_id, visual_page_nbr,
page_text FROM page WHERE page_unique_id = TO_NUMBER(’2380′)
14 READER SELECT TO_CHAR(page_unique_id), page_seq_nbr, book_unique_id, visual_page_nbr,
page_text FROM page WHERE page_unique_id = TO_NUMBER(’9975′)
Five reasons NOT to use Linux
Wednesday, September 16th, 2009I love Linux. I use it on my servers, I use it on my desktops, and I use it on my entertainment center, but, Linux isn’t for everyone. Seriously. Here are my top five reasons why you shouldn’t move to Linux . . . (more…)
9/11 Tribute
Friday, September 11th, 2009Have we forgotten that we were attached by people that want to destroy freedom and everything we love and hold in this nation.
SQL Tip for Today (09/08/2009)
Tuesday, September 8th, 2009Tiffany asked for a SQL tip; Here ya’ go Tiff (This is a copy from some O’bscure Book), but I have used this method a lot lately at work. (more…)
Linux Audio Users Guide
Tuesday, September 8th, 2009Audio software on the Linux platform is becoming very advanced. Many of the coolest projects have been in development for close to 10 years and there are several installation and setup options for new users to choose from which smooth out the rough edges and ease you through the process of getting started with Linux Audio. (more…)
How to install 3D-Desktop using Beryl
Monday, September 7th, 2009Windows Vista provides two entirely new features to manage windows using Windows Flip and Windows Flip 3D. Linux is already using this technology. 3D-Desktop is an OpenGL program for switching virtual desktops in a seamless 3-dimensional manner on Linux. The current desktop is mapped into a full screen 3D environment where you may choose other screens. (more…)
A review of Linux Mint 7.0
Sunday, September 6th, 2009The new Linux Mint is one of the best distributions I have yet seen for anyone interested in making the crossover from Windows to Linux. Mint is based on Ubunto which is one of the most used distributions in the world. (more…)
Zenity Brings a Little GUI Goodness to Linux Shell Scripts
Saturday, September 5th, 2009One of the powers of the Linux command shell is the wealth of commands available to accomplish most any task. That amount of power can be intimidating to the less experienced users. Most typical computer users rarely open up a terminal window, or a DOS command box in Windows, unless they need to do some trouble shooting. (more…)
Full Circle
Friday, September 4th, 2009When I first went to college, my COBOL instructor was Assad Kahalahenie. Dr. Kahalahenie and I became close friends and he told me his vision of the future (the one that didn’t involve the good Dr. and The Apocalypse). He said that distributed computing and personal computing were the future, and that these mainframes were a thing of the past. (more…)