SAP HANA Academy - SDA: Connecting to Oracle (1/2) [SPS 06]

In this video covering the Smart Data Access features of SAP HANA Studio, we examine how to connect to a remote Oracle server.

---

Closed Caption:

welcome to this a planner academy my
name is Bob and in this series of videos
we're going to be looking at some of the
new features and sat panner Support
Package stack 7 in this series of two
videos
we're going to be looking at setting up
snot data access using Oracle Database
12c in the second of these videos what
we're going to be doing is focusing on
the saplings studio part where we create
a remotes connection and then we extract
some data from oracle and combine it
with data or federated with data in SAP
HANA in this video we're going to be
focusing on the SAP HANA server parts
where we have to set up the Oracle odbc
drivers and unixodbc in order to make
all the pieces work so in this video
we're going to be looking at getting our
SAP HANA server ready with smart day or
access so essentially what we need to do
is to main things we need to install and
configure I SQL which is unixodbc and
then we need to install and test our
Oracle odbc drivers now firstly let's
have a lockout the data set so i'm using
oracle 12c and the Oracle 12c its
database which I've put on this windows
box so of course because it's in a
windows box we want to be able to access
the data through our SAP HANA server
which is on linux that's why we need to
install those unixodbc drivers so just
to give you a bit of background if i go
into my oracle database what we've got
in our list of tables is a table called
EMP which is employee and essentially
this is the tater the the data that
we're going to be connecting to and
importing so I'm sorry federating with
our sub- to do so will be connecting to
this data set and you see simply a list
of employees with various states
and salaries and departments so the only
thing that you really need to make sure
on your Oracle box is that your
listeners been configured so it's very
straightforward to do if you go to your
your database and you launch your net
your configuration and migration tools
you've got your net configuration
assistant and I've already done this and
essentially i've configured my listener
to listen on the port 1521 so of course
what that means is that you have to open
up the ports where 1521 when the wood
where where the Oracle database is
running so again here i'm using the
standard ports are 1521 so now that
we've done that i can just finish we can
shut down oracle you can see that we're
just going to be connected to a table
called em in the root schema it's a very
basic default oracle 12c installation so
the foot there's a few things that we
need to do initially so to to do this
let's just go and have a look at the
upcoming guide so if i do a search for
sap hana administration guide we should
have a link to it and if I actually go
to do a search for oracle so if i go on
the box and search for oracle you see
here in section 6.1 in this version of
the guide we've got Oracle Database 12c
settle so there's about half a page of
instructions and essentially this is
what I'm going to go through in a bit
more in-depth the key thing initially is
that's the perversion of unixodbc has to
be too . 3.1 by default on our SAT panel
one instances the versions that we've
configured we have a much lesser version
i think it's 2.2
arrow in a previous video i installed
microsoft sequel server with smart day
or access and then we have to upgrade to
2.3 points0 but here we need to upgrade
to 2.3 . one so these are the
instructions here which I'm going to go
through in order to get the odbc drivers
working on the side panel and xbox for
smart day or access using oracle so to
do this i'm just going to minimize
Madeline guide and of course we're going
to use potty so i've configured my look
at my host ini file so I'm connecting
Tanana which will connect to my box
itself and if i go into if I just do it
is ql double dash help that will tell me
the version of MySQL at the moment and
you can see here it's too . 3.0 that's
what we're going to have to upgrade so
just to give you some other things that
I've done I've created a folder you can
just don't make their you doing this
from scratch and i created a folder
called my odbc underscore drivers and if
I just do an LS in that folder you can
see that I've already got so again and
this is from a previous video i've
already got microsoft sequel server or
DC drives installed and obviously I had
to upgrade unixodbc 222 . 3.0 so
obviously the tar files and the actual
microsoft drivers and the unixodbc too .
3.0 so obviously we need to get hold of
those drivers so I'm going to do this on
my windows box you can actually do it
directly on your linux box if you prefer
so there's a few things that we have to
download obviously the first thing is
going to be unix odbc to get the right
version if you do is searching google
for unixodbc you can then go to the
unixodbc dot org and then if you go to
the download section on the left hand
side you can get the two . three points
to version of the file so I'm going to
keep this window open and actually
download that tar . Jeezy file
that's only two megabytes it should take
a few seconds to load so now that we've
got that file we made about the same
time download our Oracle odbc drivers
for linux so i'm going to open a new tab
and i'm simply going to search for
instant client downloads for linux x86
64 bit so when i do that i'll come to
this link and again they're there
the package that going to download the
zip file is actually detailed here there
are two you basically need the instant
client basic zip file and the odbc the
next step 5 as well so when I go to this
URL
firstly you need to accept your license
agreement you might need to create an
account to do this and then essentially
we just need the zip file so i'm going
to download the basic linux again i'm
going to log in with my oracle then it
takes a few seconds to create yourself
Arsenal on oracle accounts so I've got
the client package and of course we need
that odbc as well so fire scroll down
users also asked sqlplus which is a
basic like a sample studio to in order
to run sequel but we actually only need
this
odbc in linux as well so I'll go grab
back topside to accept the agreement and
I'll download that already bc linux as
well go
so now we've got those files that we
need so we can shut down the Oracle
browser we don't need this anymore so
now of course we need to get those on
the box so i installed a tool called
filezilla so I've got it on my windows
box if you're not sure how to install
this tool is just a simple FTP client go
on the hanger academy in the handle
recommends folder and we've got a short
video on how to configure your filezilla
if you've got any form of ppk that you
want to use when connecting to your
camera server so i'm going to log in as
the root user with my password
of course this could be configured to
use a PPK as well which I've done so
this is my fight go to the root level
I've got a folder called my odbc drivers
and just a quick overview of this i
already did this in another video but
all i would need to do here is give
write access you can actually do this
with ch Marty for a unix admin guy
actually in pussy but he actually can do
it here as well in in filezilla so again
i'm going to go to my downloads and I've
got my two . three . two unixodbc and
I've also got my to oracle and zip files
one is already bc and one is the basic
linux so we'll get those on our server
by simply dragging like so so just take
a few seconds there we go
and now that we've done that we can shut
down I'll for our files are so that
we've got those files we can actually go
back to partying again like FileZilla
we've recorded some videos which go
through how to set up party using your
ppk or converting your pen to a PPK
they're all covered in the hand
recommend course under the party section
so if i do an LS in my folder now that
we've we've got those additional zip
files and we need to deal with those it
so one of them is actually a tar.gz said
and then one of them and the Oracle file
simply zip file so let's deal with the
tar.gz at all the unixodbc file so the
first we might need to do is guns it the
unixodbc file so that'll basically
extracted from allergies that file so
I'm gonna get that file and when we do
an LS after doing that you see that
we've actually converted the file to a
actual tower file again there's
different ways to do that now that we've
got the tar files we can actually
extract the content so we can just do
this with the tar xvf and then i can put
the name of my file which is that tar
file or so so when we've done that what
we should see is that we've got another
folder and you can see the folder
indicated in blue which if i go into
that folder with a CD we can see that
we've got our unixodbc two . three . two
files there now that we've got our files
if i go back into that folder what we
have to do is actually install the unix
2.3 . so again if I do and I SQL which
is essentially like HD SQL for Hannah
Hannah well it's a tool to run sequel on
our server if i do a dash hell we've got
here you can see that it's unixodbc too
. 3.0 so what we need to do of course is
install this version so going back to my
documentation here that actually gives
you the steps right on the button if i
scroll down we need to run these three
commands so the first thing we need to
do is wrong
ADOT configure so i'll go back to party
I'll go to the end of my file and I'm
going to do it . stash configure like so
takes a few seconds to execute while
it's doing that's when we need to run a
make and that make install and
essentially just installed using the
standard Guinea process on this linux
server that's all we doing we taking the
libraries and then we're basically
installing them so they're now we need
to do a weave doing the configure we now
need to run a make it takes a few
seconds to execute and lastly we're
going to run a make install which is the
final step
there we go so to make a little bit less
less confusion was going to clear the
screen so now of course what we want to
do is test that the unixodbc new version
works now the key thing here is to make
sure that we do it with the right user
that we're going to use when we're using
the sappiness to do so I've logged in to
this system here is the route to use up
when you loved when you use the
sappiness to do and you connect to your
SAP HANA server you actually use a
different user which is a user which is
the first realize your instance name and
the last realizes the word ADM so it's
best that we do all the configuration
actually as that user so to get there
I'm to type issue space dash SP 780m
we're going to go in as that user again
I'll do a clear and essentially i'm
going to go to the same folder which is
23 2 i've got the same files so now when
we do the isql let's see if it works now
what we're going to get is an error
because it can't find one of the files
which is this lip odbc . SL . to firstly
we can look to see if that file is on
the box so if i do a find and i'm
searching by name i'm going to search
for see if this actual file actually
exists on this box now don't worry about
the permissions denied but we can see
here at the end we actually do have that
file but it's in a . libs folder
of that unix 232 a folder so it's very
easy to fix we can create what's called
a symbolic link which is like a synonym
or a kind of a view to the original file
or what's even easier ways it we can
just add all the folders where that file
exists into essentially the classpath so
to do that we need to do is modify a
file called about the Bashar see file
and then enter the password that about
file my exist so before we actually
modify that bash RC file we can actually
make sure that the path actually exist
so we can see here we need to go to the
unix 232 driver manager . libs folder to
get that file so I'm gonna outpour i'm
going to basically export the path
existing path and then append the same
path so I'm going to basically copy from
driver manager afterwards like so place
that here so it will be essentially
appending that existing path to the
existing classpath all the library path
so now that we've done that we can again
go and test my SQL and you can see now
that it works because essentially what
we've done there is we've added that to
the classpath so there's a problem so if
I again do and I SQL dash help it works
now if I quit if I leave if I exit my su
sp7 ADM user I've locked out i'm going
to go back in as that user and I'll run
the isql commander gain you see it
doesn't work because essentially what we
need to do is add this to that bash RC
file so it's very very straightforward
to do so essentially i'm going to want
to command which is VI
bus crash RC which is essentially like
an environment variable file I'm going
to open that file and I'm going to go
right to the bottom because essentially
i'm going to add this command to the end
of this file so this let's say this path
so the end of this file so i am using v
i'm going to do an insert and i will add
that path you can see here I've added it
so now all i need to do is escape and do
work WQ to write this and now again if i
go back to I sequel dash help
it's going to work we know it's going to
work if I now quit out of that's user or
exit and go back to the root user but
again go into that user again obviously
remember last time it forgot what we did
but hopefully now if I do and I SQL dash
help it should remember that we've
actually added that to our classified as
you can see so now that we know the
right version of unixodbc is coming up
we can actually just make sure that it
works so how does the unixodbc
configuration work well again there's a
tool called I SQL and if you do a dash
dash help you get up so you get a the
version that we using now quite simply
this file uses you're already bc to ini
file to connect to your various
databases so obviously a quick way to
test everything works is by actually can
try to make sure that this version of
MySQL or unixodbc can connect to an
existing database now you don't have to
do this in your box if you're following
along but i'm going to show you in case
you have the opportunity to do this so
if i go to my e.t.c folder there's a
folder called odbc sorry unixodbc and
this if I do an LS in this folder this
is where our only bc to ini files cats
if i go into my BBC ini file just to
give an example of a sample DSN i
created one in a previous video
four-man microsoft sequel server so
essentially here we've got our driver
the DSM name
this is the the driver file that's being
used and this is the server and the part
that we connect into so in this example
I'm going to try and connect to one of
my hand the boxes just to show you how I
skewer works so if I exits and then i'm
going to run mysql largest clear the
screen is q l dash V for the bolts that
means it'll prince2 from the screen I'm
going to try and connect htb my user was
system and you use the password so i'm
going to use welcome one
so if you get this connected message it
means you actually are connected to your
side panel system that's what we kind of
want to do we want to do we did the same
thing in another video for mice
microsoft sequel server we doing the
same thing now for oracle but again we
have to do it as the right user as this
SPS sp7 ADM user so now that we know
that the the hannah the I SQL is working
i can obviously quit and again we know
that it's pointing to the right version
now we can go and deal with the actual
and hannah has ID and oracle odbc
drivers so again i've already installed
them if i go to the relevant folder
which was my odbc drivers and do an LS
we can see that we've got the to an
instant client zip files here or we
actually need to do is extract them so
I'm going ons it the instant client
basic
you kind of have a lot of dependences
for your soo or driver files and then
I'm going to do the same thing i'm going
to do an unzip of the odbc as well there
we go
and you put it actually in the same
folder and you can see there the folder
is called instant client underscore 12
underscore one so now that we've got
those files we can actually now go and
deal with our DSN so we've actually
we've got our at the moment now we've
got unixodbc working we've got the right
version working actually install the
drivers if i go back to my guide
we don't need it anymore we're actually
at this step we've actually export the
files and now what we're going to do is
create our DNS names if we need it an
hour already bc ini file now this is
where we do we actually need a TSN and
at ens names . or a file we can pop the
complete configuration in the actual
odbc dot ini file so that's what I'm
going to show you how we do it and
essentially we didn't get here
essentially we need to add a few class
because we need to tell exactly our SPS
SP 780m user where all the Oracle SL
files are so i'm going to show you how
to create that and connection that path
as well very straightforward so the next
step is to go back to our box we've
extracted the files and we need to
remember we modified our a bash stop and
I see file so sorry bash Darcy file so
we kind of need to add a few more class
paths to that file so if i do again a if
i go up i should be able to get that
command really quick and i'll go backs
at the moment we've pointed to our right
drivers but we just need to put a few
extra folders there as well so I've
already actually built these scripts and
i'll explain what they're doing gorgeous
to put them
so if i have done inserts i'm going to
go and just actually correct that stops
so basically I've added the two . the
232 driver manager file that was for the
problem that we had before with unix and
odbc and essentially we just need to add
these three files so one of them is the
path to our lips 64 folder our six lips
64 photos because there are some
dependence has been those files for the
Oracle odbc drivers and my son is going
to add the actual oracle odbc folder as
well when we have these class
passivation should work fine
so once I've done that I can just go to
the ends of the file there we go
I'm gonna exists to quit and then we can
actually go and configure our odbc so
now that we've added those class paths
we can go now we I can exit this user
now we don't need to do this as the
sUU's up we can do this as our root user
i'm going to go back to my old ebc
driver so yeah here we've got our
configuration so we just need to add an
extra couple of lines to have our Oracle
odbc added as well so i'm going to do
inserts and at the very end and actually
i love it here both the below the hand
of service i'm going to make a bit of a
space and I'll just placed in my
configuration details and i'll explain
how they actually work is very
straightforward so obviously the first
line is the name of the DSN orcl this is
the path to where my file execute so you
need to put in the driver that we're
going to use which roses lives cute
o our ABS OH . 12 . one this is all
mentioned in that administration SAP
HANA PDF as well
and last we need to put the server name
so you can do this in a multiple in
multiple ways if you look at the
documentation you can actually use the
tnsnames.ora file if you want
obviously this is the drive that we've
already included but a simple way is
just too actually
put in the machine name plus the actual
the instance name so for being my sorry
not instance name i sit or might get my
instance so for me it's our CL easy to
the internal a simple way to find this
of course is if you go to your oracle
server you can actually obviously
presume that you've already configured
and checks that your oracle server is up
and running you can simply go to your
existing connection and grab the details
from that connection when you configure
oracle server so in this example if I
right click on my oracle database go to
my properties and again here you can
just do a simple test you should get
success coming back and all the details
of what you can connect him to hear so
this is the name of my service name
obviously here I've got localhost
because this is the windows box and
connected to the local host but this
would actually be your machine name and
then of course once you've connected you
can see here once i connected you get
access to your objects so now that we've
done that we've actually added we've
added our machine named we've added our
instance as you saw there from the
connection what I can do now is I can do
a WQ to save that looks escape WQ right
and quits and then we can actually test
to see if it works I'll just clear the
screen of course we're not going to test
as our linux our root user we're going
to login as that sp7 ADM user and what
you might find is going to be a strange
error now if you google it which I've
actually had the luxury of doing
there's a way you can fix it so it's
going to sound strange but hopefully all
make sense so basically when I now try
and launch isql but obviously connecting
to
if i go back and try and connect to hdb
which is my hangout system it should
work
remember we tested this before
everything works fine so we know I SQL
works is connected to one of our
databases
in our example it's hannah but now when
we try and do the same thing for oracle
we get a stranger oh so it's the same
login and password system but obviously
the DSM roses different so when i try
using oracle we get this message
now this is quite strange because this
file actually exists if i go into that
folder so i'm going to go into the
folder this folder and I won't search
i'll just show you because it will just
be clear if I show you the file you'll
see that the file actually exists if i
do a LS lib star . star you see here
lives SQ hora died so doctors don't want
the file exists so why is it telling us
that the file doesn't exist story can't
find the file but it's kind of a book
with the drivers so it's very simple to
fix all we need to do is as follows if
you want what's called an ldd command on
that file so that's what I'm going to do
here i'll copy that like so there's
actually us a dependency messing for
this file now you can sell borrowing an
ldd because it will look at that driver
and it will look at all the files
dependencies which are used for that
file and if I look down I can see that
this file here can't be found which is
live odbc in stock is 0 . too so we need
to resolve this issue in order for this
to work so this file can't be found but
probably what is what does exist is a
file with a similar name so a simple way
of resolving this is by creating a
duplicate session in potty we're going
to find that file so if I do find again
we've been through this before i'm going
to search for the file name which is
that's the file name the file does exist
you can see it's found in my only bc
drivers super to odbc inst . lips so
obviously i could add this to the path
but just show you another way in which
you can fix these issues is just by
Chris
what's called a symbolic link so to do
that you do and our and dash S and then
you create you find where the actual
file is it's like a pointer so obviously
we can see that the file actually exists
here as an example so i'm going to copy
this paste it here helps go both and
then obviously want to put the name of
the file that we want it to be so I'm
gonna just copy this part placed it
because obviously we want this to be the
source and it should be replicated in
this very same folder as you can see i'm
sorry i actually already created it so I
do an LS now in that folder we can see
that the file should exist which is here
you can see it's indicated by the blue
color that exists so now that you've got
that we can run that same command again
which is the I isql so go into commands
isql dash V for the bose we're using
orcl for oracle system
welcome one and there we can see that
now we've actually managed to connect to
our Oracle database so of course the
ultimate tested to see if we can get
some data so i think i have some data in
a table called the mp4 employee and
there you go that's the data that we had
originally here it's for the same 14
rows that we had here in our employee
table within Oracle so now that we've
proved that we've actually got the the
data to connect we we've got our
unixodbc and oracle odbc drivers to
connect we can go to the next part which
we'll cover in the next video so in the
next video we're going to cover how to
create a remote connection within the
sample studio and then how obviously we
can when we make updates and at Oracle
database the immediately represented in
the holiday and
in the hand of virtual tables so we're
obviously going to show you how to build
some virtual tables and of course how
we're going to also show you how you can
update the data in the sap and studio or
the SAP HANA database when you update
the virtual table of course the table in
Oracle will also be updated and push
down to the Oracle database so all of
that will be covered in the second video
of this series
thanks for watching you can see more
videos at academy . SAP HANA . com