Generating and running SQL on the fly

[Arun Srinivasan]

I am trying to generate sql from sql. Like
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;

is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;
)
select * from new table (call admin_cmd(select sql from temp1));

I know the above is a wishful thinking. But this will clear a major
headache I've been having in recent past...

[Knut Stolze]

Arun Srinivasan wrote:

I am trying to generate sql from sql. Like
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;

is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;
)
select * from new table (call admin_cmd(select sql from temp1));

I know the above is a wishful thinking. But this will clear a major
headache I've been having in recent past...

You need dynamic SQL for that. So you can create a compound statement that
executes your query, fetches the generated statements via a cursor and then
executes the values/statements fetched from the cursor.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Roger]

Arun, are you embedding the sql on an application or running it as a
script? If its a script, why don't you do this, in a shell script

1. Direct the output of your select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....; to a file
2. after this execute the file

Another option you have is a small stored procedure that can prepare
dynamic sqls

HTH

[Norm]

On Feb 14, 8:33 pm, Roger <[e-mail]:

Arun, are you embedding the sql on an application or running it as a
script? If its a script, why don't you do this, in a shell script

1. Direct the output of your select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....; to a file
2. after this execute the file

Another option you have is a small stored procedure that can prepare
dynamic sqls

HTH

If you are a CLP, you can avoid temporary files with:

db2 -x "select 'select count(*) from '||tabschema.... from
syscat.tables where ..." | db2
The output from the select statement is piped to another db2 process.

Norm

[Serge Rielau]

Arun Srinivasan wrote:

I am trying to generate sql from sql. Like
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;

is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....;
)
select * from new table (call admin_cmd(select sql from temp1));

I know the above is a wishful thinking. But this will clear a major
headache I've been having in recent past...

I admit I have never tried that, but....
* Create a procedure that takes your string and performs an EXECUTE
IMMEDIATE (or PREPARE/EXECUTE)
* Wrap the procedure in a function
* invoke that function from SQL.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

Norm wrote:

On Feb 14, 8:33 pm, Roger <[e-mail]:

>Arun, are you embedding the sql on an application or running it as a
script? If its a script, why don't you do this, in a shell script

1. Direct the output of your select 'select count(*) from ' ||
tabschema ..............syscat.tables where.....; to a file
2. after this execute the file

Another option you have is a small stored procedure that can prepare
dynamic sqls

HTH

If you are a CLP, you can avoid temporary files with:

db2 -x "select 'select count(*) from '||tabschema.... from
syscat.tables where ..." | db2
The output from the select statement is piped to another db2 process.

This generally works, but be aware that it can be problematic because
both front-end 'db2' processes are hitting talking to the same back-end
db2bp process.

Ian Bjorhovde