#DB2 #performance #optimization #sql #sql/pl

Oracle : Foreign key and validation

Illustrating how the foreign key could be disabled and using the NOVALIDATE - the rows in the child table could be preserved even after enabling the foreign key. The newer rows will be checked for presence in the ‘parent’ table.

SQL> create table test1(a number(8,2) primary key)
  2  /
                                                                           
Table created.

SQL> create table test2(a number(8,2) primary key, constraint fkey1 foreign key(a) references test1(a))
  2  /

Table created.

SQL> insert into test1 values(1)
  2  /

1 row created.

SQL> c/1/2
  1* insert into test2 values(1)
SQL> /

1 row created.


SQL>insert into test1 values(2)
SQL> /

1 row created.

SQL> c/(2)/(3)
  1* insert into test1 values(3)
SQL> /

1 row created.

SQL> c/1/2
  1* insert into test2 values(3)
SQL> /

1 row created.

SQL> alter table test2 disable constraint fkey1
SQL> /

Table altered.

SQL> truncate table test1 
  2  /

Table truncated.

SQL> alter table test2 enable novalidate constraint fkey1
SQL> /

Table altered.

SQL> insert into test2 values(2)
  2  /
insert into test2 values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FKEY1) violated - parent key not found

Steve Jobs on Startups

daslee:

“I hate it when people call themselves ‘entrepreneurs’ when what they’re really trying to do is launch a startup and then sell or go public, so they can cash in and move on. They’re unwilling to do the work it takes to build a real company, which is the hardest work in business.”

NoSQL's Next Step Forward: DataStax Makes Cassandra Commercial →

The huge problem for online services is that traditional SQL database managers don’t scale up when database sizes approach “exascale” - the tremendous and fast-growing repositories needed by services like Facebook and Twitter. There’s nothing conceptually wrong with SQL, it’s just that the underlying RDBMS architecture does not perform well with these tremendous workloads.

Simpler database constructs can handle bigger workloads, as long as the work they do stays more along the lines of simple storage and retrieval and doesn’t get too analytical. Today, a new vendor named DataStax whose backers include Rackspace is launching a commercial rendition of an exascale database manager that marries an open source database manager project launched at Facebook with an open source distributed processing project started at Google.

Port number that db2 listens to

We can query the dbm cfg and grep the service name against the services file in one command.

grep `db2 get dbm cfg` |grep SVCENAME |awk -F”= ” ‘{print $2}’` /etc/services

Credit : Linkedin DB2 UDB DBA group

#Archive Secrets surrounding db2 locking behaviour

Great article :

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0501melnyk/

#ARCHIVE Magical subquery column

I was working on a simple procedure in DB2, and had to join two tables.

arun.test1 : key varchar(20), value varchar(35)
arun.test2 : id varchar(20)

select * from arun.test1;

‘Name’ ‘Arun’
‘Age’ 27
‘Name’ ‘Kumar’
‘Age’ 35
‘Height’ 176
‘Height’ 164
‘Sex’ ‘M’
‘Sex’ ‘M’

select * from arun.test2;

‘Name’
‘Age’

What my report wanted was the values of all Keys in test1 that matched Ids in test2. So I wrote this :

select * from arun.test1 where key in(select key from arun.test2);

‘Name’ ‘Arun’
‘Height’ 176
‘Sex’ ‘M’
‘Age’ 27
‘Height’ 164
‘Sex’ ‘M’
‘Name’ ‘Kumar’
‘Age’ 35


What happened here? I was wanting to see just the Names and Ages, instead, got everything. I was so hungover from the party the night before to grab the error that starred me eye to eye, so, taking the usual route of explaining the query, I noticed the above was rewritten as :

SELECT DISTINCT Q2.KEY AS “KEY”, Q2.VALUE AS “VALUE”
FROM ARUN.TEST2 AS Q1, ARUN.TEST1 AS Q2
WHERE Q2.KEY IS NOT NULL

Ok, dumb of me to select ‘key’ from test2, instead of ‘id’, but how did the engine not trap this but gave me a result ?
In a sub-query, the outer query (arun.test1) columns are available to the inner query, thus the same ‘key’ column was extracted by the inner query and all rows were selected, just like
‘where 1=1’.
To not do this in the future, I was advised to use aliases, and on using them,

select * from arun.test1 t1 where key in(select t2.key from arun.test2 t2);
[IBM][CLI Driver][DB2/NT64] SQL0206N “T2.KEY” is not valid in the context where it is used. SQLSTATE=42703


Now for the correct one :
select * from arun.test1 t1 where key in(select t2.id from arun.test2 t2);

‘Name’ ‘Arun’
‘Age’ 27
‘Name’ ‘Kumar’
‘Age’ 35

update : this was one of the top ten sql mistakes made by developers, happy to find that I am not alone…

#ARCHIVE Interesting table

 

create table test (a integer not null generated always as identity (start with 1 increment by 1))

I cannot insert data since a is defined with “generated always”, and there aint no other columns. This is a hole, just found this when I was taking prescription for everyday boredom.

insert into test3 values(default) works 

From description of DEFAULT in VALUES clause in the reference: 

DEFAULT 
Specifies that the default value is assigned to a column. The value that 
is inserted depends on how the column was defined, as follows: 
* If the column is a ROWID or identity column, the database manager will generate a new value. 

DEFAULT must be specified for a ROWID or an identity column that was defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified to indicate that any user-specified value will be ignored and a unique system-generated value will be inserted.

#Archive(ing) my blogger posts

I have started to move all the posts from my blogger.com home to this new one. I will tag it using #archive. Hope to help more DB2 administrators and developers out there. 

Automate : Applications using most active log space

Below is the sql that I found online, tweaked some and am using to find if an application was the cause of transaction log full error. Good for detective work. 

Automate : Partition rotation

The following is a shell script that I put together quickly. All it does is to automate the detach operation from a master table and attach it to an archival table. The qualifier for a partition to get detached is that it’s lowvalue(syscat.datapartitions) is start day of 42 months before the current date. 

Eg: current - ‘04/11/2011’ , partition qualified - LOWVALUE - ’2007-10-01’