Db2 select statment

[73blazer]

Hello,
I'm looking for a SQL syntax to put a variable name into the from clause.
Specifically I have a colume in a table that is table name, and I want
to use that result in the from clause in a select subquery
ideally I'd like:

-----
select coid,coid_ref from physical.ext ext where coid_ref not in (select
coid from ext.coname)
-----

ext.coname being a column in table physical.ext and its value varies but
is always a table name (like physical.part_list)

Is there a way to that in db2?
I'm not sure I can't really remember, but I believe I've done this
oracle and it worked.

Thanks in advance for any help..

Ken

[Serge Rielau]

73blazer wrote:

Hello,
I'm looking for a SQL syntax to put a variable name into the from clause.
Specifically I have a colume in a table that is table name, and I want
to use that result in the from clause in a select subquery
ideally I'd like:

-----
select coid,coid_ref from physical.ext ext where coid_ref not in (select
coid from ext.coname)
-----

ext.coname being a column in table physical.ext and its value varies but
is always a table name (like physical.part_list)

Is there a way to that in db2?
I'm not sure I can't really remember, but I believe I've done this
oracle and it worked.

Thanks in advance for any help..

Ken

SQL is not LISP...
You will need to retrieve the table name and then use
PREPARE a dynamic cursor which is composed by glueing the tablename into
the SELECT using concat...

DECLARE tabname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SELECT tname INTO tabname
FROM ....;
SET stmttxt = 'SELECT .....FROM ' || tabname;
PREPARE stmt FROM stmtxt; -- double check syntax (!)
OPEN cur;
.....

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

[73blazer]

Serge Rielau wrote:

73blazer wrote:

Hello,
I'm looking for a SQL syntax to put a variable name into the from clause.
Specifically I have a colume in a table that is table name, and I want
to use that result in the from clause in a select subquery
ideally I'd like:

-----
select coid,coid_ref from physical.ext ext where coid_ref not in
(select coid from ext.coname)
-----

ext.coname being a column in table physical.ext and its value varies
but is always a table name (like physical.part_list)

Is there a way to that in db2?
I'm not sure I can't really remember, but I believe I've done this
oracle and it worked.

Thanks in advance for any help..

Ken

SQL is not LISP...
You will need to retrieve the table name and then use
PREPARE a dynamic cursor which is composed by glueing the tablename into
the SELECT using concat...

DECLARE tabname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SELECT tname INTO tabname
FROM ....;
SET stmttxt = 'SELECT .....FROM ' || tabname;
PREPARE stmt FROM stmtxt; -- double check syntax (!)
OPEN cur;
....

Cheers
Serge

Thanks for that. This does work. So at least I have a method and I'm
appreciative.
However it is not so fast. It seems essetially it has to run a select
for every entry in the one table. Is there any other possible way, with
JOIN or UNION perhaps? I have a P5 machine, 4-way 16gb ram, DB2 8.2, AIC
5.3ml01, there's ~153000 entries in that table and it takes about 18
minutes to run. Which is fine I guess, I only need to run this once a
day at night. But it seems it could be faster.
I did some LISP years ago, perhaps that's where I got the idea my query
would work!

[Serge Rielau]

73blazer wrote:

Serge Rielau wrote:

73blazer wrote:

Hello,
I'm looking for a SQL syntax to put a variable name into the from
clause.
Specifically I have a colume in a table that is table name, and I
want to use that result in the from clause in a select subquery
ideally I'd like:

-----
select coid,coid_ref from physical.ext ext where coid_ref not in
(select coid from ext.coname)
-----

ext.coname being a column in table physical.ext and its value varies
but is always a table name (like physical.part_list)

Is there a way to that in db2?
I'm not sure I can't really remember, but I believe I've done this
oracle and it worked.

Thanks in advance for any help..

Ken

SQL is not LISP...
You will need to retrieve the table name and then use
PREPARE a dynamic cursor which is composed by glueing the tablename
into the SELECT using concat...

DECLARE tabname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SELECT tname INTO tabname
FROM ....;
SET stmttxt = 'SELECT .....FROM ' || tabname;
PREPARE stmt FROM stmtxt; -- double check syntax (!)
OPEN cur;
....

Cheers
Serge

Thanks for that. This does work. So at least I have a method and I'm
appreciative.
However it is not so fast. It seems essetially it has to run a select
for every entry in the one table. Is there any other possible way, with
JOIN or UNION perhaps? I have a P5 machine, 4-way 16gb ram, DB2 8.2, AIC
5.3ml01, there's ~153000 entries in that table and it takes about 18
minutes to run. Which is fine I guess, I only need to run this once a
day at night. But it seems it could be faster.
I did some LISP years ago, perhaps that's where I got the idea my query
would work!

Well, you could batch them uop maybe a few dozen at a time and combine
the individual queries with a UNION ALL you construct. I won't dare say
whether that will actually be faster....
What is the information you are retrieving? Is this metadata to be used
in some profile, monitoring?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[73blazer]

