DB2 improvement suggestion

[dba2adm]

I have a view which references two different tables at different times
say t_1 on certain days and t_2 on certain other days.

In oracle I have many many ways of doing this without having much
impact.

In DB2, if I define the view on t_1 and if I want to switch to t_2, I
have to drop and recreate the view and grant the privileges again. I
can not even rename the underlying table. If it allows atleast the
renaming of the tables, I could rename t_1 to t_x, t_2 to t_1 and then
t_x to t_1. So, I have to define the view only once (view v as select
* from t_1).

Also, I have other views with UNION ALL of three or more tables.

ex.
view v as
select * from t_1
union all
select * from t_2

The next day, after loading t_3, I will have the view pointing table
t_3 as in

view v as
select * from t_2
union all
select * from t_3

Questions?

Why does DB2 chose to make the views inoperable if any change is done
to the underlying table, rather than making them invalid and compile
when referred.

We recently switched from Oracle to DB2. Since DB2 UDB does not have
the range partition, we had to create N tables (1 table for each
range) and create a UNION ALL view (What a mess?). Then when rollig
over the tables, everytime drop and recreate the views, grant
priileges etc. This is so tedious. Admin tasks which takes less than a
few seconds in Oracle takes minutes and hours in DB2. (ex, rename/drop
column, move table/index to any tablespace etc)

Would Stinger or any upcoming db2, provide Range Partitioning? Would
they come up with "create or replace view/alias"? Also, would they
allow to rename a table referenced by the view and make the view
invalid rather than inoperable? (If allowed to rename the unerlying
table, then there is no need for the recreate view. )

Thanks,
Da

[Serge Rielau]

Duly noted. Teh requirement to relax object dependencies is well known
and will be addressed in a future release.
There are no significant changes on this front in Stinger.
Stinger does have a GUI (and procedure) support to alter table (add,
drop, alter column, ...) including dealing with dependent objects.

Here is a trick that you can use for rolling windows which should give
you familiar behaviour. Maybe it can be adapted for your other usages.

CREATE TABLE sales_t1(isbn BIGINT NOT NULL,
date INTEGER NOT NULL,
amount INTEGER NOT NULL);
CREATE TABLE sales_t2 LIKE sales_t1;

ALTER TABLE sales_t1 ADD CONSTRAINT H1_2003
CHECK (date BETWEEN 20030101 AND 20030630);

ALTER TABLE sales_t2 ADD CONSTRAINT H2_2003
CHECK (date BETWEEN 20030701 AND 20031231);

CREATE VIEW sales AS
SELECT * FROM sales_t1
UNION ALL
SELECT * FROM sales_t2;
Rolling window:
(ensure autocommit is OFF)
ALTER TABLE sales_t1
ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
ALTER TABLE sales_t1
DROP CONSTRAINT H1_2003
ADD CONSTRAINT H1_2004
CHECK (date BETWEEN ‘2004-01-01’
AND ‘2004-06-31’);
COMMIT;

As you see there is no schema revalidation needed.
The view text does not change.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Vernon]

<db*****@yahoo.com> wrote in message
news:90**************************@posting.google.c om...

I have a view which references two different tables at different times
say t_1 on certain days and t_2 on certain other days.

In oracle I have many many ways of doing this without having much
impact.

[snip]

You can write scripts to help ease the admin of course, but I guess the ease
of use on this one could be better.

E.g. this ksh script recreates all invalid views.

function recreate_views {
for v in $(db2 -x "SELECT RTRIM(VIEWSCHEMA) || '.' || VIEWNAME FROM
SYSCAT.VIEWS WHERE VALID <> 'Y'");
do
db2 -v "$(db2 -x "SELECT TEXT FROM SYSCAT.VIEWS WHERE RTRIM(VIEWSCHEMA) ||
'.' || VIEWNAME = '$v'")"
done
}

Can't remember if recreating vs. drop/create a view keeps it's prior
privileges. Have a feeling they get reset, so will still have to rerun your
grants. Again a script can help to recreate GRANTs, this time I just give
you the SQL.

-- Recreate GRANTS/REVOKES --
select g.grant_revoke
|| g.priv || ' ON "' || RTRIM(tabschema) || '"."' || tabname || '" ' ||
from_to ||' ' || GRANTEE
|| CASE WHEN g.auth = 'G' THEN ' WITH GRANT OPTION ' ELSE '' END || ' ;'
from syscat.tabauth
, table(values
('Y', 'ALTER', 'GRANT ', 'TO')
, ('Y', 'CONTROL', 'GRANT ', 'TO')
, ('Y', 'DELETE', 'GRANT ', 'TO')
, ('Y', 'INDEX', 'GRANT ', 'TO')
, ('Y', 'INSERT', 'GRANT ', 'TO')
, ('Y', 'SELECT', 'GRANT ', 'TO')
, ('Y', 'REFERENCES', 'GRANT ', 'TO')
, ('Y', 'UPDATE', 'GRANT ', 'TO')
, ('G', 'ALTER', 'GRANT ', 'TO')
, ('G', 'CONTROL', 'GRANT ', 'TO')
, ('G', 'DELETE', 'GRANT ', 'TO')
, ('G', 'INDEX', 'GRANT ', 'TO')
, ('G', 'INSERT', 'GRANT ', 'TO')
, ('G', 'SELECT', 'GRANT ', 'TO')
, ('G', 'REFERENCES', 'GRANT ', 'TO')
, ('G', 'UPDATE', 'GRANT ', 'TO')
, ('N', 'ALTER', 'REVOKE ', 'FROM')
, ('N', 'CONTROL', 'REVOKE ', 'FROM')
, ('N', 'DELETE', 'REVOKE ', 'FROM')
, ('N', 'INDEX', 'REVOKE ', 'FROM')
, ('N', 'INSERT', 'REVOKE ', 'FROM')
, ('N', 'SELECT', 'REVOKE ', 'FROM')
, ('N', 'REFERENCES', 'REVOKE ', 'FROM')
, ('N', 'UPDATE', 'REVOKE ', 'FROM')
) AS g(auth, priv, grant_revoke, from_to)

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Serge Rielau]

