Python with SAP Databases [Part – 2]
In continuation to my previous blog: Here. Let us now see if we can utilize Python for some of our real case scenarios.
Data is an integral part of business growth and so is the availability of database and validity of backups. Monitoring Database backups and its validation is a part of routine job of SAP Basis administrators, Database administrators and other IT administrators. It takes significant efforts to get these jobs done.
Can we simplify it with few code lines in Python? – the answer is ‘Yes’.. continue reading.
In this blog I am writing about Backup monitoring using Python connector modules from a remote machine with the help of database instant client and ODBC connector.
In my example I’ll be using Oracle Database running RMAN backups and I’ll be querying SAP ABAP table ‘SDBAH’ and Oracle RMAN table ‘V$RMAN_BACKUP_JOB_DETAILS’.
Shown as example below – SID1, SID2 and SID3 are three SAP systems. SID1 and SID2 are SAP ABAP systems for which we will fetch the data from SDBAH table while SID3 is SAP JAVA system for which data will be fetched from RMAN_BACKUP_JOB_DETAILS.
The results can be seen as program return and will also be saved in a basic text file with some formatting.
Have a look at the program below:
Step wise break up of the program for better understanding (Please make sure that you read the comments in the program as well):
Step 1: With each program run our intention is to overwrite the content of the text file (append action is also possible).
Step 2: Import the Python Function module which will be used to establish Database connectivity. In this example we are using ‘cx_oracle’. (Remember we have other modules as well, see my earlier blog Here)
Step 3: Oracle Instant client installation is necessary on the machine from where you need to establish remote Database connection. Once installed inform the program where to find the locally installed Oracle client. This can be achieved as shown below.
Step 4: The variables shown in the screen shot below SID1, SID2, SID3 define the connection strings. Each defining the user/password@<DB host>:<listener port>/SID.
One variable defines connection to one system. I am only showing three for example but you can add as many as desired.
Step 5: Once the variables are defined we will now define the Functions. I will define two functions: ‘database_conn_abap’ and ‘database_conn_java’ (One for ABAP systems and the other for JAVA systems).
These functions will then use the variables defined above to make connection and execute the function body. For basics on Python Function read Here
Step 6: Following screen shot shows the function body which I am using to fetch the database ID and the database backup logs, the logs are read from SAP table ‘SDBAH‘ DBA log table. I am only fetching the latest two rows assuming the database backup checks are performed daily. The query can however be modified to fetch more logs.
Also in the function body we are writing and saving the program output to a file called ‘backup_status.txt‘ location of which can be defines as seen.
Step 7: Function definition for SAP JAVA system (same as we did in step 5 above).
Step 8: Function body for JAVA systems. Similar to the function for ABAP, I am fetching the database ID here but for database backup logs I am querying the Oracle RMAN table V$RMAN_BACKUP_JOB_DETAILS.
Also printing and saving the logs to the same text file (this time the action will be to append in addition to the logs written by the first function).
Step 9: Finally all we need to do is to call the respective functions. For SAP ABAP systems call function – database_conn_abap
and for SAP JAVA systems call database_conn_java function.
In my case SID1 and SID2 are ABAP systems and SID is JAVA systems. Remember that you need to pass the variable values along with the function name.
Result of the program run:
Output on IDLE: Python’s Integrated Development and Learning Environment.
Saved as a text file
In both the outputs above, we see the results contain System ID, Backup type, Backup status and the date of execution. It can be further customized with changes in the respective queries.
Further file formatting is also possible. Example above shows only basic formatting.
Conclusion: In a similar manner you can write programs to check Database availability, Database version, Database uptime etc.
Please share your feedback and your thoughts. Write back to me for questions and queries.