Deep Dive mit Theo Bär: SQL für den Systemadministrator – Nicht verwendete Bildschirmeinheiten

Innerhalb eines Systems sammeln sich ungenutzte und nicht mehr benötigte virtuelle Bildschirmeinheiten an. In diesem Beitrag erläutert Theo Bär, wie sich diese nicht mehr verwendeten Objekte mit Hilfe von SQL gezielt identifizieren und anschließend effizient entfernen lassen.

Im Laufe der Zeit kann es vorkommen, dass virtuelle Bildschirmeinheiten erstellt werden und permanent gespeichert bleiben, ohne dass sie verwendet werden. Im aktuellen Artikel werden diese Einheiten mit SQL aufgespürt. Weiterhin wird gezeigt, wie diese dann auch mit SQL gelöscht werden können.

Virtuelle Bildschirmeinheiten

Virtuelle Bildschirmeinheiten werden als permanente Objekte der Art *DEVD in der Bibliothek QSYS gespeichert. Mit dem CL-Befehl WRKOBJ können diese angezeigt und gelöscht werden. Die Eigenschaften ‚Erstellungsdatum‘ und ‚Zuletzt benutzt am‘ können auch für jedes einzelne Objekt ermittelt werden. Allerdings ist es nicht ganz einfach und sehr zeitaufwändig, diese Informationen zu ermitteln und dann zu entscheiden, ob die Einheit gelöscht werden soll. Das Löschen kann danach ebenfalls innerhalb des CL-Befehls WRKOBJ erfolgen.

Die Tabellenfunktion Qsys2.Object_Statistics()

Eine wesentlich einfachere Möglichkeit dazu bietet SQL. Alle diese Eigenschaften können mit der Tabellenfunktion Qsys2.Object_Statistics() ermittelt und für Abfragen verwendet werden.

Der folgende Link bietet eine vollständige Übersicht über die Tabellenfunktion:

IBM i / 7.5.0 / OBJECT_STATISTICS table function

Die kontinuierlich erfolgten Erweiterungen der Funktion seit IBM i 7.2 TR2 finden Sie unter:

www.ibm.com/support/pages/qsys2objectstatistics

Die Funktion erwartet 2 bzw. 3 Parameter:

       -- Table Function Parameters
SELECT *
FROM TABLE(QSYS2.OBJECT_STATISTICS(
  OBJECT_SCHEMA => ?,    
  OBJTYPELIST   => ?,        
  OBJECT_NAME   => DEFAULT ) ) ;

^{Die Tabellenfunktion QSYS2.OBJECT_STATISTICS}

Die beiden erforderlichen Parameter werden bei Ausführung des obigen Skripts angefordert:

Damit wird das folgende SQL-Skript erzeugt:

SELECT *
FROM TABLE(QSYS2.OBJECT_STATISTICS(
  OBJECT_SCHEMA => 'QSYS',    
  OBJTYPELIST   => '*DEVD',        
  OBJECT_NAME   => DEFAULT ) ) ;

^{Liste aller Objekte vom Typ *DEVD in Bibliothek QSYS}

Damit erhält man eine Liste aller Objekte in QSYS vom Objekttyp *DEVD.

Diese Liste enthält allerdings nicht nur virtuelle Bildschirmeinheiten, sondern auch Drucker und andere Eiinheiten.

Über die Spalte ‚Objattribute‘ werden alle diese Einheiten klassifiziert.

-- Welche Objektattribute gibt es?
SELECT distinct(Objattribute  )
FROM TABLE(QSYS2.OBJECT_STATISTICS(
  OBJECT_SCHEMA => 'QSYS',    
  OBJTYPELIST   => '*DEVD',        
  OBJECT_NAME   => DEFAULT ) ) 
  order by 1;

^{Welche Objektattribute gibt es?}

Virtuelle Bildschirmeinheiten sind mit dem Objektattribut DSPVRT gekennzeichnet. Damit ergibt sich nun die folgende SQL-Anweisung, um eine Liste aller Virtuellen Bildschirmeinheiten zu erhalten:

-- Liste aller Virtuellen Bildschirmeinheiten
Select *
  From Table (
       Qsys2.Object_Statistics(Object_Schema => 'QSYS', Objtypelist => '*DEVD' )
    )
    Where Objattribute = 'DSPVRT'  ;

^{Liste aller Virtuellen Bildschirmeinheiten}

Nicht verwendete Virtuelle Bildschirmeinheiten

Wie sieht es aber aus mit Einheiten, die zwar existieren, aber längere Zeit nicht benutzt wurden? Auch hierfür bietet die Tabellenfunktion wieder Hilfe an. Sie enthält unter anderm eine Spalte, die angibt, wann die Einheit das letzte Mal benutzt wurde: Last_Used_Timestamp.

Für unsere Auflistung und die Abfragen genügt eine Darstellung der ‚Timestamp‘-Spalten als Datums-Typ.

