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;
26 proc sql;
27 create 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 */
194 proc sql;
195 create 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
209 from dictionary.tables
210  where upcase(libname)="&lib"
211 %if &ds ne #ALL %then %do;
212  and upcase(memname)="&ds"
213 %end;
214  ;
215 data 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;
223 run;
224 
225 proc sql;
226 create 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 */
256 proc 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;