PDA

View Full Version : Berechtigung QSYS View ermöglichen



Seiten : [1] 2

Gutmann
26-09-19, 15:39
Ich möchte gerne (Teile) der Informationen aus Systemview QSYS2.USER_INFO nutzen. Bislang haben wir in einem Nachtlauf eine Datei aufgebaut, mit der Konsequenz veraltete Daten zu haben.
Charmant wäre es, aktuelle Daten wie letzten Login PREVIOUS_SIGNON zu erhalten.
Dazu wollte ich eine UDF nutzen, welche mit *OWNER umgewandelt wird:


CREATE FUNCTION obj.GetUsrInfo()
RETURNS TABLE
(
AUTHORIZATION_NAME VARCHAR(10),
PREVIOUS_SIGNON TIMESTAMP,
SIGN_ON_ATTEMPTS_NOT_VALID INTEGER,
STATUS VARCHAR(10),
… usw ...
CREATION_TIMESTAMP TIMESTAMP,
LAST_USED_TIMESTAMP TIMESTAMP,
DAYS_USED_COUNT INTEGER,
LAST_RESET_TIMESTAMP TIMESTAMP
)
LANGUAGE SQL READS SQL DATA
SET OPTION USRPRF=*OWNER
BEGIN
RETURN select * from qsys2.user_info;
END;


Die Abfrage wäre dann wie folgt: SELECT * FROM TABLE(hgwobj.GetUsrInfo()) x where x.status='*DISABLED';


Die View der USER_INFO greift auch wieder nur auf eine Funktion zu mit Parametern, gefühlt wird die Variante mit noch einer UDF langsamer. Für eine Abfrage wie oben mit SELECT ist es initial 8 Sekunden!
Wenn ich direkt den SELECT mit meinen niedrigen User-Rechten ausführe, sehe ich immerhin 112 der 1500 Userprofile. Woran liegt das?
Gibt es eine elegantere Variante den Zugriff auf z.B. eine Teilmenge zuzulassen Berechtigungen aufweichen / Column Access - Hier bitte Vorschläge - am liebsten wäre mir, ich könnte wenn aus Gründen der Systemsicherheit und Updatefähigkeit (Zugriff/Abhängigkeit/Locks auf Systemtable?) keine Problme auftreten. Ich kenne keine andere Variante wie die UDF bislang.
Müssen für die UDF alle Spalten per Hand angegeben werden? Ich hab mir zwar mit Excel eine Hilfe anhand der Syscolumns gebaut, aber einfacher wäre natürlich anhand des SELECTS den DatenTyp zu ermitteln :)
Wäre hier eine MQT für die Performante Abfrage eine Lösung - wie sieht es hier mit dem Refresh und der Berechtigung aus - brauche ich dann z.B. eine Stored Procedure mit *OWNER oder "schafft" die MQT das selbst per AutoUpdate (gibt ja zwei Modi *DEFFERED u. noch einen anderen)
Bei der Abfrage mit SELECT * FROM TABLE(hgwobj.GetUsrInfo()) x where x.authorization_name='HUGO';kommt eine Fehlermeldung - allerdings weiß ich nicht warum. Beim Feld Status='*DISABLED' in der WHERE-CLAUSE kommt kein Fehler, Ändere ich den Status = 'TEST' kommt wieder eine Fehlermeldung. Im Joblog sehe ich folgendes:

<tbody>

Ursache . . . . : Beim Aufrufen der benutzerdefinierten Funktion GETUSRINFO in Bibliothek OBJ ist ein Fehler aufgetreten. Der Fehler trat beim Aufrufen des zugeordneten externen Programms oder Serviceprogramms GETUSRINFO in Bibliothek OBJ, Programmeingangspunkt bzw. externem Namen GETUSRINFO_1, spezieller Name GETUSRINFO, auf. Der Fehler trat bei Teildatei QSQPTABL Datei QSQPTABL in Bibliothek QSYS2 auf. Der Fehlercode ist 1. Fehlercodes und ihre Bedeutung: 1 -- Das externe Programm oder Serviceprogramm hat SQLSTATE 22012 zurückgegeben. Die vom Programm zurückgegebene Textnachricht ist: Fehler bei Datenumsetzung oder beim Zuordnen von Daten. .

