0

i want to execute a dynmic select in DB where schema and table are defiend by a select statement.

I tried

SELECT * FROM SELECT Creator || '.' || Name FROM sysibm.systables where CREATOR = (SELECT "column" FROM schema.table where "column" = "value") and "column" = "value"

But it doesnt work.

I also tried

SELECT * (SELECT 'FROM' || ' ' || Creator || '.' || Name FROM sysibm.systables where CREATOR = (SELECT "column" FROM schema.table where "column" = "value") and "column" = "value")

Any idea? Or is this not possible in DB2?

CYA_D0c

D0c_cR4Zy
  • 3
  • 1
  • [Table names can't be parameter markers or join conditions](https://stackoverflow.com/questions/36281235/using-dynamic-table-name-in-db2) (in every RDBMS that I'm aware of, not just DB2). Note that dynamic SQL is hard(er) in the first place, and _can_ be more open to attack. – Clockwork-Muse May 19 '20 at 23:31

1 Answers1

0

Two issues with your approach:

  1. Or is this not possible in DB2?

Yes, this is not possible in a single step. as @clockwork-muse said, not a db2 privilege.

And, realize that you want to select from several different tables, so actually, you dont want a single SELECT statement, but several. one select for each table that matches your criteria.

You have to perform this in a 2 step way. 1st query will just generate the individuals SELECT statements for each target table. 

db2 -z Generated_SELECTs.sql -x SELECT 'SELECT * FROM "' || RTRIM(TABSCHEMA) || '"."' || RTRIM(TABNAME) || '" ;' FROM SYSCAT.TABLES WHERE  <your condition>

This will dump the individual SELECTs statements to the .sql file.

Then you can execute them all using db2 -tvf 

db2 -tvf Generated_SELECTs.sql

.2 Second issue with your approach, is the use of CREATOR instead of SCHEMA.

Table prefix on db2 is named SCHEMA, most of times SCHEMA is the same as CREATOR, but not always, IGOR can be the creator of a table inside SCHEMA 'Beta'.
The table would be referenced as Beta. , and not IGOR. So you really should use SCHEMA.

Samuel Pizarro
  • 267
  • 1
  • 8