-- Einheiten, die seit 6 Monaten nicht verwendet wurden
Select Objattribute As "Attrib", Objname, Date(Objcreated) As "Created",
     Date(Last_Used_Timestamp) As "Last used", Objtext
  From Table (
       Qsys2.Object_Statistics('QSYS', 'DEVD')
    )
  Where Objattribute = 'DSPVRT' And Date(Last_Used_Timestamp) < Current_Date -
        6 Months;

^{Liste aller Virtuellen Bildschirmeinheiten, die seit 6 Monaten nicht verwendet wurden}

Weiterhin könnte es interessant sein zu wissen, welche Einheiten erstellt, aber nie verwendet wurden. Dazu wird die WHERE-Klausel in der vorigen SQL-Abfrage ersetzt durch:

WHERE OBJATTRIBUTE = 'DSPVRT'
         AND LAST_USED_TIMESTAMP IS NULL;  -- nie verwendete Bildschirmeinheiten

^{Bildschirmeinheiten, die nie verwendet wurden}

Die beiden Ergebnismengen könnten nun mit der UNION-Anweisung miteinander „verknüpft“ und damit eine Tabelle erstellt werden.

-- beide Ergebnismengen mit UNION verknüpfen (nicht QTEMP verwenden)
CREATE OR REPLACE TABLE TB012.AUDIT01
  (DEVICE,CREATED,LAST_USED,OBJTEXT)
  AS
  (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT
     FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
    WHERE OBJATTRIBUTE = 'DSPVRT'
      AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 5 MONTHS
   UNION
   SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT
     FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
    WHERE OBJATTRIBUTE = 'DSPVRT'
      AND OBJCREATED < CURRENT_TIMESTAMP - 6 MONTHS
      AND LAST_USED_TIMESTAMP IS NULL)
  WITH DATA ON REPLACE  DELETE ROWS ;

^{Tabelle mit den zu löschenden Bildschirmeinheiten erstellen}

Falls diese Unterlagen für ein Audit benötigt werden, kann aus dieser Tabelle eine CSV-Datei oder eine Excel-Tabelle erstellt werden. Dazu kann die Ergebnismenge manuell in eine Excel-Tabelle umgesetzt werden.

Oder Sie erstellen mit der Skalarfunktion SYSTOOLS.GENERATE_SPREADSHEET() direkt eine Excel-Tabelle:

-- Generate Spreadsheet CSV  (Spalten werden durch Komma getrennt und nicht durch Semikolon)
VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/TB/Audit01',
                SPREADSHEET_QUERY => 'select * from tb012.audit01',
                COLUMN_HEADINGS => 'COLUMN');

-- -- Generate Spreadsheet als .XLSX
VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/TB/audit01',
                     LIBRARY_NAME => 'TB012',
                     FILE_NAME => 'AUDIT01',
                     SPREADSHEET_TYPE => 'xlsx',
                     COLUMN_HEADINGS => 'LABEL');

^{CSV-Datei und XLSX-Tabelle erstellen}

Löschen von Virtuellen Bildschirmeinheiten

Zum Löschen von nicht mehr verwendeten Bildschirmeinheiten kann wiederum SQL-Code verwendet werden:

QSYS2.QCMDEXC('DLTDEVD DEVD(' || OBJNAME || ')')

Für die Auswahl der zu löschenden Einheiten werden die zuvor beschriebenen SQL-Anweisungen verwendet.

Zur besseren Übersichtlichkeit wird das Konstrukt COMMON TABLE EXPRESSION (= CTE) verwendet.

-- Löschen der ausgewählten Bildschirmeinheiten
  CREATE TABLE QTEMP.OUTFILE
  (DEVICE_NAME,CREATE_DATE,LAST_USED_DATE,
  DELETE_COMMAND,RETURN_CODE)
  AS
  (WITH T0(OBJNAME,CRTDATE,LASTUSEDDATE)
  AS
  (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP)
    FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
    WHERE OBJATTRIBUTE = 'DSPVRT'
    AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS)
   
  SELECT OBJNAME,CRTDATE,LASTUSEDDATE,
      'DLTDEVD DEVD(' || OBJNAME || ')',
      QSYS2.QCMDEXC('DLTDEVD DEVD(' || OBJNAME || ')')
   FROM T0)
  WITH DATA ;

^{Löschen der selektierten Bildschirmeinheiten}

Hinweis: Die Verwendung der hier dargestellten Skripte erfolgt in eigener Verantwortung.

Viel Spaß dabei!

Vorschau:
Im nächsten Artikel werde ich Ihnen weitere SQL-Tools für den Systemadministrator vorstellen.

Bis dahin wünsche ich Ihnen weiterhin viel Spaß beim Vermehren Ihrer Fertigkeiten.

---

Den Autor Theo Bär erreichen Sie unter

EDV-Beratung Theo Bär
Ringmauerweg 1
69250 Schönau

Tel. (+49) 6228 912 630
E-Mail: info@edv-baer.com