DB2 SQL Procedural Language Outside Procedure

[Spencer]

Is it possible to execute procedural language outside of a procedure
in DB2 Command Center? For instance I need the ability to declare
some variables and do some conditional processing similar to the
sample below. We are on DB2 UDB 8.1.6.

BEGIN SAMPLE
DECLARE SYSTEM_NAME CHAR(10);
SET SYSTEM_NAME = 'IWT'
IF SYSTEM_NAME = 'IWP' THEN
CREATE UNIQUE INDEX TEST1 ON STG.TABS(NUMBER);
END IF;
END SAMPLE

When I try to run I get the following error returned.

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "BEGIN SAMPLE" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<create_proc>".
SQLSTATE=42601

Spencer
sp*****@tabbert.net

[Serge Rielau]

Spencer,

DB2 UDB for LUW supports the dynamic compound statement.
However dynamic compound can only do inline SQL PL. Not teh full blown
SQL PL.
In your case that means no DDL.

Often people use shell-scripts or perl instead.

Cheers
Serge

[Spencer]

Yes, a shell script could be used however because of security purposes
I would like to be able to do this directly in SQL. Would it be
possible to develop a stored procedure or UDF that I could call or
execute from a sql prompt that would do the drop index or create
index? Essentially what I am looking for is a way to disable the
index but since that does not exist in DB2 I would like to drop and
recreate the index.

Ideally...I would like to have a sql procedure built that would take
as input two parameters. A table name and operation(DropIndex or
CreateIndex). On a call specifying a DropIndex the procedure would
then dynamically determine the indexes on the table it needs to drop
but before doing so record the syntax in a working table such that on
a call specifying CreateIndex the procedure would be able to recreate
all table indexes and as they existed previous to dropping the index
and then perform a runstats. This would be very handy and ensure that
indexes are created in the proper fashion by non DBA's. It would also
ensure that if indexes are renamed or removed by DBA's and Data
Modelers that our processes are not incorrectly creating indexes or
attempting to drop indexes no longer existing.
Spencer
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<41********@news3.prserv.net>...

Spencer,

DB2 UDB for LUW supports the dynamic compound statement.
However dynamic compound can only do inline SQL PL. Not teh full blown
SQL PL.
In your case that means no DDL.

Often people use shell-scripts or perl instead.

Cheers
Serge

[Serge Rielau]

Spencer,

Absolutely. There is a limited set of DDL such for example CREATE and
DROP TABLE which SQL PL supports "natively".
For all other SQL you can use: EXECUTE IMMEDIATE <sql statement>
Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords. Such as
choosing the tablespace...
Cheers
Serghe

[Serge Rielau]

Spencer,

Absolutely. There is a limited set of DDL such for example CREATE and
DROP TABLE which SQL PL supports "natively".
For all other SQL you can use: EXECUTE IMMEDIATE <sql statement>
Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords. Such as
choosing the tablespace...
Cheers
Serge

[Spencer]

Thanks Serge. Just what I was looking for. Can you recommend some
good reference material(Books, Whitepapers, Articles) on the SQL PL
language and it's capabilities and in some cases limitations
specifically in relation to 8.1.x.
Spencer
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<C_******************@news04.bloor.is.net.cab le.rogers.com>...

Spencer,

Absolutely. There is a limited set of DDL such for example CREATE and
DROP TABLE which SQL PL supports "natively".
For all other SQL you can use: EXECUTE IMMEDIATE <sql statement>
Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords. Such as
choosing the tablespace...
Cheers
Serge

[Spencer]

In regards to what is/isn't supported in inline SQL PL will
8.2/Stinger support the ability to use EXECUTE IMMEDIATE or CALL such
that I would be able to perform DDL operations inline SQL PL?
Spencer
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<C_******************@news04.bloor.is.net.cab le.rogers.com>...

Spencer,

Absolutely. There is a limited set of DDL such for example CREATE and
DROP TABLE which SQL PL supports "natively".
For all other SQL you can use: EXECUTE IMMEDIATE <sql statement>
Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords. Such as
choosing the tablespace...
Cheers
Serge

[Serge Rielau]

DB2 V8.2 (FP7) indeed supports CALL in inline SQL PL.
At present I don't see any further improvements to inline SQL PL. w.r.t
specific SQL PL capabilities.
What I do see happen in the future is the ability to create functions
(and trigger) which use native SQL PL as an option. (and no I won't say
when that may happen)

W.r.t. literature here si teh "standard" book:
http://www.informit.com/bookstore/pr...007726&redir=1
It's due to be refreshed for V8.2 later this year.

Cheers
Serge

[Mark Townsend]

Serge Rielau wrote:

Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords.

Use this approach wisely. Many, many SQL Injection attacks are directly
attributable to the use of EXECUTE IMMEDIATE (or other forms of dynamic
SQL)

[Serge Rielau]

Agreed. I encourage those using dynamic SQL in routines to read up on
the DYNAMICRULES bindoption in the BIND command.

Cheers
Serge

[Spencer]

I would agree but it also allows for a tremendous amount of
flexibility in certain situations.
Mark Townsend <ma***********@comcast.net> wrote in message news:<67iXc.48358$9d6.22499@attbi_s54>...

Serge Rielau wrote:

Using EXECUTE IMMEDIATE is also popular because it allows users to "glue
together" the DDL statement in order to add variable keywords.
>

Use this approach wisely. Many, many SQL Injection attacks are directly
attributable to the use of EXECUTE IMMEDIATE (or other forms of dynamic
SQL)