SAP Sybase ASE to SAP HANA Data Replication

SAP Sybase Replication Server version 15.7.1 SP 100 is the first edition which supports data replication to SAP HANA. Source can be any SAP Sybase Replication Server primary supported RDBMS:

• Adaptive Server
• Oracle
• Microsoft SQL Server
• DB2 UDB on Linux, UNIX, and Windows

RepServer provides a new connector called ECH (ExpressConnect for HANA) which has been implemented similar to the Oracle (ECO). ExpressConnect for Oracle (ECO) is a library that is loaded by Replication Server 15.5 or later for Oracle replication.

More info regarding ECO/ECH -->  The advantages of ECO/ECH include:

• It does not require a separate server process for starting up, monitoring, or administering.
• Since Replication Server and ECO/ECH run within the same process, no SSL is needed between them.
• Server connectivity is configured via Replication Server using the “create connection” and “alter connection” commands, thus there is no need to separately configure the equivalent to the ECDA for Oracle connect_string setting.
• ECH consists in 2 dynamic libraries shipped with SAP Sybase Replication Server (libsybhdb and libsybhdbodbc) that are linked with SAP HANA odbc driver (libodbcHDB)
• Direct load materialization support for HANA, no need to use materialization queues (this requires Rep Agent 15.7.1 SP100)

Using ExpressConnect for HANA DB

A Replication Server database connection to HANA DB can be:

• secure, in which the connection uses the hdbuserstore key specified in the database connection, or
• standard, in which the connection uses an entry in the interfaces file for the host and port number for HANA DB.

Replication Server provides new a function string class, new connection profiles, and replicate database objects to support HANA DB.

New function strings have been added to the Replication Server rs_hanadb_function_class. These function strings are designed to communicate with a HANA DB data server and access the tables and procedures.

Replication Server provides new connection profiles for replicating into HANA DB:

• rs_ase_to_hanadb – installs Adaptive Server-to-HANA DB class-level translations.
• rs_oracle_to_hanadb – installs Oracle-to-HANA DB class-level translations.
• rs_udb_to_hanadb – installs DB2 UDB-to-HANA DB class-level translations.
• rs_msss_to_hanadb – installs Microsoft SQL Server-to-HANA DB class-level translations.

Direct Load Materialization

Use direct load materialization to materialize data between different kinds of primary databases and HANA DB.

Direct load materialization can be used to materialize data:

• from Adaptive Server to HANA DB
• from Microsoft SQL Server to HANA DB
• from Oracle to HANA DB
• from DB2 UDB to HANA DB

Note: Direct load materialization is not supported for materializing data into an Adaptive Server database.

Direct load materialization is enabled through the direct_load option of the create subscription command.

When using direct load materialization, note these restrictions for create subscription:

• When the direct_load option is used, no other subscription can be created or defined at the same time for the same replicate table.
• The direct_load option is for subscriptions to table replication definitions only and is used withwithout holdlock. It cannot be used with the without materialization or incrementally options.
• The user and password options are used only with direct_load.
• You cannot use the direct_load option against a logical or alternate connection. The primary connection in the replication definition and the replicate connection in the subscription must be physical connections.
• The maintenance user of the primary database cannot be used in the user and password options to create subscriptions.
• You cannot use other automatic materialization methods if the primary database is not Adaptive Server. The only automatic materialization option for Oracle or other databases is direct load materialization. You cannot drop a subscription with the with purge option if the replicate database is not Adaptive Server.
• The direct_load option is available only if the replicate Replication Server site version and route version are 1571100 or later.
• You can use row filtering, name mapping, customized function strings and datatype mapping with subscriptions created using the direct_load option.
• Replication Server rejects any attempt to create a subscription with the direct_load option if the number of subscriptions being created has reached or exceeded num_concurrent_subs.

Primary Database Considerations

In directly materializing data from a primary database, Replication Server connects to Replication Agent for non-Adaptive Server databases, and directly to the primary database for Adaptive Server.

