Sybase Ase 464b4o

Agenda

• SAP ASE evaluation • Understanding SAP ASE • Managing the Databases • Managing the Database parameters • Backup and restore

SAP ASE evaluation • SAP Sybase ASE (Adaptive Server Enterprise) which is commonly known as ASE or just Sybase DB, is a relational model database server product for businesses developed by Sybase Corporation which became part of SAP AG. ASE is predominantly used on the Unix platform, but is also available for Microsoft Windows.

• Originally for Unix platforms in 1987, Sybase Corporation's primary relational database management system product was initially marketed under the name Sybase SQL Server. In 1988, SQL Server for OS/2 was co-developed for the PC by Sybase, Microsoft, and Ashton-Tate. Ashton-Tate divested its interest and Microsoft became the lead partner after porting SQL Server to Windows NT. Microsoft and Sybase sold and ed the product through version 4.2.1.

• Sybase released SQL Server 4.2 in 1992. later Microsoft is separated from Sybase, and they started developing MS SQL server versions • Latest name of Sybase ASE is SAP ASE

Sybase system databases •

There are five required system databases in Sybase ASE

•

master DB

•

model DB

•

temp DB

•

sybsystemprocs DB

•

sybsystemdb

System databases

•

master DB : contain metadata and brain of Sybase ASE. Its first DB which come online after boot.

•

model DB : template DB. Whenever new DB is created , a copy of model DB is copied.

•

temp DB : temporary database. Used during sorting and other temporary work.

•

sybsystemprocs DB : store all system procedure info.

•

sybsystemdb : used for distributed transaction management system.

•

Additional databases

•

sybmgmt DB : stores job schedule information.

databases

•

Defined Databases <SID> is SAP database & sap tools (Dbaockpit History database ) These databases created by SAP installer in SAP ASE.

SAP ASE file system in SAP File System/logical volume /sybase/

Description SAP ASE software ($SYBASE) Size: at least 4 GB

/sybase/ /sybsystem /sybase/ /sybtemp /sybase/ /saptemp

Location for devices master.dat sybmgmtdb.dat sybsysdb.dat sysprocs.dat Size: at least 1 GB Location for device tempdb, Size: at least 3 GB Location for device saptempdb; Size: at least 3 GB

/sybase/ /sapdiag

Location for devices containing the database saptools Size: at least 3 GB

/ sybase/ /sapdata_ / sybase/ /saplog_ / sybase/ /sybsecurity

Location for devices containing data Location for devices containing logs Location for devices containing the database sybsecurity Size: at least 1 GB

Database roles

• • •

System – by default the system (the sa) has the following role

• • • •

sa_role sso_role oper_role sybase_ts_role

•

Managing disk storage

• •

Backuping and loading databases granting permissions to adaptive server s

Database

• List of database s 1. SA 2. SAPSA 3. Sapsso We can list the by using stored procedure >sp_help database name >go For adding database sp_add name, , database name for adding a , you execute with sso_role

Connecting to database

• Isql –Usapsa –S<SID> -D<SID> -X U – iname S –server name D – Database name X- Network authentication

Sybase central control

•

Sybase Central control

•

Sybase Central is the primary tool for working with database objects on windowing systems.

•

You can use Sybase Central to manage servers, databases, and dbspaces. It lets you create, modify, and delete all kinds of database objects, including tables, procedures, views, indexes, s, and groups. To use Sybase Central on multiplex servers, see Using Sybase IQ Multiplex.

•

This chapter is concerned with the SQL statements for working with database objects. If you use Sybase Central, these SQL statements are generated for you. The primary source of information about Sybase Central is the Sybase Central online help. This chapter gives only brief pointers for tasks that you can carry out using Sybase Central

List of process run at OS level

• • • •

SAP ASE list of Process run at background RUN_SID RUN_SID_BS JSAGENT

SAP ASE s

• • • • • •

SAP ASE s EHp2 For SAP Netweaver 7.0 onwards It's 's operating systems both for UNIX systems and windows systems UNIX: AIX7.1 & HP-UNIX 11.31 & Solaris 10 & 11 LInux: Redhat enterprise linux 6 & 7 and suse enterprise linux 10 & 11 Windows server 2008 onwards Cluster edition SAP ASE15.7 130

Managing databases space

1) Adding the datafile to database device •use master •go • disk init name='sybmgmtdb2', physname='E:\sybase\DEV\sybsystem\sybmgmtdb2.dat', size='1.5G‘ •i) Asg the created database device space to database •alter database sybmgmtdb on sybmgmtdb2 = 1000M •go

(mention in mbs )

Note : Here sybmgmtdb2 is the device name and sybmgmtdb is the database 2) Resizing the database device •disk resize name = "sybmgmtdb2", size = "4M“ •Note : sybmgmtdb2 is the database device name

Managing databases space

