SAP Sybase Blog

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

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

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.

Always consider to migrate the Development environment first , then UAT. Before moving to production Perform Regression testing on UAT enviornment.

Please consider to create the script to perform update stats,xp_postload(drop and re create index) for each and every database.

Steps for an ASE Database( You can repeat same steps for other databases) :

Step 1: Run the consistency checks in ASE database in Source (AIX) environment, to make sure that everything is fine.

Step 2: Put the database in single user mode.

Step3: Make sure there is no user activity on the Source Database .

Step 4: Run the sp_flushstats in the database.

Step 5: Take the backup of the database in Source (AIX) environment.

Step 6: Ftp the Files to Target environment. (AIX to Linux)

Step 7: Create and build the dataserver and databases in target Linux environment with exactly same configuration.
You might require to change some of the config param in Linux environment for performance point of view. ( Lets not discuss it here, as it is out of context).

Step 8: Also migrate the Login, roles from source server to target server

Step 9: Load the database in Linux environment.
(If there were user activity during dump process, load will be fail.)

Step 10: Online the database. If the target ASE version is new with source, It will also perform upgrade in this step.

Step 11:  Fix the corrupt indexes using the xp_postload. If the Database size is more than 20G, try drop and re-create index , in this case xp_postload would not be effective.

Step 12: Update the stats on all tables.

Step 13:  If there is replication setup in your environment, please setup replication after that.

Issue Faced:

1. If there is any user online during backup process, your load will fail( in the step for cross platform conversion).

2. After online database, we seen the -ve values in sp_helpdb output for few databases. There are two ways to fix this :

i) Try to run dbcc

dbcc usedextents(<DB name or DB ID>, 0, 1, 1)

ii) Use the Traceflag  7408 and 7409 in Run Server file and reboot the instance. It will not take much time as compare first option.

Traceflag 7408 : Force the server to scan *log segment* allocation pages; to recalculate free log space rather than use saved counts at boot time.
Traceflag 7409 : Force the server to scan *data* segment allocation pages; to recalculate free data page space rather than use saved counts at boot time.

Please let me know if you are planning for migration and need any assistance.

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.

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.

Follow the below video for “How to start and stop replication server”

Follow the below video for “How to Rename Sybase ASE server”

Oracle products vs. Sybase products

Both Oracle and Sybase provide a range of database-related products. The following list illustrates how the main high-level Oracle products compared to Sybase products. While this list is deliberately kept brief, it provides some basic guidance on how Oracle and Sybase can be aligned.

About Sybase ASE
Sybase ASE is the database that powers Wall Street. ASE has been delivering rock-solid reliability and top-level performance for the past 25 years. Sybase ASE has a lower total cost of ownership than Oracle, and delivers better performance on the same hardware. Sybase ASE is ready to be the database in any application that runs on Oracle today.

You can get all index size in a Database by using sp__getallindexsize as below :

I used the posted method to calculate index size : Calculating Table & Index Usage in ASE

Down load the stored proc here: sp_getallindexsize.sql (9)

1> use pubs3
2> go
1> sp__getallindexsize
2> go
table_name      index_name            size  reserved unused
————— ——————— —– ——– ——
authors         aunmind               2 KB  32 KB    30 KB
roysched        titleidind            2 KB  32 KB    30 KB
titleauthor     auidind               2 KB  32 KB    30 KB
salesdetail     titleidind            2 KB  32 KB    30 KB
titleauthor     titleidind            2 KB  32 KB    30 KB
salesdetail     salesdetailind        2 KB  32 KB    30 KB
titles          titleind              2 KB  30 KB    28 KB
blurbs          tblurbs               14 KB 16 KB    2 KB
sales           sales_8320029642      2 KB  16 KB    14 KB
stores          stores_7840027932     2 KB  16 KB    14 KB
titles          titles_6720023942     2 KB  16 KB    14 KB
authors         authors_5760020522    2 KB  16 KB    14 KB
publishers      publishers_6240022232 2 KB  16 KB    14 KB
store_employees store_empl_8960031922 2 KB  16 KB    14 KB

(14 rows affected)
(return status = 0)