Serge Rielau wrote:

73blazer wrote:

Serge Rielau wrote:

73blazer wrote:

Hello,
I'm looking for a SQL syntax to put a variable name into the from
clause.
Specifically I have a colume in a table that is table name, and I
want to use that result in the from clause in a select subquery
ideally I'd like:

-----
select coid,coid_ref from physical.ext ext where coid_ref not in
(select coid from ext.coname)
-----

ext.coname being a column in table physical.ext and its value varies
but is always a table name (like physical.part_list)

Is there a way to that in db2?
I'm not sure I can't really remember, but I believe I've done this
oracle and it worked.

Thanks in advance for any help..

Ken

SQL is not LISP...
You will need to retrieve the table name and then use
PREPARE a dynamic cursor which is composed by glueing the tablename
into the SELECT using concat...

DECLARE tabname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SELECT tname INTO tabname
FROM ....;
SET stmttxt = 'SELECT .....FROM ' || tabname;
PREPARE stmt FROM stmtxt; -- double check syntax (!)
OPEN cur;
....

Cheers
Serge

Thanks for that. This does work. So at least I have a method and I'm
appreciative.
However it is not so fast. It seems essetially it has to run a select
for every entry in the one table. Is there any other possible way,
with JOIN or UNION perhaps? I have a P5 machine, 4-way 16gb ram, DB2
8.2, AIC 5.3ml01, there's ~153000 entries in that table and it takes
about 18 minutes to run. Which is fine I guess, I only need to run
this once a day at night. But it seems it could be faster.
I did some LISP years ago, perhaps that's where I got the idea my
query would work!

Well, you could batch them uop maybe a few dozen at a time and combine
the individual queries with a UNION ALL you construct. I won't dare say
whether that will actually be faster....
What is the information you are retrieving? Is this metadata to be used
in some profile, monitoring?

Cheers
Serge

It's actually not a query, but a delete. I want to delete these entries.
The (somewhat poorly coded) application using this database leaves these
(what we call) dead links around when you delete things through the
application, the dead links then cause problems elsewhere in the same
application(great application). We submit bug to the application maker,
but they won't fix unless we can describe to them exactly how the dead
links get created, and we just can't reproduce on demand. All we know is
at the end of the day, there a few in there. (they KNOW it leaves dead
links, they are just hiding behind a technicality)

Regards,
Ken

[Serge Rielau]

73blazer wrote:

Serge Rielau wrote:

73blazer wrote:

Serge Rielau wrote:

73blazer wrote:

> Hello,
> I'm looking for a SQL syntax to put a variable name into the from
> clause.
> Specifically I have a colume in a table that is table name, and I
> want to use that result in the from clause in a select subquery
> ideally I'd like:
>
> -----
> select coid,coid_ref from physical.ext ext where coid_ref not in
> (select coid from ext.coname)
> -----
>
>
>
> ext.coname being a column in table physical.ext and its value
> varies but is always a table name (like physical.part_list)
>
> Is there a way to that in db2?
> I'm not sure I can't really remember, but I believe I've done this
> oracle and it worked.
>
> Thanks in advance for any help..
>
> Ken


SQL is not LISP...
You will need to retrieve the table name and then use
PREPARE a dynamic cursor which is composed by glueing the tablename
into the SELECT using concat...

DECLARE tabname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SELECT tname INTO tabname
FROM ....;
SET stmttxt = 'SELECT .....FROM ' || tabname;
PREPARE stmt FROM stmtxt; -- double check syntax (!)
OPEN cur;
....

Cheers
Serge
Thanks for that. This does work. So at least I have a method and I'm
appreciative.
However it is not so fast. It seems essetially it has to run a select
for every entry in the one table. Is there any other possible way,
with JOIN or UNION perhaps? I have a P5 machine, 4-way 16gb ram, DB2
8.2, AIC 5.3ml01, there's ~153000 entries in that table and it takes
about 18 minutes to run. Which is fine I guess, I only need to run
this once a day at night. But it seems it could be faster.
I did some LISP years ago, perhaps that's where I got the idea my
query would work!

Well, you could batch them uop maybe a few dozen at a time and combine
the individual queries with a UNION ALL you construct. I won't dare
say whether that will actually be faster....
What is the information you are retrieving? Is this metadata to be
used in some profile, monitoring?

Cheers
Serge

It's actually not a query, but a delete. I want to delete these entries.
The (somewhat poorly coded) application using this database leaves these
(what we call) dead links around when you delete things through the
application, the dead links then cause problems elsewhere in the same
application(great application). We submit bug to the application maker,
but they won't fix unless we can describe to them exactly how the dead
links get created, and we just can't reproduce on demand. All we know is
at the end of the day, there a few in there. (they KNOW it leaves dead
links, they are just hiding behind a technicality)

Regards,
Ken

In this case I would stick with what you have.
Throw in COMMIT between the individual DELETEs and let it plod along for
however long it takes (remember to use a cursor WITH HOLD to find the
table names).

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