http://goo.gl/EmKxy0

Archive

Archive for the ‘SAP’ Category

Understanding Statistics and its affect on Query Performance

December 21st, 2014 No comments

    Query Processing and its performance is the heart of any RDBMS.  SAP ASE uses cost based optimizer for query processing and optimization means it creates the different access path (query plan) for a query and it picks up best plan which has least cost for a given query.  This query plan cost  is calculated from the data distribution and statistics  information of a table and its columns.

There are two types of statistics –

Object Level Stats :  The object level statistics describe a table and its indexes and include values such as number of rows and pages in the table and/or index (es), the number of empty pages, and the cluster ratios among others.  Some of the object level statistics are updated automatically by ASE, others when ‘update statistics’ is run.

Column Level Stats : The column level statistics describe the distribution of values in the column; they consist of the column’s histogram and density values and are updated when an index is created or an ‘update statistics’ command is run.

System Tables which store these stats:

The systabstats and sysstatistics tables store statistics for all tables, indexes, and any un-indexed columns for which you have explicitly created statistics.

systabstats stores information about the table or index as an object, and is updated by query processing, data definition language, and update statistics commands.  Simply it stores object level of statistics.These statistics include:

  • Number of data pages for a table, or the number of leaf level pages for an index.
  • Number of rows in the table
  • Height of the index
  • Average length of data rows and leaf rows
  • Number of forwarded and deleted rows
  • Number of empty pages
  • Statistics to increase the accuracy of I/O cost estimates, including cluster ratios, the number of pages that share an extent with an allocation page, and the number of OAM and allocation pages used for the object
  • Stopping points for the reorg command so that it can resume processing
  •  systabstats contains one row for each clustered index, one row for each nonclustered index, one row for each table without a clustered index, and one row for each partition.
    •  The storage for clustered index information depends on the locking scheme for the table:
    •  For data-only-locked tables, systabstats stores an additional row for a clustered index.
    • For allpages-locked tables, the data pages are treated as the leaf level of the index, so the systabstats entry for a clustered index is stored in the same row as the table data.The indid column for clustered indexes on allpages-locked tables is always 1.

 sysstatistics table stores one or more rows for each indexed column on a user table; it also stores statistics for unindexed columns.

  •  The first row for each column stores basic statistics about the column, such as the density for joins and search arguments, the selectivity for some operators, and the number of steps stored in the histogram for the column.
  • If the index has multiple columns, or if you specify multiple columns when you generate statistics for unindexed columns, there is a row for each prefix subset of columns.
  • Additional rows store histogram data for the leading column. Histograms do not exist if indexes were created before any data was inserted into a table. To generate a histogram, run update statistics after inserting data.

Stay Tuned ! We will talk about more in coming posting!

SAP ASE 16 – Design for Extreme Transaction Processing

April 7th, 2014 No comments

SAP ASE 16 Launched Recently : Lot of New Features and Extreme Transaction Ready

SAP ASE 16 provides scalability and speed to support higher throughput and lower latency; security to ensure data privacy and system auditability; and, simplicity of database operations to maximize operational efficiency and lower costs.

Scalability, speed, security, simplicity…these were the guiding principles for our engineers. We’ve increased scalability and speed with extensive optimization in its transaction concurrency management, query plan execution, data compression and utilization of computing resources in large SMP servers. Security enforcement and system auditability have been augmented to provide customers more flexibility to adapt to their specific regulatory compliance needs. And SAP Control Center delivers simplified database management helping to reduce overall cost of ownership.

SAP ASE 16 Overview white paper

SAP SCN

Configuring sqsh – Interactive database shell- replacement for isql

January 7th, 2014 No comments

Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for the venerable ‘isql’ program supplied by Sybase.

Sqsh is much more than a nice prompt, it is intended to provide much of the functionality provided by a good shell, such as variables, aliasing, redirection, pipes,back-grounding, job control, history, command substitu-tion, and dynamic configuration. Also, as a by-product of the design, it is remarkably easy to extend and add functionality.

Sqsh was developped by Scott C. Gray, and is currently maintained by Michael Peppler (mpeppler@peppler.org) and also by Martin Wesdorp (mwesdorp@users.sourceforge.net).

