SPS updating of the ASE database through the sap host control command.

Greetings, BASIS/ASE DBA community.

On this article, I’d like to explore a step-by-step procedure for updating ASE DB on a Windows environment.

Current DB Version: 16.0 SP03 PL14

Updated DB Version: 16.0 SP04 PL06 HF1

Operating System: Windows Server 2019

We should patch our SAP-ASE (Adaptive Server Enterprise) Sybase database from time to time as recommended by SAP.

This helps in correcting bugs in certain patch levels

Below pre-requisites should be maintained for updating ASE DB with SAPHOSTAGENT

SAP-ASE version must be 15.7 SP100 or higherSAP Host Agent version must be 7.21 PL43 or higherMake sure that no active clients are connected to SAP-ASE and no automated actions are scheduled while running the update.

A few definitions relating to minor, major upgrades, BuS, and nonBuS.

Minor Update: Updates within SAP-ASE 15.7 or 16.0 (eg. 16.0.02.04 to 16.0.03.06) are named minor updates.

Major Update: Updates from SAP-ASE 15.7 to 16.0 and higher are named major updates (eg. 15.7.0.138 to 16.0.02.04)

BuS: Using SAP ASE together with SAP Business Suite shall be named BuS.

nonBuS: While running any other application on SAP-ASE shall be named nonBuS.

The update progress is written to the LiveUpdate.log file in the SAP Host Agent work directory and can be looked up there.

In Windows: Drive<Name>: Program filesSAPhostctrlwork

In Linux: /usr/sap/hostctrl/work

The update generates logs in:

C:Program FilesSAPhostctrlworkliveupdate.log
<drive>:sybase<SID>sapdbctrl-configASE_UPDATE.dat
<drive>:sybase<SID>logASE.log
C:Program FilesSAPhostctrlworkdev_sapdbctrl

The SAP Host Agent saves the update progress and in case the update is interrupted, restarting the update will continue from the last task executed. Once the update process has replaced the software, you should no longer cancel the update but rather finish the update, if necessary, by skipping failing tasks.

Step-1: To get the database version, execute the following command and Check the OS/DB compatibility in PAM

F:sybaseGMK>isql -S<SID> -U<db_user_name> -P<Password> -X -w9999

1> select @@version

2>go —————————————————————————————————————————————————————————————————————————————————————

Adaptive Server Enterprise/16.0 SP03 PL14/EBF 30731 SMP/P/AMD64/Windows 2008 R2 SP1/ase160sp03pl14x/0/64-bit/FBO/Wed May 10 14:16:43 2

Step-2: Download the required DB patches from SAP portal and place them at “<Drive>:DB_UPG”

SAP Support Portal Home–> Software downloads–> Databases –> Database and Database Patches –>SAP ADAPTIVE SERVER ENTERPRISE –> DATABASE PATCHES –> SAP ASE FOR BUSINESS SUITE –> SAP-ASE 16.0 FOR BUS. SUITE –> Select the required patch by filter with DB OS Type

Step-3: SAP Host Agent version must be 7.21 PL43 or higher

If not, please update the hostagent to latest version.

Follow the below article to automate the upgrade of SAP host agent

Auto-Upgrade of SAPHOST-AGENT: – SAP Community

Step-4: In worst case, if you need to roll back to the old existing version of SAP-ASE, then you must take a backup of %SYBASE% directory.

Unix: /sybase/<SID>
Windows: <drive>:/Sybase/<SID>

For Windows, you may use “Windows Server Backup” application to take a local backup of Sybase directory.

Step-5: Validate the content of SAP Secure stores with below command.

C:Program FilesSAPhostctrlexe/saphostctrl -user sapadm <sapadm_user_password> -function LiveDatabaseUpdate -dbname <SAPSID> -dbtype syb -updateoption TASK=VALIDATE_SEC_STORES

Not found: Secure Store does not exist or cannot be read. Or authentication entry is not maintained.

