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
... View more