You must have Replication Agent version 15.7.1 SP100 or later to materialize data from a non-Adaptive Server primary database using direct load materialization.

When invoking the create subscription command, Replication Server connects to Replication Agent using the Replication Agent administrator login name.

rs_init sets default configuration parameters after you install your Replication Server. 

======================================================================

Configuring/ Setup replication from ASE to HANA

6 Steps required for establishing a replication system from ASE to HANA using SAP Sybase Replication Server.

1. Create the connection to primary ASE database (easy using rs_init)

create connection

Adds a database to the replication system and sets configuration parameters for the connection. To create a connection for an Adaptive Server database, use Sybase Central or rs_init. To create a connection for a non-Adaptive Server database, see create connection using profile command.

         create connection to ASE_PS.pubs2

         set error class ansi_error
         set function string class sqlserver_derived_class
         set username pubs2_maint
         set password pubs2_maint_pw

Syntax

         create connection to data_server.database

         set error class [to] error_class

         set function string class [to] function_class

         set username [to] user

         [set password [to] passwd]

         [set dsi_connector_sec_mech [to] hdbuserstore]

         [set replication server error class [to] rs_error_class]

         [set database_param [to] 'value' [set database_param [to] 'value']...]

         [set security_param [to] 'value' [set security_param [to] 'value']...]

         [with {log transfer on, dsi_suspended}]

         [as active for logical_ds.logical_db |

         as standby for logical_ds.logical_db

         [use dump marker]]

Parameters

data_server – The data server that holds the database to be added to the replication system.

database – The database to be added to the replication system.

error_class – The error class that is to handle errors for the database.

function_class – The function string class to be used for operations in the database.

user – The login name of the Replication Server maintenance user for the database. Replication Server uses this login name to maintain replicated data. You must specify a user name if network-based security is not enabled.

passwd – The password for the maintenance user login name. You must specify a password unless a network-based security mechanism is enabled.

dsi_connector_sec_mech – Specifies the DSI connector security mechanism.

rs_error_class – The error class that handles Replication Server errors for a database. The default is rs_repserver_error_class.

database_param – A parameter that affects database connections from the Replication Server.

value – A character string that contains a value for the option.

security_param – A parameter that affects network-based security. See "Parameters Affecting network-Based Security" table for a list and description of security parameters that you can set with create connection. This parameter does not apply to non-ASE, non-IQ connectors.

log transfer on – Indicates that the connection may be a primary data source or the source of replicated functions. When the clause is present, Replication Server creates an inbound queue and is prepared to accept a RepAgent connection for the database. If you omit this option, the connection cannot accept input from a RepAgent.

dsi_suspended – Starts the connection with the DSI thread suspended. You can resume the DSI later. This option is useful if you are connecting to a non-Sybase data server that does not support Replication Server connections.

as active for – Indicates that the connection is a physical connection to the active database for a logical connection.

as standby for – Indicates that the connection is a physical connection to the standby database for a logical connection.

logical_ds – The data server name for the logical connection.

logical_db – The database name for the logical connection.

use dump marker – Tells Replication Server to apply transactions to a standby database after it receives the first dump marker after the enable replication marker in the transaction stream from the active database. Without this option, Replication Server applies transactions it receives after the enable replication marker.

2. Create the connection to the HANA database (only can do manually with create connection command).  

         create connection to

         <hana_server>.<hana_db>

         using profile rs_ase_to_hanadb;ech

         set username <userid>

         set password <password>

HANA DB Replicate Database Permissions

Replication Server requires a maintenance user ID that you specify using the Replication Server create connection command to apply transactions in a replicate database.

The maintenance user ID must be defined at the HANA DB data server and granted authority to apply transactions in the replicate database. The maintenance user ID must have these schema privileges:

• CREATE ANY – allows the user to create tables, views, sequences, synonyms, SQL script functions, or database procedures in a schema.
• DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, and UPDATE – granted on every object stored in the specified schema.

Connection has to be created using profile rs_ase_to_hanadb;ech

Additional Settings

