db2look and dependencies?
Lennart Jonsson
didnt find it in any apar nor mentioned in this newsgroup, so I'm
curios if anyone else experienced it, and how they solved it?
The problem is that db2look generates a function before it generates
the table that it is dependent of. Example:
create db test
connect to test
create table A (id int not null primary key, name char(10) not null)
create function b (n char(10))
returns int
language sql
reads sql data
no external action
deterministic
return select max(id) from A where A.name = n
db2look -d test -e -td@ -o test.ddl
---------------------------------
-- DDL statements for User Defined Functions
---------------------------------
SET CURRENT SCHEMA = "JON ";
SET CURRENT PATH = "SYSIBM","SYSFUN","JON";
create function B (N char(10)) returns int language sql reads sql data
no
external action deterministic return select max(id) from A where
A.name
= n
;
------------------------------------------------
-- DDL Statements for table "JON "."A"
------------------------------------------------
CREATE TABLE "JON "."A" (
"ID" INTEGER NOT NULL ,
"NAME" CHAR(10) NOT NULL )
IN "USERSPACE1" ;
-- DDL Statements for primary key on Table "JON "."A"
ALTER TABLE "JON "."A"
ADD PRIMARY KEY
("ID");
--
Am I missing something or is there a workaround? I suppose I could
write a perlhack that builds a graph of dependencies, and then
shuffles around the ddl according to that (but I'm trying to avoid
this ;-)
Kind regards
/Lennart
Lennart Jonsson
Serge Rielau
db2look has been improved in V8 and it will be further improved in
Stinger. I'm not 100% sure that all objects are created in order of
create time in V8, Maybe soemone else knows oh hand.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Lennart Jonsson
> Hi Lennard,
>
> db2look has been improved in V8 and it will be further improved in
> Stinger. I'm not 100% sure that all objects are created in order of
> create time in V8, Maybe soemone else knows oh hand.
>
> Cheers
> Serge
Hi Serge, thanx for the answer. While I'm at it, heres another
question regarding db2look. I noticed that the -td flag doesnt make a
difference on the output. Instead (this is only guessing from my
side), db2look generates triggers with a comment after ";" within the
trigger body. Is this a temporary solution that will change in the
future, or is it safe to start using this in my own ddl?
thanx
/Lennart
Haider Rizvi
> The problem is that db2look generates a function before it generates
> the table that it is dependent of. Example:
<misc stuff deleted>
> Am I missing something or is there a workaround? I suppose I could
> write a perlhack that builds a graph of dependencies, and then
> shuffles around the ddl according to that (but I'm trying to avoid
> this ;-)
A workaround: run it twice. AFAIK there is nothing in db2look output
that will cause trouble if you run it twice. You can filter for expected
errors such as 'table already exists', etc.
Regards,
--
Haider
Serge Rielau
You should use -td which was introduced in V7.2 for all inline SQL PL,
but if you don't db2look tricks the CLP with the --
CLP looks for a ';' at the end of the line, not counting trailing
whitespace. So the -- disables the ';' as end on statement qualifier.
A change here would be deemed an incompatible change.
It would need to flagged in advance and likely there would be a
compatibility mode of some sorts
Lennart Jonsson
I have been thinking about that, but I'm not sure whether twice is
sufficient. Funcions and Triggers may depend upon functions (perhaps
even tables can be dependent[*] ?), so if worse comes to worse one
might have to run it n-1 times
[*] = I assume it is possible to do: ... generates always as myfunc(
... )
/Lennart
Lennart Jonsson
> Hah, I'm personally guilty of that one :-)
>
> You should use -td which was introduced in V7.2 for all inline SQL PL,
> but if you don't db2look tricks the CLP with the --
> CLP looks for a ';' at the end of the line, not counting trailing
> whitespace. So the -- disables the ';' as end on statement qualifier.
>
> A change here would be deemed an incompatible change.
> It would need to flagged in advance and likely there would be a
> compatibility mode of some sorts
>
> Cheers
> Serge
Thanx for the info Serge. Much appreciated
/Lennart