You can download sqsh from Source Forge.

Sqsh is held under the GNU General Public License (GPL) and therefore may be freely distributed under the terms of this license.

Basic instructions for Setup :

1. Download the sqsh from here.
sqsh-1

2. Extract  the sqsh tar :
sqsh-2
3. After extract , you will have following directories :
sqsh-3
4.  Move to sqsh directory and create the installation dir with permission to sybase user (in my case installation dir /opt/sqsh). After that execute ./configure –prefix=”/opt/sqsh”sqsh-4
5. Run the make in same dir as below sqsh-5
6. Now finally run the make install sqsh-6
7. Install the manual pages for sqsh as
sqsh-7
8. Move to /opt/sqsh/bin and try to connect data server using sqsh
[sybase@LinuxServer ~]$ cd /opt/sqsh
[sybase@LinuxServer sqsh]$ cd bin
[sybase@LinuxServer bin]$ pwd
/opt/sqsh/bin
[sybase@LinuxServer bin]$ ls -ltr
total 588
-rwxr-xr-x. 1 sybase sybase 599071 Jan  7 15:51 sqsh
[sybase@LinuxServer bin]$ sqsh -v
sqsh-2.4
[sybase@LinuxServer bin]$ sqsh -U sa -S LINUX_PROD
sqsh-2.4 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2013 Michael Peppler and Martin Wesdorp
This is free software with ABSOLUTELY NO WARRANTY
For more information type ‘\warranty’
Password:
[4] LINUX_PROD.master.1> select @@servername,getdate();
 ———————————————————— ——————-
 LINUX_PROD                                                   Jan  7 2014  3:58PM

(1 row affected)
[5] LINUX_PROD.master.1>

You can configure sqsh prompt using /opt/sqsh/etc/sqshrc.  Like in sqshrc file I changed my prompt as ;
#\set prompt='[$histnum] ${DSQUERY}.${database}.${lineno}> ‘
\set prompt=’${DSQUERY}.${database}.${lineno}> ‘

Now see the prompt of sqsh with history , also I suppressed the banner message using -b
[sybase@LinuxServer bin]$ sqsh -b -SLINUX_PROD -Usa
Password:
LINUX_PROD.master.1> select @@servername,getdate()
LINUX_PROD.master.2> go
———————————————————— ——————-
LINUX_PROD                                                   Jan  7 2014  4:06PM
(1 row affected)
LINUX_PROD.master.1>

Link from Rob Tricks with sqsh & Manual Page for sqsh

Isolation Levels by example

December 28th, 2013 No comments

For Earlier post on Isolation Level please refer here.

Understanding Isolation Level “1” : Avoids Dirty Reads (Default isolation level for ASE)

Transaction T1 (Session 1)modifies a data item. Another transaction T2 (Session 2)then reads that data item before T1 performs a COMMIT or ROLLBACK. If T1 then performs a ROLLBACK, T2 has read a data item that was never committed and so never really existed.

 Session1  Session 2   Remarks
1> select @@isolation “Isolation Level”
2>go
 Isolation Level
 —————
               1
(1 row affected)
1> insert into pmtmaster values(1,100)
2> go
1> begin tran
2> update pmtmaster set id2=200 where id1=1
3> go
1>
   

In Session1, updating the row with id2=1
 
 1> print “Session 2″
2> go               
Session 2           
1> select @@isolation “Isolation Level”
2> go
 Isolation Level
 —————
               1
(1 row affected)
1> select * from pmtmaster where id1=1
2> go
^C^C
[CanCan]
 In Isolation Level 1, that is default mode, we can not read dirty data as it is still not committed by other tran.
     So Isolation Level 1, avoids dirty reads.

Understanding Isolation Level “0”

Session1 Session 2 Remarks
1> select @@isolation “Isolation Level”
2>go
Isolation Level
—————
1
(1 row affected)
1> insert into pmtmaster values(1,100)
2> go
1> begin tran
2> update pmtmaster set id2=200 where id1=1
3> go
1>
   Same as Above , In Session1, updating the row with id2=1
 
