below is complete log, only remove header part (from 1 to 86) to keep it short.
87 %macro ststs;
88
89 proc sql;
90 create table stats as
91 select owner,
2 The SAS System 23:44 Tuesday, August 1, 2023
92 table_name,
93 partition_name,
94 subpartition_name,
95 stattype_locked,
96 stale_stats /* YES or NO */
97 from pcds.dba_tab_statistics
98 where table_name = 'ADDR_TLNBR_MAINT_EVENT_C';
99 quit;
100
101 data stats_upd_ned;
102 set stats;
103 where upcase(trim(stale_stats))="YES";
104 run;
105
106 proc sql noprint;
107 select count(*) into :cnt from stats_upd_ned;
108 quit;
109
110 %if &cnt le 0 %then
111 %do;
112 %put "No RECORDS AVAILABLE";
113 %end;
114 %else
115 %do;
116
117 proc sql noprint;
118 select trim(OWNER), trim(TABLE_NAME),trim(PARTITION_NAME)
119 into
120 :_OWNER SEPARATED BY " ",
121 :_TABLE_NAME SEPARATED BY " ",
122 :_PARTITION_NAME SEPARATED BY " "
123 from stats_upd_ned
124 where trim(OWNER)='FMT_TRAN_DBA' and trim(TABLE_NAME) = 'ADDR_TLNBR_MAINT_EVENT_C';
125 quit;
126
127 %put &_OWNER.;
128 %put &_TABLE_NAME.;
129 %put &_PARTITION_NAME.;
130 /* options symbolgen;*/
131
132 %DO I=1 %TO &cnt;
133
134 proc sql;
135 connect to oracle (user="&pcdsuser" orapw="&pcdspw" path="@pcds");
136 execute( BEGIN
137 DBMS_STATS.GATHER_TABLE_STATS
138 (OWNNAME =>('FMT_TRAN_DBA'),
139 TABNAME =>('ADDR_TLNBR_MAINT_EVENT_C'),
140 PARTNAME =>%TSLIT(&_PARTITION_NAME.),
141 GRANULARITY =>('PARTITION'),
142 CASCADE => TRUE,
143 estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
144 method_opt=>('FOR ALL COLUMNS SIZE AUTO'),
145 FORCE=>TRUE);
146 END;
147 ) by oracle;
148 disconnect from oracle;
149 quit;
3 The SAS System 23:44 Tuesday, August 1, 2023
150
151 %end;
152 %end;
153 %mend ststs;
154
155 %ststs;
NOTE: Compressing data set WORK.STATS increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.STATS created, with 632 rows and 6 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 5299.87k
OS Memory 24228.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 16 Switch Count 3
Page Faults 0
Page Reclaims 503
Page Swaps 0
Voluntary Context Switches 50
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
NOTE: There were 1 observations read from the data set WORK.STATS.
WHERE UPCASE(TRIM(stale_stats))='YES';
NOTE: The data set WORK.STATS_UPD_NED has 1 observations and 6 variables.
NOTE: Compressing data set WORK.STATS_UPD_NED increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 2567.87k
OS Memory 21928.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 17 Switch Count 2
Page Faults 0
Page Reclaims 512
Page Swaps 0
Voluntary Context Switches 24
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 0
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6202.56k
OS Memory 26024.00k
4 The SAS System 23:44 Tuesday, August 1, 2023
Timestamp 08/01/2023 11:51:40 PM
Step Count 18 Switch Count 0
Page Faults 0
Page Reclaims 248
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
SYMBOLGEN: Macro variable CNT resolves to 1
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6211.96k
OS Memory 26024.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 19 Switch Count 0
Page Faults 0
Page Reclaims 252
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
SYMBOLGEN: Macro variable _OWNER resolves to FMT_TRAN_DBA
FMT_TRAN_DBA
SYMBOLGEN: Macro variable _TABLE_NAME resolves to ADDR_TLNBR_MAINT_EVENT_C
ADDR_TLNBR_MAINT_EVENT_C
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
PTN_W20230730
SYMBOLGEN: Macro variable CNT resolves to 1
SYMBOLGEN: Macro variable PCDSUSER resolves to aaaaaaaa
SYMBOLGEN: Macro variable PCDSPW resolves to xxxxxxxx
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V1 resolves to PTN_W20230730
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V2 resolves to "PTN_W20230730"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V3 resolves to 'PTN_W20230730'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
ERROR: ORACLE execute error: ORA-20000: Unable to analyze TABLE "FMT_TRAN_DBA"."ADDR_TLNBR_MAINT_EVENT_C" PTN_W20230730,
insufficient privileges or does not existORA-06512: at "SYS.DBMS_STATS", line 24270ORA-06512: at "SYS.DBMS_STATS", line
24332ORA-06512: at line 1.
5 The SAS System 23:44 Tuesday, August 1, 2023
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Statement not executed due to NOEXEC option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.92 seconds
user cpu time 0.01 seconds
system cpu time 0.09 seconds
memory 249.18k
OS Memory 19872.00k
Timestamp 08/01/2023 11:51:43 PM
Step Count 20 Switch Count 0
Page Faults 0
Page Reclaims 43
Page Swaps 0
Voluntary Context Switches 45
Involuntary Context Switches 60
Block Input Operations 0
Block Output Operations 0
156
157 %LET _CLIENTTASKLABEL=;
158 %LET _CLIENTPROCESSFLOWNAME=;
159 %LET _CLIENTPROJECTPATH=;
160 %LET _CLIENTPROJECTPATHHOST=;
161 %LET _CLIENTPROJECTNAME=;
162 %LET _SASPROGRAMFILE=;
163 %LET _SASPROGRAMFILEHOST=;
164
165 ;*';*";*/;quit;run;
166 ODS _ALL_ CLOSE;
167
168
169 QUIT; RUN;
170
