SQL DB2 - 如何在 IBM System i Access for Windows GUI 工具中使用参数运行即席选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2336820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 - How to run an ad hoc select query with a parameter in IBM System i Access for Windows GUI Tool
提问by Ken Burkhardt
I would like to run some ad hoc select statements in the IBM System I Navigator tool for DB2 using a variable that I declare.
我想使用我声明的变量在 IBM System I Navigator 工具中为 DB2 运行一些特别的选择语句。
For example, in the SQL Server world I would easily do this in the SQL Server Management Studio query window like so:
例如，在 SQL Server 世界中，我可以在 SQL Server Management Studio 查询窗口中轻松执行此操作，如下所示：
DECLARE @VariableName varchar(50);
SET @VariableName = 'blah blah';
select * from TableName where Column = @VariableName;
How can I do something similar in the IBM System I Navigator tool?
如何在 IBM System I Navigator 工具中执行类似操作？
回答by Brian
I ran across this post while searching for the same question. My coworker provided the answer. It is indeed possible to declare variables in an ad hoc SQL statement in Navigator. This is how it is done:
我在搜索同样的问题时遇到了这篇文章。我的同事提供了答案。确实可以在 Navigator 中的特定 SQL 语句中声明变量。这是如何完成的：
CREATE OR REPLACE VARIABLE variableName VARCHAR(50);
SET variableName = 'blah';
SELECT * FROM table WHERE column = variableName;
DROP VARIABLE variableName;
If you don't drop the variable name it will hang around until who knows when...
如果你不删除变量名，它会一直存在，直到谁知道什么时候......
回答by robertnl
At the moment, we're working on the same issue at work. Unfortunaly, we concluded that this is not possible. I agree, it would be great but it just doesn't work that way. iNavigator doesn't support SET or Define. You can do that in embedded SQL but this is not embedded SQL. Even if you create a separate document (xxx.sql), then need to open this document to run the script what makes it an interactive script (that is, DECLARE SECTION is not allowed).
目前，我们正在处理工作中的同一问题。不幸的是，我们得出结论，这是不可能的。我同意，这会很棒，但它不能那样工作。iNavigator 不支持 SET 或 Define。您可以在嵌入式 SQL 中执行此操作，但这不是嵌入式 SQL。即使你创建了一个单独的文件（xxx.sql），那么需要打开这个文件来运行脚本什么使它成为一个交互式脚本（即不允许DECLARE SECTION）。
As an alternative, in the SQL screen/script you can use CL:. Anything after this prefix is executed as CL command. You may manipulate your tables (e.g. RNMF) this way.
作为替代方案，在 SQL 屏幕/脚本中，您可以使用CL:。此前缀之后的任何内容都作为 CL 命令执行。您可以通过这种方式操作您的表（例如 RNMF）。
As a second alternative, the iSeries does support Rexx scripts (default installed with the os). Rexx is good dynamic script language and it does support embedded SQL. I've done that a lot of times and it works great. I even created scripts for our production environment.
作为第二种选择，iSeries 确实支持 Rexx 脚本（默认安装在操作系统中）。Rexx 是一种很好的动态脚本语言，它确实支持嵌入式 SQL。我已经这样做了很多次，而且效果很好。我什至为我们的生产环境创建了脚本。
Just create one 'default' script with an example PREPARE and CURSOR statement and copy at will. With that script you can play around. See the Rexx manual for the correct syntax of exec-sql. Also, you do have STDIN and STDOUT but you can use 'OVRDBF' to point to a database table (physical file). Just let me know if you need an example Rexx script.
只需使用示例 PREPARE 和 CURSOR 语句创建一个“默认”脚本并随意复制。有了那个脚本，你就可以玩了。有关 exec-sql 的正确语法，请参阅 Rexx 手册。此外，您确实有 STDIN 和 STDOUT，但您可以使用“OVRDBF”来指向数据库表（物理文件）。如果您需要一个示例 Rexx 脚本，请告诉我。
Notice that the manual "SQL embedded programming" does have Rexx examples.
请注意，手册“SQL 嵌入式编程”确实有 Rexx 示例。
回答by Tim
Here are a couple of other alternatives.
这里有几个其他的选择。
Data Transfer Tool - You can run the iSeries Data Transfer Tool from the command line (RTOPCB). First, run the GUI version and create a definition file. If you edit this file with a text editor, you will see that this is just an old-fashioned INI file and you can easily find the line with the query in it. From there, you could write a batch file or otherwise pre-process the text file to allow you to manipulate the query before submitting it to the query tool.
数据传输工具 - 您可以从命令行 (RTOPCB) 运行 iSeries 数据传输工具。首先，运行 GUI 版本并创建一个定义文件。如果您使用文本编辑器编辑此文件，您将看到这只是一个老式的 INI 文件，您可以轻松找到其中包含查询的行。从那里，您可以编写批处理文件或以其他方式预处理文本文件，以允许您在将查询提交到查询工具之前对其进行操作。
QSHELL - If you can log on to the iSeries interactively, then you may find the QSHELL environment more familiar than CL or REXX (although REXX is kind of fun). QSHELL is a full POSIX environment running on the iSeries. Use the command STRQSH to start QSHELL. You can have ksh or csh as a shell. Inside QSHELL, there is a command called "db2" that submits queries. So, you should be able to do something like this inside QSHELL:
QSHELL - 如果您可以交互登录 iSeries，那么您可能会发现 QSHELL 环境比 CL 或 REXX 更熟悉（虽然 REXX 有点有趣）。QSHELL 是在 iSeries 上运行的完整 POSIX 环境。使用命令 STRQSH 启动 QSHELL。您可以将 ksh 或 csh 作为 shell。在 QSHELL 中，有一个名为“db2”的命令用于提交查询。所以，你应该能够在 QSHELL 中做这样的事情：
system> VariableName = 'blah blah'
system> db2 "select * from TableName where Column = \'$VariableName\'"
You may have to fiddle with the quotes to get ksh to pass them correctly.
您可能需要摆弄引号才能让 ksh 正确传递它们。
Also, inside QSHELL, you should have a full Perl installation that will allow you to use DBI to get data.
此外，在 QSHELL 中，您应该有一个完整的 Perl 安装，它允许您使用 DBI 来获取数据。
Some other ways to interact with data on the iSeries: query from the client with Python via ODBC; query from the client with Jython via JDBC; install Jython directly on the iSeries and then query via JDBC.
与 iSeries 上的数据交互的其他一些方法：通过 ODBC 从客户端使用 Python 进行查询；使用 Jython 通过 JDBC 从客户端查询；直接在 iSeries 上安装 Jython，然后通过 JDBC 查询。