Command Batching : HANA DB does not support command batching. Do not turn on command batching for the HANA DB database connection.

Dynamic SQL: The dynamic_sql configuration parameter is set to 'on' by default, and this setting is recommended for all HANA DB connection profiles.

HVAR : If you have a Replication Server license for Advanced Service Options, you can use Replication Server High-Volume Adaptive Replication (HVAR) in replicating to HANA DB. 

Direct Load Materialization

Use direct load materialization to materialize data between different kinds of primary and replicate databases.

• Direct load materialization is only for use with subscriptions to table replication definitions.
• Direct load materialization differs from other automatic materialization methods:
• No materialization queue is used with direct load materialization. Data is loaded directly from a primary table into a replicate table.
• Replication to other tables is not suspended during direct load materialization. DML operations on a primary table being materialized are stored in a catch-up queue and applied to the replicate table after the initial materialization phase. DML operations on a primary table that is not being materialized are replicated into the replicate table as the DSI receives them. Multiple tables can be concurrently materialized with direct load materialization.
• When subscription materialization stops due to an error, regular replication to other tables is not suspended.
• Multiple parallel threads can be used to load data from one primary table to its corresponding replicate table. You can tune this multi-threaded behavior with max_mat_load_threads.
• The atomic and nonatomic materialization methods described here are only supported for an Adaptive Server primary. For an explanation of the different types and methods of materialization, see the Replication Server Heterogeneous Replication Guide > Materialization > Types of Materialization and the Replication Server Administration Guide: Volume 1 > Manage Subscriptions > Subscription Materialization Methods.

Direct load materialization can be used to materialize data:

• from Adaptive Server to HANA DB
• from Microsoft SQL Server to HANA DB
• from Oracle to HANA DB
• from DB2 UDB to HANA DB.

 Note: Direct load materialization is not supported for materializing data into an Adaptive Server database

Restrictions and Limitations for create subscription

• When the direct_load option is used, no other subscription can be created or defined at the same time for the same replicate table.
• The direct_load option is for subscriptions to table replication definitions only and is used withwithout holdlock. It cannot be used with without materialization or incrementally.
• The user and password options are used only with direct_load.
• You can only use the direct_load option against a physical database connection, not an alternate or logical connection. This is the case for both the primary connection—the connection specified in the replication definition—and the replicate connection—the connection specified in the subscription.
• The maintenance user of the primary database cannot be used in the user and password options to create subscriptions.
• You cannot use atomic materialization if the primary database is not Adaptive Server. For a primary database other than Adaptive Server, the only automatic materialization option supported is direct load.You cannot drop a subscription with the with purge option if the replicate database is not Adaptive Server.
• The direct_load option is available only if the replicate Replication Server site version and route version are 1571100 or later.
• You can use row filtering, name mapping, customized function strings and datatype mapping with subscriptions created using the direct_load option.
• Replication Server rejects any attempt to create a subscription with the direct_load option if the number of subscriptions being created has reached or exceeded num_concurrent_subs.

3. Create replication definition at primary ASE database and mark it

         create replication definition authors_rep

         with primary at ASE_PS.pubs2

         with all tables named 'authors'

         (au_id varchar(11), au_lname varchar(40),

         au_fname varchar(20), phone char(12),

         address varchar(12), city varchar(20),

         state char(2), country varchar(12), postalcode char(10))

         primary key (au_id)

         searchable columns (au_id, au_lname)

         replicate minimal columns

4. Create table at replicate HANA database similar to Primary Table 

using SAP HANA Studio tools:

• Select your Schema and Right click on and select "New Table" 
• Enter the Table Name
• Choose the Table Type, e.g. "Row Store"
• Enter the table columns/fields, data types, Key characteristics, etc. by clicking on the "+" sign similar to Sybase ASE Table, and click on the Create table icon (or F8) when it’s ready. 

using standard SQL