1> print “Session 2″2> goSession 2
1> set transaction isolation level 0
2> go
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
0
(1 row affected)
1> begin tran
2> select * from pmtmaster where id1=1
2> go
id1         id2
———– ———–
1         200
(1 row affected)
 
 Now with isolation level 0 , I am trying to read data and it is allowing dirty reads.
 rollback    If Session 1  rollbacks, session will have inconsistent.

Understanding Isolation Level “2” : Avoid Repeatable Reads

What is Repeatable Reads?

Transaction T1 (session 1) reads a data item. Another transaction T2 (session 2) then modifies or
deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified                                      value or discovers  that the data item has been deleted.

Session1 Session 2 Remarks
 

1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
1
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 Transaction T1 (session 1) reads a data item.
 Session1 Continues..  

 1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
1> commit
2> go
 Another transaction T2 (session 2) then modifies.
 1> select * from pmtmaster where id2=200
2> go
 id1         id2
 ———– ———–
           1         200
(1 row affected)
 
 If T1 then attempts to reread the data item, it receives a modified value/different result set in same transaction. This is issue in repeatable reads. Lets review how can we avoid it.

How to avoid Repeatable Reads?

 

1> set transaction isolation level 2
2> go
1> select @@isolation “Isolation Level”
2> go
 Isolation Level
 —————
               2
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
 id1         id2
 ———– ———–
           1         200
           2         200
(2 rows affected)
To avoid Repeatable read problem, enable the isolation level 2
 Session1 Continues…
 1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
^C^C
[CanCan]
 Now Transaction T2 will not allow to modify the restult set which was read earlier tran 1(Session 1)
 1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 Still T1 will get same number of rows.

but still it has problem of Phantom Read?

Transaction T1 reads a set of data items satisfying some . Transaction T2 then creates data items that satisfy T1’s and commits. If T1 then repeats its read with the same ,  it gets a set of data items different from the first read.

1> begin tran
2> insert into pmtmaster values (3,200)
3> go
1> commit
2> go  
You can not modifies the result set but still you can insert new values and affect the result set.
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
2(1 row affected)
1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
3         200
(3 rows affected) 
 Now you can see the same session 1 is still returning different number of result set. This is called as Phantom Reads.
   
 To Avoid Phantom Reads enable isolation level 3 as below.

Understanding Isolation Level “3″ : Avoid Phantom Reads

 1> set transaction isolation level 3
2> go
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
3
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
id1         id2
———– ———–
1         200
2         200(2 rows affected)
To Avoid Phantom Reads , Lets enable isolation level 3
 
1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
^C^C
[CanCan] 
 Now you can not modify the result set.
  1> begin tran
2> insert into pmtmaster values (3,200)
3> go
^C^C
[CanCan]
 You can not create data items to affect the result set
 1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 All the time you will get same result set.

SAP Recognized as a Market Leader by Gartner, Inc. in Operational Database Management Systems Magic Quadrant

November 17th, 2013 No comments

As per Gartner:

SAP

Located in Walldorf, Germany, SAP (www.sap.com) has several DBMS products that are used for transaction systems: SAP Sybase Adaptive Server Enterprise (ASE), SAP Sybase iAnywhere and SAP Hana. Both ASE and iAnywhere are available as software only, while SAP Hana is marketed as an appliance.

Strengths
  • Vision leadership — Moving into DBMS technology, SAP has introduced SAP Hana as an in-memory platform for hybrid transaction/analytical processing (HTAP) and acquired Sybase to add to the DBMS product line.

  • Strong DBMS offerings — In addition to SAP Hana, SAP Sybase ASE continues to support global-scale applications and was first to introduce an in-memory DBMS (IMDBMS) version.

  • Performance — References cited performance (scalability and reliability) as a major strength (one of the highest scores), mostly for SAP Sybase ASE.

Source : http://global.sap.com/corporate-en/news.epx?category=ALL&articleID=21912&searchmode=C&page=1&pageSize=10

& http://www.gartner.com/technology/reprints.do?id=1-1MNA5V2&ct=131105&st=sb

 

 

SAP® Sybase® Adaptive Server® Enterprise Gains Momentum With Rapid Customer Adoption

November 17th, 2013 No comments

 

In less than 18 months since the offering’s release in April 2012, more than 1,000 customers have chosen to run SAP Business Suite on SAP Sybase ASE and there are more than 2,000 customer installations. Both new and existing SAP customers can run a high-performance relational database management system (RDBMS) optimized for SAP Business Suite that helps improve operational efficiency and significantly reduce overall costs. The announcement was made at the SAP Database and Technology Partner Summit in Barcelona.

Source :: http://www.prnewswire.com/news-releases/sap-sybase-adaptive-server-enterprise-gains-momentum-with-rapid-customer-adoption-229819941.html

http://www.hispanicbusiness.com/2013/11/5/sap_sybase_adaptive_server_enterprise_gains.htm

Real Time HANA Replication from ASE Using SAP Sybase Replication Server

July 31st, 2013 No comments

SAP® Sybase® IQ Software Smashes Previous Results and Sets World Record for Fastest Loading of Big Data

July 27th, 2013 No comments

SAP® Sybase® IQ Software Smashes Previous Results and Sets World Record for Fastest Loading of Big Data

http://finance.yahoo.com/news/newsbyte-sap-sybase-iq-software-130000253.html

ASE 15.7 SP100: Sybase ASE’s version numbering goes the SAP way

June 25th, 2013 No comments

Need of In-memory Technology : SAP HANA

May 6th, 2013 No comments

Challenge 1: Massive Data Growth

Massive amounts of data is being created every year and as per he IDC EMC report data growth would be 40K Exabytes by 2020 :

http://germany.emc.com/collateral/about/news/idc-emc-digital-universe-2011-infographic.pdf

http://www.emc.com/collateral/analyst-reports/idc-the-digital-universe-in-2020.pdf

Capture 2

Challenge 2: Fast access to business decision making information.

Business & People want fast exact and correct answer of all questions from this massive amount of data.

Challenge 3: Current Technologies Can not deliver with this massive data growth.

Historical DBMS :

Historically database systems were designed to perform well on computer systems with limited RAM, this had the effect that slow disk I/O was the main bottleneck in data throughput. Consequently the architecture of these systems was designed with a focus on optimizing disk access, e. g. by minimizing the number of disk blocks (or pages) to be read into main memory when processing a query.

New Hardware Architecture ( up to or more 128 Cores of CPU and 2TB of RAM)

Computer architecture has changed in recent years. Now multi-core CPUs (multiple CPUs on one chip or in one package) are standard, with fast communication between processor cores enabling parallel processing. Main memory is no-longer a limited resource, modern servers can have 1 TB of system memory and this allows complete databases to be held in RAM. Currently server processors have up to 80 cores, and 128 cores will soon be available. With the increasing number of cores, CPUs are able to process increased data per time interval. This shifts the performance bottleneck from disk I/O to the data transfer between CPU cache and main memory

Hana1

Need of In-memory Technology SAP HANA :

From the discussion above it is clear that traditional databases might not use current hardware most efficiently and not able to fulfill current and future business need.

The SAP HANA database is a relational database that has been optimized to leverage state of the art hardware. It provides all of the SQL features of a standard relational database along with a feature rich set of analytical capabilities.

Using groundbreaking in-memory hardware and software, HANA can manage data at massive scale, analyze it at amazing speed, and give the business not only instant access to real time transactional information and analysis but also more flexibility. Flexibility to analyze new types of data in different ways, without creating custom data warehouses and data marts. Even the flexibility to build new applications which were not possible before.

HANA Database Features

Important database features of HANA include OLTP & OLAP capabilities, Extreme Performance, In-Memory , Massively Parallel Processing, Hybrid Database, Column Store, Row Store, Complex Event Processing, Calculation Engine, Compression, Virtual Views, Partitioning and No aggregates. HANA In-Memory Architecture includes the In-Memory Computing Engine and In-Memory Computing Studio for modeling and administration. All the properties need a detailed explanation followed by the SAP HANA Architecture.

Source : www,sap.com and emc and idc reports.