db2 stored proc using script

usc · #1 (**permalink**) 04-13-07, 20:08

I am new to AIX DB2.

I need to create db2 stored procedure using shell script. Can anyone provide a sample script that connects to db2 database and does the create/

Thanks!

stolze · #2 (**permalink**) 04-14-07, 08:53

Code:

#!/bin/sh

db2 "connect to <db-name>"
db2 "create procedure proc() begin ... end"
db2 "connect reset"

usc · #3 (**permalink**) 04-16-07, 12:52

the id i use is a dba id. i need to create the stored proc with a different schema. how do i accomplish this.

Thanks!

Damian Ibbotson · #4 (**permalink**) 04-19-07, 15:37

Your SP will likely contain several delimited statements, so you would need to use a different delimiter when creating the SP.

e.g.

db2 -td@ << !!
create procedure myschema.myproc(in var1 int, in var2 int)
...
@
!!

stolze · #5 (**permalink**) 04-21-07, 04:37

Code:

#!/bin/sh

PROC_SCHEMA=$1
if [ -z "${PROC_SCHEMA}" ]; then
    echo "Usage: $0 <schema-name>"
    exit 1
fi

db2 "connect to <db-name>"
db2 "create procedure ${PROC_SCHEMA}.proc() begin ... end"
db2 "connect reset"

Internet Services

Web Development

Digital Marketing

Consumer Tech