Invalid: Maintained credentials could not be confirmed.

Valid: Maintained credentials could be confirmed.

Empty: Authentication was read but credentials are empty.

Set: Unix only! Credentials are maintained, but as OS user switch on Unix can be done without password. No password is checked.

If any account is missing, then you can add them with the following command to the list of the sap host agent.

Executing the following command will return a list of users and their credentials which are available in the secure store.

For SA user:

rsecssfx get DB_CONNECT/SYB/SADB_USERrsecssfx put DB_CONNECT/SYB/SADB_USER sapsa -plainrsecssfx put DB_CONNECT/SYB/SADB_PASSWORD <sapsa_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapsa -dbpass <sa_user_password> USER=SAPSA

For SAPSSO user:

rsecssfx get DB_CONNECT/SYB/SSODB_USERrsecssfx put DB_CONNECT/SYB/SSODB_USER sapsso -plainrsecssfx put DB_CONNECT/SYB/SSODB_PASSWORD <sapsso_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapsso -dbpass <sapsso_user_password> USER=SAPSSO

For SA user:

rsecssfx get DB_CONNECT/SYB/SAUPDDB_USERrsecssfx put DB_CONNECT/SYB/SAUPDDB_USER sa -plainrsecssfx put DB_CONNECT/SYB/SAUPDDB_PASSWORD <sa_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sa -dbpass <sa_user_password> USER=SAUPD

For SYBSID user:

rsecssfx get DB_CONNECT/SYB/SYBSID_USERrsecssfx put DB_CONNECT/SYB/SYBSID_USER <Domain>sybsid -plainrsecssfx put DB_CONNECT/SYB/SYBSID_PASSWORD <sybsid_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser <Domainsybsid> -dbpass <sybsid_user_password> USER=SYBSID

For SAPSID user:

rsecssfx get DB_CONNECT/SYB/SAPSID_USERrsecssfx put DB_CONNECT/SYB/SAPSID_USER <Domainsapsid> -plainrsecssfx put DB_CONNECT/SYB/SAPSID_PASSWORD <sapsid_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser <Domainsapsid> -dbpass <sapsid_user_password> USER=SAPSID

For sapadm user:

rsecssfx get DB_CONNECT/SYB/SAPADM_USERrsecssfx put DB_CONNECT/SYB/SAPADM_USER sapadm -plainrsecssfx put DB_CONNECT/SYB/SAPADM_PASSWORD <sapadm_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapadm -dbpass <sapadm_user_password> USER=SAPADM

Adding sapsso user credentials and validation

Verifying sapsso user validation in secure store
Check the validation for sapsso user in the secure store

Adding sapadm user entry and password validation in secure store

Verifying sapdm user validation in secure store

Step-6: Stop all applications running on the SAP-ASE server and make sure no jobs are scheduled for the time of the update. To enforce that no jobs are running you can stop job scheduler before starting the update (SAP Host Agent 7.21 SP44 and higher will do this either way After the system is checked and no active database connections were found).

Step-7: Execute below command to check if the upgrade will be successful or will it fail due to some error. It’s just a check and it won’t actually update any files.

saphostctrl -function LiveDatabaseUpdate –dbname <SAPSID> -dbtype syb -updatemethod Check -updateoption TASK=CHECK_UPDATE_ASE -updateoption DROP_LOCATION=”<Drive>:DB_UPG”

If any client connections are still present, you will receive a warning similar to this

Step-8: Execute the below command to update ASE DB

saphostctrl -function LiveDatabaseUpdate –dbname <SAPSID> -dbtype syb -updatemethod Execute -updateoption TASK=UPDATE_ASE -updateoption DROP_LOCATION=”<Drive>:DB_UPG”

