Prev: Can I specify order=data and order=freq in proc freq?
Next: DDE to read Excel file with autofilter turned on?
From: Jack Hamilton on 29 Oct 2009 09:25
It should work. Is the session started by the spawner starting from the same location and under the same Windows userid as the stand-alone session?




--
Jack Hamilton
jfh(a)alumni.stanford.org

-----Original Message-----
From: Vilim Zaksek <vilim.zaksek(a)STERIA-MUMMERT.DE>
Sent: Thursday, October 29, 2009 4:53 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: [SAS-L] Remote session can't connect to oracle

Hi,

i have an oracle db running on a unix system and i can access it easyly from
my local SAS windows session, for example with this libname statement:

libname oralib oracle path=mypath
user=&user password=&pwd
schema=&schema ;

I can also establish a remote session from z/OS on the same Windows PC with
the same options (exept the DMR option). I have a spawner running on that
Windows PC, and the connection works fine.

The problem is: I need to combine these two things, and it doesn't seem to
work. The libname statement for the oracle db, which works fine when it is
executed in the interactive PC session, will not work if the session is
invoked remotely from z/OS. The error message is: "ERROR: ORACLE connection
error: ORA-12541: TNS:no listener.". The listener is running, because i can
connect to oracle immediately after the error occurs from an interactive
windows session.
I have tried to allocate the oralib with a rsubmit libname statement, with a
remote libname statement, with an autocall macro that was stored on the PC
and invoked inside a rsubmit and even with the libname statement in the
autoexec file that is executed when the remote session starts. All with the
same error message.
There must be a significant difference between using the oracle libname
engine in an interactive and in a remote session, and I cand find it.

Every hint is appreciated!

PS: I have no SAS on the unix box, and no SAS access to oracle on z/OS. That
is the reason why I have to take this approach.
From: Vilim Zaksek on 29 Oct 2009 09:47
The spawner is running as a service (I started the spawner in DOS) and was
running as local system.
I changed that, so that the spawner is now running under my user-id, but
still the same error message.

Do I have to set some special options when I set up the spawner, that I may
have forgotten?

Vilim


On Thu, 29 Oct 2009 06:25:01 -0700, Jack Hamilton <jfh(a)STANFORDALUMNI.ORG>
wrote:

>It should work. Is the session started by the spawner starting from the
same location and under the same Windows userid as the stand-alone session?
>
>
>
>
>--
>Jack Hamilton
>jfh(a)alumni.stanford.org
>
>-----Original Message-----
>From: Vilim Zaksek <vilim.zaksek(a)STERIA-MUMMERT.DE>
>Sent: Thursday, October 29, 2009 4:53 AM
>To: SAS-L(a)LISTSERV.UGA.EDU
>Subject: [SAS-L] Remote session can't connect to oracle
>
>Hi,
>
>i have an oracle db running on a unix system and i can access it easyly from
>my local SAS windows session, for example with this libname statement:
>
>libname oralib oracle path=mypath
> user=&user password=&pwd
> schema=&schema ;
>
>I can also establish a remote session from z/OS on the same Windows PC with
>the same options (exept the DMR option). I have a spawner running on that
>Windows PC, and the connection works fine.
>
>The problem is: I need to combine these two things, and it doesn't seem to
>work. The libname statement for the oracle db, which works fine when it is
>executed in the interactive PC session, will not work if the session is
>invoked remotely from z/OS. The error message is: "ERROR: ORACLE connection
>error: ORA-12541: TNS:no listener.". The listener is running, because i can
>connect to oracle immediately after the error occurs from an interactive
>windows session.
>I have tried to allocate the oralib with a rsubmit libname statement, with a
>remote libname statement, with an autocall macro that was stored on the PC
>and invoked inside a rsubmit and even with the libname statement in the
>autoexec file that is executed when the remote session starts. All with the
>same error message.
>There must be a significant difference between using the oracle libname
>engine in an interactive and in a remote session, and I cand find it.
>
>Every hint is appreciated!
>
>PS: I have no SAS on the unix box, and no SAS access to oracle on z/OS. That
>is the reason why I have to take this approach.
From: Michael Raithel on 29 Oct 2009 10:31
Dear SAS-L-ers,

Vilim Zaksek posted the following interesting problem:

