We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Working with Amazon RDS and SAS/ACCESS

by SAS Employee MarkKirk on ‎07-22-2015 02:48 PM - edited on ‎10-05-2015 02:56 PM by Community Manager (799 Views)

Working with Amazon
RDS and SAS/ACCESS®

 

   

Amazon RDS is a service offered by Amazon.com that allows a user to build and operate a MySQL, Oracle, PostgreSQL, Microsoft SQL Server, or Amazon Aurora database without the need to maintain their own hardware and without the need of a DBA to set it up. Database behavior running on Amazon RDS is no different than an equivalent on-premises deployment of a given database.  Amazon is running an off-the-shelf product, so once connectivity is established there will be no noticeable difference in how SAS/ACCESS behaves. This article provides some tips on configuring ACCESS to connect with an Amazon RDS database instance using an Oracle as an example.

 

 

Prior to establishing connectivity, you will need to set up a database instance with a userid and password to connect. Amazon has GettingStarted
documentation that walks through this process.

 

Once you have a hostname and port, connecting to the instance is no different than connecting to any Oracle database.

 

Start by verifying that the database host can be accessed. On Linux, this can be accomplished with the following example command:

 

nc
–z mk-oracle-11-al32utf8-test.cyydrdghsaxs.us-east-1.rds.amazonaws.com 1521

 

 

Edit the tnsnames.ora file on the client to create a reference to the database instance. For example:

 

test_ora=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL= TCP)(Host=mk-oracle-11-al32utf8-test.cyydrdghsaxs.us-east-1.rds.amazonaws.com)(Port=1521))

    (CONNECT_DATA = (SID = ORCL))

  )

 

 

Open a DOS/UNIX session and try to query the database using SQLPlus or a database query tool of your choice. If necessary, set TNS_ADMIN={Oracle tnsnames.ora directory path}.  For example:

 

sqlplus admin/pocadmin@test_ora

 

 

  • Check that the database is running: select * from dual;
  • Check the character set:

    You should be ready to proceed with testing. Below are some common actions that can be performed along with some example syntax to get you started.  LIBNAME libname
x oracle user=admin password=pocadmin path=test_ora;
Table creation and query 

  1. data x.test_tbl;

input
test_string $25.;
datalines; Bugs Bunny Homer Simpson;  run;  proc sql; create
table x.create_test as
select
* from x.test_tbl;
quit; data
_null_; set x.create_test; put _all_; run;
  Bulk Loadingdata sasds;  c=1;run; proc sql; drop
table ora.bulktest1;
quit;

  1. data ora.bulktest1(bulkload=yes);

set sasds;

run;

 

proc print data=ora.bulktest1;

run;

 

 

Insert

proc sql;

   insert into x.test_tbl

      set test_string='Fred Flintstone';

 

proc sql;

   insert into x.test_tbl

     values ('Barney Rubble');

 

 

Update

proc sql;

   update x.test_tbl

      set test_string='George Jetson'

      where test_string='Bugs Bunny';

 

data _null_; set x.test_tbl; put _all_; run;

 

proc sql; 

   update x.test_tbl

      set test_string='George Jetson'

      where test_string='Bugs Bunny';

 

data _null_; set x.test_tbl; put _all_; run; 

 

 

Delete

 

proc sql; 

   delete 

      from x.test_tbl 

      where test_string like 'F%';

  

data _null_; set x.test_tbl; put _all_;

  

 

There’s not much to it than that.  As you can see once an Amazon RDS db is set up it is handled the same as any other db. Please share your comments and questions on this article.

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.