• Select your Schema. Right click and select "SQL Console". Otherwise you can also click on “SQL" button on top panel.
• Type the SQL statement in the SQL editor. 

          CREATE TABLE REP_TABLE1 ( UID INTEGER, UNAME VARCHAR(10), 

          UREMARKS VARCHAR(100), PRIMARY KEY (ID) ); 

         

         click on the Execute (or F8)

5. Create subscription and verify.

At the replicate Replication Server, create the subscription:

         Create subscription sub_hana_authors_rep

         for authors_rep

         with Replication at HanaDB.TestDB

         without holdlock

         direct_load

         go

Syntax

         create subscription subscription_name

         for replication_definition

         with replicate at dataserver.database

         [where search_conditions]

         without holdlock

         direct_load

         go

         check subscription subscription_name for repdef_name

         with replicate at replicate_dataserver.replicate_database

         go

When materialization is complete, Replication Server returns a message similar to:

Subscription <subscription_name> has been MATERIALIZED at the replicate.

When the subscription process is complete, Replication Server returns a message similar to:

Subscription <subscription_name> is VALID at the replicate.

If there is an error in the subscription process, the check subscription command returns a message similar to:

Subscription <subscription_name> encountered ERROR.

6. Start repagent and replication is ready

(Thanks much to the SAP Sybase Documentation and Many SAP geeks)

.

SAP Sybase Replication Server

Replication Server maintains replicated data in multiple databases while ensuring the integrity and consistency of the data. It provides clients using databases in the replication system with local data access, thereby reducing load on the network and centralized computer systems.

The Replication Command Language (RCL) enables you to customize replication functions and to monitor and maintain the replication system. For example, you can request subsets of data for replication at the table, data row, or column level. This feature further reduces overhead by allowing you to replicate only the data that is needed at the replicate site.

Replication Server supports heterogeneous data servers. You can build a replication system from existing databases and applications without having to convert them. As your enterprise grows and changes, you can add data servers to your replication system to meet your needs.

Replication Server uses a basic publish-and-subscribe model for replicating data across networks. Users "publish" data that is available in a primary database, and other users "subscribe" to the data for delivery in a replicate database. Users can replicate both changes to the data (update/insert/delete operations) and stored procedures using this method.

Instructions to publish and subscribe to data are given at Replication Servers that control, or have a connection to, each database. The user creates a replication definition at a primary Replication Server, which controls the primary database containing the data to be published. The replication definition specifies information such as which columns are to be replicated, or in the case of a database replication definition, of the database objects to be replicated. The user creates a subscription at a replicate Replication Server, which controls the replicate database that will receive the information.

Replication Servers communicate with each other via user-defined routes. Most commonly, a primary Replication Server sends data to a replicate Replication Server through one or more routes set up to transmit data from the primary database to the replicate database. Users may also transmit stored procedures from the replicate to the primary to request updates of the primary data; in this case, data flows through one or more routes from the replicate Replication Server to the primary Replication Server.

Connections and routes define the structure of the replication system. They allow Replication Servers to send messages to each other and to send commands to databases. A connection transfers messages from a Replication Server to a database. A route transfers requests from a source Replication Server to a destination Replication Server.

RepServer Diagnostic tools

Diagnostic tools retrieve the status and statistics of Replication Server components which, depending on the type of problem, you can use to analyze the replication system. Check the troubleshooting section of the problem category for detailed information. This section summarizes the available diagnostic tools.
Use:

·        isql to log in to a Replication Server or data server to see if servers are up. You can also use isql to execute SQL commands to see if data is the same in the primary and replicate databases, or if data has been materialized or dematerialized.

·        admin who_is_down to find out which Replication Server threads are down.

·        admin who, sqm to display information, such as the number of duplicate transactions or the size of stable queues, about stable queues at a Replication Server.

·        admin who,sqt to display information, such as the number of open transactions, about stable queues at a Replication Server.

·        admin statistics,md to display information, such as the number of messages delivered, about messages delivered by a Replication Server.

·        sp_config_rep_agent to display the current RepAgent configuration settings.

·        sp_help_rep_agent to display static and dynamic information about a RepAgent thread.

