469,613 Members | 1,365 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

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

Feb 9 '07 #1
8 8323
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
Feb 9 '07 #2


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:
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

Feb 10 '07 #3
rick wrote:
>
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 ?
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
Feb 10 '07 #4
rick wrote:
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 ,
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.
I also tried to use getroutine but the problem is we have 600 of
them,
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
Feb 11 '07 #5

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:
rick wrote:
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 ?

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

Feb 11 '07 #6
rick wrote:
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)
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
Feb 12 '07 #7
i will work on it serge
thanks
rik

On Feb 11, 8:52 pm, Serge Rielau <srie...@ca.ibm.comwrote:
rick wrote:
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)

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

Feb 12 '07 #8
On 11 Feb, 17:49, Knut Stolze <sto...@de.ibm.comwrote:
rick wrote:
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 ,

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.
I also tried to use getroutine but the problem is we have 600 of
them,

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

Feb 12 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Tim Bolla | last post: by
4 posts views Thread by Mike L. Bell | last post: by
2 posts views Thread by Kent Lewandowski | last post: by
2 posts views Thread by Timppa | last post: by
5 posts views Thread by Tim Marshall | last post: by
1 post views Thread by Nirbho | last post: by
45 posts views Thread by John | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.