This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: sql

Korn Shell Calling SAP HANA – Hello Hello!

“So you’ve automated SAP HANA stuff huh?
What tools do you use?
Python? Chef? Puppet? Ansible? DSC/Powershell?“

No. I use Korn shell. ¯\_(ツ)_/¯

Me, Trying to Support Korn…

I find Korn shell is a lowest common denominator across many Linux/Unix systems, and also extremely simple to support.
It does exactly what I need.

For me it’s readable, fairly self-explanatory, easily editable and dependable.

…and Failing?

But I do know what you’re saying:

  • there’s no built in version control
  • it’s not easy to debug
  • it’s definitely not very cool 🤓
  • you can’t easily do offline development
  • my Grandad told me about something called Korn shell ?

Have you Considered

If you have no budget for tools, then you can start automating by using what you already have. Why not.
Don’t wait for the right tool, start somewhere and only then will you understand what works for you and doesn’t work for you.

Sometimes it’s not about budget. There are companies out there that do not allow certain sets of utilities and tools to be installed on servers, because they can provide too much help to hackers. Guess what they do allow? Yep, Korn shell is allowed.

Let’s Get Started

Here’s a sample function to run some SQL in HANA by calling the hdbsql (delivered with the HANA client) and return the output:

#!/bin/ksh
function run_sql {
   typeset -i l_inst="${1}" 
   typeset l_db="${2}" 
   typeset l_user="${3}" 
   typeset l_pw="${4}" 
   typeset l_col_sep="${5}" 
   typeset l_sql="${6}" 
   typeset l_output="" 
   typeset l_auth="" 
   typeset -i l_ret=0

   # Check if PW is blank, then use hdbuserstore (-U). 
   if [[ -n "${l_pw}" && "${l_pw}" != " " ]] ; then 
      l_auth="-u ${l_user} -p ${l_pw}" 
    else l_auth="-U ${l_user}" 
   fi

   l_output="$(hdbsql -quiet -x -z -a -C -j -i ${l_inst} ${l_auth} -d ${l_db} -F "${l_col_sep}"<<-EOF1 2>>/tmp/some-script.log 
		${l_sql}; 
		quit 
EOF1 )"
   
   l_ret=$?

   # For HANA 1.0 we need to trim the first 6 lines of output, because it doesn't understand "-quiet". 
   #if [[ "$(check_major_version)" -lt 2 ]] ; then 
      # l_output="$(print "${l_output}"| tail -n +7)" 
   #fi

   print "${l_output}" 
   return $l_ret 

}

To call the above function, we then just do (in the same script):

l_result="$(run_sql "10" "SystemDB" "SYSTEM" "SYSTEMPW" " " "ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence','log_mode')='overwrite' WITH RECONFIGURE")"

We are passing in the HANA instance number 10, you can use whatever your instance number is.

We can check the function return code (did the function return cleanly) like so:

if [[ $? -ne 0 ]] ; then 
   print "FAILED" 
   exit 1; 
fi

Here’s what we’re passing in our call to hdbsql (you can find this output by calling “hdbsql –help”):

-i instance number of the database engine
-d name of the database to connect
-U use credentials from user store
-u user name to connect
-p password to connect
-x run quietly (no messages, only query output)
-quiet Do not print the welcome screen
-F use as the field separator (default: ‘,’)
-C suppress escape output format
-j switch the page by page scroll output off
-Q show each column on a separate line
-a do not print any header for SELECT commands

If you wanted to return a value, then the “l_result” variable would contain the output.

Ideally, the function we wrote would be put into a chunk of modular code that could be referenced time and again from other Korn shell scripts.

You would also be looking to create some sets of standard functions for logging of messages to help with debugging. You can make it as complex as you wish.

In the call to “run_sql” we pass a column separator.
I usually like to use a “|” (pipe), then parse the returned values using the “awk” utility like so:

l_result="$(run_sql "10" "SystemDB" "SYSTEM" "SYSTEMPW" "|" "SELECT file_name,layer_name,section,key, value FROM SYS.M_INIFILE_CONTENTS WHERE layer_name='SYSTEM'")"

echo "${l_result}" | /bin/awk -F'|' '{ print $2" "$3" "$4 }'

When we execute the script we get the first 3 columns like so:

daemon.ini SYSTEM daemon 
diserver.ini SYSTEM communication 
global.ini SYSTEM auditing 
configuration global.ini SYSTEM 
backup global.ini SYSTEM
...

Obviously we don’t really embed the password in the script; it gets passed in.
You can either pass it in using the command line parameter method (./myscript.ksh someparam) or via the Linux environment variables (export myparam=something; ./myscript.ksh).
If you want you can even pipe it in (echo “myparam”| ./myscript.ksh) and “read” it into a variable.
You can also take a look at the “expect” utility to automate command line input.
Also, take a look at the “hdbuserstore” utility to store credentials for automation scripts (remember to set appropriatly secure privs on these database users).

That’s all there is to it for you to get started using Korn shell to call HANA.

You may also be interested in:

Locking HANA Database Users During Maintenance

Running SAP S/4HANA means there are now more direct HANA DB accesses through a variety of Analytics tools, development tools and external reporting systems.
This can present a problem when it comes to patching and maintenance of the system, since you would not want to officially release the HANA database back to end-users until you had performed your preliminary checks to conclude th patching was successful at all levels of the application stack.

Most BASIS administrators are familiar with the usual “tp locksys” command to be able to lock everyone except SAP* and DDIC out of the SAP ABAP application layer.
But what can be done to stop connections direct into the HANA database?
SAP note 1986645 “Allow only administration users to work on HANA database”, provides an attached SQL file which delivers a few new stored procedures and some new database tables.

The stored procedures include:
– 1 for “locking” out non-system users.
– 1 for “unlocking” non-system users (the exact reverse operation against the exact same set of users that was initially locked).
– 1 for adding users to an exception list table.
– 1 for removing users from an exception list table.

The tables are used to store an exception list of users to be excluded from the locking operation.
You will need to add the “SAPABAP1” S/4HANA schema, XSA DB user and cockpit user to the exception list!
Also add any backup operator user accounts needed to perform backups or if you need to leave enabled a specific set of test user accounts.
There is also a table used for storing the list of users on which the last “locking” operation was performed.

As well as “locking” (the HANA DB accounts are actually disabled) the user accounts, any active sessions for those user accounts are kicked off the database instantly.
This feature is useful in other ways (for example, emergency access to a severely overloaded/failing HANA database system).
Of course if you are running something other than S/4HANA on HANA (maybe Solman), then direct database access may not be a requirement, therefore this set of SQL stored procedures are not so relevant.

How do you implement the SQL?
– Download the SQL from the SAP note and save to a file.
– Either execute the SQL using in the TenantDB as the SYSTEM user in HANA Studio, HANA Cockpit or use hdbsql in batch mode (hdbsql doesn’t like the code to be pasted at the prompt).

How do you add users to the exception list:
– As SYSTEM in the TenantDB, simply execute the store procedures:

CALL SESSION_ADMINS_ADD_TO_EXCEPTED_USER_LIST (‘SAPABAP1’);

How do you utilise the feature?
– As SYSTEM in the TenantDB, simply execute the store procedures:

CALL START_SESSION_ADMINS_ONLY;

When you’ve finished and wish to “unlock” the previously locked accounts:

CALL STOP_SESSION_ADMINS_ONLY;

Manual Explain Plan in SAP ASE

When running SAP on top of SAP ASE database, you can use the DBACOCKPIT transaction to help produce an EXPLAIN PLAN (execution plan).
It’s also possible to use the “EXPLAIN” button from an SQL trace (from ST05 or ST12).
However, under certain circumstances it may not be possible (or it just may not function – it’s Java based) and you may want to use command line iSQL to generate the EXPLAIN PLAN output at the database level directly.
Here’s how.
From the SQL trace (ST05 or ST01) in the SAP GUI, you can double click to obtain the SQL text, which will usually be a prepared statement (contain ‘?’ in place of actual data).
It may look like the following (sorry for the large statement):

SELECT
    “MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” ,   “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
  FROM
    “PA0006”
  WHERE
    “MANDT” = ? AND “PERNR” IN ( ? , ? , ? , ? , ? )  /* R3:SAPDBPNP:11498 T:PA0006 M:100 */  /*unc. rd.*/
Variable
A0(CH,3)  = 100
A1(NU,8)  = 00000001
A2(NU,8)  = 00000002
A3(NU,8)  = 00000003
A4(NU,8)  = 00000004
A5(NU,8)  = 00000005

