Posts Tagged ‘ASE’

Big Data tools cost too much, do too little:SHOCKING REVELATION!!!

March 3rd, 2013 No comments


Big data is a necessity at scale: if you’re trying to listen to every transatlantic phonecall, you need to use MapReduce. … if you need to search the entire internet in milliseconds you need to use MapReduce, if you need to run the largest social network in the world you need to use MapReduce. If you don’t you can probably scale with a database.

Full Story @


How to lock last login with “sa_role” role

February 24th, 2013 No comments

By default ASE doesn’t allow  to lock last unlocked login that have sa_role/sso_role role. However, it can be locked if a role has explicit password set to enable the role while login.

If a role is altered to have a limitation on failed attempts, and a login attempts to enable the role and fails the required number of times, the role is locked for all holders of the role.  Likewise, since we can’t explicitly lock the last unlocked login with sa_role or sso_role, it is possible for failed login attempts to indirectly lock that login.

What is SAP HANA?

February 18th, 2013 No comments

SAP HANA can be defined as an appliance that combines SAP software components optimized on hardware provided by SAP’s leading hardware partners.  It comes in a bundle of softwares along with the hardware (servers). SAP HANA servers are sold in “t-shirt” sizes ranging from Extra-Small (128GB RAM) all the way up to Extra Large (>2TB RAM) with multicore CPUs.

SAP HANA is both a database (in the traditional sense) and a database platform (in the modern sense).

In its current form SAP HANA can be used for four basic types of use case:

1. Agile Data Mart (Stand-alone database for reporting)

2. SAP business suite accelerator (secondary database for SAP business suite for reporting, calculation and analysis purpose)

3. A primary database for SAP Netweaver warehouse.

4. A development platform for new applications


Source: SAP

Categories: ASE, Database, Developement, HANA, SAP Tags: , , ,

Column Based Search

February 17th, 2013 No comments

To store a table in memory, two option exists: 1. Row based storage and 2. Column based storage.

In row based storage, A table is stored as a sequence of records, i.e. one full row in a data page/or consecutive data pages. It means all columns values of a table stored sequentially per row.

In column based storage, column values of a column are stoger in contiguous memory location.

Advantages of column based table storage in following circumstances

1. Calculations are typically executed on a single column or few columns only.

2. Table is searched based on values of a few columns.

3. Table has large number of columns.

4. Table has large number of rows, so that columnar operations are required (aggregate, scan etc)

5. High compression rates can be achieved because the majority of the  columns contain only few distinct values (compared to the number of rows)

Advantages of row based storage in following circumstances

1. The application needs to only process a single record at one time. (This applies to many selects and or updates of single record)

2. The application typically needs to access a complete record (or row)

3. The columns contain primarily distinct values so that compression rate would be low.

4. Neither aggregations nor fast searching is required.

5. The table has small number of rows (e.g. configuration tables)


Source: SAP

How to handle the database objects security in database through stored procedures

February 13th, 2013 No comments

Case: An user X owns a stored procedure that access a table owned by another user Y, and that table has a trigger that updates another table owned by user Z

Clumsy Solution –> Grant explicit permissions to user X on table1 and table2, similarly grant execute permissions and table access to users Y and Z. In a production environment think of many users available in a database and thousands of objects, it is not possible.

Good Solution –> Create all objects with dbo user and provide execute permission ONLY on stored procedures to user X, thus when user would execute the stored procedure, SP would be able to modify the table1 and trigger on table1 would be able to modify tabl2, though explicit access on tables have not been granted to user X. This is how stored procedure maintain the security features. User X shall not be able to access table1 and table2 explicity but stored procedure run by user X can modify those tables.

This is how objects gets created in production environment.

SAP SYbase ASE, SAP Sybase IQ and SAP HANA – In a snapshot

February 3rd, 2013 No comments
High performance, reliable, scalable and resource-efficient OLTP database. Heavily optimize ASE for virtualized and cloud infrastructure  


Aims to provide maximum performance and efficiency when running both OLTP and OLAP workloads on the SAME DATA and at the SAME TIME
Capability to handle transactional workloads. It is good to chose in case of budgetary constraints.   In- memory database, very high capability to handle transactional workloads.
SAP Sybase ASE is planned to be the preferred transactional database in the SAP Real-Time Data Platform for running SAP applications, SAP partner applications and custom applications


  SAP HANA is planned to be the preferred database in the SAP Real-Time Data Platform for applications that need to run analytic and transactional workload on the same data at the same time


  Sybase IQ is planned to focus on evolving columnar compression capabilities for effectively storing and processing data


SAP HANA is planned to leverage IP from Sybase IQ, such as in-database algorithms, Hadoop integration, index optimizations, and ELT capabilities for performing transforms in the Hana database


  Plan to leverage Sybase IQ as near-line storage for SAP BW as well as leverage the technology in SAP ILM portfolio for archiving from the Business Suite Plan to leverage SAP HANA as database for native applications, SAP BW, as well as the Business Suite
  High-performance analytics server planned to deliver cost effectively for large volume data scenarios


 Open platform for large scale data warehouse

 Analytical data mart for aged data in a BW+HANA+IQ scenario

 Cost effective data management scaling to extreme data volumes






