Date: Mon, 17 Sep 2007 11:49:20 -0400
Reply-To: Michael Raithel <michaelraithel@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Michael Raithel <michaelraithel@WESTAT.COM>
Subject: Re: Long running batch - ERROR, thread creation
In-Reply-To: <6y%Gi.166$bd.160905@news.sisna.com>
Content-Type: text/plain; charset="us-ascii"
Dear SAS-L-ers,
Richard A. DeVenezia posted the following interesting question:
> HW is Pentium 4 3.06 ghz w/1G ram.
> OS is Windows 2000 SP4, all windows updates.
> SAS is 9.1.3 SP4 (E9BX01). Some command line options of
> impact are -bufno 10 -noterminal -errorabend. Rest of system
> uses stock config settings.
>
> In a system for building a data warehouse, a long running
> batch program error abends with a less than helpful log message
> ERROR: Thread creation failure.
>
> The thread creation failed with in Proc SQL.
> MPRINT(<hidden>): proc sql;
> MPRINT(<hidden>): reset noprint;
> MPRINT(<hidden>): select sum(choice2 ne ' ') into
> :doChoice2 from Q;
> MPRINT(<hidden>): create TABLE fmtQ as select
> '$qmessag' as fmtname ,
> varname as start , message as label , ' ' as HLO , seq from Q
> UNION select '$qchoice' as fmtname , varname as start ,
> choice as label , 'S' as HLO , seq from Q UNION select
> '$qchoicB' as fmtname , varname as start , choice2 as label ,
> 'S' as HLO , seq from Q order by fmtname, seq ;
> NOTE: SAS threaded sort was used.
> ERROR: Thread creation failure.
> NOTE: Table WORK.FMTQ created, with 0 rows and 5 columns.
>
> The final goodbye note was
> ----
> ERROR: SAS ended due to errors.
> You specified: OPTIONS ERRORABEND;.
> ERROR: Errors printed on page 12790.
>
> NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
> NOTE: The SAS System used:
> real time 3:05:13.42
> user cpu time 39:16.90
> system cpu time 28:48.03
> Memory 1789151k
> ----
>
> Any ideas how to prevent this error ?
>
Richard, 3-hours and a blowout; what a drag!
I'll bet that the error was due to SAS's attempt to run multi-threading
for the SQL Procedure on your server, and the server resources being
constrained such that SAS could not create a new thread when it needed
to. You might want to try the NOTHREADS system option:
options nothreads;
It is documented in the SAS Online Documentation:
http://support.sas.com/onlinedoc/912/getDoc/lrdict.hlp/a002197022.htm
...or if you want to localize the non-use of threads to your SQL
Procedure, you could use the NOTHREADS option in the PROC SQL statement:
http://support.sas.com/onlinedoc/912/getDoc/proc.hlp/a002473669.htm
...check them both out; either one could be your way out of this
problem. Of course, your SQL program might run longer without
multi-threading. But, at least you could get the job done.
Richard, best of luck in threading your way through this problem!
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@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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Just think of the tragedy of teaching children not to doubt. - Clarence
Darrow
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