·        sysadmin dump_queue to dump stable queues and view them.

·        rs_helproute to display the status of routes at a Replication Server.

·        rs_subcmp to compare a subscription's tables in the primary and replicate databases to make sure the tables are the same.

·        check subscription to display the status of subscriptions at a Replication Server.

·        rs_helppub to display publications.

·        rs_helppubsub to display publication subscriptions.

·        sp_setrepcol to check the replication status of text or image columns.

 Warm standby applications

In a warm standby application, Replication Server maintains a pair of Adaptive Servers (or SQL Servers), one of which acts as the backup of the other.

Typically, client applications update the active database while Replication Server maintains the other database as a standby copy of the active database. If the active database fails, or if you need to perform maintenance on the active data server or database, you can switch to the standby database (and back) with little interruption of client applications.

In a warm standby application, you create three connections:

·        A logical connection that Replication Server maps to the currently active database

·        A physical connection for the active database

·        A physical connection for the standby database

The logical database in a warm standby application may, with respect to other databases in the replication system, function as one of the following:

·        A database that does not participate in replication

·        A primary database

·        A replicate database

The procedure in this section demonstrates how to set up a warm standby system for a database that acts as a primary database in a replication system.

The below diagram illustrates a warm standby application operating on the BOSTON_DS data server for a pubs2 database on the NY_DS data server. The database is replicated to TOKYO_DS.
In this scenario the pubs2 database acts as a primary database in a replication environment. The primary pubs2 database for which a standby is created is called the active database.

Setting up a warm standby application

The following procedure is used to set up a warm standby application for an active database. In this procedure, an active database is already established. The procedure will be somewhat different if the active database has not yet been created. Make sure you review the warm standby information in the Replication Server Administration Guide before proceeding.
You must use Adaptive Server or SQL Server databases for this procedure.

1.1.1.1    In the active database:

1.     Mark the entire active database for replication to the standby database with the sp_reptostandby stored procedure.

sp_reptostandby enables replication of data manipulation language (DML) and supported data definition language (DDL) commands and stored procedures. Refer to Chapter 15, "Managing Warm Standby Applications," in the Replication Server Administration Guide for detailed information.

2.     Reconfigure RepAgent using the sp_config_rep_agent stored procedure with the send_warm_standby_xacts option. Restart RepAgent.

3.     Grant replication_role to the active database maintenance user.

4.     On the active data server, add the maintenance user of the standby database to the active database, and grant replication_role to the new maintenance user. This step ensures that the maintenance user ID exists in the standby database after the database is loaded (step 8).

5.     Log in to the Replication Server that is to manage the warm standby database, and create a logical connection for the active database, using the create logical connection command. The name of the logical connection must be the same as the name of the active database.

If you create the logical connection before you create the active database connection, use different names for the logical connection and the active database.

6.     On the standby data server, create the standby database with the same size as the active database.

7.     Use Sybase Central or rs_init to create the standby database connection. For more information, see the Replication Server online help and the Replication Server installation and configuration guide for your platform.

After the connection is created, log in to Replication Server and use the admin logical_status command to make sure that the new connection is "active."

8.     Initialize the standby database using dump and load without the rs_init "dump marker" option. (Or you can use bcp. Refer to the Replication Server Administration Guide for more information.)

1.     On the Replication Server, suspend the active database connection.

If you cannot suspend the active database, use dump and load with the rs_init "dump marker" option.

2.     On the active Adaptive Server, dump the active database.

3.     Load the active database dump into the standby database.

4.     On the standby Adaptive Server, put the standby database online.

9.     On the Replication Server, resume connections to the active and standby databases, using the resume connection command.

Check the logical status, using the admin logical_status command. Do not continue unless both active and standby databases are marked "active."

10.  Verify that modifications occur from active to standby database.

Using isql, update a record in the active database and then verify the update in the standby database.

Switching to the standby database

If it becomes necessary to switch from the active database to the standby database, you need to take steps to prevent client applications from executing transactions against or updating the active database. After the switch is complete, clients can connect to the new active database to continue their work. See "Switching clients to the new database" for details.
Before switching to a standby database, you should determine whether a switch is necessary:

