Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to copy a SQL package from one database to another without recompile?

    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
    Last edited by grofaty; 08-23-06 at 09:38.

  2. #2
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39
    It can be done by db2 get routine and db2 put routine. It will move the executable code.
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    thanks a lot. I will try this and let you know.
    Grofaty

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by msateeshkumar
    It can be done by db2 get routine and db2 put routine. It will move the executable code.
    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

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  6. #6
    Join Date
    Aug 2004
    Posts
    18
    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.

  7. #7
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39
    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.
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    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"
    Code:
                                     Bound     Total                          Isolation
    Package    Schema    Version     by        sections      Valid   Format   level     Blocking
    ---------- --------- ----------- --------- ------------- ------- -------- --------- --------
    BO06A      TEST                  TEST                  6 Y       3        CS        N
    ED00A      TEST                  TEST                  1 Y       3        CS        N
    EG30A      TEST                  TEST                  2 Y       3        CS        N
    IN17A      TEST                  TEST                 34 Y       3        CS        N
    IN20A      TEST                  TEST                 32 Y       3        CS        N
    IS00A      TEST                  TEST                  7 Y       3        CS        N
    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
    Last edited by grofaty; 08-25-06 at 02:44.

  9. #9
    Join Date
    Jul 2002
    Posts
    48
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    but is there any other way to move sql-package from one database to another?
    Thanks,
    grofaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •