Loading...
Searching...
No Matches
mpe_refreshtables.sas
Go to the documentation of this file.
1/**
2 @file mpe_refreshtables.sas
3 @brief Refreshes the data catalog
4 @details Assumes library is already assigned.
5 Usage:
6
7 %mpe_refreshtables(sashelp)
8
9 <h4> SAS Macros </h4>
10 @li bitemporal_dataloader.sas
11 @li mf_existfeature.sas
12 @li mf_getengine.sas
13 @li mf_getschema.sas
14 @li mp_getconstraints.sas
15
16 @version 9.3
17 @author 4GL Apps Ltd
18**/
19
20%macro mpe_refreshtables(lib,ds=#all);
21%let lib=%upcase(&lib);
22%let ds=%upcase(&ds);
23%local engine; %let engine=%mf_getengine(&lib);
24%local schema; %let schema=%mf_getschema(&lib);
25%put running &sysmacroname &lib(&engine &schema) for &ds;
26proc sql;
27create table cols as
28 select libname as libref
29 ,upcase(memname) as dsn
30 ,memtype
31 ,upcase(name) as name
32 ,type
33 ,length
34 ,varnum
35 ,label
36 ,format
37 ,idxusage
38 ,notnull
39 from dictionary.columns
40 where upcase(libname)="&lib"
41%if &ds ne #ALL %then %do;
42 and upcase(memname)="&ds"
43%end;
44 ;
45
46%mp_abort(iftrue= (&syscc ne 0)
47 ,mac=&_program
48 ,msg=%str(syscc=&syscc afer &lib cols extraction)
49)
50
51%if &engine=SQLSVR %then %do;
52 proc sql;
53 connect using &lib;
54 create table work.indexes as
55 select * from connection to &lib(
56 select
57 s.name as SchemaName,
58 t.name as memname,
59 tc.name as name,
60 ic.key_ordinal as KeyOrderNr
61 from
62 sys.schemas s
63 inner join sys.tables t on s.schema_id=t.schema_id
64 inner join sys.indexes i on t.object_id=i.object_id
65 inner join sys.index_columns ic on i.object_id=ic.object_id
66 and i.index_id=ic.index_id
67 inner join sys.columns tc on ic.object_id=tc.object_id
68 and ic.column_id=tc.column_id
69 where i.is_primary_key=1
70 and s.name=%str(%')&schema%str(%')
71 order by t.name, ic.key_ordinal ;
72 );disconnect from &lib;
73 create table finalcols as
74 select a.*
75 ,case when b.name is not null then 1 else 0 end as pk_ind
76 from work.cols a
77 left join work.indexes b
78 on a.dsn=b.memname
79 and upcase(a.name)=upcase(b.name)
80 order by libref,dsn;
81%end;
82%else %do;
83 %local dsn;
84 %if &ds = #ALL %then %let dsn=;
85
86 %mp_getconstraints(lib=&lib.,ds=&dsn,outds=work.constraints)
87
88
89 /* extract cols that are clearly primary keys */
90 proc sql;
91 create table work.pk4sure as
92 select libref
93 ,table_name
94 ,constraint_name
95 ,constraint_order
96 ,column_name as name
97 from work.constraints
98 where constraint_type='PRIMARY'
99 order by 1,2,3,4;
100
101 /* extract unique constraints where every col is also NOT NULL */
102 proc sql;
103 create table work.sum as
104 select a.libref
105 ,a.table_name
106 ,a.constraint_name
107 ,count(a.column_name) as unq_cnt
108 ,count(b.column_name) as nul_cnt
109 from work.constraints(where=(constraint_type ='UNIQUE')) a
110 left join work.constraints(where=(constraint_type ='NOT NULL')) b
111 on a.libref=b.libref
112 and a.table_name=b.table_name
113 and a.column_name=b.column_name
114 group by 1,2,3
115 having unq_cnt=nul_cnt;
116
117
118 /* extract cols from the relevant unique constraints */
119 create table work.pkdefault as
120 select a.libref
121 ,a.table_name
122 ,a.constraint_name
123 ,b.constraint_order
124 ,b.column_name as name
125 from work.sum a
126 left join work.constraints(where=(constraint_type ='UNIQUE')) b
127 on a.libref=b.libref
128 and a.table_name=b.table_name
129 and a.constraint_name=b.constraint_name
130 order by 1,2,3,4;
131
132 /* extract cols from the relevant unique INDEXES */
133 create table work.pkfromindex as
134 select libname as libref
135 ,memname as table_name
136 ,indxname as constraint_name
137 ,indxpos as constraint_order
138 ,name
139 from dictionary.indexes
140 where nomiss='yes' and unique='yes' and upcase(libname)="&lib"
141 %if &ds ne #ALL %then %do;
142 and upcase(memname)="&ds"
143 %end;
144 order by 1,2,3,4;
145
146 /* create one table */
147 data work.finalpks;
148 set pkdefault pk4sure pkfromindex;
149 pk_ind=1;
150 /* if there are multiple unique constraints, take the first */
151 by libref table_name constraint_name;
152 retain keepme;
153 if first.table_name then keepme=1;
154 if first.constraint_name and not first.table_name then keepme=0;
155 if keepme=1;
156 run;
157
158 /* join back to starting table */
159 proc sql;
160 create table finalcols as
161 select a.*
162 ,b.constraint_order
163 ,case when b.pk_ind=1 then 1 else 0 end as pk_ind
164 from work.cols a
165 left join work.finalpks b
166 on a.libref=b.libref
167 and a.dsn=b.table_name
168 and upcase(a.name)=upcase(b.name)
169 order by libref,dsn,constraint_order;
170
171%end;
172
173%mp_abort(iftrue= (&syscc ne 0)
174 ,mac=&_program
175 ,msg=%str(syscc=&syscc afer &lib indexes extraction)
176)
177
178/* load columns */
179%bitemporal_dataloader(base_lib=&mpelib
180 ,base_dsn=mpe_datacatalog_vars
181 ,append_dsn=finalcols
182 ,PK=LIBREF DSN NAME
183 ,etlsource=&sysmacroname
184 ,loadtype=TXTEMPORAL
185 ,tech_from=TX_FROM
186 ,tech_to=TX_TO
187%if &ds ne #ALL %then %do;
188 ,close_vars=LIBREF DSN
189%end;
190 ,dclib=&mpelib
191)
192
193/* prepare tables */
194proc sql;
195create table work.tabs as select
196 libname as libref
197 ,upcase(memname) as dsn
198 ,memtype
199%if %mf_existfeature(DBMS_MEMTYPE)=1 %then %do;
200 ,dbms_memtype
201%end;
202%else %do;
203 ,'n/a' as dbms_memtype format=$32.
204%end;
205 ,typemem
206 ,memlabel
207 ,nvar
208 ,compress
209from dictionary.tables
210 where upcase(libname)="&lib"
211%if &ds ne #ALL %then %do;
212 and upcase(memname)="&ds"
213%end;
214 ;
215data tabs2;
216 set finalcols;
217 length pk_fields $512;
218 retain pk_fields;
219 by libref dsn;
220 if first.dsn then pk_fields='';
221 if pk_ind=1 then pk_fields=catx(' ',pk_fields,name);
222 if last.dsn then output;
223run;
224
225proc sql;
226create table work.finaltabs as
227 select a.libref
228 ,a.dsn
229 ,a.memtype
230 ,a.dbms_memtype
231 ,a.typemem
232 ,a.memlabel
233 ,a.nvar
234 ,a.compress
235 ,b.pk_fields
236 from work.tabs a
237 left join work.tabs2 b
238 on a.libref=b.libref
239 and a.dsn=b.dsn;
240
241%bitemporal_dataloader(base_lib=&mpelib
242 ,base_dsn=mpe_datacatalog_tabs
243 ,append_dsn=finaltabs
244 ,PK=LIBREF DSN
245 ,etlsource=&sysmacroname
246 ,loadtype=TXTEMPORAL
247 ,tech_from=TX_FROM
248 ,tech_to=TX_TO
249 ,dclib=&mpelib
250%if &ds ne #ALL %then %do;
251 ,close_vars=LIBREF
252%end;
253)
254
255/* prepare table frequently changing attributes */
256proc sql;
257%if &engine=SQLSVR %then %do;
258 connect using &lib;
259 create table work.attrs as select * from connection to &lib(
260 SELECT SCHEMA_NAME(schema_id) as 'schema', name, create_date, modify_date
261 FROM sys.tables ;
262 );
263 create table work.nobs as select * from connection to &lib(
264 SELECT SCHEMA_NAME(A.schema_id) AS 'schema'
265 ,A.Name, AVG(B.rows) AS 'RowCount'
266 FROM sys.objects A
267 INNER JOIN sys.partitions B ON A.object_id = B.object_id
268 WHERE A.type = 'U'
269 GROUP BY A.schema_id, A.Name
270 );
271 disconnect from &lib;
272 create table statustabs as select
273 a.libref
274 ,a.dsn
275 ,b.create_date as crdate
276 ,b.modify_date as modate
277 ,. as filesize
278 ,c.RowCount as nobs
279 from work.tabs a
280 left join work.attrs(where=(schema="&schema")) b
281 on upcase(a.dsn)=upcase(b.name)
282 left join work.nobs(where=(schema="&schema")) c
283 on upcase(a.dsn)=upcase(c.name);
284%end;
285%else %do;
286 create table statustabs as select
287 libname as libref
288 ,upcase(memname) as dsn
289 ,crdate
290 ,modate
291 ,filesize
292 ,nobs
293 from dictionary.tables
294 where upcase(libname)="&lib"
295 %if &ds ne #ALL %then %do;
296 and upcase(memname)="&ds"
297 %end;
298 ;
299%end;
300
301%bitemporal_dataloader(base_lib=&mpelib
302 ,base_dsn=mpe_datastatus_tabs
303 ,append_dsn=statustabs
304 ,PK=LIBREF DSN
305 ,etlsource=&sysmacroname
306 ,loadtype=TXTEMPORAL
307 ,tech_from=TX_FROM
308 ,tech_to=TX_TO
309 ,dclib=&mpelib
310%if &ds ne #ALL %then %do;
311 ,close_vars=LIBREF
312%end;
313)
314
315%if &ds = #ALL %then %do;
316 proc sql;
317 create table work.sumcat as
318 select libname as libref,
319 count(distinct memname) as catalog_cnt
320 from dictionary.catalogs
321 where upcase(libname)="&lib"
322 group by 1;
323 create table work.sumdsn as select
324 libref
325 ,sum(filesize) as libsize
326 ,count(*) as table_cnt
327 from statustabs
328 group by 1;
329 create table work.libs as
330 select libref from work.sumcat
331 union
332 select libref from work.sumdsn;
333 create table work.statuslibs as
334 select a.libref,
335 b.libsize,
336 b.table_cnt,
337 c.catalog_cnt
338 from work.libs a
339 left join work.sumdsn b
340 on a.libref=b.libref
341 left join work.sumcat c
342 on a.libref=c.libref;
343
344 %bitemporal_dataloader(base_lib=&mpelib
345 ,base_dsn=mpe_datastatus_libs
346 ,append_dsn=statuslibs
347 ,PK=LIBREF
348 ,etlsource=&sysmacroname
349 ,loadtype=TXTEMPORAL
350 ,tech_from=TX_FROM
351 ,tech_to=TX_TO
352 ,dclib=&mpelib
353 )
354%end;
355
356%mend mpe_refreshtables;