·        Don't switch if the active data server is experiencing a transient failure. A transient failure is a failure from which the Adaptive Server recovers when restarted, without additional recovery steps.

·        Do switch if the active database will be unavailable for a long period of time.

You must use the switch active command to switch the active and standby databases. The following procedure illustrates how to switch the warm standby system illustrated in Figure 3-8 from the active database to the standby database.

1.     On the Replication Server, use switch active to switch processing to the standby database.

2.     Monitor progress of the switch. The switch is complete when the standby connection is active and the previously active connection is suspended.

1.     On the Replication Server, check the logical status, using the admin logical_status command.

2.     To follow the progress of the switch, check the last several entries in the Replication Server error log.

3.     Start the RepAgent for the new active database.

4.     Decide what you want to do with the old active database. You can:

1.     Bring the database online as the new standby database, and resume connections so that Replication Server can apply new transactions, or

2.     Drop the database connection using the drop connection command. You can add it again later as the new standby database.

o   Using isql, update a record in the new active database, and then check the update the new standby database.

Switching clients to the new database

Switching from the active to the standby database does not switch client applications to the new active data server and database. You must devise a method to handle client switching. For example, you could:

·        Set up two interfaces files, one for client applications and one for Replication Server. At switch time, modify the client interfaces file to point to the new active server.

·        Create an interfaces file entry with a symbolic data server name for use by client applications. At switch time, modify the address information associated with the symbolic name.

·        Use a mechanism, such as an intermediate Open Server, to map the client application data server connections to the currently active data server automatically.

 

 

 

 

2.     Standby applications

A warm standby application is a Replication Server application that maintains a pair of Adaptive Server or SQL Server databases, one of which functions as a standby copy of the other.
Client applications generally update the active database, while Replication Server maintains the standby database as a copy of the active database. Replication Server keeps the standby database consistent with the active database by replicating transactions retrieved from the active database transaction log.
If the active database fails, or if you need to perform maintenance on the active database or data server, you can switch to the standby database so that client applications can resume work with little interruption.
Figure 2-4 illustrates a warm standby system.

Figure 2-4: A warm standby system

The two databases in a warm standby application appear as a single logical database in the replication system. Depending on your application, this logical database may not participate in replication, or it may be a primary database or a replicate database with respect to other databases in the replication system.

1.2    Basic primary copy model

The basic primary copy model allows you to replicate data from a primary database to destination databases. This model is well suited to decision-support applications, although low-volume transaction-processing applications can update primary data remotely, either directly over the WAN or through request functions (replicated stored procedures). Primary data that is updated from remote sites can then be replicated back to subscribing sites.
You can implement the basic primary copy model by using any or all of the following:

·        Table replication definitions

·        Applied functions

·        Request functions

This section provides basic examples for using table replication definitions and applied functions. For examples of request functions and other, more advanced uses of the primary copy model, see "Model variations and strategies".

Using table replication definitions

Using table replication definitions allows you to replicate data from a primary source as read-only copies.
You can create one or many replication definitions for a primary table although a particular replicate table can subscribe to only one of them. See "Multiple replication definitions" for an example using multiple replication definitions.
You also can collect replication definitions in a publication and subscribe to all of them at one time with a publication subscription. See "Publications" for an example using publications.
For each table you want to replicate according to the basic primary copy model, you need to:

·        Set up routes and connections between Replication Servers.

·        Create the table you want to replicate in the primary database.

·        Create the table (or tables) to which you want to replicate in destination databases.

·        Create indexes and grant appropriate permissions on the tables.

1.2.1.1    At the primary site:

·        Mark the primary table for replication using the sp_setreptable system procedure.

·        Create one (or more) replication definitions for the table at the primary Replication Server.

1.2.1.2    At the replicate sites:

·        Create subscriptions for the table replication definitions at each replicate Replication Server.