1)Adding space to log file •alter database saptools log on saptools_log_001 = 1024 •go 2) Shrinking log file , if unused space is available •alter database SID log off SID_log_001.dat = "19456M" •go

DATA SPACE ISTRATION

We can monitor database space. DB02 (Data space istration)

We can also extend database space using dbacockpit  by selecting the Sybase Database

DATA SPACE ISTRATION Extending database size

Enter the database name and type of file

DATA SPACE ISTRATION Extending database size

Enter the database name and type of file

DATA SPACE ISTRATION Extending database size

Enter the database name and type of file

DATABASE BACKUP RECOMMENDATIONS Mandatory backups: database (usually system id), database transaction logs (usually system id) daily Optional backups: Master, sybmgmtdb, sap tools at weekly once Recommended Cycle for 28 days Daily Full online backup of sap system DB Frequent Daily Transaction Logs of SAP system DB Weekly once full offline sap system DB (in Sunday) Weekly once optional database backups : master, sybmgmtdb, saptools Weekly once operating system level backup is recommended Taking the database Backup C:\s\bwd>isql -Usapsa -SBWD -DBWD -X : 1> dump database BWD to "E:\backup\BWD_DB_08_04_2014.dmp" 2> go 1> dump transaction BWD to "E:\backup\BWD_DB_08_04_2014.trn1" 2>go dump database saptools to "E:\backup\BWD_DB_08_04_2014.trn1" go

DATABASE RESTORE Restoring database using latest Backup Stopsap and start only database services C:\s\bwd>isql -Usapsa -SBWD -DBWD -X : 1>use master 2>go 3>load database BWD from “E:\BACKUP\BWD_OFF_04_11_2014.dmp” 4>go 5>load transaction BWD from “E:\BACKUP\BWD_TRANS_04_11_2014.trn” 6>go 7>online database BWD 8>go Updating statics for table manually Isql –Usapsa -SPRD - DPRD -X 1>use PRD 2>go 3>set quoted_identifier on 4>go 5>update index statistics SAPSR3."REPOSRC" 6>go

How to start and stop database services manually 1. Starting Sybase database service to syb<sid > Cd /sybase/<SID>/ASE-16_0/install Startserver –f RUN_<SID> 2. Stopping Sybase database service to syb<sid > isql -Usapsa -SBWD -DBWD -X 1>shutdown 2> go 3. Starting Sybase backup service to syb<sid > Cd /sybase/<SID>/ASE-16_0/install Startserver –f RUN_<SID>_BS

4. To start and stop database job scheduler (JSAGENT) 1.Starting database job scheduler to syb<SID> isql -Usapsa -SBWD -DBWD -X 1>use sybmgmtdb 2> go 3>exec sp_sjobcontrol @name =NULL, @option=“start_js” 4>go 2.Stopping database job scheduler to syb<SID> isql -Usapsa -SBWD -DBWD -X 1>use sybmgmtdb 2> go 3>exec sp_sjobcontrol @name =NULL, @option=“stop_js” 4>go

Monitoring the database error messages to syb<SID> Cd /sybase/ESD/ASE-16_0/install For database error logs  <SID>.log For database backup logs  <SID>_BS.log For JS agent logs  <SID>_JSAGENT.log Monitoring tools in Sybase 1> monitoring the Sybase database parameters utilization isql -Usapsa -SBWD -DBWD -X 1>sp_monitorconfig “all” 2>go 2> monitoring Sybase database performance in last two mins 1> sp_sysmon '00:02:00' 2> go

Monitoring the database space utilization by query isql -Usapsa -SBWD -DBWD -X 1> SELECT "Database" = CONVERT(char(20), db_name(D.dbid)), "Data Size" = STR(SUM(CASE WHEN U.segmap != 4 THEN U.size*(@@maxpagesize/1024)/1024 END),10,1), "Used Data" = STR(SUM(CASE WHEN U.segmap != 4 THEN size curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)END)*(@@maxpagesize/1024)/1024,10,1),"Data Full%" = STR(100 * (1 - 1.0 * SUM(CASE WHEN U.segmap != 4 THEN curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs) END)/SUM(CASE WHEN U.segmap != 4 THEN U.size END)),9,1) + "%", "Log Size" = STR(SUM(CASE WHEN U.segmap = 4 THEN U.size*(@@maxpagesize/1024)/1024 END),10,1),"Free Log" = STR(lct_("logsegment_freepages",D.dbid)*(@@maxpagesize/1024)/1024,10,1), "Log Full%" = STR(100 * (1 - 1.0 * lct_("logsegment_freepages",D.dbid) / SUM(CASE WHEN U.segmap = 4 THEN U.size END)),8,1) + "%" FROM master..sysdatabases D, master..sysusages U WHERE U.dbid = D.dbid AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256)) GROUP BY D.dbid ORDER BY db_name(D.dbid) 2> go

For reference tuning the Sybase database Please follow the document