>
> i have an oracle db running on a unix system and i can access it easyly
> from
> my local SAS windows session, for example with this libname statement:
>
> libname oralib oracle path=mypath
> user=&user password=&pwd
> schema=&schema ;
>
> I can also establish a remote session from z/OS on the same Windows PC
> with
> the same options (exept the DMR option). I have a spawner running on
> that
> Windows PC, and the connection works fine.
>
> The problem is: I need to combine these two things, and it doesn't seem
> to
> work. The libname statement for the oracle db, which works fine when it
> is
> executed in the interactive PC session, will not work if the session is
> invoked remotely from z/OS. The error message is: "ERROR: ORACLE
> connection
> error: ORA-12541: TNS:no listener.". The listener is running, because i
> can
> connect to oracle immediately after the error occurs from an
> interactive
> windows session.
> I have tried to allocate the oralib with a rsubmit libname statement,
> with a
> remote libname statement, with an autocall macro that was stored on the
> PC
> and invoked inside a rsubmit and even with the libname statement in the
> autoexec file that is executed when the remote session starts. All with
> the
> same error message.
> There must be a significant difference between using the oracle libname
> engine in an interactive and in a remote session, and I cand find it.
>
> Every hint is appreciated!
>
> PS: I have no SAS on the unix box, and no SAS access to oracle on z/OS.
> That
> is the reason why I have to take this approach.
>
Vilim, very intriguing, odd, and, of course, very aggravating for you!

Here is my idea: Perhaps there is a difference between the SAS you run when invoking interactive SAS on your desktop and the SAS you are invoking through the spawner. So, if I were in your sandals, I would:

1. Check my SAS shortcut on my desktop to see which config file, autoexec file, and SAS exec it is using.

2. Check the SAS config, autoexec files, and SAS exec that are used by the spawner.

3. Make note of any differences, and change the files in #2, accordingly.

Try, again, to logon to z/OS, remote to your PC, and then access Oracle.

So, will this work? Only time and your efforts will tell! Give it a shot and let the list know if there is any merit in this proposed solution.

Vilim, best of luck in all of your SAS endeavors!


I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel(a)westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I'm gonna keep my head on straight up and steady on,
I just hope it's not too late... - Steady On, by Shawn Colvin
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From: Jack Hamilton on 29 Oct 2009 11:00
Not that I know of, but I've never used a Windows-based SAS spawner.

I don't have the exact setup you have, but we have Oracle and SAS on a
Unix box, and want to read Oracle tables from z/OS. I set up a
SAS/Connect session to Unix, and have used several of the methods you
mention to read Oracle data from z/OS. I've used remote libnames,
remote connections, and libnames created via rsubmit. It should be
essentially the same as what you're trying to do.

The message indicates that either the listener isn't running, which you
say it is, or the program can't find the path to it. So it's probably a
network setup problem. Are you using fully qualified addresses
everywhere - in both your SAS programs and the tnsnames.ora file?



On Thu, 29 Oct 2009 09:47:58 -0400, "Vilim Zaksek"
<vilim.zaksek(a)STERIA-MUMMERT.DE> said:
> The spawner is running as a service (I started the spawner in DOS) and
> was
> running as local system.
> I changed that, so that the spawner is now running under my user-id, but
> still the same error message.
>
> Do I have to set some special options when I set up the spawner, that I
> may
> have forgotten?
>
> Vilim
>
>
> On Thu, 29 Oct 2009 06:25:01 -0700, Jack Hamilton
> <jfh(a)STANFORDALUMNI.ORG>
> wrote:
>
> >It should work. Is the session started by the spawner starting from the
> same location and under the same Windows userid as the stand-alone
> session?
> >
> >
> >
> >
> >--
> >Jack Hamilton
> >jfh(a)alumni.stanford.org
> >
> >-----Original Message-----
> >From: Vilim Zaksek <vilim.zaksek(a)STERIA-MUMMERT.DE>
> >Sent: Thursday, October 29, 2009 4:53 AM
> >To: SAS-L(a)LISTSERV.UGA.EDU
> >Subject: [SAS-L] Remote session can't connect to oracle
> >
> >Hi,
> >
> >i have an oracle db running on a unix system and i can access it easyly from
> >my local SAS windows session, for example with this libname statement:
> >
> >libname oralib oracle path=mypath
> > user=&user password=&pwd
> > schema=&schema ;
> >
> >I can also establish a remote session from z/OS on the same Windows PC with
> >the same options (exept the DMR option). I have a spawner running on that
> >Windows PC, and the connection works fine.
> >
> >The problem is: I need to combine these two things, and it doesn't seem to
> >work. The libname statement for the oracle db, which works fine when it is
> >executed in the interactive PC session, will not work if the session is
> >invoked remotely from z/OS. The error message is: "ERROR: ORACLE connection
> >error: ORA-12541: TNS:no listener.". The listener is running, because i can
> >connect to oracle immediately after the error occurs from an interactive
> >windows session.
> >I have tried to allocate the oralib with a rsubmit libname statement, with a
> >remote libname statement, with an autocall macro that was stored on the PC
> >and invoked inside a rsubmit and even with the libname statement in the
> >autoexec file that is executed when the remote session starts. All with the
> >same error message.
> >There must be a significant difference between using the oracle libname
> >engine in an interactive and in a remote session, and I cand find it.
> >
> >Every hint is appreciated!
> >
> >PS: I have no SAS on the unix box, and no SAS access to oracle on z/OS. That
> >is the reason why I have to take this approach.
>
>


--
Jack Hamilton
Sacramento, California
jfh(a)alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org