INIT_UPDATE_ASE
Internal preparations for the SAP ASE update.CHECK_UPDATE_ASE
Check user input (eg. software package specified in DROP_LOCATION).
Check compatibility of package (with asemanifest.mf).
Check that /tmp is not mounted with no exec property (prevents setup.bin from being executed properly).
Check that SAP-ASE is up and running in case update is initial.
Check that no clients are connected to SAP-ASE.
Check if major update is restarted with wrong software package.
Check if required credentials are valid (sapsid, sapsa, sapsso, sa, sybsid, SAPSR3, DR_admin).
Check-in case of replication that site is standby or option UPDATE_PRIMARY=1 is setup.
Check if the SAP-ASE runs with BuS or nonBuS application.
Check that PATCH.SAR exists in case SAP-ASE runs with BuS application.
If hadr_mode != 1 set ‘grant allow hadr login to sso_role’ (SAP-ASE 15.7.0.136 does not have it but requires it).
Check CDS object requirements are met.
Check without error that sybsystemprocs & sybmgmtdb have the recommended size (you might want to increase the size before/after the update).UPDATE_ASE_PRE_CONFIG
If required configure database options to support the update to target version.
Disable job scheduler.
Drop <SID>_XP and SYB_EJB servers for major update on BuS.
If major update check and set net password encryption required.
Disable auditing.
If major update unlock sa.
If BuS enforce dboption ddl in tran to be true in SID database.UPDATE_ASE_SOFTWARE
Stop SAP ASE servers.
If major update copy sysam directory.
If on AIX run slibclean.
Update software with Sybase installer.
If BuS and target SAP-ASE version is SP03 or higher install PATCH.SAR contents.
If BuS and target SAP-ASE version is SP03 or higher and OS is Unix, change sapsid user env for ODBC (dbenv.sh/csh).UPDATE_ASE_POST_CONFIG
If major update and OS is Windows Update user profile environments in registry
Start the SAP-ASE servers.
If major update run sybatch/sqlupgraderes tool for SAP-ASE server.
If major update run sybatch/sqlupgraderes tool for backup server.
If BuS and OS is Windows delete XP service.
If major update renames obsolete 15_0 directories to 15_0_OLD and restart SAP-ASE servers (make sure changes in env & on disk grab the right libraries).
If required lock sa.
Check and reset net password encryption required.
Execute SAP ASE install scripts which are not default to sybatch/sqlupgraderes but recommended (eg. installdbextend).
Enable job scheduler.UPDATE_ASE_BS_DEFAULTS
If required update remaining database options to support the update to target version.
Execute syb_update_db.txt script to set BuS defaults.RECREATE_CDS_OBJECTS
If target is SP03 recreate CUDFs.
If target is SP02PL04 or SP03 and source is lower than target or force recreate GTTs.
If target is SP02PL04 or SP03 and source is lower than target or force recreate TUDFs.
If target is SP03PL06 and source is SP03PL03 or target > SP03PL03 and (source is SP03PL03 or < SP03PL03) or force inline TUDFs.SETUP_BALDR
If target SAP-ASE version is SP03 or higher execute instbaldr script else setup DCF
(wrapper for SetDatabaseProperty SETUP_LIST_METRICS=saptools).CONFIG_GP
Configure and enable granular permissions (replication will be turned off for this step).obsolete: REPLACE_DBI
Close all database connections between SAP Host Agent and SAP-ASE/RMA.
Unload the database drivers.
Remove/rename the local libraries.
Copy the required files from OCS folders to SAP Host Agent exe folder.RESET_UPDATE_ASE
Backup files in sapdbctrl-config.
Finish the SAP-ASE update.

Final restart of the database for further usage

Check the DB version

Documentation below can be referred for more information.

Targeted ASE 16.x Release Schedule and CR list Information | SAP Help Portal2800483 – SYB: Upgrade / Update SAP ASE with SAP Host Agent 7.21 PL43 and later – SAP for Me1797040 – SYB: SAP Host Agent – Using global or local secure storage – SAP for Me

Thanks & Regards,
Mani

