Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Thumbs up Unanswered: Migration from Sybase to Oracle

    Can someone help me with procedure to migrate from Sybase to Oracle. Can you help me in knowing as to how much time it takes to migrate from Sybase 10.0.3 to Oracle 8i

  2. #2
    Join Date
    Jun 2002
    Location
    Dublin, Ireland
    Posts
    23
    Well depends on what objects you have in your Sybase db. If you have complex sybase stored procedures that use temp tables and output multiple result sets. I would think again about migrating. Worked on a project 3 years ago, that was estimated at 3 months using Oracles sybase to oracle stored procedure converter. A year and half later the project was canned and we stuck with sybase. Must admit the sybase stored procedures were horribly written. But that is my experience with sybase to oracle conversion.

    I am sure if you have fairly straight forward stored procedures you should have no problem converting sybase to oracle but you may encounter performance issues with queries in the stored procedures, since the oracle engine would interpret different execution plans than that of sybase.

  3. #3
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Thumbs up

    Oracle work with cursors and don't have temporary isolate tables. sybase work with group logic.

    I'm working to find the cost to migrate sybase to other databases. So I make this script in ksh, awk and sed to read the code of stores and triggers and get information about what are doing every code (run in solaris).

    The output is something like this:

    --------------------------------------------------------------------------------
    Store Procedure Lines Sel Ins Del Upd Temp
    --------------------------------------------------------------------------------
    webchanged 9 0 0 0 1 0
    webcopytemplate 25 5 2 0 0 0
    --------------------------------------------------------------------------------
    Triggers Lines Sel Ins Del Upd Temp
    --------------------------------------------------------------------------------
    webdatachange 5 0 0 1 0 0
    webdeletetemplate 11 2 0 3 0 0
    webchangetemplate 48 2 0 0 4 0
    webaddtemplate 33 2 1 0 2 0
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------





    #!/usr/bin/ksh
    #

    ################################################## ##############################
    # Script: checkcode #
    # #
    # Description: #
    # #
    # #
    # Synopsis: #
    # #
    # $checkcode [-S server_name][-U login][-P password][-D database][-?] #
    # #
    # Examples: #
    # #
    # $checkcode -D desmedddb #
    # $checkcode -S tgssybpr -D proscoddb #
    # $checkcode -? #
    # #
    ################################################## ##############################

    SERVER=`hostname`
    LOGIN="sa"
    DATABASE="master"

    while [ $# -gt 0 ]
    do
    case $1 in
    -S) if [ $# -gt 0 ]; then
    shift
    SERVER=$1
    fi;;

    -U) if [ $# -gt 0 ]; then
    shift
    LOGIN=$1
    fi;;

    -P) if [ $# -gt 0 ]; then
    shift
    PASSWD=$1
    fi;;

    -D) if [ $# -gt 0 ]; then
    shift
    DATABASE=$1
    fi;;

    *) echo "usage: $0 [-S server_name][-U login][-P password][-D database][-?]"
    exit;;

    esac
    if [ $# -gt 0 ]; then
    shift
    fi
    done

    if [ -z $PASSWD ]; then
    printf "Input Passwd: "
    stty -echo
    read PASSWD
    stty echo
    clear
    fi

    OUTPUT=$0$$

    ################################################## ##############################
    # INICIO ISQL #
    ################################################## ##############################
    isql -S$SERVER -U$LOGIN -P$PASSWD -b -Q << EOF > /dev/null

    USE tempdb
    go

    IF EXISTS (SELECT *
    FROM sysobjects
    WHERE name = "vi_text_${OUTPUT}"
    AND type = 'V'
    AND uid = user_id() )
    DROP VIEW vi_texto_${OUTPUT}
    go

    CREATE VIEW vi_text_${OUTPUT}
    AS
    SELECT c.text
    FROM ${DATABASE}..syscomments c,
    ${DATABASE}..sysobjects o
    WHERE o.id = c.id
    AND ( o.type = "P" OR o.type = "TR" )
    AND o.uid = user_id()
    go

    EOF
    ################################################## ##############################
    # FIN ISQL #
    ################################################## ##############################

    ## BCP CODE
    bcp tempdb.dbo.vi_text_${OUTPUT} out ${OUTPUT}1.txt -c -X -A 4096 -e vi_texto.err -S$SERVER -U$LOGIN -P$PASSWD -t @#@# -r @#@# 2> /dev/null 1>&2

    ################################################## ##############################
    # INICIO ISQL #
    ################################################## ##############################
    isql -S$SERVER -U$LOGIN -P$PASSWD -b -Q << EOF > /dev/null

    USE tempdb
    go

    IF EXISTS (SELECT *
    FROM sysobjects
    WHERE name = "vi_text_${OUTPUT}"
    AND type = 'V'
    AND uid = user_id() )
    DROP VIEW vi_text_${OUTPUT}
    go
    EOF
    ################################################## ##############################
    # FIN ISQL #
    ################################################## ##############################

    ## DROP MARK OF BCP
    sed -g 's/@#@#//' ${OUTPUT}1.txt > ${OUTPUT}2.txt

    ## TRANSLATES ALL LOWER-CASE CHARACTERS
    tr "[:upper:]" "[:lower:]" < ${OUTPUT}2.txt > ${OUTPUT}3.txt

    ## ADD \n TO COMMENTS and REPLACE TAB and "
    /usr/bin/nawk '
    /\t/ { gsub("\t", " ") }
    /--/ { sub("--", "\n&") }
    /\/[\*]/ { sub("\/[\*]", "\n&\n") }
    /[\*]\// { sub("[\*]\/", "\n&\n") }
    /\042/ { gsub("\042", "\n&\n") }
    /\047/ { gsub("\047", "\n&\n") }
    /select / { sub("select ", "\n&") }
    /insert / { sub("insert ", "\n&") }
    /delete / { sub("delete ", "\n&") }
    /update / { sub("update ", "\n&") }
    /create / { sub("create ", "\n&") }
    {print $0}
    ' ${OUTPUT}3.txt > ${OUTPUT}4.txt

    ## DELETE COMMENTS
    sed -g "/^--/d;/\/\*/,/\*\//d" ${OUTPUT}4.txt > ${OUTPUT}5.txt

    ## DELETE "..."
    sed -g "/\"/,/\"/d" ${OUTPUT}5.txt > ${OUTPUT}6.txt

    ## DELETE '...'
    ##sed -g "/\'/,/\'/d" ${OUTPUT}6.txt > ${OUTPUT}7.txt

    ## DELETE BOTH LEADING AND TRAILING BLANK AND DELETE ALL BLANK LINES
    sed -g 's/^ *//;s/ *$//;/^ *$/d' ${OUTPUT}6.txt > ${OUTPUT}8.txt

    ## UNIFIDED WHITE SPACES
    nawk '/ +/ { gsub(" +", " ") } {print $0}' ${OUTPUT}8.txt > ${OUTPUT}9.txt


    /usr/bin/nawk '
    ################################################## ##############################
    # INICIO AWK #
    ################################################## ##############################

    BEGIN {
    object=1
    }

    /create +proc/ {
    sub("dbo\.", "", $3)
    STORES[++object]=$3
    LINES[object]=0
    SELECTS[object]=0
    INSERTS[object]=0
    DELETES[object]=0
    UPDATES[object]=0
    TEMPDBS[object]=0
    }

    /create +trig/ {
    sub("dbo\.", "", $3)
    TRIGGERS[++object]=$3
    LINES[object]=0
    SELECTS[object]=0
    INSERTS[object]=0
    DELETES[object]=0
    UPDATES[object]=0
    TEMPDBS[object]=0
    }

    /create +table +#/ {
    TEMPDBS[object]++
    }

    /create +table +tempdb\.\./ {
    TEMPDBS[object]++
    }

    /select/ {
    SELECTS[object]++
    }

    /insert / {
    INSERTS[object]++
    }

    /delete / {
    DELETES[object]++
    }

    /update / {
    UPDATES[object]++
    }

    {
    LINES[object]++
    }

    END {

    print "--------------------------------------------------------------------------------"
    print "Store Procedure Lines Sel Ins Del Upd Temp"
    print "--------------------------------------------------------------------------------"

    for (store in STORES) {
    store_name=STORES[store]
    store_lines=LINES[store]
    store_selects=SELECTS[store]
    store_inserts=INSERTS[store]
    store_deletes=DELETES[store]
    store_updates=UPDATES[store]
    store_tempdbs=TEMPDBS[store]

    printf ("%-30s\t%i\t%i\t%i\t%i\t%i\t%i\n", store_name, store_lines, store_selects, store_inserts, store_deletes, store_updates, store_tempdbs)

    }

    print "--------------------------------------------------------------------------------"
    print "Triggers Lines Sel Ins Del Upd Temp"
    print "--------------------------------------------------------------------------------"

    for (trigger in TRIGGERS) {
    trigger_name=TRIGGERS[trigger]
    trigger_lines=LINES[trigger]
    trigger_selects=SELECTS[trigger]
    trigger_inserts=INSERTS[trigger]
    trigger_deletes=DELETES[trigger]
    trigger_updates=UPDATES[trigger]
    trigger_tempdbs=TEMPDBS[trigger]

    printf ("%-30s\t%i\t%i\t%i\t%i\t%i\t%i\n", trigger_name, trigger_lines, trigger_selects, trigger_inserts, trigger_deletes, trigger_updates, trigger_tempdbs)

    }

    print "--------------------------------------------------------------------------------"

    }

    ################################################## ##############################
    # FIN AWK #
    ################################################## ##############################
    ' ${OUTPUT}9.txt

    rm ${OUTPUT}*.txt

  4. #4
    Join Date
    Mar 2003
    Posts
    2

    cost for the database migration

    This method looks to be very systematic approach. Can you help me in understanding how can one use this data, to calculate the cost.

  5. #5
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Cool

    Well, almost all code that you must replace is in store procedures and triggers.
    Depends what kind of code will be more easy o hard to replace the code.
    For example:
    Store procedure that only make selects are very easy, but when you have update, delete or insert working with group of row, you will have to replace the group logic of Sybase by the cursor logic of oracle, if you want that your application have good performance.
    The other factor that complicated are the temp table. In oracle there is no temp table like Sybase, You must create your own table and sure yourself that this new table is isolated from other users (for example add the user name and time to the table name). So if your code have temp table then translate this to pl/sql will be more difficult.
    I only finish the script to analyzed the code two days ago. I must classified the store, assign a pound to every class, and depends the count of line calculate and time to program in pl/sql.

    I thing a good classification will be

    - only select
    - only select with temp table
    - update, insert and delete
    - update, insert and delete with temp table

    Then I try to pound with hours

    - only select
    1 select will be -> 0.25 to 0.5 hours

    and for every store and trigger I must and the time of test
    Test time: 0.5 hours


    - only select with temp table
    1 select will be -> 1 to 2 hours

    Test time: 1 hours (I must check there is no problem of concurrence )


    - update, insert and delete
    1 update will be -> 1 to 1.5 hours (if I want make performance code with cursor)
    ...

    Test time: 1 hours to 2 hours


    - update, insert and delete with temp table
    1 update will be -> 1.5 to 3 hours (if I want make performance code with cursor)
    ...

    Test time: 2 hours to 5 hours (I must check the there is no problem of concurrence )


    And I can adjust the time of every store with a pound for the count of line.

    This is only an example and I'll had to check the cost of time.

    If you need more information IBM have a migration kid from Sybase to db2, may be you can find some interesting, that help with oracle.

    Actually I'm studying DB2, and my company is study with database can replace Sybase an Informix (may be by Oracle or SQL Server:-( )
    I'm the only dba and I think Sybase is a good database with bad marketing.

Posting Permissions

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