Archive for July, 2012

ASE 15 Migration Study: Why you should handle prepared statements with great care.

July 30th, 2012 No comments

I have been involved for the past two months in analyzing migration problems of two large local ASE sites.  I decided to share with you the things discovered during the failed ASE 15 migration analysis so that if you happen to be in a similar situation you may discover the way out with less pains.

For these customers, migration to ASE 15.0- ASE 15.5 has been a painful fiasco for two consecutive years.  Cases have been opened.   Professional Services have been sent on site.  A lot of work have been done on rethinking and rewriting code for the new optimizer whims.  Tears, money, and what not shed all through the process.

The truth is, Sybase TS has been telling us  for years that we have bad code, and we – as customers or support teams – were each time infuriated by the insolence of telling us this.  I cannot say that TS has been completely wrong. I can say, however, that were we thinking more WHAT is so peculiar about our code rather than WHY are we told that our code sucks we might have spared ourselves a lot of pain.

I will not write you a detailed report on what we have found here in the blog pages – it will require a lot of pyrotechnics to make things legible here.  Rather, I attach you the report of the study.  You may download it and read at your leisure.   I think it is worth the pains.  Who knows, may be it will solve migration problems for more customers out there.  Local customers were not SO peculiar after all.

Here is the link:  Migration to ASE 15 – 2 Case Studies Involving Prepared Statements.

For those who have little time reading this, let me just warn:  if you use prepared statements in your application code – awares or unawares – beware.  You may be paying very high penalty for this.  Especially in ASE 15 that has been made to work fast – sometimes very fast.  The penalty may be so high that you will consistently fail migrating your old ASE 12.5.x servers to ASE 15 without knowing that the solution is so close.

Here a preview of some data:

Have fun reading this.  I have had a lot of fun digging up the roots of the failed migrations (using my own tools, to be sure, and writing new ones along the way).

If you have any questions – be my guest.



Optimization Goals

July 22nd, 2012 No comments

Query — How to set the optimization Goals?

Answer — Optimization goals allow you to choose an optimization strategy that best fits your query environment:

• allrows_mix – the default goal, and the most useful goal in a mixed-query environment. allows_mix balances the needs of OLTP and DSS query environments.
• allrows_dss – the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.
• allrows_oltp – the optimizer considers only nested-loop joins.

At the server level, use sp_configure. For example:sp_configure “o

ptimization goal”, 0, “allrows_mix”
At the session level, use set plan optgoal. For example:

set plan optgoal allrows_dss

At the query level, use a select or other DML command. For example:
select * from A order by A.a plan “(use optgoal allrows_dss)”
In general, you can set query-level optimization goals using select, update, and delete statements. However, you cannot set query-level optimization goals in pure insert statements, although you can set optimization goals in insert…select

Courtesy: Sybooks

Categories: ASE Tags: , ,

Query Processing and Abstract plan of stored procedure

July 22nd, 2012 No comments

Query — When does the optimization of statements in a stored procedure transpired, at compile time or at run time?

Answer — ASE 15.0.2 and later defers the query optimization of stored procedures until it execute the statement.


Query — Why does stored procedure runs slower at first execute?

Answer — Stored procedure runs slower at first execute because it performs the optimization of query and creates query plan and stores the query plan in cache. Hence, In subsequent  run it picks up the existing query plan from the cache and runs faster.


Query — What is the difference between stored procedure’s query plans of ASE 12.5.0 and ASE 15.0.2/later?

Answer — ASE versions before 15.0.2 used to create query plan of stored procedure at compile time wherein values of variables were not available, thereby sometimes stored procedure would not run as expected.

However, in versions 15.0.2 and later,execution engine step has been segregated into 2 steps 1. Procedural execution engine and 2. Query execution engine

The procedural engine executes command statements such as create table, execute procedure, and declare cursor directly. For data manipulation language (DML) statements, such as select, insert, delete, and update, the engine sets up the execution environment for all query plans and calls the query execution engine.

The query execution engine executes the ordered steps specified in the query plan provided by the code generator, which have the values of variables used in stored procedure. Hence, now query plans of stored procedure are more accurate than query plans of stored procedure in previous versions.

Courtesy: Sybooks

Physical read and Logical read

July 22nd, 2012 No comments

Query —  What is the difference between Physical and logical reads?

Answer — Physical reads are defined as pages read from the disk.

Logical reads are defined as pages read from the main memory (RAM/cache).

Index coverage

July 22nd, 2012 No comments

Query — What is  “Index Coverage of the query”?

Answer — It is defined as whether the query can be satisfied by retrieving data from the index pages without accessing the data pages . ASE can use indexex that covers query, even if no where clause are included in the query.


Query —  Can a query use index even if no where clause are included in the query?

Answer — Yes, as described above ASE can use indexes that covers query, even if no where clause are included in the query.


Query processing and Abstract Plans

July 22nd, 2012 No comments

Query –> What are the 6 modules of query processor?

Answer –>

1. Parser

2. Normalization

3. Preprocessor

4. Optimizer

5. Code generator

6. (1) Procedural execution engine

6. (2) Query execution engine

Query   –> At which level in Query Processor Modules we can determine if the statement may benefit from using the statement cache?

Answer  –> Level 2 (Normalization)


Query  –> What does Normalization infers while query parsing (Query Processor Modules) ?

Answer  –> Normalization involves determining column and table names, transforming the query tree into conjugate normal form (CNF), and resolving datatypes.

Query –> Define “Query Plan” ?

Answer –> Query Plan consists of  retrieval tactics and an ordered set of execution steps, which retrieve the data needed by the query.

Courtesy: sybooks

Categories: ASE Tags: , , ,

Freudenberg IT Creates a Center of Excellence for SAP HANA® and SAP® Sybase ASE

July 11th, 2012 No comments

Freudenberg IT (FIT) announced today the creation of its Advanced Databases Center of Excellence (COE). The Advanced Databases COE will allow customers to leverage the business advantages of the latest SAP® database platforms, including SAP HANA® and SAP® Sybase Adaptive Server® Enterprise (SAP Sybase ASE),while minimizing the cost, risk, and complexity of a database platform change.
Read more:

Categories: ASE, HANA, News, SAP Tags: , , , ,

Bhavnagar Energy Company Limited Selects SAP Sybase ASE

July 8th, 2012 No comments

SAP AG has announced that Bhavnagar Energy Company Limited (BECL) has selected SAP Enterprise Resource Planning (ERP) running on Sybase Adaptive Server Enterprise (ASE). BECL will get a streamlined end-to-end experience, from integrated product experience to customer service, support and lifecycle management with SAP Business Suite application on Sybase ASE.

Full story @


Categories: ASE, News, SAP Tags: , , ,