Neben den Eigenschaften von einzelnen Benutzerprofilen kann es auch interessant sein zu wissen, wieviele Benutzerprofile existieren, welche davon aktiv oder nicht aktiv sind, wieviele tatsächlich verwendet werden. Eine kompakte Darstellung dieser und weiterer Informationen können mit Hilfe eines SQL-Konstrukts Dynamic Compound Statement dargestellt werden. Ein derartiges Konstrukt wird in diesem Artikel vorgestellt und damit letztendlich eine immer wieder zu verwendende Funktion erstellt.
Das Dynamic Compound Statement (= dynamische zusammengesetzte Anweisung)
Ein Dynamic Compound Statement – im Folgenden abgekürzt mit DCS – ähnelt einer SQL-Prozedur, mit dem Unterschied, dass kein permanentes Objekt erstellt werden muss. Beispielsweise kann ein DCS verwendet werden, um Skripten Logik hinzuzufügen.
Wenn ein DCS ausgeführt wird, besteht der Mehraufwand für das Erstellen, Ausführen und Löschen eines Programms. Aufgrund dieses Mehraufwands sollte eine SQL-Funktion oder eine SQL-Prozedur für Situationen verwendet werden, in denen die Anweisung häufig ausgeführt werden muss.
Es gibt keine Eingabe- oder Ausgabeparameter für ein DCS. Sie können stattdessen globale Variablen verwenden, um Eingabewerte und Rückgabewerte zu übergeben.
BEGIN
// Declarations (optional)
declare variable1 type;
declare variable2 type;
// Statements
statement1;
statement2;
// Control Structures
if (condition) then
statement3;
else
statement4;
end if;
loop
statement5;
exit when condition;
end loop;
// More statements
statement6;
END;Struktur eines Dynamic Compound Statements
Der “dynamische” Aspekt bedeutet, dass der Inhalt der DCS zur Laufzeit generiert oder geändert werden kann. Dies kann in Szenarien wie der Skripterstellung nützlich sein, in denen der auszuführende Code möglicherweise erst zur Laufzeit bekannt ist.
Insgesamt ist eine dynamische zusammengesetzte Anweisung ein leistungsfähiges Konstrukt, das es ermöglicht, komplexe und facettenreiche Operationen zu kapseln und als zusammenhängenden Block auszuführen, der oft während der Programmausführung bestimmt wird.
SQL DCS zur Erstellung der Benutzerstatistik
Die zuvor beschriebene Technik wird hier verwendet, um verschiedene statistische Werte zu den existierenden Benutzerprofilen zu ermitteln.
Zur Ermittlung der einzelnen Informationen wird der Db2 for i Service in Form einer SQL-View QSYS2.USER_INFO verwendet.
Übrigens:
Dieser Service existiert auch in der Bibliothek SYSIBMADM. Mir sind keine Unterschiede zwischen den beiden Views bekannt.
Das komplette Skript finden Sie hier.
Für mein Beispiel habe ich einige Änderungen und Ergänzungen hinzugefügt.
In der Folge zeige ich Ihnen einige Ausschnitte aus den einzelnen Blöcken des Skripts.
Begin
-- Deklaration der Variablen
-- Count total users
Declare All_Users Dec(7, 0);
-- Users with Special Authorities
Declare Users_With_Spcaut Dec(7, 0);
Declare Pct_Users_With_Spcaut Dec(5, 2);
Declare Users_With_Splctl Dec(7, 0);
Declare Pct_Users_With_Splctl Dec(5, 2);
Declare Users_With_Allobj Dec(7, 0);
Declare Pct_Users_With_Allobj Dec(5, 2);
…..
Declare Pct_Group_Profiles_Pwd Dec(5, 2);
Declare Users_Pwd_Expint Dec(7, 0);
Declare Pct_Users_Pwd_Expint Dec(5, 2);Deklarationsteil des DCS
-- Wertzuweisungen und Berechnungen
Set All_Users = (Select Count(Authorization_Name)
From Qsys2.User_Info);
Set Users_With_Spcaut = (Select Count(Authorization_Name)
From Qsys2.User_Info
Where Special_Authorities <> '');
Set Pct_Users_With_Spcaut = (Users_With_Spcaut / All_Users) * 100;
Set Users_With_Splctl = (Select Count(Authorization_Name)
From Qsys2.User_Info
Where Special_Authorities Like '%*SPLCTL%');
Set Pct_Users_With_Splctl = (Users_With_Splctl / All_Users) * 100;
Set Users_With_Allobj = (Select Count(Authorization_Name)
From Qsys2.User_Info
Where Special_Authorities Like '%*ALLOBJ%');
Set Pct_Users_With_Allobj = (Users_With_Allobj / All_Users) * 100;
…..
-- Users with password sysval override
Set Users_Pwd_Expint = (Select Count(Authorization_Name)
From Qsys2.User_Info
Where Password_Expiration_Interval <> 0);
Set Pct_Users_Pwd_Expint = (Users_Pwd_Expint / All_Users) * 100;Wertzuweisungen und Berechnungen des DCS
--create the userstats table
Drop Table TB012.Userstats If Exists;
Create Or Replace Table TB012.Userstats (
Description Char(75), Amount Int, Percent Dec(5, 2));Tabelle userstats erstellen
-- insert scripts
Insert Into TB012.Userstats (
Description, Amount, Percent)
Values ('Total Users', All_Users, 100);
Insert Into TB012.Userstats (
Description, Amount, Percent)
Values ('Users with Special Authorities', Users_With_Spcaut,
Pct_Users_With_Spcaut);
Insert Into TB012.Userstats (
Description, Amount, Percent)
Values ('Users with *SPLCTL', Users_With_Splctl, Pct_Users_With_Splctl);
Insert Into TB012.Userstats (
Description, Amount, Percent)
Values ('Users with *ALLOBJ', Users_With_Allobj, Pct_Users_With_Allobj);Zeilen mit den berechneten Werten in die Tabelle userstats einfügen
Insert Into TB012.Userstats (
Description, Amount, Percent)
Values ('Users with password sysval override', Users_Pwd_Expint,
Pct_Users_Pwd_Expint);
-- Ende des Dynamic Compound Statements
End;Ende des DCS
Die Ausführung
-- Ausführung
select * from TB012.userstats;Erstellen der Auswertung userstats
Und so sieht das Ergebnis auf meiner IBM i aus:
Quelle: BärFunktion USERSTATS() erstellen
Mit diesem oben erstellten DCS kann nun auf einfache Art eine Funktion erstellt werden. Dazu kann das komplette DCS eingesetzt werden. Als einzige erforderliche Änderung muss nur noch die Rückgabeanweisung vor der END-Anweisung hinzugefügt werden:
return (select * from TB012.userstats);
/*-------------------------------------------------------------------------------------*/
-- Funktion mit dem Dynamic Compound Statement erstellen
/*--------------------------------------------------------------------------------------*/
CREATE or replace FUNCTION TB012.userstats() RETURNS TABLE (
Description Char(75), Amount Int, Percent Dec(5, 2))
LANGUAGE SQL
MODIFIES SQL DATA
SET OPTION DBGVIEW=*SOURCE
Begin
….
return (select * from TB012.userstats);
End;Erstellen der Funktion userstats
Der Aufruf erfolgt mit:
Select *
From Table (
Tb012.Userstats()
);Ausführen der Funktion userstats
Auf ähnliche Art und Weise kann das DCS auch für die Erstellung einer Stored Procedure verwendet werden.
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