Paul, you need to through in a couple of order-bys to match the order of
creation (view-on-view dependnecies).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[dba2adm]

Thanks for all you suggestions.

Serge, the recommendation wouldn't work in our case. We don't want to
truncate the table and load data into the table. Rather, we want to
load another table/partition and if that is successful then truncate
the other partition. Business requirement is, we can not aford to lose
the old table/partition before loading the new one.

Thanks.

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<cb**********@hanover.torolab.ibm.com>...

Paul, you need to through in a couple of order-bys to match the order of
creation (view-on-view dependnecies).

Cheers
Serge

[Paul Vernon]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:cb**********@hanover.torolab.ibm.com...

Paul, you need to through in a couple of order-bys to match the order of
creation (view-on-view dependnecies).

Cheers Serge.

function recreate_views {
for v in $(db2 -x "SELECT RTRIM(VIEWSCHEMA) || '.' || VIEWNAME FROM
SYSCAT.VIEWS V, SYSCAT.TABLES T\
WHERE T.TABSCHEMA = V.VIEWSCHEMA AND T.TABNAME = V.VIEWNAME AND V.VALID
<> 'Y' ORDER BY CREATE_TIME");
do
db2 -v "$(db2 -x "SELECT TEXT FROM SYSCAT.VIEWS WHERE RTRIM(VIEWSCHEMA) ||
'.' || VIEWNAME = '$v'")"
done
}
Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Ian]

db*****@yahoo.com wrote:

Thanks for all you suggestions.

Serge, the recommendation wouldn't work in our case. We don't want to
truncate the table and load data into the table. Rather, we want to
load another table/partition and if that is successful then truncate
the other partition. Business requirement is, we can not aford to lose
the old table/partition before loading the new one.

I have run across this in many environments. Usually, there are N+1
tables, where N is the number of months of data you need (2, in your
case).

The extra table, which is generally empty, is used to load the new
data. Once the new data is loaded, the oldest table is truncated.

With the check constraints as Serge recommended, this works quite
well.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[--CELKO--]

>>I have a view which references two different tables at different
times
say t_1 on certain days and t_2 on certain other days. <<

This hits me as a very bad programming practice. Remember basic
Software Engineering? It lacks cohesion. On Tuesday, it is apples;
on Wednesdays, it is oranges; and on the weekends it is squid.

In DB2, if I define the view on t_1 and if I want to switch to t_2, I have to drop and recreate the view and grant the privileges again.
<<

Gee, just like it was something totally new! Wait, it is! A view is a
table and should be treated as such. You call it partitions and I
call it attribute splitting (one table for each value of an
attribute).
We recently switched from Oracle to DB2. Since DB2 UDB does not

have the range partition, we had to create N tables (1 table for each
range) and create a UNION ALL view (What a mess?). <<

Why did you create (n) tables instead of modeling it properly as one
big table? Oracle has some physical/logical problems. If table size
is a serious problem, then perhaps a VLDB like Teradata with a lot of
parallelism might be a better choice.

One way of getting the VIEWS to change might to have CASE expressions
that use the CURRENT_TIMESTAMP to look at the clock and change the
VIEW on the fly. Another way is to have a JOIN to a small table that
you change to drive changes to the VIEW. Now just mes with the small
table.

CREATE VIEW Foobar (a, b, c, ..)
AS
SELECT a, b, c, ..
FROM Foo, Parms
WHERE Foo.something BETWEEN Parms.start AND Parms.finish
AND ...;

[Mark A]

> We recently switched from Oracle to DB2. Since DB2 UDB does not

have the range partition, we had to create N tables (1 table for each
range) and create a UNION ALL view (What a mess?). <<

I don't think it is a mess. In fact there are many advantages to this, such
as restoring old data to a truncated or dropped table that contains
historical data without impacting any of the current data.

[Buck Nuggets]

jc*******@earthlink.net (--CELKO--) wrote in message news:<18**************************@posting.google. com>...

We recently switched from Oracle to DB2. Since DB2 UDB does not

have the range partition, we had to create N tables (1 table for each
range) and create a UNION ALL view (What a mess?). <<

