Boîte à outils SAS/ACCESS to Netezza
1 2 3 4 5 6 7 |
%let netezza_server='server'; %let netezza_database='base'; %let netezza_user='login'; %let netezza_password='mot_de_passe'; |
1 2 3 4 5 6 7 8 9 10 |
proc sql; connect to netezza( SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); create table _t_sessctx as select * from connection to netezza (select * from _t_sessctx;); quit; |

1 2 |
select * from _t_sessctx WHERE session_state_name='active' AND client_type_name='sql-odbc' AND client_os_username='sasdemo'; |
1 |
select current_sid; |
1 2 3 4 5 6 7 8 9 10 11 12 |
proc sql; connect to netezza(SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); create table table_system as select * from connection to netezza (SELECT sr.objname, v.virdescr, v.virprevname, v.virstatus FROM _v_sys_relation sr INNER JOIN _t_virtual v ON sr.objid = v.virrelid); quit; |
Avoir une estimation du nombre de ligne d'une table Netezza
Il s'agit d'une estimation basée sur les tables systèmes Netezza.
1 2 3 4 5 6 7 |
proc sql; connect to netezza(SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); SELECT TABLENAME, RELTUPLES FROM NTZ_ECD._V_TABLE where objtype = 'TABLE' ORDER BY RELTUPLES; quit; |

Décrire les métadonnées d'une table/vue Netezza ( describe d'une table/vue Netezza)
Il n'y a pas d'option descirbe pour décrire les métadonnées de colonne dans Netezza. Il est toutefois possible d'utiliser le code SAS ci-dessous :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
proc sql; connect to netezza( SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); create table ntz_table as select * from connection to netezza (SELECT tablename, attname AS COL_NAME, b.FORMAT_TYPE AS COL_TYPE, attnum AS COL_NUM FROM _v_table a JOIN _v_relation_column b ON a.objid = b.objid WHERE a.tablename = 'NOM_DE_LA_TABLE' ORDER BY attnum;); quit; |

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
proc sql; connect to netezza( SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); create table ntz_view as select * from connection to netezza (SELECT * FROM _v_view a JOIN _v_relation_column b ON a.objid = b.objid WHERE a.VIEWNAME = '_V_VIEW_STORAGE_STAT' ORDER BY attnum;); quit; |

Vérifier les sessions / requêtes actives Netezza
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
proc sql; connect to netezza( SERVER=netezza_server database=netezza_database USER=netezza_user PASSWORD=netezza_password); create table _t_sessctx as select * from connection to netezza (select q.qs_sessionid, q.qs_planid, q.qs_clientid, s.dbname, s.username, s.TYPE SESSION_TYPE, s.STATUS SESSION_STATUS, s.PRIORITY SESSION_PRIORITY, q.qs_cliipaddr, q.QS_STATE, substr(q.qs_sql,1,200), case when q.qs_state = 1 then 'pending' when q.qs_state = 2 then 'queued' when q.qs_state = 3 then 'running' when q.qs_state = 4 then 'aborted' when q.qs_state = 5 then 'done' else 'unknown' end QS_STATE_DESCRIPTION, q.qs_tsubmit+t.tzoffset qs_tsubmit, q.qs_tstart+t.tzoffset tzoffset, case when q.qs_tstart = t.EPOCH then 0 else abstime 'now' - q.qs_tstart end running_duration, q.qs_pritxt, q.qs_estcost, q.qs_estdisk, q.qs_estmem, q.qs_snippets, q.qs_cursnipt, q.qs_resrows, q.qs_resbytes from _v_qrystat q, _v_session s, _vt_pg_time_offset t where q.qs_sessionid = s.id); quit; |