Tots units fem for�a!
From: Vilim Zaksek on 30 Oct 2009 04:53
Hi,

I have already checked the SAS invocation, it's excactly the same with the
same config and auotoexec.
I have also compared the options of the interactive windows session and the
remote windows session. They are exactly the same, exept for the DMR option,
which indicates the batch session.

But I have found out something else: I thought that the reomte session reads
the tnsnames.ora like the interactive session does. The error message
indicates that the information for the connection to oracle is there and
that there is some problem communicating with the listener. But that is not
the case. I have used the fully qualified name (the server name:
xxxxx.yyy.zzz.com) to name the connection in the tnsnames.ora. The remote
session was never able to read the tnsnames.ora, but tried to connect to the
server where the oracle db is located, because the server name was in the
libname statement (libname oralib oracle path='xxxxx.yyy.zzz.com'...).
I found that out by looking into the sqlnet.log. There I have seen that the
standard port for oracle listeners is trying to be accessed, but I have a
different port in the tnsnames.ora. To confirm this, I have renamed the
connection in the tnsnames.ora to "myora" and have tried that in the libname
statement (libname oralib oracle path='myora'...). Now I have the following
error message: "ORACLE connection error: ORA-12154: TNS:could not resolve
service name."
Now I have to find a way to let my remote session know where the
tnsnames.ora is located, or to include all neccesary information in the
libname statement.

The location of the tnsnames.ora is already known in my windows registry as
TNS_ADM. I have also put that parameter ino my config, but without any
positive effect.

Any ideas?


On Thu, 29 Oct 2009 10:31:49 -0400, Michael Raithel
<michaelraithel(a)WESTAT.COM> wrote:

>Dear SAS-L-ers,
>
>Vilim Zaksek posted the following interesting problem:
>
>>
>> i have an oracle db running on a unix system and i can access it easyly
>> from
>> my local SAS windows session, for example with this libname statement:
>>
>> libname oralib oracle path=mypath
>> user=&user password=&pwd
>> schema=&schema ;
>>
>> I can also establish a remote session from z/OS on the same Windows PC
>> with
>> the same options (exept the DMR option). I have a spawner running on
>> that
>> Windows PC, and the connection works fine.
>>
>> The problem is: I need to combine these two things, and it doesn't seem
>> to
>> work. The libname statement for the oracle db, which works fine when it
>> is
>> executed in the interactive PC session, will not work if the session is
>> invoked remotely from z/OS. The error message is: "ERROR: ORACLE
>> connection
>> error: ORA-12541: TNS:no listener.". The listener is running, because i
>> can
>> connect to oracle immediately after the error occurs from an
>> interactive
>> windows session.
>> I have tried to allocate the oralib with a rsubmit libname statement,
>> with a
>> remote libname statement, with an autocall macro that was stored on the
>> PC
>> and invoked inside a rsubmit and even with the libname statement in the
>> autoexec file that is executed when the remote session starts. All with
>> the
>> same error message.
>> There must be a significant difference between using the oracle libname
>> engine in an interactive and in a remote session, and I cand find it.
>>
>> Every hint is appreciated!
>>
>> PS: I have no SAS on the unix box, and no SAS access to oracle on z/OS.
>> That
>> is the reason why I have to take this approach.
>>
>Vilim, very intriguing, odd, and, of course, very aggravating for you!
>
>Here is my idea: Perhaps there is a difference between the SAS you run
when invoking interactive SAS on your desktop and the SAS you are invoking
through the spawner. So, if I were in your sandals, I would:
>
>1. Check my SAS shortcut on my desktop to see which config file, autoexec
file, and SAS exec it is using.
>
>2. Check the SAS config, autoexec files, and SAS exec that are used by the
spawner.
>
>3. Make note of any differences, and change the files in #2, accordingly.
>
>Try, again, to logon to z/OS, remote to your PC, and then access Oracle.
>
>So, will this work? Only time and your efforts will tell! Give it a shot
and let the list know if there is any merit in this proposed solution.
>
>Vilim, best of luck in all of your SAS endeavors!
>
>
>I hope that this suggestion proves helpful now, and in the future!
>
>Of course, all of these opinions and insights are my own, and do not
reflect those of my organization or my associates. All SAS code and/or
methodologies specified in this posting are for illustrative purposes only
and no warranty is stated or implied as to their accuracy or applicability.
People deciding to use information in this posting do so at their own risk.
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Michael A. Raithel
>"The man who wrote the book on performance"
>E-mail: MichaelRaithel(a)westat.com
>
>Author: Tuning SAS Applications in the MVS Environment
>
>Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second
Edition
>http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172
>
>Author: The Complete Guide to SAS Indexes
>http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>I'm gonna keep my head on straight up and steady on,
>I just hope it's not too late... - Steady On, by Shawn Colvin
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 |  Next  |  Last
Pages: 1 2
Prev: Can I specify order=data and order=freq in proc freq?
Next: DDE to read Excel file with autofilter turned on?