Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: migration - routine related questions

    Getting ready to upgrade two DB2 servers (DB2 is bundled with other IBM products - can't really choose WSE/ESE or 32/64-bit). OS upgrade (RHEL 4 -> RHEL 5) will be done at the same time - OS first, then DB2.


    upgrade #1: DB2 will be upgraded from v9.1 32-bit ESE -> v9.7 32-bit WSE (in-place or via a restore - undecided yet)
    upgrade #2: DB2 will be upgraded from v9.5 32-bit WSE -> v9.7 64-bit WSE (via a restore - moving DB2 to a brand new server)


    My questions are related to what needs to be done prior to and after the upgrade with regard to DB2 routines. Here is what I have but not sure if this really needs to be done for the 1st or 2nd migration:


    1) Backup all external routines prior to the upgrade ($INSTHOME/sqllib/function )

    http://publib.boulder.ibm.com/infoce...Fc0023747.html


    When I query syscat.routines on one of the servers, I see:

    xxxxxxx:/home/db2inst1$ db2 "select substr(ROUTINESCHEMA,1,12) ROUTINESCHEMA, substr(ROUTINENAME,1,20) ROUTINENAME , ROUTINETYPE, LANGUAGE , FENCED, THREADSAFE, ORIGIN from syscat.routines where ROUTINESCHEMA not like 'SYS%'"

    HTML Code:
    ROUTINESCHEMA ROUTINENAME          ROUTINETYPE LANGUAGE FENCED THREADSAFE ORIGIN
    ------------- -------------------- ----------- -------- ------ ---------- ------
    SQLJ          DB2_INSTALL_JAR      P           C        N      Y          E
    CANDLE        PRIORXMONTHS         F           SQL                        Q
    CANDLE        CASTTOINT            F           SQL                        Q
    SQLJ          REMOVE_JAR           P           C        N      Y          E
    CANDLE        CASTDECTOFLOAT       F           SQL                        Q
    SQLJ          DB2_INSTALL_JAR      P           C        N      Y          E
    SQLJ          DB2_UPDATEJARINFO    P           C        N      Y          E
    CANDLE        DATEFORM             F           SQL                        Q
    CANDLE        CASTTOVARCHAR        F           SQL                        Q
    CANDLE        ITMTIMESTAMPGMT      F           SQL                        Q
    CANDLE        DAYSINMONTHTMZ       F           SQL                        Q
    SQLJ          RECOVERJAR           P           C        N      Y          E
    CANDLE        DATERETURN           F           SQL                        Q
    CANDLE        DAYSINMONTH          F           SQL                        Q
    CANDLE        PRIORXFROMCURRENT    F           SQL                        Q
    SQLJ          REFRESH_CLASSES      P           C        N      Y          E
    SQLJ          REMOVE_JAR           P           C        N      Y          E
    CANDLE        SECONDSINMONTHTMZ    F           SQL                        Q
    SQLJ          DB2_REPLACE_JAR      P           C        N      Y          E
    CANDLE        EPOCHSEC             F           SQL                        Q
    CANDLE        SECONDSINMONTH       F           SQL                        Q
    CANDLE        ADDXUNITS            F           SQL                        Q
    CANDLE        CASTTOFLOAT          F           SQL                        Q
    CANDLE        ITMTIMESTAMP         F           SQL                        Q

    Routines with schema SQLJ ( Are they considered system routines? ) are all external. There are also some SYS* (SYSIBM, SYSPROC, SYSFUN... ) external routines, which I excluded from this list. Routines with schema CANDLE are not external. In this case, do I still need to backup .../sqllib/function or is this only required if I have user-defined external routines?

    When I do "ls -l .../sqllib/function", all are soft links to the install path
    When I do "ls -l .../sqllib/function/routine", there is nothing there



    2) When upgrading from v9.1 to v9.7, "all external unfenced routines that have no dependency on the DB2 engine libraries (libdb2e.a or libdb2apie.a) are altered to FENCED and NOT THREADSAFE..."

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    How can I check which routines "have no dependency on the DB2 engine libraries (libdb2e.a or libdb2apie.a)" ?


    Is there anything else that needs to be checked/done before or after the upgrade?
    Last edited by db2girl; 01-14-12 at 16:22.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SQLJ are system routines, you can ignore them.

  3. #3
    Join Date
    Jan 2011
    Posts
    24
    We had to recompile all external functions.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I checked the databases on the second server and the only non-system routines are SQL functions and procedures. No external routines other than system. So, I think everything should be ok. Thank you.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    I checked the databases
    You don't do weekends, do you?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    You don't do weekends, do you?
    Used to, but not anymore. This one is normal hours with some occasional weekend support during prod implementation.

    Yesterday, I was just getting ready for db2 prod upgrade coming up next month - free of charge for them

Posting Permissions

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