RTDM: Oracle DB from SAS DS2 process

[CMark02]

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!

[shill · Accepted solution]

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.

[CMark02]

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