Greetings, BASIS/ASE DBA community.On this article, I’d like to explore a step-by-step procedure for updating ASE DB on a Windows environment.Current DB Version: 16.0 SP03 PL14Updated DB Version: 16.0 SP04 PL06 HF1Operating System: Windows Server 2019We should patch our SAP-ASE (Adaptive Server Enterprise) Sybase database from time to time as recommended by SAP.This helps in correcting bugs in certain patch levelsBelow pre-requisites should be maintained for updating ASE DB with SAPHOSTAGENTSAP-ASE version must be 15.7 SP100 or higherSAP Host Agent version must be 7.21 PL43 or higherMake sure that no active clients are connected to SAP-ASE and no automated actions are scheduled while running the update.A few definitions relating to minor, major upgrades, BuS, and nonBuS.Minor Update: Updates within SAP-ASE 15.7 or 16.0 (eg. 16.0.02.04 to 16.0.03.06) are named minor updates.Major Update: Updates from SAP-ASE 15.7 to 16.0 and higher are named major updates (eg. 15.7.0.138 to 16.0.02.04)BuS: Using SAP ASE together with SAP Business Suite shall be named BuS.nonBuS: While running any other application on SAP-ASE shall be named nonBuS.The update progress is written to the LiveUpdate.log file in the SAP Host Agent work directory and can be looked up there.In Windows: Drive<Name>: Program filesSAPhostctrlworkIn Linux: /usr/sap/hostctrl/workThe update generates logs in:C:Program FilesSAPhostctrlworkliveupdate.log<drive>:sybase<SID>sapdbctrl-configASE_UPDATE.dat<drive>:sybase<SID>logASE.logC:Program FilesSAPhostctrlworkdev_sapdbctrlThe SAP Host Agent saves the update progress and in case the update is interrupted, restarting the update will continue from the last task executed. Once the update process has replaced the software, you should no longer cancel the update but rather finish the update, if necessary, by skipping failing tasks.Step-1: To get the database version, execute the following command and Check the OS/DB compatibility in PAMF:sybaseGMK>isql -S<SID> -U<db_user_name> -P<Password> -X -w99991> select @@version2>go ————————————————————————————————————————————————————————————————————————————————————— Adaptive Server Enterprise/16.0 SP03 PL14/EBF 30731 SMP/P/AMD64/Windows 2008 R2 SP1/ase160sp03pl14x/0/64-bit/FBO/Wed May 10 14:16:43 2Step-2: Download the required DB patches from SAP portal and place them at “<Drive>:DB_UPG”SAP Support Portal Home–> Software downloads–> Databases –> Database and Database Patches –>SAP ADAPTIVE SERVER ENTERPRISE –> DATABASE PATCHES –> SAP ASE FOR BUSINESS SUITE –> SAP-ASE 16.0 FOR BUS. SUITE –> Select the required patch by filter with DB OS TypeStep-3: SAP Host Agent version must be 7.21 PL43 or higherIf not, please update the hostagent to latest version.Follow the below article to automate the upgrade of SAP host agent Auto-Upgrade of SAPHOST-AGENT: – SAP CommunityStep-4: In worst case, if you need to roll back to the old existing version of SAP-ASE, then you must take a backup of %SYBASE% directory.Unix: /sybase/<SID>Windows: <drive>:/Sybase/<SID>For Windows, you may use “Windows Server Backup” application to take a local backup of Sybase directory.Step-5: Validate the content of SAP Secure stores with below command.C:Program FilesSAPhostctrlexe/saphostctrl -user sapadm <sapadm_user_password> -function LiveDatabaseUpdate -dbname <SAPSID> -dbtype syb -updateoption TASK=VALIDATE_SEC_STORESThe returned values are: [GLOBAL | LOCAL]_<AUTHENTICATION>=[not found | invalid | valid | empty | set]Not found: Secure Store does not exist or cannot be read. Or authentication entry is not maintained. Invalid: Maintained credentials could not be confirmed.Valid: Maintained credentials could be confirmed.Empty: Authentication was read but credentials are empty.Set: Unix only! Credentials are maintained, but as OS user switch on Unix can be done without password. No password is checked.If any account is missing, then you can add them with the following command to the list of the sap host agent.Executing the following command will return a list of users and their credentials which are available in the secure store.For SA user:rsecssfx get DB_CONNECT/SYB/SADB_USERrsecssfx put DB_CONNECT/SYB/SADB_USER sapsa -plainrsecssfx put DB_CONNECT/SYB/SADB_PASSWORD <sapsa_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapsa -dbpass <sa_user_password> USER=SAPSAFor SAPSSO user:rsecssfx get DB_CONNECT/SYB/SSODB_USERrsecssfx put DB_CONNECT/SYB/SSODB_USER sapsso -plainrsecssfx put DB_CONNECT/SYB/SSODB_PASSWORD <sapsso_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapsso -dbpass <sapsso_user_password> USER=SAPSSOFor SA user:rsecssfx get DB_CONNECT/SYB/SAUPDDB_USERrsecssfx put DB_CONNECT/SYB/SAUPDDB_USER sa -plainrsecssfx put DB_CONNECT/SYB/SAUPDDB_PASSWORD <sa_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sa -dbpass <sa_user_password> USER=SAUPDFor SYBSID user:rsecssfx get DB_CONNECT/SYB/SYBSID_USERrsecssfx put DB_CONNECT/SYB/SYBSID_USER <Domain>sybsid -plainrsecssfx put DB_CONNECT/SYB/SYBSID_PASSWORD <sybsid_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser <Domainsybsid> -dbpass <sybsid_user_password> USER=SYBSIDFor SAPSID user:rsecssfx get DB_CONNECT/SYB/SAPSID_USERrsecssfx put DB_CONNECT/SYB/SAPSID_USER <Domainsapsid> -plainrsecssfx put DB_CONNECT/SYB/SAPSID_PASSWORD <sapsid_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser <Domainsapsid> -dbpass <sapsid_user_password> USER=SAPSIDFor sapadm user:rsecssfx get DB_CONNECT/SYB/SAPADM_USERrsecssfx put DB_CONNECT/SYB/SAPADM_USER sapadm -plainrsecssfx put DB_CONNECT/SYB/SAPADM_PASSWORD <sapadm_user_password>saphostctrl -user sapadm <sapadm_user_password> -function SetDatabaseProperty -dbname <SAPSID> -dbtype syb -dbuser sapadm -dbpass <sapadm_user_password> USER=SAPADMAdding sapsso user credentials and validationVerifying sapsso user validation in secure storeCheck the validation for sapsso user in the secure storeAdding sapadm user entry and password validation in secure storeVerifying sapdm user validation in secure storeStep-6: Stop all applications running on the SAP-ASE server and make sure no jobs are scheduled for the time of the update. To enforce that no jobs are running you can stop job scheduler before starting the update (SAP Host Agent 7.21 SP44 and higher will do this either way After the system is checked and no active database connections were found).Step-7: Execute below command to check if the upgrade will be successful or will it fail due to some error. It’s just a check and it won’t actually update any files.saphostctrl -function LiveDatabaseUpdate –dbname <SAPSID> -dbtype syb -updatemethod Check -updateoption TASK=CHECK_UPDATE_ASE -updateoption DROP_LOCATION=”<Drive>:DB_UPG”If any client connections are still present, you will receive a warning similar to thisStep-8: Execute the below command to update ASE DBsaphostctrl -function LiveDatabaseUpdate –dbname <SAPSID> -dbtype syb -updatemethod Execute -updateoption TASK=UPDATE_ASE -updateoption DROP_LOCATION=”<Drive>:DB_UPG” INIT_UPDATE_ASE Internal preparations for the SAP ASE update.CHECK_UPDATE_ASECheck user input (eg. software package specified in DROP_LOCATION).Check compatibility of package (with asemanifest.mf).Check that /tmp is not mounted with no exec property (prevents setup.bin from being executed properly).Check that SAP-ASE is up and running in case update is initial.Check that no clients are connected to SAP-ASE.Check if major update is restarted with wrong software package.Check if required credentials are valid (sapsid, sapsa, sapsso, sa, sybsid, SAPSR3, DR_admin).Check-in case of replication that site is standby or option UPDATE_PRIMARY=1 is setup.Check if the SAP-ASE runs with BuS or nonBuS application.Check that PATCH.SAR exists in case SAP-ASE runs with BuS application.If hadr_mode != 1 set ‘grant allow hadr login to sso_role’ (SAP-ASE 15.7.0.136 does not have it but requires it).Check CDS object requirements are met.Check without error that sybsystemprocs & sybmgmtdb have the recommended size (you might want to increase the size before/after the update).UPDATE_ASE_PRE_CONFIGIf required configure database options to support the update to target version.Disable job scheduler.Drop <SID>_XP and SYB_EJB servers for major update on BuS.If major update check and set net password encryption required.Disable auditing.If major update unlock sa.If BuS enforce dboption ddl in tran to be true in SID database.UPDATE_ASE_SOFTWAREStop SAP ASE servers.If major update copy sysam directory.If on AIX run slibclean.Update software with Sybase installer.If BuS and target SAP-ASE version is SP03 or higher install PATCH.SAR contents.If BuS and target SAP-ASE version is SP03 or higher and OS is Unix, change sapsid user env for ODBC (dbenv.sh/csh).UPDATE_ASE_POST_CONFIGIf major update and OS is Windows Update user profile environments in registryStart the SAP-ASE servers.If major update run sybatch/sqlupgraderes tool for SAP-ASE server.If major update run sybatch/sqlupgraderes tool for backup server.If BuS and OS is Windows delete XP service.If major update renames obsolete 15_0 directories to 15_0_OLD and restart SAP-ASE servers (make sure changes in env & on disk grab the right libraries).If required lock sa.Check and reset net password encryption required.Execute SAP ASE install scripts which are not default to sybatch/sqlupgraderes but recommended (eg. installdbextend).Enable job scheduler.UPDATE_ASE_BS_DEFAULTSIf required update remaining database options to support the update to target version.Execute syb_update_db.txt script to set BuS defaults.RECREATE_CDS_OBJECTSIf target is SP03 recreate CUDFs.If target is SP02PL04 or SP03 and source is lower than target or force recreate GTTs.If target is SP02PL04 or SP03 and source is lower than target or force recreate TUDFs.If target is SP03PL06 and source is SP03PL03 or target > SP03PL03 and (source is SP03PL03 or < SP03PL03) or force inline TUDFs.SETUP_BALDRIf target SAP-ASE version is SP03 or higher execute instbaldr script else setup DCF(wrapper for SetDatabaseProperty SETUP_LIST_METRICS=saptools).CONFIG_GPConfigure and enable granular permissions (replication will be turned off for this step).obsolete: REPLACE_DBIClose all database connections between SAP Host Agent and SAP-ASE/RMA.Unload the database drivers.Remove/rename the local libraries.Copy the required files from OCS folders to SAP Host Agent exe folder.RESET_UPDATE_ASEBackup files in sapdbctrl-config.Finish the SAP-ASE update.Final restart of the database for further usageCheck the DB versionDocumentation below can be referred for more information.Targeted ASE 16.x Release Schedule and CR list Information | SAP Help Portal2800483 – SYB: Upgrade / Update SAP ASE with SAP Host Agent 7.21 PL43 and later – SAP for Me1797040 – SYB: SAP Host Agent – Using global or local secure storage – SAP for Me Thanks & Regards,Mani Read More Technology Blogs by Members articles

#SAP

#SAPTechnologyblog