Hi,
I'm working on a porting project: an old native Oracle Database
application will be converted to IBM DB2 (task quite finished).
My environment is DB2 9.7FP5 on CentOS 5.6 x64, I converted about 200
PL/SQL stored procedure to DB2 PL, now I would like to move all SP to a
test system for poerformance measures.
How can I massively export all my SP and import them in the test system
(a script I mean)?
Test system is same version of mine but on AIX.
Thanks in advance

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

On Wed, 23 Nov 2011 11:23:13 +0100, NEXUZ6 wrote:

You can use the db2look command that export the entire database definition, including SP's (which are
included together in one section the output for easy copying if you don't want the rest of it).

Be sure to use the -td<delimiter> option and make it "@" to distinguish between end of line inside a
SP, and end of SP. Then when you create the SP on the new system, you will use the command line
option to change delimiter to "@" also.

So this will get you the SP's (and everything else than can be deleted if not needed):
dblook -d <db-name> -td@ -e -a > db2look.sql

On Wed, 23 Nov 2011 11:26:40 +0000, Mark A wrote:

Should be:
db2look -d <db-name> -td@ -e -a > db2look.sql

On 23/11/2011 12:29, Mark A wrote:
Many thanks for fast response =)
The working command line for me is:

db2look -cor -u db2inst1 -z db2inst1 -td @ -e -d $DB -o $DB.sql

but I would like to NOT include in my DDL some item:
- schema occurs ex: "DB2INST1".table_name
- string "SET CURRENT SCHEMA = "DB2INST1"@"
- string "SET CURRENT PATH = "SYSIBM", "SYSFUN", "SYSPROC"" and so on
- no tablespace in table DDL ex: "IN USERSPACE1"
- no index options ex: "COMPRESS NO ALLOW REVERSE SCANS" "PCTREE 10"
- no explain objects..

auto search & replace via bash script ? Ideas ?

Thanks

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

On 2011-11-23 16:23, NEXUZ6 wrote:
If you just want the procedures something like:

[...]$ cat parse.py
#!/usr/bin/python

import re
import sys

infile = '...'

proc_begin_pattern = 'CREATE PROCEDURE [\S\s]*'
proc_begin_rexp = re.compile(proc_begin_pattern)
proc_end_pattern = '[\S\s]*@\S*'
proc_end_rexp = re.compile(proc_end_pattern)
empty_pattern = '^$'
empty_rexp = re.compile(empty_pattern)
state = 0
for line in open(infile):
if state == 0:
if proc_begin_rexp.match(line):
state = 1

if state == 1:
if not empty_rexp.match(line):
print line
if proc_end_rexp.match(line):
state = 0

might work. Tested against one file so there might be a bug or two in
there ;-)

/Lennart