Assigning library in DI Studio User Written Transformation

[strsljen]

Hi,

I am working on User Written Transformation which can call Oracle package/stored procedure.

PROC SQL;
CONNECT USING SEQLIB AS Oracle;

execute (execute MY_SCHEME.MY_PROCEDURE()) by oracle;


DISCONNECT FROM Oracle;
QUIT;

The problem I am facing is how to generate correct LIBNAME string to assign proper library if I have LIBNAME as paremeter.

My transformation uses no inputs/outputs.

I tried:

LIBNAME SEQLIB ORACLE PATH=&SERVER  SCHEMA=&SCHEMA_NAME  
AUTHDOMAIN="Oracle.&SERVER..&SCHEMA_NAME" ;

But not all of my defined libraries on SAS follow the same naming scheme for AUTHDOMAIN. As a result, this code works for only some of the libraries/Oracle schemes I use.

I have also tried querying metadata server for that info as described:

http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=lrmeta&docsetTarget=p08jvy...

Error:

31 LIBNAME MARIO meta library=REPO_BIZ;
ERROR: No metadata objects found matching the specified URI.
ERROR: Error in the LIBNAME statement.

Any suggestion how do I overcome this?

Thanks in advance!

Best regards,

[Kurt_Bremser]

DI Studio is meant to work from SAS metadata, so you should have all library definitions there.

Also see https://communities.sas.com/t5/SAS-Data-Management/DI-Studio-WORST-Practice-from-SAS-UK-Forum-2018/t...

by @AngusLooney

[AngusLooney]

So, "best" way (well certainly better than hand/hard coding) to do this is work with DI to make sure that the library or table is being generated by DI. Neatest is in a User Transform, rather than a User Written node, you can make a table an input it, then attach the table to the UT, which means the libname will be in the code, and the relevant macro variables will be populated with library names and tables names etc. Less elegant is to arrange for something to occur earlier in you job that forces the libname to be declared.

It can be VERY education to have a look at the code DI is generating, as there are a load of macro variables that get populated which you can exploit, include ones that contain the full text of the libnames, which can allow you to extract schema names etc if you need them for pas through coding, and remain entirely "metadata driven".

The way I always like to think of things is - can I view the specific thing I'm trying to as a instance of a generic thing - if you're trying to run a specific stored process, look at it as "can I write a user transform that lets me invoke any/all stored processes"?

Time and time again, something "one off" will reoccur generically down the line.

But key thing, you should NEVER have to hard code a libname, it will come back to haunt you if you do.

[strsljen]

Hi guys,

Completely agree. Have not and will not hardcode anything I don't have to.

I treid to get LIBNAME statement info based on that document and metadata, but got only error.

The thing is: this transformation has to support being first transformation in DI job, meaning: no LIBNAME is pregenerated before it starts. 

That is the  reason I need to find generic way to generate (not to hardcode) LIBNAME statement based on SAS LIBNAME parameter provided to it.

I see what SAS DI generates in code in regards to LIBNAME; but fail to understand where he get it from and how can I do the same.

[AngusLooney]

I'm assuming that "needs to be first" translates to "needs to be before other stuff"...

If it was a User Transform (rather than User Written Code), with an Input slot/port for a table, attach the table object from metadata to it, that will force the libname statement BEFORE any of the code in the UT, and also create lineage that the table is being "manipulated" by the UT.

So, let's assume your libname is - myOraLib, and the table myOraTable

If the input is "_input" (but better to give it a "meaningful name" IMHO), drop the relevant object onto the input port of the UT in your Job, then in the code as it executes &_input1. will resolve to myOraLib.myTable

Also, what you're REALLY after is the details in the libname relating to the schema (etc), these are in the text of the libname, and are exposed to you in the macro variables DI generates, in the example therw will be a macro var _INPUT_connect contains the libane text, from which you can extract the schema etc.

[strsljen]

Hi Angus,

Thanks for your feedback.

I would certainly do so from _INPUT, but this particular trasnformation has neither inputs, nor outputs, hence the issue with generating LIBNAME statement.

Now.. I *could* add input to it, but it would be pretty misleadning to connect some random input table from the same schema I call stored procedure/package if has nothing to do with the whole process..

Best regards,

Mario.

[AngusLooney]

The point of the input is to force DI to generate and instantiate the libname and macro variables, you don't have to use actually use the "input" in your code....