Run code on one metadata server login then swap to another and then back

[deleted_user]

Hello,

I have two metadata servers, A and B.

A has oracle tables (through sas/access to oracle)
B has teradata tables (through sas/access interface to teradata)

We want to do the following, having code change metadata servers automatically (not manually)

1. perform a data step returning tables from oracle, saving the data set to a lan location
2. have enterprise guide change metadata servers to the workspace that has access to teradata (so, swap from the logged in A, and establish a connection to B)
3. perform a data step (on the lan stored file) saving into teradata through metadata server B

Yep, thats it, in code, after a data step, swap metadata servers and in the other, perform a second data step.

Please tell me if this is on the right/wrong track.

tools > options > tasks (or programs) pre-code run "option metaserver=b" etc

Or if there is another way to implement during a macro controlled process, swapping metadata servers.

I don't have any influence on any smarter solution.

Very interested to see responses, that address "option metaserver=b" to swap metadata servers or something easier.

We use "SAS Enterprise Guide 4.1 (4.1.0.1019)"

Thanks in advance, for any help, guidance, solutions on this point.

Tim Tam

[ShaneGibson_OptimalBI]

Well you know the obvious answer is have one metadata server 😉

But as you have said you cant change this.

If you have SAS Connect licensed you should be able to remote submit the SAS code from one workspace to the other, that would allow the Workspace server on the Oracle side pass a query to the workspace server on the Teradata side, using the SAS/Access to Teradata engine.

The issue is if you try and swap metadata servers mid processing then the SAS work file that have written to the Oracle/Workspace server will no longer be available amd vice versa.

If you wrote temporary SAS Datasets to a physical shared file path (accesable via EG) rather than a metadata based path that might work.

Or have two eg project's one doing the Oracle call and the other doing the teradata call and then call one within the other (somehow).

Or you could maybe do a manual metalib statement to define a libname on the Teradata Metadata server from the Oracle one.

Lots of maybe's but you would need to test each one to see if any work.

Interesting problem though...

[deleted_user]

Hi Shane,

One metadata server, yer...i wish 🙂

Well all of your ideas are thoughtful, and gratefully you understood the question - i posed.

The response i got back from SAS technical support, stated the following - so i am still going to test some of your suggestions.

Tim Tam

SAS technical support response.
"There is no simple EG scripting way to switch from one metadata server (and associated workspace server definitions). However I’m not sure its necessary!

At its simplest from a coding perspective to copy data from an Oracle table to a Teradata table you can use a simple single program:

Libname in Oracle OPTIONS-FOR_ORACLE USER=user password=password;……;
Libname out Teradata OPTIONS-FOR_TERADATA USER=user password=password;

Data out.name_of_table;
Set in.name_of table_wanted;
Run;

So no metadata servers are needed for this, however you need to write the Libnames, and include the correct database “names” and userids/passwords.

Some questions to see how to correctly code the metadata.
1) Do you know the userid and password for accessing the databases?
2) If you right mouse button on the library definitions and select “Properties” you will see options such as: schema=xxxx PATH=yyy
These are what is needed in the Libname.


This should be a simple problem if your environment is set up in a more flexible way. I understand you don’t have control over this yourself. There does not seem to be any technical reason, from what you have told me, that the metadata couldn’t be replicated so both libraries could be defined in a single metadata server."

[ShaneGibson_OptimalBI]

How did you get on?