Hi all,
Summary of problems occurred in my job:
If we take the log just before the first (Insufficient memory) error :
NOTE: Renaming a table ...
MPRINT(CALL_INBOUND_JOB): proc datasets lib = work nolist nowarn memtype = (
MPRINT(CALL_INBOUND_JOB): change etls_M601CR_FR = M601CR_FR;
MPRINT(CALL_INBOUND_JOB): quit;
NOTE: Changing the name WORK.ETLS_M601CR_FR to WORK.M601CR_FR (memtype=DATA).
NOTE: The PROCEDURE DATASETS used the following resources:
BOTTOM OF PAGE 000000032 -----------------------------------------------------
30 The SAS System
CPU time - 00:00:00.00
Elapsed time - 00:00:00.02
EXCP count - 10
Task memory - 471K (40K data, 431K program)
Total memory - 10001K (4896K data, 5105K program)
NOTE: The address space has used a maximum of 3256K below the line and 15052K above the limit
And then comes
MPRINT(CHK_EXCESS_TXTLINE): proc sql noprint;
MPRINT(CHK_EXCESS_TXTLINE): SELECT DISTINCT TRANS_ID INTO :mv_List_Trans_Id
((EXCESS_052_FLG = 1 OR EXCESS_062_FLG = 1 ) AND REC_TYP IN ('052', '062')) ;
NOTE: No rows were selected.
MPRINT(CHK_EXCESS_TXTLINE): quit;
NOTE: The PROCEDURE SQL used the following resources:
CPU time - 00:00:44.50
Elapsed time - 00:01:15.43
EXCP count - 111
Task memory - 1085K (0K data, 1085K program)
Total memory - 784771K (779060K data, 5711K program)
NOTE: The address space has used a maximum of 4908K below the line and 786628K
And then the insufficient memory error comes.
MPRINT(CALL_INBOUND_JOB): ;
MPRINT(CHK_CUST_NO): proc sql noprint;
MPRINT(CHK_CUST_NO): SELECT TRANS_ID into :mv_List_Trans_Id_1 SEPARATED by ',' FROM ( SELECT DISTINCT TRANS_ID, CRED_CUST_NO, 1
AS CNT FROM Work.M601CR_FR WHERE REC_TYP IN ('022', '042', '052', '062') ) GROUP BY TRANS_ID HAVING SUM(CNT) > 1 ;
NOTE: SAS threaded sort was used.
ERROR: Insufficient memory.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of state
MPRINT(CHK_CUST_NO): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The PROCEDURE SQL used the following resources:
CPU time - 00:01:09.06
Elapsed time - 00:02:06.13
EXCP count - 14958
Task memory - 1253K (0K data, 1253K program)
Total memory - 785579K (779700K data, 5879K program)
NOTE: The address space has used a maximum of 4912K below the line and 791356K
Before and inclusive the step of "renaming table" (the first entry I have listed), - the storage usage above the limit has been no more than 15MB (in fact for many procedures/steps it has been steady on 15052K).
Then comes the "MPRINT(CHK_EXCESS_TXTLINE): proc sql noprint" with the select statement which in fact returns ZERO rows,
but as you can see returning ZERO rows results in an storage increase from the (steady) 15052K to around 780MB
Still not an error, - but as you can see the next step kind of builds on top of the storage allocated, - and after the "Insufficient memory" message the storage allocation is now around 790MB.
So the question is, - should there be somehow a built-in command to release storage after the procedure/step resulting in ZERO rows ?. Does the application miss to do this because ZERO rows returned triggers another code logic path ?.