DB2 SQL过程语言外部过程 [英] DB2 SQL Procedural Language Outside Procedure
问题描述
是否可以在DB2 Command Center中的过程
之外执行过程语言？例如，我需要能够声明
一些变量并做一些类似于下面的
样本的条件处理。我们使用的是DB2 UDB 8.1.6。
BEGIN SAMPLE
DECLARE SYSTEM_NAME CHAR（10）;
SET SYSTEM_NAME =' 'IWT''
如果SYSTEM_NAME =''IWP''那么
在STG.TABS上创建独特的索引TEST1（数字）;
END IF;
结束样本
当我尝试运行时，我收到以下错误。
DB21034E命令被处理为SQL语句，因为它是
而不是
有效的命令行处理器命令。在SQL处理期间它返回
：
SQL0104N意外的令牌BEGIN SAMPLE发现在
BEGIN-OF-STATEMENT之后。预期的令牌可能包括：< create_proc>。
SQLSTATE = 42601
Spencer
sp ***** @ tabbert.net
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
推荐答案
Spencer，
DB2 UDB for LUW支持动态复合语句。
但是动态化合物只能执行内联SQL PL。不是全面发展
SQL PL。
在您的情况下，这意味着没有DDL。
通常人们使用shell脚本或改为perl。
干杯
Serge
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
是的，可以使用shell脚本，因为出于安全目的
我希望能够直接在SQL中执行此操作。是否可以开发一个我可以调用的存储过程或UDF，或者从一个sql提示符执行
执行drop index或者创建
指数？基本上我正在寻找的是一种禁用
索引的方法，但由于这在DB2中不存在，我想放弃并且
重新创建索引。
理想情况......我想建立一个sql程序，将
作为输入两个参数。表名和操作（DropIndex或
CreateIndex）。在一个指定DropIndex的调用上，该过程将会b / b
然后动态确定它需要删除的表上的索引
但是在这样做之前记录工作表中的语法，使得在
a调用指定CreateIndex的过程中，该程序将能够重新创建所有表索引并且因为它们在删除索引之前就存在了
然后执行一个runstats。这将非常方便，并确保非DBA的正确方式创建
索引。如果索引由DBA和数据重命名或删除，那么
确保我们的流程没有错误地创建索引或
试图放弃索引不再存在。
Spencer
Serge Rielau< sr ***** @ ca.eye-bee-em.com>在消息新闻中写道：< 41 ******** @ news3.prserv.net> ...
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支持动态复合语句。
然而动态复合只能执行内联SQL PL。没有全面发展
SQL PL。
在你的情况下，这意味着没有DDL。
人们经常使用shell脚本或perl。
干杯
Serge
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，
绝对。有一组有限的DDL，例如CREATE和
DROP TABLE，SQL PL支持本机。
对于您可以使用的所有其他SQL：EXECUTE IMMEDIATE< ; sql语句>
使用EXECUTE IMMEDIATE也很受欢迎，因为它允许用户将
粘在一起。 DDL语句以添加变量关键字。比如
选择表空间......
干杯
Serghe
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
这篇关于DB2 SQL过程语言外部过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
