DB2 : everything about UDB universe

One of the junior DBAs asked me this question. I thought it was a good enough reason to blog.

I am not touching what primary key helps us achieve, but what's difference in executing just

create unique index x.y on table xx.yy allow reverse scans;

than

create unique index x.y on table xx.yy allow reverse scans;
alter table xx.yy add constraint -- and give the columns in the above defined unique index

unique index can have one row with null value in either of columns, while primary key disallows it :)

Just to link with a usenet group's thread reg. the colseq column in keycoluse catalog view. I always thought this was supposed to list the foreign key columns and it's relation with the parent columns. But it seems db2 has a bug w.r.t this view.

Here is a list of statements I ran.
DROP TABLE ARUN.TEST2;
DROP TABLE ARUN.TEST1;
create table arun.test1 (a char(1) not null, b char(1) not null);
create unique index arun.test1_idx2 on arun.test1(b,a) CLUSTER;
create unique index arun.test1_idx1 on arun.test1(a,b) ;

alter table arun.test1 add constraint pk_test1 primary key(a,b);

create table arun.test2(a2 char(1),b2 char(1));
create unique index arun.test2_idx1 on arun.test2(a2,b2) cluster;

alter table arun.test2 ADD constraint fk_test2 foreign key(b2,a2) references arun.test1;



--The below statement gives the relation between foreign key columns and their corresponding parents

SELECT
SUBSTR(R.CONSTNAME, 1, 18) AS KEYNAME,
SUBSTR(KF.COLNAME, 1, 18) AS COLNAME,
SUBSTR(KP.COLNAME, 1, 18) AS REFCOLNAME
FROM
SYSCAT.REFERENCES R
INNER JOIN SYSCAT.KEYCOLUSE KF
ON R.TABSCHEMA = KF.TABSCHEMA
AND R.TABNAME = KF.TABNAME
AND R.CONSTNAME = KF.CONSTNAME
INNER JOIN SYSCAT.KEYCOLUSE KP
ON R.REFTABSCHEMA = KP.TABSCHEMA
AND R.REFTABNAME = KP.TABNAME
AND R.REFKEYNAME = KP.CONSTNAME
WHERE
R.TABSCHEMA = 'ARUN' AND
R.TABNAME = 'TEST2'
AND KF.COLSEQ = KP.COLSEQ
ORDER BY
R.TABSCHEMA,
R.TABNAME,
R.CONSTNAME,
KF.COLSEQ
WITH UR;

I hoped for this output, which is what I wanted..

Keyname Colname Refcolname

'FK_TEST2 ' 'A2 ' 'A '
'FK_TEST2 ' 'B2 ' 'B '

Instead I was blessed with

Keyname Colname Refcolname

'FK_TEST2 ' 'B2 ' 'A '
'FK_TEST2 ' 'A2 ' 'B '

Link to the usenet group's thread..

Everybody loves the new flavor DB2 introduced to the delete command ,

delete from (select * from table [where ] [fetch first n rows only])

because they can limit the number of rows to be deleted in one shot. It has been of immense help since I frequently delete millions of rows and it alleviates the problem of lock memory and log file usage. I just put this statement in a loop and run that for a specific number of times (or till it gets 0 rows returned, with a little complex code). Now the only setback we have is, we cannot use a select statement that joins tables, and would be staring at SQL0150N error. Now I just set out to solve this and using the mighty 'exists' clause, which has saved me during more than one bad situation, was actually able to mimic join and still limit delete to a set of rows.

The original query was
select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3;

The following will not work because joins are not allowed in the 'delete from (' clause
delete from (select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3 fetch first n rows only)
This would throw SQL0150N error.

So I changed the above to
delete from
(
SELECT * FROM table1 T1 where exists
(select 1 from TABLE2 T2 where T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3)
);
Works wonderfully.

I loved this new option 'ENHANCED_MULTIPLE_DISTINCT' that they gave for the registry variable in ver 9 fp 2. I normally face with sqls having multiple distinct clauses, select count(distinct(x)) , sum (distinct(x)) from table1, and what happens here is that the optimizer tries to do the groupby twice, once for each aggregation function and then unions them at the end.
Because of the new option we can prevent the above so that both functions are executed against the data whilst loading it only once.
This has good improvement potential in single processor systems and will not be useful all the time.

