AutoSys Workload Automation

 View Only
Expand all | Collapse all

Migration of Sybase to SQL server

  • 1.  Migration of Sybase to SQL server

    Posted Jul 29, 2020 11:03 AM

    Hi,

     

    I am running CA Workload Automation AE 11.3.6 sp7 on Linux. We are using Sybase as our database. I want to migrate off of Sybase to Sql Server.

     

    Is there any documentation detailing the steps to take to do this?

     

    Thanks,

     

    Ray Magliano | Senior Manager

    COG Technology | Macquarie Asset Management

    2005 Market Street

    Philadelphia, PA 19103 USA

    T 1-215-255-2810 I M 1-215-287-9041 I E raymond.magliano@macquarie.com

    WebEx: https://mgl.webex.com/join/raymond.magliano

     

    www.macquarie.com
    logo

     


    This email and any attachment is confidential. If you are not the intended recipient, please delete this message. Macquarie does not guarantee the integrity of any emails or attachments. For important disclosures and information about the incorporation and regulated status of Macquarie Group entities please see: www.macquarie.com/disclosures


  • 2.  RE: Migration of Sybase to SQL server
    Best Answer

    Posted Aug 04, 2020 02:46 AM
    Hi

    1) You have to upgrade to Autosys 12:  it supports Autosys on Linux with  SQL Server. 
    I think you have to upgrade first on Linux / Sybase, then by running the script CReateAEDB.pl, you will create database and schema on SQL Server.

    2) I don't think there a tool to move from Sybase to SQL Server. The solution is to export all objects from AE-Sybase and insert them in AE-SQLserver
    - jobs:  autorep -q -j ALL > JOBS.jil
    -  machines: autorep -q -m ALL > MACHINES.jil
    - resources:  autorep -q -V ALL  > RES.jil
    - global variables:  autorep -V ALL |awk '{print "sendevent -E SET_GLOBAL -G "$1"="$2}'
    - std cal:  autocal_asc -E std.cal -s ALL
    - ext cal: autocal_asc -E ext.cal -e ALL
    - cycle: autocal_asc -E cyc.txt -c ALL

    You can also run autorep -J ALL to make a picture od job statuses and add the statuses of ON_HOLD / ON_ICE / ON_NO_EXEC  / SUCCESS / FAILURE in JOBS.jil. Like that you will avoid running sendevent  command after the jil insert.


  • 3.  RE: Migration of Sybase to SQL server

    Posted Aug 05, 2020 11:34 AM

    Thanks!

     

    Since Oracle is supported for r11.3.6 SP7 is that a difficult migration?

     

    Ray Magliano | Senior Manager

    COG Technology | Macquarie Asset Management

    2005 Market Street

    Philadelphia, PA 19103 USA

    T 1-215-255-2810 I M 1-215-287-9041 I E raymond.magliano@macquarie.com

    WebEx: https://mgl.webex.com/join/raymond.magliano

     

    www.macquarie.com
    logo

     


    This email and any attachment is confidential. If you are not the intended recipient, please delete this message. Macquarie does not guarantee the integrity of any emails or attachments. For important disclosures and information about the incorporation and regulated status of Macquarie Group entities please see: www.macquarie.com/disclosures





  • 4.  RE: Migration of Sybase to SQL server

    Posted Aug 06, 2020 02:57 AM
    Hello

    For 11.3.6 SP7, Oracle is the alternative to Sybase on Unix / Linux.
    It is the same procedure: you have to export all objects and import them as described for Sybase to SQL Server.

    The difficulty is the find the right timeframe to do the handover: no Box Running, no critical job running ... Otherwise the Autosys restart may be painfull, even with the Global Auto Hold mode.


  • 5.  RE: Migration of Sybase to SQL server

    Posted Aug 07, 2020 10:04 AM

    Just as  follow up.

     

    Does r11.3.6 sp7 support AWS RDS for Oracle?

     






  • 6.  RE: Migration of Sybase to SQL server

    Posted Aug 07, 2020 11:01 AM
    yes, it supports Amazon RDS,
    according to:
    https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/intelligent-automation/workload-automation-ae-and-workload-control-center/11-3-6-SP7/release-notes/ae-release-notes/ae-compatibility-matrix.html



  • 7.  RE: Migration of Sybase to SQL server

    Posted Aug 07, 2020 01:02 PM

    Thanks,again!

     






  • 8.  RE: Migration of Sybase to SQL server

    Broadcom Employee
    Posted Aug 10, 2020 01:41 PM
    Database vendors have done a great job to ensure it is easy to migrate from a competitor's database. Microsoft is no exception with SQL Server. Take a look https://docs.microsoft.com/en-us/sql/ssma/sybase/sql-server-migration-assistant-for-sybase-sybasetosql?view=sql-server-ver15 to see how to migrate from Sybase to SQL Server.

    ------------------------------
    Broadcom - CA Technologies
    ------------------------------



  • 9.  RE: Migration of Sybase to SQL server

    Posted Sep 09, 2020 11:26 AM

    Is there any documentation on what to install and how to connect to Oracle from the app servers?

     






  • 10.  RE: Migration of Sybase to SQL server

    Posted Sep 14, 2020 04:37 AM

    Hello

    yes, on https://techdocs.broadcom.com/
    1. Install an Oracle client 32 bits on your Autosys manager
    2. configure tnsnames.ora to reach the Oracle instance
    3. $ORACLE_HOME/lib and tnsnames.ora must be readable by autosys user
    4. Make sure the root account can run this command before launching the  setup:

      sqlplus sys/xxxxx@instance as sysdba
    After installation, if App server does not start, check that libclntsh.so  is in LD_LIBRARY_PATH  (look at file $AUTOUSER/autosys.sh.hostname, line aslib= ) and variable TNS_ADMIN is the correct path to find tnsnames.ora






  • 11.  RE: Migration of Sybase to SQL server

    Posted Sep 14, 2020 09:17 AM

    Thanks, Does the client have to be 32 bits? Won't 64 bits work?

     






  • 12.  RE: Migration of Sybase to SQL server

    Posted Sep 14, 2020 09:24 AM
    Oracle client must be 32 bits with Autosys 11.3.6 SP7.
    It can be 64 bits with Autosys 12.


  • 13.  RE: Migration of Sybase to SQL server

    Posted Sep 15, 2020 08:44 AM

    Thanks for all your help.

     

    Although, I am having difficulty finding the documentation under https://techdocs.broadcom.com/

     

    I am specifically looking for where to replace Sybase references with Oracle references.

     






  • 14.  RE: Migration of Sybase to SQL server

    Posted Sep 29, 2020 02:07 PM

    Hi,

     

    Trying to connect to AWS RDS Oracle from my app servers.

     

    I can connect via sqlplus.

    tnsnames.ora is in the directory that $TNS_ADMIN is references

     

    I am getting the following errors:

     

    [09/29/2020 13:51:47]      CAUAJM_I_10655 System is running in single server mode.  Event server:  ORCL.

    [09/29/2020 13:51:47]      CAUAJM_E_18407 Failed to connect to Oracle server: ORCL

    [09/29/2020 13:51:47]      CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.

    [09/29/2020 13:51:47]      CAUAJM_E_18401 Function <Olog> invoked from <openProc> failed <160>

    [09/29/2020 13:51:47]      CAUAJM_E_18402 ORA-12154: TNS:could not resolve the connect identifier specified

    [09/29/2020 13:51:47]      CAUAJM_E_10649 Server ORCL was not available during connection operation.

     

    I can connect via sqlplus.

    tnsnames.ora is in the directory that $TNS_ADMIN is references

     

    I am testing w/as_server. Are there diagnostics that can be enabled to get more info?

     

    Thanks,

     






  • 15.  RE: Migration of Sybase to SQL server

    Posted Sep 30, 2020 03:08 AM
    On Linux, the startup script loads $AUTOUSER/autosys.sh.hostname.
    Have you set TNS_ADMIN in  $AUTOUSER/autosys.sh.hostname ?
    Do you have the 32 bits $ORACLE_HOME/lib  in the line aslib of $AUTOUSER/autosys.sh.hostname  ?


  • 16.  RE: Migration of Sybase to SQL server

    Posted Sep 30, 2020 10:22 AM

    Thanks, it turns out that I had a malformed tnsnames.ora file.

     

     






  • 17.  RE: Migration of Sybase to SQL server

    Posted Sep 30, 2020 11:48 AM

    I have another question pertaining to HA.

     

    If I set up my Oracle instance as MultiAZ how is HA configured for Autosys?

     

    Is it still?

    EventServer_1 = Primary

    EventServer_2 = Secondary ?

     

    Or is there another configuration?

     






  • 18.  RE: Migration of Sybase to SQL server

    Posted Oct 01, 2020 02:44 AM
    In a Dual DB configuration, the configuration in config.$AUTOSERV is:
    EventServer_1=ORCL1:aedbadmin,1521,host1
    EventServer_2=ORCL2:aedbadmin,1521,host2
    DBEventReconnect=50,5


  • 19.  RE: Migration of Sybase to SQL server

    Posted Sep 30, 2020 01:32 PM

    One more

     

    When I start the app_server it connects to Oracle, but I get this error

     

    [09/30/2020 12:34:51]      CAUAJM_I_10655 System is running in single server mode.  Event server:  ORCL.

    [09/30/2020 12:34:55]      CAUAJM_E_18416 Event Server: <ORCL>  Failed Query: <select int_val from ujo_alamode where type='DB'>

    [09/30/2020 12:34:55]      CAUAJM_E_18402 ORA-00942: table or view does not exist

     

    I ran CreateAEDB successfully.

    Is there another step that I need?

     

    Thanks, again!

     






  • 20.  RE: Migration of Sybase to SQL server

    Posted Oct 01, 2020 02:57 AM
    can you grep this line in $AUTOUSER/confg.$AUTOSERV  ?
    DBAccess=autosys/2B4A60C0D02773E4E3FF7452033F9D3C

    Then logon Oracle with this user:
    sqlplus autosys/xxxxxx@ORCL and try to run the same sql statement select int_val from ujo_alamode where type='DB'

    If it fails, you can check if in Oracle, autosys user has the role UJOADMIN.





  • 21.  RE: Migration of Sybase to SQL server

    Posted Oct 01, 2020 12:52 PM

    Thanks, it looks like the tables do not get created?

     

    I think there are bugs in CreateAEDB.pl.

     

     






  • 22.  RE: Migration of Sybase to SQL server

    Posted Oct 05, 2020 03:32 PM

    I am running autosys 11.3.6 sp7 and trying to run against AWS RDS for Oracle.

     

    The CreateAEDB.pl seems to have bugs. The tables do not get created and it seems that grants are executed.

    I ran the following to manually create a sql script to install

    /app/CA/WorkloadAutomationAE/JRE_WA/bin/java -Dfile.encoding=UTF-8 -Xmx256m -cp ../buildadb.jar com.ca.ewa.BuildADB -dbvendor Oracle -infile ../ddl.adb -outfile /tmp/ora_createaedb_sec/DEV/tmp.sql -oradataspace AEDB

     

    When autosys trys to access select int_val from ujo_alamode where type='DB'

    It does not find it. Is there a script to create roles?

     

    Is there a new version of CreateAEDP.pl for Oracle that I can download?

     

    Thanks,

     






  • 23.  RE: Migration of Sybase to SQL server

    Posted Oct 07, 2020 02:03 PM

    I was able to deploy the schema and data in Oracle. I have two questions:

     

    1. How can I create the objects lowercase instead of uppercase?
    2. What is the sql to grant the role UJOADMIN to autosys? I try to grant it but it does not seem to take?

     

    Thanks,