Real-time platform for analytics and applications that simplified data management approaches to deliver real-time results


 Operational / agile data mart for real-time scenarios

 Analytical data mart for complex real-time calculations

 Database for BW powered by SAP HANA




SAP Hana With Free Sybase Database

January 17th, 2013 No comments

“Customers could choose to deploy ASE or Hana in whatever mix of configurations they want,” explained Prakash Darji, SAP’s global VP for data warehouse solutions & SAP Hana platform. SAP customers could use Hana to accelerate certain Business Suite processes while leaving other processes on ASE”

Source :

Categories: ASE, Database Tags: , , ,

ASE (ASE 15.7 ESD#3) Released & Its New Features !

December 24th, 2012 3 comments

New features of ESD#2 and ESD#3 here:

  • Fixes: With ESD#3 you get all of the fixes for database issues that have been made available since the initial release of release 15.7.
  • In-row LOB compression: ASE can store LOB data in-row or off-row (on special LOB pages). Data compression was introduced with ASE 15.7, but in-row LOBs have not been compressed. With the new version you will get in-row LOB compression. For some SAP tables this can make a big difference. For an example, please have a look at this blog from Hüseyin Bilgen who shares his experiences.
  • Hash-based statistics: This feature helps speed up the updating of index statistics.
  • Plan sharing: In the past a database query that was executed more than once in parallel would result in multiple query plans which could be different (depending on the values provided at compilation time). When you are running ESD#3, plans for queries running in parallel are cloned, saving the cost of recompilation and ensuring that the queries use the same plan.
  • Enhanced monitoring: You will see more metrics on several screens of the DBA Cockpit. Since we are using early versions of the ESDs in our development systems, we have already been able to include some of these in the DBA Cockpit versions released during summer 2012 (such as SAP_BASIS 7.02 SP12). For example: enhanced SQL statement statistics, spinlock monitoring, separation of I/O time in read time and write time… There are also new monitoring features that are going to be used in future versions of the DBA Cockpit. One example is the new history of database and transaction dumps.
  • Talking about database and transaction dumps: There are several new features here that will make your life as a DBA easier. You can now run the dump transaction while a dump database is running. There is a new tool ‘sybrestore’ available with ESD#3 that will help you to restore the database when needed. We already updated our backup and recovery SAP Notes when ESD#2 was released to make use of the new features.
  • Reorg rebuild can be run online. I don’t think I need to say more.
  • The maximum size of a database is now 64TB. Just in case your compressed database was close to the 32TB limit before…
  • Asynchronous DB initialization: If you are expanding your database, the database is now available immediately and initialization is done in the background.

Source :

Categories: ASE, Database, SAP Tags: , , ,

Replication Server Interview Questions

December 20th, 2012 No comments

Came across below Replication Server Questions, might help you for yours interview preparation. It also includes few ASE questions.
Also Updated the Interview Question Page.

1. What is the difference between MSA and Warm standby ?

2. How can we perform fail over in Case of Warm Stand by and MSA?

3. What will happen, if the queue has data in case of fail over to warm standby?

4. For standby point of view which one is better warm standby or MSA?

5. How can we sync the warm standby database ? If there is some data in queue, what will you do?

6. What is the config parameter you can use to tune a rep agent?

7. Suppose there are 20,000 trans which are getting fail and you need to skip all these trans using error class. Please let me know high level overview to setup
error class?

8. How the multiple replication server can improve the performance as compare to single replication server ?

9. Suppose the query is running slow , how will you investigate?

10. Suppose tempdb log segment is full, how will you troubleshoot?

11. Suppose for a very big database , backup takes 4 hrs to complete . During the backup, due to heavy activity from user log is full, after 2 hours ? What will you do now?

12. How can we guess, in how much time queue will be drain?

13. Suppose queue is getting full, what can be point of bottleneck and why?

14. Suppose log for a database is getting full, how will you trouble shoot? Why log increase is not a better idea? ( Server version is below 15.7)

15. How will you check the latency and what is the latency ?

16. Suppose you want to sync a table in a warm stand replication , How will you do?

17. What are the various factor which can effect rep agent performance?

18. How can you make sure rep agent is running fine ( means scanning is going well in Primary site)?

19. How can we see the number of commands in a transaction in queue?

20. Suppose you have 5000 locks at primary side , and your one delete statement ( which is effecting 7000 rows)went fine. Now in case of Replicate dataserver ( has same number of locks), you are getting out of lock situation? Why it was successful at primary side , what it did not go well in replicate side, despite of primary and replicate have same number of locks?

21. What are the situations when we require to drain the queues?

22. In unix , suppose one dba modifies some of files including dataserver binary to non sybase user, and you want to fix this situation asap? What you will do?

23. Suppose there is thread down issue in replication for target side, and you fetched that its down due to duplicate row? How can we make sure , is it due to duplicate row or duplicate key?

SAP D&T Academy Video – How to Create/Add a Disk Partition in SAP Sybase Replication Server?

December 20th, 2012 No comments