473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

db2look and dependencies?

Hi, I'm using db2 udb linux v7.2 fixpak 9 and came across a problem. I
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
Nov 12 '05 #1
6 4736
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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...
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
Nov 12 '05 #3
le*****@kommunicera.umea.se (Lennart Jonsson) writes:
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
Nov 12 '05 #4
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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5
Haider Rizvi <ha****@nouce.ca.ibm.com> wrote in message news:<7z************@thinkhr.torolab.ibm.com>...
le*****@kommunicera.umea.se (Lennart Jonsson) writes:
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,


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
Nov 12 '05 #6
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...
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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dbtoo_dbtoo | last post by:
One of the databases has 50 views and when I do a db2look I only get schema for 40 of them. If I select from the sysviews, I can see all 50 (the text column contains schema for all 10 (missing)...
0
by: Lennart Jonsson | last post by:
Hi, I'm using db2 udb linux v7.2 fixpak 9 and came across a problem. I didnt find it in any apar nor mentioned in this newsgroup, so I'm curios if anyone else experienced it, and how they solved...
1
by: Lyn Duong | last post by:
Hi When I want to get the ddl for a table, i use db2look -d dbname -e -t tabname. Why does this produce all ddl for functions, federated objects and all other unwanted stuff. Is there a way...
1
by: db2group88 | last post by:
about db2look, if i want to extract the ddl from one schema then reproduce the database objects on different server with different schema, is it a way to do it? e.g. we provide the same ddl...
6
by: sriram | last post by:
Hi, I have been seing a weird problem with db2look tool in db2 8.2 on Windows 2000 platform. When i spool the DDL using db2look, it spools the DDL in the ascending order of database objects...
8
by: db2sysc | last post by:
All: I tried to run db2look on V8.2 DB for getting ALL the DLLS for the database. When I look into the output file, the SQL procedures code is not present. Instead I see an external entry...
2
by: shsandeep | last post by:
Hi, I executed the following db2look statement but got an 'Execute' privilege error. What privileges do I need to execute the 'db2look' utility? /home# db2look -d dbname -e -o sql.txt -- No...
1
by: Gregor Kovač | last post by:
Hi! On one machine I have DB2 8.2.4 and on one I have DB2 9.1. If I run db2look from the DB2 9.1 machine against a database that is on the DB2 8.2.4 I get: SQL5060N The configuration parameter...
2
by: Justin | last post by:
Is there a way to get the ddl for a subset of tables? In this case, get all the tables beginning with emp from the sample db? Example db2look -e -tw emp* -d sample
1
by: blaisestephen | last post by:
I have a dilema where I want to run DB2LOOK to keep copies of our table DDL's and procedures for our schema. the problem is that we do not even have select privilege for the SYSCAT schema, which...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shllpp 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.