Plan, execute, measure and optimize customer experiences

RTDM: Oracle DB from SAS DS2 process

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

RTDM: Oracle DB from SAS DS2 process

Hello,

 

We are using SAS RTDM (SAS CI Studio 6.4M1 Hotfix 01) to host a number of decision services. The biggest of those has grown rather large, to the point that performance when editing the diagram in SAS CI Studio is an issue.

 

As a side-project, I planned to migrate parts of the logic to DS2 code snippets.

 

This is where I happened upon a problem that has me stumped:

The diagram pulls data from an Oracle DB (access defined in SMC) every few nodes. I initially thought I could access the DB using the SQLSTMT package and the libref I see when choosing the DB table for a new data process. Since I'm here talking about it, this approach obviously wasn't successful.

 

So here's my question(s): How do I pass the libref to the DS2 code? Or is there a way to call data process functions? Do I need to change anything in SAS Management Console to make it work?

 

Right now, trying to access with libref.tablename syntax in SQLSTMT

 

DECLARE PACKAGE SQLSTMT get('SELECT id, var1, var2 from oracledb.testtable');

 

throws an error

 

[com.sas.tkts.sql.FSException: [com.sas.tkts.sql.FSException: T_EXECUTE2: Table Service: Function failed.] SQLSTATE:[42000] MESSAGE:[Syntax error or access violation] NATIVEERROR:[-2130708373]

 

The syntax is correct, the whole package works fine when running it in SAS Base.

 

An alternative would be to use sub-diagrams, but by now I'd really like to know what I'm doing wrong.

 

Thanks for reading!


Accepted Solutions
Solution
‎04-11-2017 12:34 PM
SAS Employee
Posts: 70

Re: RTDM: Oracle DB from SAS DS2 process

Accessing data from DS2 doesn't actually have any connection to SMC libraries at all. It's based entirely on the configuration of Fed Server (not SMC) and getting the fully qualified path correct (catalog.schema.table) and making sure it's accessible in your DSFederatedDSN. The best way to work through this is to go into Fed Server Manager and use the "SQL" tab to open a query window, then select the DSFederatedDSN (which should contain your BASE_DSN, plus your Oracle connection) and paste the query in that window. Once you get that query to execute successfully in that window, your SQLSTMT package should work correctly.

View solution in original post


All Replies
Solution
‎04-11-2017 12:34 PM
SAS Employee
Posts: 70

Re: RTDM: Oracle DB from SAS DS2 process

Accessing data from DS2 doesn't actually have any connection to SMC libraries at all. It's based entirely on the configuration of Fed Server (not SMC) and getting the fully qualified path correct (catalog.schema.table) and making sure it's accessible in your DSFederatedDSN. The best way to work through this is to go into Fed Server Manager and use the "SQL" tab to open a query window, then select the DSFederatedDSN (which should contain your BASE_DSN, plus your Oracle connection) and paste the query in that window. Once you get that query to execute successfully in that window, your SQLSTMT package should work correctly.

New Contributor
Posts: 2

Re: RTDM: Oracle DB from SAS DS2 process

Thanks for the reply! Going to try that once I've got all the login details cleared up.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 156 views
  • 2 likes
  • 2 in conversation