如何从IBM命令行运行存储过程？ [英] How can you run a stored procedure from an IBM command line?

问题描述

我试图找到一种从IBM AS400命令行运行存储过程的方法，以便我可以将其输入到ADDJOBSCDE条目中，并计划它每晚运行。我以为这将是很简单 - 错！ ：）

我的过程已创建，它不需要输入参数。它在库JENNYB，它的名字叫P_CD020AUDIT。我可以从运行SQL脚本没有问题，它运行成功。然而，我不想每天早上在凌晨1点做，所以我需要一个方法来安排。

我在这里找到这篇文章：
http://archive.midrange.com/midrange-l/200907/msg00293.html

1. 创建一个SQL源文件（仅需创建一次），用于容纳源成员

CRTSRCPF FILE（MYLIB / QSQLSRC）TEXT（'SQL scripts'）
2.使用STRSEU创建SQL源成员，可在其中运行SQL语句。例如，CALL MYLIB.MYSTOREDPROCEDURE（p1，p2）。

3. 使用WRKJOBSCDE / ADDJOBSCDE

$ b

ADDJOBSCDE CMD（RUNSQLSTM SRCFILE（IDIGPROC / QSQLSRC）SRCMBR（aSRCmember））





谢谢，yan

但是当我输入命令STRSEU时我得到'Error found on command'所以我想可能有一些我们没有安装在我们的服务器上。

我看到有人说你可以通过以下命令编辑源文件：

CRTSRCPF FILE（MYLIB / SQLSRC）

EDTF FILE（MYLIB / SQLSRC）MBR（CALLMOFO）

所以我做了这个（并在我的文件中创建了成员），但我不知道我在EDTF看 - 我只想输入我的SQL脚本，但我甚至不能设法创建一个新行。

EDTF

任何人都可以提供有关如何从命令行运行简单的，无输入参数的存储SQL过程的白痴指南？提前谢谢。

JennyB

解决方案

FWiW除了在另一个答案中运行SQL语句（RUNSQLSTM）：

•相对较新版本的IBM i操作系统将具有运行SQL（RUNSQL）命令，作为一个更简单的方法为一个语句：

RUNSQL'call jennyb.p_cd020audit'

•大多数发行版都有通过QSHELL的DB2命令行界面

qsh cmd（'db2'call jennyb.p_cd020audit'）

还有REXX SQL;一个完整的例子被省略 - 请在注释中，如果需要一个例子：

STRREXPRC ... CMDENV（* EXECSQL）

I'm trying to find a way of running a stored procedure from an IBM AS400 command line, so that I can enter this into an ADDJOBSCDE entry and schedule it to run nightly. I thought this would be pretty simple - wrong!! :)

My procedure has been created, and it requires no input parameters. It's in library JENNYB and it's called P_CD020AUDIT. I can call it from 'Run SQL Scripts' no problem, and it runs successfully. However I'd like not to have to do that every morning at 1am, so I need a way to schedule it.

I've found this post here: http://archive.midrange.com/midrange-l/200907/msg00293.html

1. Create a SQL source file (only need to create once), which is used to accommodate source members

CRTSRCPF FILE(MYLIB/QSQLSRC) TEXT('SQL scripts') 2. Create a SQL source member by using STRSEU, in which SQL statements can be run. For example, "CALL MYLIB.MYSTOREDPROCEDURE(p1,p2)". Here, the MYSTOREDPROCEDURE is the sp I want to schedule as a job.

3. Use WRKJOBSCDE/ADDJOBSCDE command to add a new job

ADDJOBSCDE CMD(RUNSQLSTM SRCFILE(IDIGPROC/QSQLSRC) SRCMBR(aSRCmember))

thanks, yan

But when I enter the command STRSEU I get 'Error found on command' so I think possibly there's something we don't have installed on our server.

I saw someone else saying you could edit the source file by following these commands:

CRTSRCPF FILE(MYLIB/SQLSRC)

EDTF FILE(MYLIB/SQLSRC) MBR(CALLMOFO)

So I've done this (and created the member in my file too), but I'm not sure what I'm looking at in EDTF - I just want to enter my SQL script, but i can't even manage to create a new line.

EDTF

Could anyone offer an idiots guide to how to run a simple, no-input-parameter stored SQL procedure from a command line? Thank you in advance..

JennyB

解决方案

FWiW, in addition to the Run SQL Statements (RUNSQLSTM) in another answer:
• Relatively newer releases of the IBM i OS will have the Run SQL (RUNSQL) command, as a much simpler means for just the one statement:
RUNSQL 'call jennyb.p_cd020audit'
• Most releases also have a DB2 command line interface via the QSHELL
qsh cmd('db2 "call jennyb.p_cd020audit"')
• There is also REXX SQL; a complete example is omitted -- ask for an example here, in a comment, if one is desired:
STRREXPRC … CMDENV(*EXECSQL)

这篇关于如何从IBM命令行运行存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！