Archive

Archive for the ‘Database’ Category

Indian Railways Running on SAP Sybase ASE

August 23rd, 2012 No comments

Indian Railways: Centralizing Ticketing

 

Discover how Asia’s largest rail network is efficiently managing 17 million daily passengers across 6,000 stations with computerized ticketing. With Sybase database technology, Indian Railways is enhancing customer service with 24/7 ticketing and transaction times of less than 20 seconds per ticket.*

Full Story ::  http://www.sap.com/solutions/technology/database/customer-reviews.epx

Nested Transaction

August 12th, 2012 No comments

NOTE: There must be at least one commit tran executed for each tran statement executed. But, regardless of how many begin tran/commit tran pairs appear to be nested in the code, there is only one transaction.

 

Example:

begin tran

change 1

begin  tran

change 2

commit tran

commit tran

In above pseudocode, there are two nested begin tran statement. Despite, the fact that it appears that there are two transactions (one inside another), there is actually only one open transaction. Thus, the transaction is active until the second commit tran statement is executed.

 

Example:

begin tran

change 1

begin tran

change 2

begin tran

change 3

commit tran

change 4

commit tran

change 5

rollback tran

In this example, none of the five changes would remain in the data after the rollback; again, although it looks like there are three levels of nesting, and it looks like changes 2,3,4, and 5 are fully bounded by begin/commit pairs, there is really only one transaction, and all work must complete in full or not at all. In fact, the transaction shown above is functionally identical to:

begin tran

change 1

change 2

change 3

change 4

change 5

rollback tran

chained and unchained transaction mode

August 12th, 2012 No comments

A chained mode transaction may be thought of as “auto-starting”; when you log in and begin any SQL statement, ASE invisibly begins a transaction which  will continue until you explicitly end it. In this way, you are always making all changes as part of a transaction.

 

In unchained mode, the rule shift in an important manner: while any single data change statement is treated as a transaction, group of statements are not transactions without an explicit command to begin a transaction.

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.

Cheerfully,

A.T.M.

Performance Issue

June 24th, 2012 No comments

Few days back I have faced performance issue in one of our prod data server. I would like to share here.

User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.

On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.

We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.

The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.

select top 10  s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC

http://sybaseblog.com/sybasewiki/index.php?title=Query_-_Which_currently_executing_queries_are_consuming_the_most_CPU_%3F

We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were  taking maximum cpu . As they requested us to kill, we aborted/killed from data server.

After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.

Application batch of 10K inserts moved very quickly and finally issue resolved.

You can get full details on MDA queries @ http://sybaseblog.com/sybasewiki/index.php?title=Category:MDA_Table_Query

Thanks.

SAP Becomes Fastest-Growing Vendor in Relational Database Market

June 8th, 2012 No comments

WALLDORF, Germany, June 7, 2012 /PRNewswire/ — SAP AG (NYSE: SAP) today announced it is the fastest-growing among the top vendors in the relational database management systems (RDBMS) market, according to a new report(1) from IDC.

 

Source & Complete  Report @http://www.itnewsonline.com

Categories: ASE, Database, News, SAP, Start Sybase Tags: , , ,

Changing ASE Sort Order using sqllocres

June 6th, 2012 No comments
Task : Sort Order change of PROD_ASE_DS2 
Current Sort Order :50 Binary Sort Order (Server Default Sort Order)
New Sort Order : 51 Dictionary Based Sort Order
 
Before running any command , please import Sybase Enviorment in your current Shell, you can get the same by executing SYBASE.sh which resides in $SYBASE ( Sybase Installation Directory).
 
1. Started the Dataserver PROD_ASE_DS2 :
[sybase@localhost install]$ ./startserver -f RUN_PROD_ASE_DS2 ……………….. …………………… 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default unicode sort order is ‘binary’. 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default sort order is: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘bin_iso_1’ (ID = 50) 00:00:00000:00001:2012/06/06 00:26:06.61 server on top of default character set: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘iso_1’ (ID = 1). 00:00:00000:00001:2012/06/06 00:26:06.61 server Master device size: 30 megabytes, or 15360 virtual pages. (A virtual page is 2048 bytes.)

2. Checking the Current Sort Order of Server

[sybase@localhost install]$ isql -Usa -SPROD_ASE_DS2 -w999
Password:
1> select @@servername,getdate()
2>
3> go
———————————————————— ————————–
PROD_ASE_DS2 Jun 6 2012 12:26AM
(1 row affected)
1> sp_helpsort
2> go
Sort Order Description
————————————————————————————————————————————
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) – Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
Characters, in Order
————————————————————————————————————————————
! ” # $ % & ‘ ( ) * + , – . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~ Â
¡ ¢ £ ¤ Â¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ Ã
à à à Ã
à à à à à à à à à à à à à à à Ã
à à à à à à à à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ
(return status = 0)
1> exit

3. Moving in Sample Resource file Directory Read more…

SAP’s Hasso Plattner : Father of SAP’s In Memory Technology HANA

May 30th, 2012 No comments

Sir Hasso Plattner is a cofounder of software giant SAP AG. Today he is Chairman of the Supervisory Board of SAP AG.

Plattner founded the Hasso Plattner Institute for software systems engineering based at the University of Potsdam, and in Palo Alto, California, its only source of funding being the non-profit Hasso Plattner Foundation for Software Systems Engineering.

You can see his Book : In-Memory Data Management An Inflection Point
for Enterprise Applications

You can read more about him and his book @ http://no-disk.com/

Sir Hasso’s Interview :
[youtube http://www.youtube.com/watch?v=W6S5hrPNr1E]

Finally SAP’s EVP of Database Technology Platform steve’s views on SAP Database Technology Roadmap.

See the Future of Sybase ASE with SAP Real time Data Platform:
[youtube http://www.youtube.com/watch?v=OReE_qu8zmI]

Happy Learning SAP Sybase !!

Source : Google.com, wikipedia,sap.com,youtube.com,no-disk.com