Why did you create (n) tables instead of modeling it properly as one
big table? Oracle has some physical/logical problems. If table size
is a serious problem, then perhaps a VLDB like Teradata with a lot of
parallelism might be a better choice.

Don't sweat the physical stuff. Logically it is still a table, it's
just that physically it may be stored in 2,3,4,or a dozen different
tables. This isn't a big deal - Oracle does this very well, and DB2
now does it also. Just not quite as well as Oracle (yet).

Spending a million dollars on teradata licensing, hardware, and labor
just to maintain relational purity is a bit overboard...
buck

[dba2adm]

Thanks Serge, Ian, Paul and others.

I am not sure I get this correct.

1, I create view on top of all the three tables (sales_t1, sales_t2
and sales_t3)
2, Say sales_t1 ad sales_t2 have data. Now, I load sales_t3 (if
succesfull, I will truncate sales_t1)

Questions:

1, What is the purpose of check constaint on these tables? I am
loading only the particular range of data in these tables.
2, What will happen to the view, when I am loading the data on the
last table. Would clients be able to access the view during this time?
If so, wouldn't they try to access the newly added data, as it is part
of the view

If possible could you give me an example with 2+1 tables?

And Serge, sorry to say this. Alter table drop or rename column of
Stinger is not at all an improvement. It seems to me DB2 is going
backwards. Why do they have to recreate the entire object and load the
data again just to change the name of the column? When I looked at the
initial beta release, it was doing a whole lot of things to just to
rename a column. It seems, atleast from outside, rename just requires
updating the catalog tables and making the views/triggers invalid or
inoperable.

Thanks,
Dave.
"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<cb**********@gazette.almaden.ibm.com>...

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:cb**********@hanover.torolab.ibm.com...

Paul, you need to through in a couple of order-bys to match the order of
creation (view-on-view dependnecies).

Cheers Serge.

function recreate_views {
for v in $(db2 -x "SELECT RTRIM(VIEWSCHEMA) || '.' || VIEWNAME FROM
SYSCAT.VIEWS V, SYSCAT.TABLES T\
WHERE T.TABSCHEMA = V.VIEWSCHEMA AND T.TABNAME = V.VIEWNAME AND V.VALID
<> 'Y' ORDER BY CREATE_TIME");
do
db2 -v "$(db2 -x "SELECT TEXT FROM SYSCAT.VIEWS WHERE RTRIM(VIEWSCHEMA) ||
'.' || VIEWNAME = '$v'")"
done
}
Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Serge Rielau]

db*****@yahoo.com wrote:

Thanks Serge, Ian, Paul and others.

I am not sure I get this correct.

1, I create view on top of all the three tables (sales_t1, sales_t2
and sales_t3)
2, Say sales_t1 ad sales_t2 have data. Now, I load sales_t3 (if
succesfull, I will truncate sales_t1)

Questions:

1, What is the purpose of check constaint on these tables? I am
loading only the particular range of data in these tables. How would DB2 know if you don't tell it? The whole idea is that DB2
knows that this view is partitioned and hence DB2 can optimize.
2, What will happen to the view, when I am loading the data on the
last table. Would clients be able to access the view during this time?
If so, wouldn't they try to access the newly added data, as it is part
of the view. DB2 supports online load. The data will only become visible once the
load is complete and/or you have run SET INTEGRITY on the table.
If I remember corectly the table will be readable to DB2 and should look
empty during the process.
If possible could you give me an example with 2+1 tables? Actually I'm on vacation ;-)
And Serge, sorry to say this. Alter table drop or rename column of
Stinger is not at all an improvement. It seems to me DB2 is going
backwards. Why do they have to recreate the entire object and load the
data again just to change the name of the column? When I looked at the
initial beta release, it was doing a whole lot of things to just to
rename a column. It seems, atleast from outside, rename just requires
updating the catalog tables and making the views/triggers invalid or
inoperable.

The procedure/GUI driven part of altering a schema is one piece of the
puzzle. It's not the puzzle.
Rename column could indeed be done very simply as long as you simply
inoperate the views and triggers and functions and constraints. But
rename column isn't the important one. It's a pleasant side-effect of
doing the other work.

What the tool is trying to do is to provide a transactional approach to
changing the version of your table. If DB2 simply dropped dependent
objects you would have to find and recreate them yourself (the tooling
does it for you).
Now lets presume DB2 would do auto-revalidation instead of drop (an
option that may happen "in a future release"), that still wouldn't
measure up in my book, because the user still has the burden of tracking
all the objects that failed to revalidate. After all you don't want to
find a revalidation problem during normal operations.
So the "perfect" world to me is this:
In place, online table evolution
DB2 Stinger added generated column, default evolution, more to come
Auto revalidation
future....
Guided version management
The tool you speak of is the first step.
It tracks all dependent objects, and rehearses the change.
If the rehearsal fails it points to the problems and lets you fix up
the schema until the whole thing goes thorugh.
Then you can either execute or you store away the script for future
use.
It is this part that is the value add. Full table evolution will not
take away the need for this tool.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab