CODEWITHSUNDEEP
databasestored-proceduresdb2
JennyB
JennyB

Reputation: 21

How can you run a stored procedure from an IBM command line?

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.

  1. 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

Upvotes: 1

Views: 3529

Answers (2)

CRPence
CRPence

Reputation: 1259

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)

Upvotes: 1

JennyB
JennyB

Reputation: 21

Figured it out!! Which kind of reminds me of the saying about monkeys and typewriters and works of shakespeare, it's taken a lot of false starts to finally get it.. Just in case it helps anyone else I did this:

Created a stored procedure called 'P_CD020AUDIT'.

Entered these commands:

CRTSRCPF FILE(JENNYB/QSQLSRC) TEXT('SQL scripts')

ADDPFM FILE(JENNYB/QSQLSRC) MBR(CD020_A)

EDTF FILE(JENNYB/QSQLSRC)

In the file editor, I just entered 'call jennyb.p_cd020audit' (without quotes) and saved the file.

Then from a command line I tried this:

RUNSQLSTM SRCFILE(JENNYB/QSQLSRC) SRCMBR(CD020_A)

And, hallelujah, it works. So I created a job schedule entry with ADDJOBSCDE, and with this command again - RUNSQLSTM SRCFILE(JENNYB/QSQLSRC) SRCMBR(CD020_A) – and when I submit this it runs my stored procedure. I hope this saves someone else from a morning of frustrated googling and error messages.. ;)

Upvotes: 1

Related Questions