Tables Archives » Musings of an IT Implementor

Password-less SAP ASE Database Access

UPDATE: Since ASE 16.0 SP03, the aseuserstore binary is now available. This has the ability to construct a local, encrypted credential store for each Linux user, and can be easily used (just like hdbuserstore for HANA) to automate password-less database calls via isql with the “-k” parameter.

Like most companies, you will have the need to automate specific database tasks directly in the database.
As an example, frequent database backups.

In this post I go through some possible options, but discuss in-depth the use of the sybxctrl binary to facilitate password-less access to execute database tasks.

What are the DB Job Automation Options?

Within SAP ASE databases, if you have an attached ABAP stack, then DB13 can be used to schedule database level jobs, with the caveat that the ABAP stack must be running for jobs to run.

Another alternative, is to use the SAP ASE JobScheduler to automate tasks.
The JobScheduler comes with the SAP ASE system and runs as a separate process.
Using the JobScheduler you can configure jobs/tasks to be executed even if there is no ABAP stack.
However, it means that any configuration must be repeated across all databases and you will lack any central job scheduling control. Also, if the JobScheduler has a problem connecting to the database, then you will not get any backups (especially transaction log backups).

As a good catch-all and an alternative to the above options, there is another way.
Use an enterprise job scheduler (e.g. SAP Business Process Automation) combined with a centrally accessible server-side script to call the database routine/utility.
One central schedule and one script. Simple.
The added advantage being that the central job scheduler will also be responsible for controlling your SAP business processes. This allows an operator to tightly control and monitor the backup window around the critical business processes and also allows tighter integration of backups with cloud level operations (VM snoozing…).

There’s generally one problem with the use of a custom script, and that is where to put the password for the SAP ASE database login.
You can hardcode it into the script, and restrict permissions as much as possible, but this means you will have just one password (unless you put ALL the passwords for all the databases the script will be executed against). It’s also a big “NO” from the auditors and cyber security teams, since there are a undoubtedly some exploits available that will allow access to the script file in some form or another.

Introducing sybxctrl

A possible solution to the problem of where to put the password, is to use a little known binary of the SAP Kernel for ASE databases, called sybxctrl.
Within your usual DIR_CT_RUN location, there will exist somewhere, a binary called sybctrl and its twin, sybxctrl.
They are the same binary code but separate files.

What’s the difference? Well, it’s purely about Linux permissions.
After you install an ABAP Kernel (on Linux), you’re supposed to run the saproot.sh script, which prepares certain SAP binaries, adjusts ownerships and configures executable permissions.
With regards to sybctrl, it is owned by the Linux root user, and it also has the SUID permission set (numerical: 4750, character: swxr-x—).

When this binary is executed by any member of the sapsys Linux group (<sid>adm or syb<sid> users are members of this group), then it will switch executable context to that of the root Linux user. In essence, it executes almost as if you were the root user executing it.
This is a useful Linux feature, to be able to execute binaries in this way; however, it can also leave a security hole.

To provide a level of protection to the O/S during the standard Linux binary execution process, when an SUID enabled binary is executed the Linux environment puts a restriction around the contents of the user’s environment by arbitrarily setting variables like PATH and LD_LIBRARY_PATH.
This means that execution of sybctrl will limit the ability for loading of additional shared libraries in non-standard directory locations, which may be required for certain functions.

Why does sybctrl need root? This is fairly easy to explain, it’s because you normally log onto a Linux SAP system as the <sid>adm Linux user then start all components including the SAP ASE database. Except the SAP ASE database runs as Linux user syb<sid>. So, to be generic, the binary sybctrl is used and executed from <sid>adm via the root user (probably so that shared memory can be freed), before it switches to syb<sid> to start or stop the database.

So how does sybxctrl differ to its more powerful sybctrl? Simple, it doesn’t have the SUID permission set on it.
It doesn’t need to run via root for what it does, but it does need access to the SYBASE_OCS and other shared library locations. So, to ensure that those environment variables are maintained throughout execution, this separate copy of sybctrl was required.

How can we use sybxctrl?