See the Replication Server Administration Guide for details on setting up the basic primary copy model.
In Figure 3-1, a client application at the primary (Tokyo) site makes changes to the publishers table in the primary database. At the replicate (Sydney) site, the publishers table subscribes to the primary publishers table--for those rows where pub_id is equal to or greater than 1000.

Basic primary copy model using table replication definitions

Marking the table for  replication

This script marks the publishers table for replication.

-- Execute this script at Tokyo data server

 -- Marks publishers for replication

 sp_setreptable publishers, 'true' 

 go

 /* end of script */

Replication definition

This script creates a table replication definition for the publishers table at the primary Replication Server.

-- Execute this script at Tokyo replication server

 -- Creates replication definition pubs_rep

 create replication definition pubs_rep

 with primary at TOKYO_DS.pubs2

 with all tables named 'publishers'

 (pub_id char(4),

     pub_name varchar(40)

     city varchar(20)

     state varchar(2)

 primary key (pub_id)

 go

 /* end of script */

Subscription

This script creates a subscription for the replication definition defined at the primary Replication Server.

-- Execute this script at Sydney replication server

 -- Creates subscription pubs_sub

 Create subscription pubs_sub

 for pubs_rep 

 with replicate at SYDNEY_DS.pubs2

 where pub_id >= 1000

 go

 /* end of script */

.HTH.

Queries to get some important Sybase ASE Tables statistics..(Ver: 15.x/16.x)

To find the Top 100 tables RowCnt, Fragmentation, Space Utilization and Datachange% (in terms of #Rows)

select top 100  "Table"=left(name,32), "#Rows"=row_count(db_id(), id), "DataChanged%"=datachange(name, NULL, NULL), "Space_Utilization"=derived_stat(object_id(name),0, 'sput'), "Fragmentation"=derived_stat(object_id(name),0, 'dpcr') from sysobjects
where type='U'
order by 2 desc  

To Find all the indexes are not used in any Queries/Processes since last boot 
(This Query works only with MDA tables) :

select DatabaseName = convert(char(20), db_name()), TableName = convert(char(20), object_name(si.id, db_id())), IndexName = convert(char(20),si.name), IndexID = si.indid
from master..monOpenObjectActivity mo, sysindexes si
where mo.ObjectID =* si.id
and mo.IndexID =* si.indid
and (mo.UsedCount = 0 or mo.UsedCount is NULL)
and si.indid > 0
and si.id > 99 -- No system tables
order by 2, 4 asc

To find the Top 21 tables (in terms of #Indexes)

SELECT top 21 left(o.name,32), count(i.indid)
FROM sysobjects o, sysindexes i
where o.id = i.id
group by o.id
order by 2 desc

To find all the tables with LockScheme:

select DatabaseName = convert(char(20), db_name()), "TableName"=left(name, 32), row_count(db_id(),id) as "#Rows", 
"lock_scheme" = case when (sysstat2 & 57334) < 8193 then "All Pages" 
when (sysstat2 & 57334) = 163848193 then "Data Pages" 
else "Data Rows" end
from sysobjects where type='U' order by 2 desc

To find all the Tables and their Triggers and Status.

select convert(varchar(30), name) as TableName,

convert(varchar(30), isnull(object_name(instrig), '')) as InsertTriggerName,

case sysstat2 & 1048576 when 0 then 'enabled' else 'disabled' end as InsertTriggerStatus,

convert(varchar(30), isnull(object_name(updtrig), '')) as UpdateTriggerName,

case sysstat2 & 4194304  when 0 then 'enabled' else 'disabled' end as UpdateTriggerStatus,

convert(varchar(30), isnull(object_name(deltrig), '')) as DeleteTriggerName,

case sysstat2 & 2097152  when 0 then 'enabled' else 'disabled' end as DeleteTriggerStatus

from sysobjects where type='U' and (instrig<>0 or updtrig<>0 or deltrig<>0)

order by name

To find the HostName and Server Details 

select left(address_info,32) as "Server's Host Name & PortNumber"

      ,host_name() as "Client Host Name"
      ,@@servername as "ASE Server Name"
      ,net_type as "N/W Protocol"


from master..syslisteners

.

SAP HANA for Sybase DBAs

Transform yourself to the better career keeping your core alive..

Sybase ASE, which stands for Adaptive Server Enterprise, was Sybase’s enterprise-class database for transactional applications which brought revolutionary with its Architecture of 1 Server multiple databases, ease of access and Robust database management with optimal performance at minimal TOC. Sybase ASE became widely popular in the finance industy, but was only able to run SAP applications with the ASE verison 15.7 released in Sep' 2011. In April' 2014, SAP released ASE 16 (dropping Sybase Name) and SAP is planning for ASE to be the preferred database for running transactional applications in SAP’s Real-Time Data Platform, a collection of data management tools that SAP is integrating to provide very fast performance.

SAP (Systems, Applications & Products in Data Processing) brought revolution of data management with HANA (The SAP HANA® platform can increase analysis speed by more than 10,000x) which is capable to handle columnar data or COLUMN-STORE (Sybase ASIQ is pioneer, which SAP IQ software holds the Guinness World Record for loading, storing, and analyzing Big Data at 34.4 terabytes per hour) and ROW-STORE data (Sybase ASE is one of the top RDBMSs). It also encapsulates the InMemoryDatabases (SAP HANA is an in-memory database, is a combination of hardware and software made to process massive real time data using In-Memory computing)  and PhysicalDatabases dynamically to handle the data effectively. While SAP HANA can support transaction and analytic processing on the same data, not every business application stands to benefit greatly enough from up-to-the-moment analysis and ad-hoc queries to justify the expense of running it on HANA. SAP plans for ASE to be a less-expensive alternative to HANA for such cases. At the same time, SAP plans to make data in ASE easily accessible to HANA and vice versa, easing integration for companies with a mixed landscape.

SAP is including a license for Sybase ASE with SAP ERP on HANA, indicating that SAP has plans for these two databases to work together. SAP is also planning to make ASE a transitional database between a standard disk-based database and SAP HANA. SAP is working toward making migration from ASE to HANA very smooth using the Sybase Replication Server. For those planning to use HANA as a database in the future, SAP recommends moving to ASE in the short term to make for an easy transition to HANA in the long term.

SAP HANA Architecture

The SAP HANA database is developed in C++ and runs on SUSE Linux Enterpise Server. SAP HANA database consists of multiple servers and the most important component is the Index Server. SAP HANA database consists of Index Server, Name Server, Statistics Server, Preprocessor Server and XS Engine. I'm providing herewith very high-level info, so as to make yourself comfortable and get ready to reach new level with SAP-HANA. Start exploring and learning the future's Database..

Index Server: (like the DATA SERVER in ASE)

• Index server is the main SAP HANA database component
• It contains the actual data stores and the engines for processing the data.
• The index server processes incoming SQL or MDX statements in the context of  authenticated sessions and transactions.

Persistence Layer: 

The database persistence layer is responsible for durability and atomicity of transactions. It ensures that the database can be restored to the most recent committed state after a restart and that transactions are either completely executed or completely undone. 

Preprocessor Server: 

The index server uses the preprocessor server for analyzing text data and extracting the information on which the text search capabilities are based. 

Name Server: 

The name server owns the information about the topology of SAP HANA system. In a distributed system, the name server knows where the components are running and which data is located on which server. 

Statistic Server: 

The statistics server collects information about status, performance and resource consumption from the other servers in the system.. The statistics server also provides a history of measurement data for further analysis. 

Session and Transaction Manager: 

The Transaction manager coordinates database transactions, and keeps track of running and closed transactions. When a transaction is committed or rolled back, the transaction manager informs the involved storage engines about this event so they can execute necessary actions. 

XS Engine: 

XS Engine is an optional component. Using XS Engine clients can connect to SAP HANA database to fetch data via HTTP. 

Source : SAP website and various SAP GURUs (Thanks much to ALL)