SAP ASE (Sybase) Archives » Page 5 of 6 » Musings of an IT Implementor

SAP ASE – Blocking Factor Madness

I spent around a day looking into a performance issue with a specific peice of SQL in an ERP 6 system running on a SAP ASE database.
The system has recently been migrated from Oracle, so we were expecting issues with hints, however this didn’t seem to be an index choice issue.
Look at the following two SQL statements, the first one is the system experiencing a performance problem:

The second picture is a system where the performance issue doesn’t exist:

Can you spot the difference?
Hint: Look at the number of question marks in the prepared statement.
The number of question marks indicate the number of items included in the “IN LIST” of the WHERE clause.

Since the ABAP SQL statement will be interpreted at the Kernel level, there is no way to see any difference in the ABAP layer other than this output from a SQL trace.
The consequence of the first statement (with fewer question marks) are that the SQL statement is executed multiple times in order to query for the same “PERNR” records. This can result in as much as 4 to five times more effort for the SAP layer, plus the database layer. Which adds more to the database response time and a little to the “processing time”.

What impacts the number of question marks? Simple, the parameters “rsdb/*blocking_factor” at the Kernel level, will adjust how many parameters are fed into the prepared statement in the DBSL layer.

SAP Note 1996340 – SYB: Default RSDB profile parameters for SAP ASE will provide all the answers.
The SAP note also answered my specific issue, which was why was one system in the landscape different. The answer was that the production system (where the problem was seen) had it’s parameters mostly carried across during a migration from Oracle. Whereas a smaller “release” system had it’s parameters left behind to die with the Oracle database.

As you will read in the SAP note 1996340, these rsdb parameters are pretty essential and should be re-evaluated when changing database platform.

Always re-evaluate all parameters when migrating from one platform to another.
Don’t assume that someone more experienced has set them with some future knowledge of the landscape/setup.

SAP ASE SQL Script – Who Has Allow HADR Login

Whilst working with SAP ASE, I found it useful to have a ready made SQL statement for determining who (which user accounts) has “allow hadr login” privileges.
In my scenario, I needed to be able to check if a specific backup server login account had the “allow hadr login” privilege, to permit backups of a standby SAP SRS replicated database.
Normally you are unable to login as any “normal” user into the standby database without the “allow hadr login” privilege.

use master
go
select p.uid,u.name,CONVERT(char(20),v.name)
from sysprotects p,
master.dbo.spt_values v,
sysusers u
where p.action=v.number
and v.name=’Allow Hadr Login’
and p.uid = u.uid
go

SAP ASE Job Server Error

Whilst administering a SAP ASE based SAP system, I came across an issue in the ASE Job server error log “JSTASK.log”:

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker ct_connect() failed.

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob: jsd_MakeConnection() failed for user sapsa to server SID

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob() failed for xid 66430

00:140737317369600:140737340581728:2016/02/24 16:55:00.87 worker Client message: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.

The issue was caused by a change of the sapsa user password whereby the SAP recommended method of using the hostctrl process, wasn’t followed.
The recommended method updates the sapsa user, the secure storage file plus also the external login for the Job Server.
This is mentioned at the very end of SAP note 1706410 (although it is suggested that the process in this note is no longer followed to change the passwords).
To fix the issue, follow finals steps in the SAP note 1706410:

isql -X -Usapsa -S<SID> -w999

use master
go
sp_helpexternlogin
go

Server Login Externlogin
———————- ——————– ————
SYB_JSTASK sapsa sapsa

Drop the SYB_JSTASK entry:

exec sp_dropexternlogin SYB_JSTASK, sapsa
go

Re-create it with the new password:

exec sp_addexternlogin SYB_JSTASK, sapsa, sapsa, ‘<new sapsa password>’
go

This should fix the issue.

SAP ASE–login to Secondary HADR DB as sapsso

When running SAP ASE in HADR mode with SAP SRS, you are not able to log into the secondary database as the sapsso database user.

This is because the user is redirected to the primary database in the HADR pair.

To get around this, you must grant the ASE privilege ‘allow hadr login’ to the sso_role as follows:

isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> grant allow hadr login to sso_role
2> go

Once you’re done doing what you needed to do as the sapsso user on the secondary database, then you can simply revoke the privilege as follows:

isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> revoke allow hadr login from sso_role
2> go

SAP ASE with SAP Replication Server – Node ID

While working on a SAP ASE database configured with SAP Replication Server (SRS) as part of a HADR pair, you may want to know how to identify which of the two databases in the HADR pair, you’re working on.

The only sure method I found is to query the @@nodeid global variable.
This nodeid value is different for each of the ASE databases.
Therefore if you’re logged onto the primary node, you will see a different ID.

I found this to be exceptionally useful when logging into a database as sapsso, since this user is redirected to the primary database in a HADR setup.

UPDATE: July 2016, I’ve since found this little gem:

Category: SAP ASE (Sybase)

SAP ASE – Blocking Factor Madness

SAP ASE Job Server Error

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker ct_connect() failed.

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob: jsd_MakeConnection() failed for user sapsa to server SID

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob() failed for xid 66430

00:140737317369600:140737340581728:2016/02/24 16:55:00.87 worker Client message: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.

isql -X -Usapsa -S<SID> -w999

use master
go
sp_helpexternlogin
go

Server Login Externlogin
———————- ——————– ————
SYB_JSTASK sapsa sapsa

exec sp_dropexternlogin SYB_JSTASK, sapsa
go

exec sp_addexternlogin SYB_JSTASK, sapsa, sapsa, ‘<new sapsa password>’
go

SAP ASE with SAP Replication Server – Node ID

> isql -Usapsa -S<SID> -w999 -X

1> select ASEHOSTNAME()
2> go

——————————-
SERVERHOSTNAME

(1 row affected)

use mastergoselect p.uid,u.name,CONVERT(char(20),v.name)from sysprotects p, master.dbo.spt_values v, sysusers uwhere p.action=v.number and v.name=’Allow Hadr Login’ and p.uid = u.uidgo

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker ct_connect() failed.

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob: jsd_MakeConnection() failed for user sapsa to server SID

00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker jsj__RunSQLJob() failed for xid 66430

00:140737317369600:140737340581728:2016/02/24 16:55:00.87 worker Client message: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.

isql -X -Usapsa -S<SID> -w999 use mastergosp_helpexternlogingo Server Login Externlogin———————- ——————– ————SYB_JSTASK sapsa sapsa

exec sp_dropexternlogin SYB_JSTASK, sapsago

exec sp_addexternlogin SYB_JSTASK, sapsa, sapsa, ‘<new sapsa password>’go

isql -Usapsa –S<SID> -w999 -X 1> use master2> go 1> grant allow hadr login to sso_role2> go

isql -Usapsa –S<SID> -w999 -X 1> use master2> go 1> revoke allow hadr login from sso_role2> go

> isql -Usapsa -S<SID> -w999 -X 1> select ASEHOSTNAME()2> go ——————————-SERVERHOSTNAME (1 row affected)

use master
go
select p.uid,u.name,CONVERT(char(20),v.name)
from sysprotects p,
master.dbo.spt_values v,
sysusers u
where p.action=v.number
and v.name=’Allow Hadr Login’
and p.uid = u.uid
go

isql -X -Usapsa -S<SID> -w999

use master
go
sp_helpexternlogin
go

Server Login Externlogin
———————- ——————– ————
SYB_JSTASK sapsa sapsa

exec sp_dropexternlogin SYB_JSTASK, sapsa
go

exec sp_addexternlogin SYB_JSTASK, sapsa, sapsa, ‘<new sapsa password>’
go

isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> grant allow hadr login to sso_role
2> go

isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> revoke allow hadr login from sso_role
2> go

> isql -Usapsa -S<SID> -w999 -X

1> select ASEHOSTNAME()
2> go

——————————-
SERVERHOSTNAME

(1 row affected)