</tbody>
<strike></strike>
Laut dem Link https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/db2z_sqlstatevalues.html ist das eine Division bei Zero Exception.

Ein andere Joblog Eintrag sagt


<tbody>
Ursache . . . . : Bei dem Versuch, einen Wert an die Variable SQL_RETURN.USER_ID_NUMBER in einer Anweisung FETCH, einer eingebetteten Anweisung SELECT, einer Anweisung CALL, SET, SET DESCRIPTOR oder VALUES INTO zurückzugeben, ist ein Fehler der Art 1 aufgetreten. Mögliche Fehlerarten sind: -- Fehlerart 1 - Überlauf. -- Fehlerart 2 - Gleitkommaüberlauf. -- Fehlerart 3 - Gleitkommaunterlauf. -- Fehlerart 4 - Gleitkommaumsetzungsfehler. -- Fehlerart 5 - Ungenaues Ergebnis. -- Fehlerart 6 - Ungültige numerische Daten. -- Fehlerart 7 - Ungültige DBCS-Daten. Die relative Position der Variablen ist 52. Ist der Name der Variablen *N, wurde in der Anweisung FETCH oder CALL ein Deskriptorbereich angegeben. Fehlerbeseitigung: Die Größe und ggf. die Art der Variablen oder des Eintrags im Deskriptorbereich so ändern, dass der Ergebniswert hineinpasst, oder die ungültigen Daten korrigieren. Die Anforderung wiederholen.

</tbody>
<strike></strike>


Freue mich auf eure Fragen/Antworten.

<strike></strike><strike></strike>

holgerscherer
26-09-19, 23:54
Ich möchte gerne (Teile) der Informationen aus Systemview QSYS2.USER_INFO nutzen. Bislang haben wir in einem Nachtlauf eine Datei aufgebaut, mit der Konsequenz veraltete Daten zu haben.

Ich mag ja altmodisch sein, aber hast Du mal mit RTVUSRPRF gegengeprobt?

Frage 1 dürfte sich in Kombination mit DSPOBJD > File > RTVUSRPRF in einem CL erklären. Bei 1500 Userprofilen, die vielleicht zufällig noch Eigner vieler Objekte sind, kann das vorkommen.
Frage 2 - Objektberechtigungen auf die Userprofile?
Frage 3 - was solls werden? Eine Auswertung gesperrter Userprofile? Auch hier würde ich zum altmodischen CL greifen, auch wenn es schön ist, daß man inzwischen vieles mit SQL regeln kann. Gewisse Nachteile hast Du ja bemerkt ;-)
Frage allgemein: Was bedeutet "veraltete" Daten? Willst Du beim Anmelden eines Users über eine SQL-basierte Anwendung Daten wie "letztes Anmelden" ermitteln? Siehe oben, geht auch mit den alten Umwegen. Wenn das zu langsam ist, analysieren lassen, was der Apparat macht...

Das Problem ist, daß diese SQL-gekapselten "Vereinfachungsroutinen" auch nicht wirklich anders arbeiten, als im Hintergrund mit den gleichen APIs zu arbeiten, als ein CL-Command.

Lasse ich zB auf PUB400.COM (1 Power7-Core, 16GB RAM, langsames Storage) per STRSQL folgendes in eine Datei ausgeben:

select * from qsys2.user_info

warte ich 77 Sekunden. Bei 8000 Userprofilen. Da sind Deine 8 Sekunden also nicht ungewöhnlich.

Mach mal spasseshalber ein DSPFD QSYS2/USER_INFO; da ist leider viel zu selektieren.

-h

Gutmann
27-09-19, 10:02
Hallo Holger, danke für die Antwort.
Es war angedacht, dass wir für eine Liste von Benutzern die wir Beispielsweise in unserer eigenen Menüverwaltung haben zusätzliche Systemwerte anzeigen, wie z.B. Letzte Logindatum.
Kannst du zu 1) nochmal genauer schildern, was du damit meinst?
Den
DSPFD QSYS2/USER_INFO habe ich ausgeführt - auf welche Zahl soll ich achten? :)

