Prev: SQL1477N Table "<table-name>" cannot be accessed
Next: Too many open files error on db2diag.log
From: Dominik Czechowski on 22 Feb 2007 18:59
Hi everyone!

I have two tables, T1 and T2 defined as follows:

create table T1(c1 integer not null,
primary key (c1));
create table T2(c1 integer not null,
c2 varchar(100) not null,
primary key (c1, c2));

populated with example data:

insert into t1 values (1), (2), (3);
insert into t2 values
(1, 'opt 1'),
(1, 'opt 2'),
(1, 'opt 3'),
(2, 'opt 1');

i would like to execute query like this one:
select t1.c1, GetOptions(t1.c1) as options_list from t1 left join t2 on
t1.c1=t2.c1

and get following results:
c1 options_list
------ -------------------------------
1 opt 1, opt 2, opt 3
2 opt 1
3 -

What is the fastest way of implementing GetOptions() function: plaing
with XML, recursive queries, or maybe some external code in C or Java
instead of SQL? Or maybe it's better to implement some processing on the
client side?

Lets assume that table T1 will have about 50k-100k rows, and there will
be 0-20 values of column c2 in table T2 (for each c1 column).

Thanks in advance for any info.

Sincerely,
Dominik Czechowski
From: Serge Rielau on 23 Feb 2007 07:15
I have an example for string aggregation here:
http://www.iiug.org/waiug/present/Forum2006/Presentations/SQLonFire_1_Serge_Rielau.ppt

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
From: --CELKO-- on 24 Feb 2007 09:47
Why do you wish to destroy First Normal Form (1NF) with a
concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Yes, there are kludges, but would you prefer to be a good SQL
programmer instead?

From: Dominik Czechowski on 24 Feb 2007 13:41
Hi,

First of all my intention is not destroying or not 1NF, but achieving
the best possible performance with described data structures (in fact my
structure is a little more complicated). I just wonder if I can write a
UDF fast enough to handle such "reports" with many clients requesting
data even every second. In fact one part of my question was if
processing data on the client side wouldn't be a better option:

>What is the fastest way of implementing GetOptions() function: plaing
>with XML, recursive queries, or maybe some external code in C or Java
>instead of SQL? Or maybe it's better to implement some processing on
>the client side?

When you write an application for more than one environment and have to
write similar code in different programming languages, in my example in
Delphi and php you start to wonder if it is not better to move some code
to database level.

P.S. Some time ago I had a similar problem with hierarchical (tree) data
structures presented in DB2. It worked quite ok with recursive queries
(DB2 v7), however returning raw queries from table and processing it on
the client side was much faster (about 10 times faster). Now, with DB2
v9 I know I could do it using XML, but the question remains - will it
work faster?

P.S.S. I love your books Joe.

Regards
Dominik Czechowski

--CELKO-- pisze:
> Why do you wish to destroy First Normal Form (1NF) with a
> concatendated list structure? It is the foundation of RDBMS, after
> all.
>
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just
> SQL and RDBMS.
>
> Yes, there are kludges, but would you prefer to be a good SQL
> programmer instead?
>
 | 
Pages: 1
Prev: SQL1477N Table "<table-name>" cannot be accessed
Next: Too many open files error on db2diag.log