The text after the first “/*” is comment text added by the SAP DBSL layer to indicate the calling module and line number, as well as the client and table against which it is executing.
All you need to do to use this SQL directly at the DB level is to populate the ‘?’ with the actual data which you can see in the variable list at the bottom left.
The variables run in order, left to right.
Therefore, A1 will be the first ‘?’ in the SQL statement.
You will also see from the variable list that the ABAP data type is included.
CH = CHARacter and NU = Number.
All you need to know, is that SAP rarely uses the underlying database data type, ensuring the agnostic nature of the OPEN SQL.
This means you enclose all of the variables in quotes as though they were characters.
Based on the example above, removing the comment area, you should then have the following SQL statement:

SELECT
    “MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” , “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
  FROM
    “PA0006”
  WHERE
    “MANDT” = ‘100’ AND “PERNR” IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )

Now to run it at the database level.
Log onto the database server as either the syb<sid> or <sid>adm (I’m using Unix/Linux) user (both usually have the isql binary in their path).
Once logged on, run isql and connect into the <SID> database as the SAPSR3 (or SAPSR3DB on Java stacks) user:

isql –USAPSR3 –S<SID> -D<SID> -W999 –X

Now you’re connected, we need to set some session settings so that we get the explain plan output.
NOTE: The “use” is optional as we’ve already specified the DB we want to use with the “-D” parameter at the isql command line.

use <SID>
go

We want to show the resultant execution (EXPLAIN) plan.

set showplan on
go

We would like some additional useful details from the EXPLAIN plan.

set statistics io on
go
set statistics time on
go
set statistics plancost on
go

We don’t want any of our SQL to be cached, just incase we want to change it and the system decides to try and use the most efficient one.

set statement_cache off
go

We make life easier by enabling the use of quotes, since SAP has already put them there for us.

set quoted_identifier on
go

Finally, we paste the SQL itself, followed by the ASE SQL command terminator “go”:

SELECT
    “MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” , “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
  FROM
    “PA0006”
  WHERE
    “MANDT” = ‘100’ AND “PERNR” IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )

Here’s the sample output plan:

================ Lava Operator Tree ================
                        Emit
                        (VA = 3)
                        r:36 er:39
                        cpu: 0
             /
            NestLoopJoin
            Inner Join
            (VA = 2)
            r:36 er:39
            l:0 el:31
            p:0 ep:25
/                     
OrScan                  IndexScan
Max Rows: 5             PA0006~0
(VA = 0)                (VA = 1)
r:5 er:-1               r:36 er:39
l:0 el:-1               l:35 el:31
p:0 ep:-1               p:1 ep:25
=====================================================

Table: PA0006 scan count 5, logical reads: (regular=35 apf=0 total=35), physical
reads: (regular=1 apf=0 total=1), apf IOs used=0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 10 ms.  Adaptive Server elapsed time: 24 ms.

(36 rows affected)

Reading the plan from the lowest “VA” value, we start with an OrScan (breaks down the 5 “IN” list values we passed into the query, into an SQL “OR” statement).
The OrScan is returning 5 rows (r:5) and performed zero logical reads (l:0) and zero physical reads (p:0).
There were no estimated logical or physical reads (el & ep) due to the type of operation.
We were then using an index (IndexScan) of PA0006~0 (a primary key on table PA0006).  Of which we estimated that we would return 39 rows (er:39), based on statistics (I would hope), but we actually returned 36 (r:36).
We performed 35 logical reads and 1 physical read on the index (I believe that logical reads encompass physical reads, just like Oracle).
Which was better than we anticipated with the estimated logical and physical values of 31 and 25 respectively.
Both the OrScan and the IndexScan are accessed from the parent NestLoopJoin(VA = 2).
We return 36 rows (r:36) upto the EMIT for return back to the client (isql in this case, but it would normally be our SAP dialog work process).
The summary at the very bottom of the SQL output shows a nice set of easy to interpret values.
Because our query was broken into an “OR” statement like this:

SELECT column1
  FROM table1
WHERE column1 = value1
      OR   column1 = value2
      OR   column1 = value3
      OR   column1 = value4
      OR   column1  = value5

It means that the index was scanned 5 times against the matching key column (scan count 5).
The sum of the logical reads and physical reads is shown (APF reads – Asynchronous Pre-Fetch, are reads to/from the data cache in a hopeful way).
We then have visibility of the actual SQL execution time plus the required CPU time and the overall elapsed time.
Additional information can be found on the Sybase infocenter site, although I feel it’s lifetime is limited.

SAP Netweaver 731 Oracle Create DB Statement

By default, when you use the Software Provisioning Manager (SWPM) to create a new NW731 Oracle database, it will generate and run an Oracle “CREATE DATABASE” statement as follows:

SQL> CREATE DATABASE DB1 CONTROLFILE REUSE 
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXLOGHISTORY 1000
MAXDATAFILES 1000
MAXINSTANCES 50
NOARCHIVELOG
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
DATAFILE '/oracle/DB1/sapdata1/system_1/system.data1' SIZE 350M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE PSAPTEMP TEMPFILE '/oracle/DB1/sapdata1/temp_1/temp.data1' SIZE 50M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
UNDO TABLESPACE PSAPUNDO DATAFILE '/oracle/DB1/sapdata1/undo_1/undo.data1' SIZE 100M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
SYSAUX DATAFILE '/oracle/DB1/sapdata1/sysaux_1/sysaux.data1' SIZE 200M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
LOGFILE GROUP 1 ('/oracle/DB1/origlogA/log_g11m1.dbf') SIZE 200M  REUSE ,
GROUP 2 ('/oracle/DB1/origlogA/log_g12m1.dbf') SIZE 200M  REUSE ;

Notice that both the character set and national character set are UTF8.

Checking R3load Export Progress

When running R3load to export an Oracle SAP database, it’s difficult to see the exact table or tables that is/are being exported.

You can log into the Oracle database during the R3load execution and use the following SQL to follow the progress:
SQL> select sess.process, sql.sql_text
       from v$session sess,
            v$sqltext sql
      where sess.type='USER'
        and sess.module like 'DBSL%'
        and sql.sql_text like '%FROM%'
      order by sql.part;
This will show the OS process ID of the R3load process, plus the table (from the FROM clause)  that is currently being exported.
For large tables, you may be able to see the progress in the V$SESSION_LONGOPS table by looking for rows where TOTALWORK != SOFAR.