As the <sid>adm Linux user, you can execute sybxctrl with no parameters to return the usage information (the parameters that it does accept).
Of interest to us for our password-less database script, are the parameters:
load_script
unload_script
exec_script

Here’s how we can use sybxctrl to embed a script into the database, then execute it without needing a password.
Before we can use the sybxctrl binary, you need to first ensure:
– You have Kernel 7.22_EXT pl500 and above (or 7.49+).
– You run the saproot.sh to correctly set up permissions on the SAP Kernel binaries.
– You run a “stopdb” as <sid>adm. This will actually copy across the sybxctrl from sybctrl.
– You run sybxctrl to load in a script.

During the execution of sybxctrl with the “load_script” parameter, a new database table called SYBSISQL is created in the DBO schema (not the usual SAPSR3[DB] schema!) of the <SID> ASE database. This table is where the script that you load into the DB, is stored.
Let’s now create a test script as Linux user <sid>adm:

echo "select user_name()\ngo" > /tmp/myscript.sql

All this script will do is print out the current database username.
Now we load the script into the database using the sybxctrl binary and the “load_script” parameter as follows:

sybxctrl load_script /tmp/myscript.sql -exe $SYBASE/$SYBASE_OCS/bin/isql -auth sapsa

You are prompted for the sapsa database user password.
Enter the password and the load should succeed.

What just happened?
You have just populated that new database table (<SID>.DBO.SYBSISQL) with the text of the script that you created, plus the details of how to run it (which binary and the full path, plus parameters).
We have told sybxctrl that we want to use the isql binary (SAP ASE command line SQL utility), and that we want it to execute as the sapsa database user (the “auth” parameter).

Now we’ve loaded the script, we can unload it and check it as follows:

rm /tmp/myscript.sql

sybxctrl unload_script myscript.sql /tmp/

NOTE: You remove the path from the name of the script.
You are prompted again for the sapsa database user password.
Enter the password and the unload should succeed.

We can now see the script contents:

cat /tmp/myscript.sql

Finally, we can now try and execute the script.
The execution happens as though it was a batch operation, so we need to provide an output file for any output from the script.

Execute the script as follows:

sybxctrl exec_script myscript.sql /tmp/myscript.out -auth sapsa

Did you notice, you didn’t need to enter the password for sapsa!

Check the output file that we specified:

cat /tmp/myscript.out

To remove the script from the database:

sybxctrl delete_script myscript.sql

Enter the sapsa password and the deletion should succeed.

We can now embed a pre-configured script into the SAP ASE database, which can be executed at the O/S level as any Linux user with permissions to run sybxctrl, without requiring a password and it will execute as the sapsa database user.
There are many possibilities for this setup when you understand the full parameter list of both sybxctrl and isql.
As an example, you could pipe in parameters to the embedded script…
Couple the script with some standard code and configure the ASE database to use ASE backup configurations and you could have an automated backup routine that is password-less.

Are there any problems in using sybxctrl?

Yes, a few.

sybctrl will be patched as part of the kernel patching, but synxctrl is not. So you must ensure that you either run a “stopdb” at some point to pickup the new script, or manually copy it into place after Kernel binary patching is completed.
isql will be patched as part of the ASE patching. This changes the checksum value of the binary file which is recorded during the “load_script” in the new SYBSISQL table.
Therefore, after ASE binary patching, you should unload and re-load the script to re-create the table entry.
Any embedded scripts will be carried along with any SAP system copies if you use the database backup/restore method. But not if you use the R3load tools.
If you use SAP Replication Server (SRS), this table and it’s contents will be replicated across to the secondary database (as the table is in the <SID> database).
Bear this in mind for the contents of the backup script, because they may need to be generic if you want to run it on secondary or tertiary databases in an SRS setup.

Happy scripting.

SAP SLD Change Log Cleanup Table Reorg

When you apply changes or content updates to the SLD (system landscape directory) in SAP, the change log grows.
It’s possible to see the change log entries from the SLD administrator page.
Due to this growth, the underlying database table will benefit from reorganisation at some point.
In our scenario, this was on DB2.
We decided to run the cleanup, but first here was the size of the table BC_SLD_CHANGELOG:

db2prd> db2 “SELECT TABNAME, NPAGES FROM SYSCAT.TABLES
ORDER BY NPAGES DESC”
TABNAME                       NPAGES
—————————- ——————–
BC_SLD_CHANGELOG              14162
BC_SLD_INST                   12830
J2EE_CONFIGENTRY              5870
BC_SLD_ASSINST                3905
EP_ATTR_VALUES3               3082
J2EE_CONFIG                   2163
DBH_STG_PKG_CACHE_METRICS     1421
SYSCOLDIST                    1193

Then delete the change log in https://<server>/sld
Select “Administration -> Maintenance -> Clean Up Change Log -> Remove Entries”.
I then scheduled a standard job to do this work from the “Cleanup Task Configuration” tab.
Finally, reorg the following tables to release the space:

db2 “REORG INDEXES ALL FOR TABLE SAPPODDB.BC_SLD_CHANGELOG”

db2 “runstats on table SAPPODDB.BC_SLD_CHANGELOG AND INDEXES ALL”

db2 “SELECT TABNAME, NPAGES FROM SYSCAT.TABLES WHERE TABNAME = ‘BC_SLD_CHANGELOG'”

TABNAME NPAGES
—————————- ——————–
BC_SLD_CHANGELOG 1205

That’s it.

HANA OOM Error Tip #1 – Partition Tables Correctly

If your HANA system is regularly experiencing OOM (Out Of Memory) errors, then there are a number of things that you can do to try and reduce memory consumption.

Tip #1: Partition Large Tables Correctly
If there are large Column Store tables in your HANA system, you should partition them.
Whilst this is an absolute must in a HANA scale-out scenario (for enabling multi-node parallelism), it might not be so obvious that it can also help in a single node HANA system.
Partitioning a column table means that only the required partitions of the table are loaded into memory when accessed, you would think.

Partitioning a large table into smaller chunks will therefore help to reduce the memory usage of the table during SQL queries and also during updates.
During updates, each partition gets its own delta cache area.

Choosing how to partition a table is slightly more difficult and will depend on whether the table is a custom table, SAP standard table or other.
Plus, you will need to know what and how queries or updates are executed against the table. A period of monitoring is suggested, to enable to you collect the required information to be able to make a decision.

One thing you should try to do, is partition the table using the most logical, selective columns.
Read on for a simple example with a twist!

A simple example, a single node HANA system has 1 large column table T1.
The table is partitioned into ranges based on the date column INVOICEDATE:

