Moving stored procedures

Hi

I m trying to move only stored procedures from one database to another
and also onto a database on another server, I tried
db2 -x "select text from syscat.procedures where procschema =
'MYSCHEMA'" | tee /esa/home/sasha/MY_SCHEMA_SP.txt, how ever all the
procedures are getting loaded to the file but some stored procedures
are getting truncated because they are too big ,

I also tried to use getroutine but the problem is we have 600 of
them,

Please can one letme know the best way to copy all the procedures to
another database

thanks
RIK

re: Moving stored procedures

Hi Rik,

the db2look tool can help you to move stored procedures from one to
another database.

Try the following:

db2look -d <dbname-a -e -o <outputfile>

In the output file you will get a section with all DDLs for the stored
procedures. With db2look -help you will a detailed description of all
options in the db2look tool

Regards

Michael Spoden
SerCon GmbH, Germany

re: Moving stored procedures

hi michael

the problem with db2look is how ever it generates all stored
procedures to one file , the thing is we have dependencies between
procedures , we firts need to find depencies and then use a method to
put them in order into other database, any suggestions how to do this
fast ?

thanks
Rik



On Feb 9, 11:50 am, spo...@de.ibm.com wrote:

re: Moving stored procedures

rick wrote:Rik,

Which version of DB2 are you on?
DB2 9 has support in db2move that allows you to move a complete schema
(or set thereof) from one database to another).
Also if you search for my name on ibm.com you will find a backup/restore
schema article which does the same for DB2 V8.2. It includes source
code, so you can customize to your hearts content.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Moving stored procedures

rick wrote:
The DB2 CLP is doing the truncation. The thing is that only the first 4 or
8KB of a LOB is written in the output of the CLP. Thus, everything larger
is not fully shown.
You could run a SELECT statement that retrieves the names of all procedures
in questions, builds the corresponding CALL statements for GET_ROUTINE and
then you only have to execute the script.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

re: Moving stored procedures

Hi serge

we have 8.1 running on AIX 5.3
we actually need to move storedprocedures and functions only.. most of
our application works on SPs and UDF and developers keep on changing
the SPs and UDFs..front end is kinda static not many changes..this
makes DBAs to keep moving SPs from dev to test (diff servers) and I
had to prepare Get routine and put routine statements for 500 SPs,
well this works fine..i thought if there was any fast way...(i need to
move only SPs and UDFs)

Thanks
rik

On Feb 10, 9:22 am, Serge Rielau <srie...@ca.ibm.comwrote:

re: Moving stored procedures

rick wrote:Just take my source and strip everything other than the access to
SYSCAT.ROUTINES (also you probabaly don't want to drop the schema on
restore...).
These modification should be doable within a couple of days tops.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Moving stored procedures

i will work on it serge
thanks
rik

On Feb 11, 8:52 pm, Serge Rielau <srie...@ca.ibm.comwrote:

re: Moving stored procedures

On 11 Feb, 17:49, Knut Stolze <sto...@de.ibm.comwrote:Hi,

We have a similar requirement, and have solved it by doing an export
from sysroutines (along the lines of db2 "EXPORT TO '/home/PXX/flytt/
$1.del' OF DEL LOBFILE '/home/PXX/flytt/$1' modified by LOBSINFILE
SELECT text||'£' FROM "SYSIBM"."SYSROUTINES" where routinename = '$1'
and routineschema = 'PXX'"). The export file and then be run as a
script against another server (db2 -td£ -vf /home/PXX/flytt/$1.001).
We add a connect and drop to the relevant server prior to executing
the script. All this is done in a shell script where we simply pass in
the routine name we want to move. If you have lots to move you can
write a simple scipt to loop through this process for each routine.
This way you can move the routines in the correct order.

One small limitation is that batch scripts cannot be over 64k in a v8
environment (this limitation is removed in v9).

A disadvantage of get and put is that your servers need to be at the
same fix pack level which may not always be tha case if for example
you are moving routines from a test environmnet to qa or production
servers.

John Enevoldson
Pulsen AB
Sweden.