Have you ever come across situations wherein you need to drop a table that has been defined with 'restrict on drop' clause and you can't access the table at all, possibly because you inserted rows with 'not logged initially' state and it crashed???
Do the following..

For each node, run the following accordingly.
1) Take db offline. Make sure db is offline by checking with "db2 list
active databases"

2) db2dart <dbname> /mt /serv 1 /oi <table id> /tsi <tablespaceid> /PW
IEOAHERU
-----Alas - IBM services are the only guys who can give the password in the PW argument!!!!

3) Connect to the db, run the following statement

db2 ALTER TABLE <tablename> DROP RESTRICT ON DROP

4) Then drop the table immediately.

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.

SQL0973N Not enough storage is available in the
"APPGROUP_SHARE_HEAP" heap to process the statement. SQLSTATE=57011 ???


For "application group shared heap size" , you need to look at three
parameters....


APPGROUP_MEM_SZ, GROUPHEAP_RATIO, and APP_CTL_HEAP_SZ.

The number of applications in one application group is calculated by:
APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ.

The application group sharedheap size is calculated by: APPGROUP_MEM_SZ
* GROUPHEAP_RATIO /100.

Understanding locking in DB2 Universal Database

An illustrated example

Level: Intermediate

I'm afraid. you are mistaken ... You can do runstats from local or remote as long as you are able to connect to the database ... But the reason you are unable to RUNSTATS from java is that - RUNSTATS is a command and not SQL ... http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0012547.htm If you are atleast on FP9 of Version 8, then you can use ADMIN_CMD stored procedure to do RUNSTATS.

Make your applications port easier.

Below is a post by Serge Releiu talking about DB2's sql avatar, and how we can
use them with caution to tackle porting worries..

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/

SQL PL can be categorized into two technologies.
One is "inline" SQL PL, the other, what I like to call "packaged" or
"compiled" SQL PL.
Procedures are compiled once into static SQL when they are created.

SQL Functions, Methods and Triggers as well as a standalone BEGIN ATOMIC
.... END statement (a dynamic compound) are not compiled into separate
objects. A trigger or SQL function get macro expanded into the
surrounding statement and compield within it's context.
This is very powerful technology, but also very tricky.
Consequently inline SQL PL has only a subset of the statements at its
disposal.
These are:
FOR loop (which is very close to a CURSOR)
WHILE
SET
ITERATE
CONTINUE
SIGNAL
GET DIAGNOSTICs
DECLARE variable
DECLARE condition
IF THEN ELSE

I know of only two things that inline SQL PL can do today that packaged
SQL PL cannot:
Multi column set (SET (a, b, c) = (....))
and SELECT and VALUES without INTO clauses

The former is on the todo list, the later is a historical feature.

Thanks to Serge Rielau, IBM Toronto labs..

This gives the step by step howto for implementing DPF in a SUSE enterprise v9

A colorful intro to db2 DPF

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

I am changing this blog into a full db2 blog that I am going to note down my everyday experiences and links that I wont want to miss.. The 1st of them is about packages, which is not an easily understandable concept in db2 atleast in UDB universe.

Packages : concepts,examples and common problems

Below is the blog of Dwaine Snow, the author of
Advanced db2 dba certification 704 ..book

Dwaine Snow's blog

And atlast this is Susan Visser's blog on IBM pubs in everything DB2 UDB.

Way of describing agents -Autonomous, rational ... cant.High-level - has some computing resources that are finite. It cant'- Brooks.Intentional systems - behavior can be predicted by the method of attributing beliefs, desire and rational.
Folk psychology- behaviour is explained using attitudes such as belief and wanting.
First order intentional systems have belief and desire..2nd- belief and desire and other intentional systems about belief and desire of them and others.
McCarthy - intentional stance is most useful when applied to systems whose structure is not fully known.Attitudes for representing agents - info attitudes and pro-attitudes Info- attitudes - Information, belief.Pro- intention, desire, commitment, oblication, choice..Related to each other.Referential opacity - the equivalence sets up context opacity.Intentional systems cant be described in classical 1st order logic since the truth equivalence does not preserve it's meaning.