CREATE COLUMN TABLE “DARRYL”.”T1″ (“INVOICEREF” VARCHAR(1) NOT NULL ,
     “INVOICEDATE” DAYDATE CS_DAYDATE NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE WITH PARAMETERS (‘PARTITION_SPEC’ = ‘RANGE year(INVOICEDATE) 2000-2001,2001-2002,2002-2003,*’)
;
CREATE UNIQUE INDEX “I1” ON “DARRYL”.”T1″ ( “INVOICEREF” ASC ) NONLEAF PARTIAL KEY LENGTH 1;

As you can see, I’ve created 3 partitions by year: 2000 to 2001, 2001 to 2002 and 2002 to 2003.
This will actually create 4 partitions: year 2000, year 2001, year 2002 and year <OTHER>.

HANA Table Distribution

Insert 5 records into the table:

insert into darryl.t1 (INVOICEREF,INVOICEDATE) values(‘1′,’2000-01-01’)
insert into darryl.t1 (INVOICEREF,INVOICEDATE) values(‘2′,’2001-01-01’)
insert into darryl.t1 (INVOICEREF,INVOICEDATE) values(‘3′,’2002-01-01’)
insert into darryl.t1 (INVOICEREF,INVOICEDATE) values(‘4′,’2003-01-01’)
insert into darryl.t1 (INVOICEREF,INVOICEDATE) values(‘5′,’2004-01-01’)

Inside the Table Distribution tab, you will now see that the records have been inserted according to their values into the respective partitions (see Raw Record Count field on the right):

HANA Table Distribution

The last two records for year 2004 and 2003 are in the fourth partition.
You can also see that each partition has a Delta Size, and that the Delta Size for the fourth partition with the most records, is larger than the other partitions.
Unload the table from memory:

HANA Unload Table from memory

Refreshing the Table Distribution tab now shows the table partitions to have zero size in memory:

HANA Table Distribution

Now select the records for the years 2004 and 2005 only:

select * from darryl.t1 where invoicedate in (‘2004-01-01′,’2003-01-01’)

Refreshing the Table Distribution tab now shows the tables to have non-zero size in memory for ALL partitions!

HANA Table Distribution

All of the records from all of the partitions appear to be loaded!
What went wrong?
Well, it’s simple, we didn’t create an index on the column INVOICEDATE.
This forced HANA to scan through the entire table to access the required records, meaning that it needed to load them all into memory.

Let’s create an index in INVOICEDATE:

CREATE UNIQUE INDEX “I1” ON “DARRYL”.”T1″ ( “INVOICEDATE” ASC ) NONLEAF PARTIAL KEY LENGTH 1;

Unload the table from memory:

HANA Unload Table from memory

Refreshing the Table Distribution tab now shows the tables to have zero size in memory:

HANA Table Distribution

Now re-execute the SELECT statement:

select * from darryl.t1 where invoicedate in (‘2004-01-01′,’2003-01-01’)

Once again, on the Table Distribution tab, we can see that it has accessed all partitions, AGAIN!:

HANA Table Distribution

What went wrong this time? Well, HANA doesn’t yet have any statistics on the table data, so it simply ignored the index.
If you now unload the table from memory once again (we haven’t done anything else):

HANA Unload Table from memory

Now re-execute the SELECT statement:

select * from darryl.t1 where invoicedate in (‘2004-01-01′,’2003-01-01’)

Look at the Table Distribution tab:

HANA Table Distribution

You can see that HANA has now only accessed the final partition of the table. The other partitions have not been loaded into memory.
At first I thought this feature might be due to statistics, so I tried removing them from the table T1 (drop statistics on T1;). Then I retried the process of unloading and re-running the query. This had no effect, HANA correctly went straight to the fourth partition.
This left me with one other option, the Plan Cache.

Clearing the Plan Cache using:

ALTER SYSTEM CLEAR SQL PLAN CACHE

I then re-ran the test by unloading the table from memory:

HANA Unload Table from memory

Re-executing the SQL SELECT:

select * from darryl.t1 where invoicedate in (‘2004-01-01′,’2003-01-01’)

Look at the Table Distribution tab:

HANA Table Distribution

Bingo!
The Plan Cache was storing some form of execution plan statistics that meant that it was accessing the fourth partition straight away.
Each time the table is unloaded, the statistics from the existing Plan Cache remain and are re-used upon next execution of the query, which means HANA is able to go straight to the fourth partition.

Summary:
Partitioning is a great way of parallelising access to a table in HANA.
It also serves to help reduce memory by only loading specific partitions into memory when they are required.
In order to effectively use partitioning, you need to partition on an indexed column.
The initial access of a newly partition table with a new index, does not enable the benefits of partition until the second subsequent access of the table due to the Plan Cache. A method/process of pre-loading the execution plan/statistics into the cache is required.

SAP Unicode Conversion Sample DB Table Size

As an example, a non-Unicode SAP table (DBTABLOG) in an Oracle 11gR2 database (character set ALE32UTF16) without any Oracle compression, was ~80GB in total size (sum of table segments, excluding indexes).

Once this was exported to disk using R3load export, in preparation for a conversion to Unicode, it occupied ~70GB in the data files (DBTABLOG.00*).

Once this was re-imported into a new Oracle 11gR2 database with character set UTF8 (again, no compression) it occupied ~90GB (sum of segments, excluding indexes).

You must remember that this table is specific in it’s usage. It doesn’t have any rows deleted from it, it’s append only, so it should grow in a nice uniform manner and not be fragmented. There may be other tables where you could save space.
If you notice that your R3load export files are significantly different in size compared to the Oracle size, then you could have some serious fragmentation inside your Oracle database.