Kann zu Fragen 4-6 noch jemand eine Hilfe abgeben?

Fuerchau
27-09-19, 14:13
Wie Holger schreibt, viele neue SQL-Funktionen sind i.W. nur wegen des iSeries Navigator entstanden, da Java eben nicht so einfach auf API's zugreifen kann.
Daher sind diese SQL-Funktionen eben Wrapper auf API's.
Und wenn du dir die API's ansiehst, dann kannst du verstehen, warum das so lange dauert.
Des weiteren liefern die Table-Function i.d.R. eben alles was du dann per Where-Klausel wieder ausfilterst.
Dabei haben die API's häufig eigene Filterkriterien.

Wenn du eine eigene Table-Function schreibst, dann sorge doch dafür dass du optionale Parameter bereits als Filterkriterium übergeben kannst!
Damit sparst du dir auch hier bereits den vollen Aufbau um anschließend wieder nicht gewünschtes auszufiltern.

Bei der Declaration von UDF's und UDP's müssen die Ein-/Ausgabeparameter gezielt deklariert werden, dadu ja im SQL-Body oder dem externen Programm ja noch diverse Änderungen an den Werten und somit auch den Typen vornehmen kannst.
Bei der Übergabe passieren dann wieder Autocasts.

Ich glaube nicht, dass MQT's auch auf Table-Functions funktionieren, ggf. weiß Birgitta da weiter.
I.d.R. machen MQT's an Hand bestimmter Kriterien ihre Updates, was bei UDTF's ja nicht klappen kann.

Was deinen Fehler angeht, so starte den Debugger und schaue die Fehlermeldungen dazu an.
Das kann an Parameterfehlern liegen als auch an falscher Interpretation der Aufgabenstellung.

holgerscherer
27-09-19, 17:17
Hallo Holger, danke für die Antwort.
Es war angedacht, dass wir für eine Liste von Benutzern die wir Beispielsweise in unserer eigenen Menüverwaltung haben zusätzliche Systemwerte anzeigen, wie z.B. Letzte Logindatum.
Kannst du zu 1) nochmal genauer schildern, was du damit meinst?
Den
DSPFD QSYS2/USER_INFO habe ich ausgeführt - auf welche Zahl soll ich achten? :)

Kann zu Fragen 4-6 noch jemand eine Hilfe abgeben?


Also keine Systemwerte anzeigen, sondern Attribute eines Userprofils, dafür gibts das CL Command RTVUSRPRF (auch via SQL aufrufbar). Das könnte flotter sein, als eben die USER_INFO abzufragen. Im DSPFD nicht auf eine Zahl achten, sondern runter blättern und das SQL anschauen und auswendig lernen - das muss nämlich jedes Mal ausgeführt werden ;-)

Fragen 4-6 dürften sich erledigen (falls nicht generell gemeint), wenn Du den einfachen Weg über die altmodische Variante (siehe oben) gehst.

Ach ja - arbeitet Ihr mit GreenScreen und RPG/CL oder mit einer PC-basierten Anwendung, die per ODBC/JDBC/SQL zugreift?

-h

Gutmann
28-09-19, 18:34
dafür gibts das CL Command RTVUSRPRF (auch via SQL aufrufbar). Das könnte flotter sein, als eben die USER_INFO abzufragen.

Ach ja - arbeitet Ihr mit GreenScreen und RPG/CL oder mit einer PC-basierten Anwendung, die per ODBC/JDBC/SQL zugreift?

-h
Danke für die Antworten. Dann bleiben noch zwei Fragen.

Die UDF besteht ja nur aus einem SELECT * FROM - was soll ich da debuggen?

Holger, wie führst du das denn über SQL aus?

Das entscheidende ist halt, ich habe eine Liste von Usern und muss dazu die korrespondierenden Infos des Benutzerprofils dazu mappen. Ein Parameter für die Funktion kann ich also nicht vornehmen. Die Infos wenn wirklich in einer Tabelle wären die einfach gejoined werden kann wäre schon hübsch.

Wir arbeiten sowohl als auch mit ODBC aber auch nativ auf der i mit RPG/CL.

