Generating and running SQL on the fly

[Arun Srinivasan]

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

is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............s yscat.tables where.....;
)
select * from new table (call admin_cmd(selec t 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]

Re: Generating and running SQL on the fly

Arun Srinivasan wrote:

I am trying to generate sql from sql. Like
select 'select count(*) from ' ||
tabschema ..............s yscat.tables where.....;
>
is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............s yscat.tables where.....;
)
select * from new table (call admin_cmd(selec t 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]

Re: Generating and running SQL on the fly

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 ..............s yscat.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]

Re: Generating and running SQL on the fly

On Feb 14, 8:33 pm, Roger <wondering...@g mail.comwrote:

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 ..............s yscat.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]

Re: Generating and running SQL on the fly

Arun Srinivasan wrote:

I am trying to generate sql from sql. Like
select 'select count(*) from ' ||
tabschema ..............s yscat.tables where.....;
>
is there a way to run this with a single statement?
I tried
with temp1(sql) as
(
select 'select count(*) from ' ||
tabschema ..............s yscat.tables where.....;
)
select * from new table (call admin_cmd(selec t 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]

Re: Generating and running SQL on the fly

Norm wrote:

On Feb 14, 8:33 pm, Roger <wondering...@g mail.comwrote:

>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 ..............s yscat.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