1

I'm running a pretty large query against Oracle database using SAS for windows 9.2. This query is pretty large where in I wrote a sub-query in WITH clause and used it 4 times. This runs fine on SQL PLUS and SQL Developer, but when i run it using SAS, the program hangs up after 20 mins and I can't even see the log window. I have never worked with SAS and not sure how to proceed but tried following option:

  • I created a SAS code file and ran it from windows batch file hoping to get log written to windows file system, but even this runs in-definitely and I don't see anything written to log file

Can some one direct me here. How can i use ALTLOG command to get log file written to windows file system so that i can understand the exact error message. By the way DBA's have mentioned that query runs fine and rows are returned from server side, but for some reason SAS program is not able to show this data. I get about 45,000 records from the query.

Thanks

Phoenix
  • 411
  • 4
  • 9
  • 22
  • 1
    Please post your complete existing SAS program (the PROC SQL part). Your comment below says you are using the ODBC access method, presumably because you do not have a license of SAS/Access to Oracle. – BellevueBob Aug 06 '12 at 21:40
  • Also note Vasja's comment; if you are just doing a SELECT the output may be going to your output window, which is filling up and SAS may be prompting you with a message. – BellevueBob Aug 06 '12 at 21:48

1 Answers1

2

I'll break it into two points:

1) running an existing Oracle SQL query in SAS without ever using SAS: best way for you is to embed your Oracle SQL code in so called PROC SQL explicit pass-through:

proc sql;
    connect to oracle as db1 (user=user1 pw=pasw1 path=DB1);
    create table test_table as
    select *
    from connection to db1
        ( /* here we're in oracle */
                  select * from test.table1 where rownum <20 
                )
    ;
    disconnect from db1;
quit;

(borrowed from my answer to another question Limiting results in PROC SQL) The point is not to try to translate it to SAS SQL (don't know if you tried or not).

Also make sure you're creating a SAS table (as in the example) from query result, not writing it to SAS OUTPUT window.

2) Regarding getting the log: the log about an action is in general written once it's done, so if the query is really running for a long time, you won't see any intermediate logs. Anyway, log buffering is the default setting for batch jobs, so log messages are written after the buffer is full. To get log messages written immediately to the log file set LOGPARM option:

-LOGPARM= “WRITE=IMMEDIATE”

the opposite option is BUFFERED.

To find out the config file(s) used run following in your SAS session:

proc options option=config;run;

Then enter the option above on separate line in the config file.

Community
  • 1
  • 1
vasja
  • 4,732
  • 13
  • 15
  • Yes, I'm running the query using proc sql and using oralce odbc connection. And i tried changing LOGPARM value using Tools-->Otions-->System but for some reason nothing happens when i select Modify Value. – Phoenix Aug 06 '12 at 21:23
  • You can only define the LOGPARM option when you start a SAS program. It must be set either in a configuration file or as a command-line option. – BellevueBob Aug 06 '12 at 21:45
  • @BobDuell Can you please elaborate on setting LOGPARM option. I don't see this option in the config file. How can I this set this using SAS command line? Can you please let me know. – Phoenix Aug 07 '12 at 02:38
  • See Vasja's revised answer; to set the LOGPARM option, you need to add that line into your config file *or* as a command line option for your batch script. But that is probably NOT your problem. Please show your code. – BellevueBob Aug 07 '12 at 15:07
  • I left the query overnight and saw an error message "Windows log is full" and then bit of googling lead me to set -dmsoutsize 999999 and -dmslogsize=999999 in the config file. Now i'm able to run the query. – Phoenix Aug 21 '12 at 03:14