Wäre die QSYRUSRI wie hier eine Option?
https://www.ibm.com/support/pages/example-rpgle-calling-qsyrusri-api
Ich müsste halt dann N-mal pro Userprofil das ich anzeigen will die Infos dazu lesen.

Fuerchau
29-09-19, 17:06
Mach mal einen DSPFD der View für die UserInfo.
Dort kannst du dir den SQL genau ansehen.
U.U. wird in from "table(getirgendwas(p1, p2))" gelesen, da durchaus 2 API's eine Rolle spielen können.
Ein List-API für USRPRF-Objekte und ein USRINFO-API je User.
Somit könntest du die Inner-Function ganz genauso aufrufen.
Ich kann das i.M. nicht online verifizieren, aber es aber bei anderen Systemviews genau so gesehen.

Gutmann
29-09-19, 19:29
Hallo Herr Fürchau, also das CREATE bzw. das FROM sieht wie folgt aus:


SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QSYS ','USRPRF ',NULL)) a, lateral (SELECT * FROM TABLE (QSYS2.QSYUSRINFO(a.OBJNAME)) as x) as b;


Wenn ich das so ausführe braucht es immerhin nur 500 ms.
Die Objectstatistics allein bringt mir nichts, die qsyusrinfo aber auch nicht, da hier der User fehlt. Da es ja nicht nur um den einen geht, muss ich sie eben joinen über den LATERAL SELECT wie oben zu sehen.
Den Befehl DSPFD kannte ich so gar nicht, ich bin immer über SCHEMATA u. SQL generieren gegangen :)
Wenn ich nun aber eine View anstelle der UDF mache, wie kriege ich das dann mit den Rechten hin?

Fuerchau
30-09-19, 06:54
"QSYS2.OBJECT_STATISTICS('QSYS ','USRPRF ',NULL)"

Die Funktion nimmt 3 Parameter:
1. Lib
2. ObjType
3. wahrscheinlich OBJNAME, wobei NULL dem *ALL entspricht.

Wahrscheinlich kann man das auch für andere Objekttypen verwenden.

"QSYS2.QSYUSRINFO(a.OBJNAME)"

Damit hast du doch genau die Funktion, die du eigentlich willst:
Parameter dürfte der Username sein, was durch den Join vom 1. API ermittelt wird.

SELECT * FROM TABLE (QSYS2.QSYUSRINFO("MEIER"))

sollte also genau von enem User die Info bringen.

BenderD
30-09-19, 07:47
Hallo Holger, danke für die Antwort.
Es war angedacht, dass wir für eine Liste von Benutzern die wir Beispielsweise in unserer eigenen Menüverwaltung haben zusätzliche Systemwerte anzeigen, wie z.B. Letzte Logindatum.
Kannst du zu 1) nochmal genauer schildern, was du damit meinst?
Den
DSPFD QSYS2/USER_INFO habe ich ausgeführt - auf welche Zahl soll ich achten? :)

Kann zu Fragen 4-6 noch jemand eine Hilfe abgeben?


Table Functions sind Murks, wenn anschließend ein Index für effektiven Zugriff benötigt würde. Beispielsweise bei ORDER BY, WHERE, JOIN - also fast immer. Dann geht nämlich die Verarbeitungszeit hoch und die Aktualität der Daten runter. Mit MQTs kann man die Zugriffszeit zu Lasten der Aktualität verbessern, das ist die Datenbank Variante zu eurer Snapshot Lösung. Murks * Murks (Table Function auf Table Function) hebt sich nicht weg, sondern potenziert sich.

Einzelne virtuelle Felder implementiert man besser als SQL Functions, die man auch in Views verwenden kann. Durch intelligentes caching kann man auch mehrere Attribute auf einmal holen und einzeln in Functions liefern.

In eurem skizzierten Fall handelt es sich um Daten, die sich selten ändern. Da ist das sauberste einen Startbestand zu erzeugen (den habt ihr ja bereits) und dann die Änderungs Events automatisch einzupflegen. Die Änderungsevents kriegt man hier über das Audit Journal mit RCVJRNE. Mit dieser Strategie kriegt man auch die Security Probleme einfach und sauber gelöst.

D*B