Hi,

I have a question associated with static SQL. I would like to copy a SQL package from one db2 Linux box to another db2 Linux. I would like to copy a package from test environment to production environment witch are on the same version of operating system and database version and fixes.

So how to copy a SQL package? Is on db2 Linux something like "unload" and "load" like on db2/vse/zSeries?

I would just like to avoid recompiling on production environment, because compiling is CPU intensive.

My system: db2 ESE v8.1 fp 11 on Linux zSeries.

Thanks,
Grofaty

It can be done by db2 get routine and db2 put routine. It will move the executable code.

Hi,
thanks a lot. I will try this and let you know.
Grofaty

msateeshkumar,
are you sure that 'get routine' command copies the package to file? As I see this command copies procedure to file. But I would like to copy SQL package from one database to another.

Can you please provide exact sintax of command to move (copy) a package from one database to another?
Thanks,
Grofaty

you can move procedures using get_routine_sar/ put_routine_sar as per info center . however i am not able to perform and check it as i am getting error.
you can check out these two functions

Rahul Singh

Hi

as far as I understand your problem you would like to create a "test-package"
in your production environment.

I would suggest to copy the bnd-file to your production environment and use the bind command with parameters ( like owner, qualifier etc ) used in your productional environment after connect to the prod-db.

The bind command creates the package.

Grofaty

db2 get routine and db2 put routine will copy the compiled version of Stored procedure from one database to another without performing a recompilation on the destination database. It also creates the package for the stored procedure which is moved to the destination database. Hope this helps.

Hi,
just to let you know I have executed commands:
db2 "get routine into eg30a.sar from procedure test.eg30a"

I got an error:
SQL0443N Routine "SYSFUN.GET_ROUTINE_SAR" (specific name "GET_SAR4PARM") has returned an error SQLSTATE with diagnostic text "-204, 02000, TEST.EG30A". SQLSTATE=38000

But I can see I have package test.eg30a witch command:
db2inst1@lin9alca:~> db2 "list packages for schema test"
As I see "get routine" command does not move SQL package to file. This command has to be working something else.

More details on problem:
I was probably not compleatly clear with my question. I have IBM Visual Age Generator (software program to make programs). In test environment I can create Cobol code (and then complile it) and execute SQL compiling to DB2_Linux database to create SQL package (so using static SQL). So far so good. But know I need to create the SQL package to production environment, so I need to compile SQL package with Visual Age Generator again. It works fine, but it is CPU intensive and time consuming operation. So I would like to copy SQL package from test database and move it to production database with some db2 commands to awoid recompiling it again. This can be done on db2/vse (zSeries mainfraime database) with "unload" command to store SQL package from test database to disk and "load" command to move package to production database. How to do this in db2_Linux?

Thanks,
Grofaty

Hi

You have to use procedure name not the package name. Please use the corresponding procedure name and more over this will work only for SQL procedures.

Thanks
Nageswaran

Hi,
but is there any other way to move sql-package from one database to another?
Thanks,
grofaty