SAP ASE SYBASE DATABASE Administrator

SAP SYBASE DB Command:

In this Blog we will explained  the SAP SYBASE Database administrator regular tasks in OS level. Please check below SAP ASE SYBASE DB Commands.

SYBASE DB ADMIN Tasks:

• Start the Sybase db manually in OS level
• Stop the Sybase db manually in OS level
• How to check the Sybase db version in OS level
• How to check the db size in OS level
• How to connect the db in OS level
• How to take Sybase DB backup
• How to restore the Sybase DB backup
• Sybase DB upgrade
• How to check the Sybase Database license
• How to unlock the Sybase db user in OS level
• How to delete the sap SAPSR3 SCHEMA user in Sybase db level
• How to update the sap SAPSR3 user password in OS level
• How to reset the Sybase db user in os level.
• How to grant access dbo to user.
• Sybase commands for Database level
• How to check the Top 10 large table in Sybase Database
• Sybase database consistency check.
• How to check what is Maximum size of the SAP ASE DB

SAP ASE SYBASE DB Commands:

Start the Sybase db manually

• Login with sybsid
• Go to /Sybase/SID/ASE_16/install
• Execute the ./RUN_SID
• Once executed the above command then Database will start and it will take 5 to 10mins for start the DB.

  Stop the Sybase db manually

• Login with sybsid
• Go to /Sybase/SID/ASE_16/install
• Execute the ./STOP_SID
• Once executed the above command then Database will stop and it will take 5 to 10mins for stoping the database.

How to check the Sybase db version in OS level

• Login with sybsid
• Go to /Sybase/SID/ASE_16/install
• Execute the dataserver –v

 How to check the db size in OS level

• Login with sybsid
• Goto /Sybase/SID/ASE_16/install
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• >use SID
• >go
• >sp_spaceused
• Output screen you will observe the DB size details.

How to connect the db in OS level

• Login with sybsid
• Go to /Sybase/SID/ASE_16/install
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• Now you will able to connect Sybase db in os level, As per user requirement execute the db command.

  How to unlock/Lock the Sybase db user in OS level

• Sybase 3 standard DB users
• User : sapsso, sapsa & sa
• Lock the user:
• Login with sybsid on target system.
• Go to /Sybase/SID/ASE_16/install
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• >sp_locklogin ‘sa’, ‘lock’
• >go
• Unlock the user
• >sp_locklogin ‘sa’, ‘unlock’
• >go

How to delete the sap SAPSR3 SCHEMA user in Sybase db level

• Go to /Sybase/SID/ASE_16/install
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• >use SID
• >go
• >delete from SAPSR3.USR02 where BNAME=’SAP*’

How to update the sap SAPSR3 user password in OS level

• Login to the root level and execute the below command.
• Goto /usr/sap/hostexec/exe
• ./saphostctrl -user sapadm password -dbuser SAPSR3 -dbpass password -function LiveDatabaseupdate -dbname SID -dbtype syb -updatemethod Execute -updateoption USER=SAPSR3 [-updateoption WITH_ASEUSERSTORE=1]

How  to reset the Sybase db user in os level.

• Login with sybsid on target system.
• Go to /Sybase/SID/ASE_16/install
• We know sapsa password but we don’t the sa user id password.
• We can login to the database with sapsa user then reset the password for sa user id.
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• >use master
• >go
• >alter login sapsa with password <sapsa password> modify password <sa user password>                       Example:
  >alter login sapsa  with password Welcome@1234 modify password Login@1234
  >go.
  Password has been changed successfully.

How to check the Top 10 large table in Sybase Database

• To list top 10 largest tables in a database:
  >use <db_name>
  >go
  >select top 10 id as object_id, convert(varchar(30),o.name) AS table_name,
  row_count(db_id(), o.id) AS row_count,
  data_pages(db_id(), o.id) AS pages,
  data_pages(db_id(), o.id) * (@@maxpagesize/1024) AS kbs
  from sysobjects o where o.id != 8
  order by kbs desc
  go

Sybase database consistency check.

• Check the ASE User store db password. Execute the below command
• aseuserstore set SAPSA <host>:<port(default is 4901)> sapsa <pw>
• As per note 3316354, Download the script files and move to the Below location.
• Location: /Sybase/SID/ASE_16/install
• Once files has moved to concern location then apply the permission and owner ships.
• As per note 3316354, Execute the below command.
• Steps to run the consistency check with 5 spids in parallel:
• Command:
• isql -kSAPSA -w1000 -X -igen_dbcc_check_parallel_5.sql
• Execute the below command also for parallelized consistency check.
• nohup isql -kSAPSA -w1000 -X -idbcc_exec_1.sql -ooutput_dbcc_1.txt &
  nohup isql -kSAPSA -w1000 -X -idbcc_exec_2.sql -ooutput_dbcc_2.txt &
  nohup isql -kSAPSA -w1000 -X -idbcc_exec_3.sql -ooutput_dbcc_3.txt &
  nohup isql -kSAPSA -w1000 -X -idbcc_exec_4.sql -ooutput_dbcc_4.txt &
  nohup isql -kSAPSA -w1000 -X -idbcc_exec_5.sql -ooutput_dbcc_5.txt &
• Once process has completed then output files has generated.
• As per output files, check the error and consistency check.
• Check error using below command.
• More txt|grep error

Sybase db commands for Database level:

• Sp_spaceused
• Sp_configure
• Sp_helpdb SID
• Sp_helpsegment logsegment
• Sp_lmconfig 

How to disk resize in OS level

• Go to /Sybase/SID/ASE_16/install
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usapsa –Pwelcome@1234 –SABC –X
• >use SID
• Log:  EXAMPLE FOR 2GB
  Disk resize name=’mqc_log_001’, size = ‘2G’
  Alter database MQC LOG on MQC_log_001 = ‘2G’
  Data:
  Disk resize name=’mqc_data_001’, size = ‘2G’
  Alter database MQC  on MQC_data_001 = ‘2G’

How to check what is Maximum size of the SAP ASE DB:

• Login with sybsid
• Goto /Sybase/SID/ASE_16/install
• Please login ASE into SA db user
• Execute the command: isql64 –U<user> -P<password> -SSID –X
• Example: isql64 –Usa –Pwelcome@1234 –SABC –X
• >use SID
• >dbcc traceon(3604)
  >go
  >dbcc serverlimits
  >go
  >dbcc traceoff(3604)
  >go
• Will get output like
• On the section of “Limits as a function of the page size”, it displays the following:            Item dependent on page size                             : 2048   4096    8192    16384
  ———————————————————————————————————–
  Server-wide, Database-specific limits and sizes
     
    Min number of virtual pages in master device                        : 11780       22532      45060    90116
    Default number of virtual pages in master device                   : 23556      45060      90116   180228
    Min number of logical pages in master device                        : 11776       11264      11264   11264
    Min number of logical pages in tempdb                                   : 2048           1536       1536    1536
    Max size of a database (Tb)                                                             : 8              16           32         64

Other info:

How to take Sybase DB backup and Restore the Backup.
Sybase DB Patch upgrade process.

Regular sybase db commands:

• Display active database            :   show server
• List of database in server     : sp_helpdb
• List of database table           : sp_help
• SAP ASE License check            : sp_lmcnfig
• Connect to the database  : use dbname
• Database patch version check : sp_version
• Check database server options: sp_configure

Conclusion:

• We have successfully learned the SAP ASE Sybase db commands for Sybase Administrator tasks.
• 
• Please check next blog

FAQ for SAP ASE- SYBASE DB Admin Tasks: