SQL Optimierung V5R4 Teil II

[KingofKning]

Hallo *all,
ich bin ja immer noch bei meinen Daten zusammenstellen.
Ich habe hier eine UDF
CREATE FUNCTION rptrade/istumsatz ( PARARTNR dec(6) ) RETURNS VARCHAR(134) LANGUAGE SQL READS SQL DATA BEGIN
DECLARE RETURNVAL VARCHAR ( 134 ) NOT NULL DEFAULT ' ' ;
DECLARE WORK VARCHAR ( 333 ) NOT NULL DEFAULT ' ' ;
FOR CSRC1 AS C1 CURSOR
FOR SELECT ist
FROM aruba_msvc/planz
WHERE knd = PARARTNR
DO SET work = work CONCAT ';' CONCAT
Right('0000000000' concat Varchar(Dec(CSRC1.ist, 8, 2)), 10); END FOR ; set work = work
concat ';0000000,00;0000000,00;' concat '0000000,00;0000000,00;0000000,00;0000000,00;' concat '0000000,00;0000000,00;0000000,00;0000000,00;' concat '0000000,00;0000000,00;';
SET RETURNVAL = left(work, 133);
RETURN LTRIM(RETURNVAL) ;
end

Die auf folgende View zugreift:

CREATE VIEW PLANZ AS SELECT ALL T01.STMOAN AS JM, T01.STKTO AS VKL,
T01.STTENR AS KND, DEC(1, 2, 0) AS FIRMA, DEC(0, 2, 0) AS ABKZ,
CAST (SUM(CASE WHEN STPLIS = 1 THEN STWT02 ELSE 0 END) AS DEC(10, 3)) PLAN,
CAST(SUM(CASE WHEN STPLIS = 2 THEN STWT02 ELSE 0 END) AS DEC(10, 3)) IST,
CAST( (SUBSTR(DIGITS(T01.STMOAN), 1, 4) || SUBSTR(DIGITS(T 01.STMOAN), 5, 2) || '01') AS CHAR(8) CCSID 273) AS DATUM,
SUBSTR(DI GITS(T01.STMOAN), 1, 4) AS JAHR,
SUBSTR(DIGITS(T01.STMOAN), 5, 2) AS MONAT
FROM STA01PF T01,ADR01PF T02
WHERE STFA = 1
AND STFAKT = 1
AND STMOAN > 201300
AND STVDST = 88
AND STSAAR '1'
AND ADABKZ = 0
AND ADKTO = STTENR
AND ADSTS = 0
AND ADFA = 1
GROUP BY STMOAN, STKTO, STTENR

Wenn ich jetzt hingehe und sage:
select ist from aruba_msvc/planz where knd = 19066
blitzt kurz auf das ein Zugriffspfad erstellt wird, und die Daten sind 2 Sekunden später da.
Mache ich aber ein
select adkto, istumsatz(adkto) from adr01pf where adfa = 1and adabkz = 0 and adsa06 = 201
bricht er mir wie gehabt mit Laufzeitfehler ab.
Mein Ziel ist es diese gewonnen Daten in eine Temp-Tabelle zu schubsen um damit arbeiten zu können.

Welche Möglichkeiten habe ich wenn der blöden Kisten alles auf einmal zu viel ist. (wobei mich das persönlich entäuscht von einer Kiste die 1988 als Datenbankmaschine angepriesen wurde)

GG

[Fuerchau]

Wenn du einen Cursor erstellst obwohl du nur einen Satz benötigst, musst du auch alle Sätze (auch wenn es nur 1 wird) in einer Schleife verarbeiten (die Syntax weiß ich im Moment nicht).
Sonst bleibt der Cursor offen und kann im Wiederholungsfall nicht noch mal verwendet werden.
Der Return muss also hinter das End!
Alternativ geht auch ein "select ... into ....", dann gibt's keinen Cursor.

[BenderD]

... das for construct mit einem cursor ist eine Leseschleife über alle Sätze und kettet hier Werte in einem String aneinander.
Das Performanceproblem resultiert m.E. aus der in ihr wohnenenden Rekursion. Kannst Du mal die zu Grunde liegende Anforderung beschreiben, vielleicht ist es dann einfacher eine adäquate Lösung zu finden.

D*B

[KingofKning]

Hallo,
die Anforderung ist einen Datensatz zu erzeugen der für jeden Kunden alle Informationen enthält (Schnittstelle zu rp-trade).
In diesem konkreten Fall ist es so das ich hier aus einer Datei die Istumsätze des Kunden für die letzten 24 Monate erzeugen muss. (Ein Datensatz pro Monat)

Da es sein kann das der Kunde keine Umsätze hatte, lese ich alle Datensätze des Kunden in einen String ein, und hänge an diesen String 24 Felder mit 000,00 an um immer einen String mit definierter Länge und Aufbau zurück zu bekommen.

Wie Fuerchau schon richtig sagte könnte man das wohl auch mit einem select lösen. Ich hatte aber bei meinen Tests damit Syntax Probleme die ich nicht lösen konnte.

Diese UDF ist auch in Anlehnung an einen Vorschlag von Birgitta zu einem anderen Problem entstanden weil ich die Ausgliederung von Teilabfragen in UDFs sympathisch finde.

GG

[BenderD]

... das mit der Auslagerung von Teilabfragen in eine UDF ist frei nach Fred Feuerstein Dummfug von der Dummfugigsten Sorte, damit schickt man jede Query Engine in den tiefsten Wald.
Wenn es denn die Summe nicht tut, kann man das auch in subselects auflösen.
In etwa so:
select knd
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201301)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201302)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201303)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201304)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201305)
...
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201404)
from planz p1

geht sicher noch eleganter, indem man die festen Monate rausnimmt, aber für einen ertsen Test, ob das brummt, reicht das sicherlich aus. (Selbstredend braucht man dann einen Index über Kunde und Monat).
wenn Du dann die leeren noch raus und das ganze in einem string haben willst, packst Du die Stringeritis noch drumherum.

D*B

[KingofKning]

Danke für den Vorschlag, das heißt du bist kein freund von udfs?
Ich persönliche mag es eigentlich lieber modular zu arbeiten und möglichst viele Sachen auszulagern um den Hauptteil möglichst klein zu halten.
wann würdest du denn udfs stored procedures etc. Einsetzen?

[BenderD]

... modular ist OK; das Mittel der Wahl sind da aber Views. UDFs sind sinnvoll, wenn man zum Beispiel aus mehreren Feldern eines einzelnen Datensatzes einen neuen Inhalt ableitet, aber selbst da holt es einen häufig ein, dass man dann aus Performancegründen andere Wege gehen muss. Was stored Procedures angeht, da wird im AS400 Umfeld momentan viel Unfug propagiert (RLA Programme, die ein resultset zusammenbasteln und zurückgeben, was dann mit einem elementaren Select einfacher, besser und schneller gegangen wäre). Vieles von diesem Kram wird auch vom IBM Marketing empfohlen weil (nicht obwohl!!!) es nicht SQL Standard konform ist.

D*B,

[KingofKning]

Dieses UDF ist ja ein Teil einer View die jetzt schon ein 30 Zeiler ist, da die Schnittstelle extrem viele Daten benötigt.
Ich werde das heute mal ausprobieren, melde mich dann mit dem Ergebnis zurück.

GG

[BenderD]

... die Crux mit den UDFs ist, dass der Optimizer nicht sieht, was innerhalb der UDF passiert; das ist sowas wie eine fixe, nicht (genauer gesagt: selten) auflösbare Klammerung.
Schwierig für den Optimizer ist auch die Art des Concat, das ist auch wieder eine starre Klammerung. Letztlich werden dann Abfragen rekursiv ausgeführt, was insbesondere mit dem Aufbau von temporären Indexen tödlich werden kann.
Aus Deiner UDF könnte man auch eine View machen und die dann in die übergeordnete View einbinden, dann kommt der Optimizer bei der Optimierung dran und kann das in seinen Access Plan einbeziehen.

D*B

[KingofKning]

Wenn du einen Cursor erstellst obwohl du nur einen Satz benötigst, musst du auch alle Sätze (auch wenn es nur 1 wird) in einer Schleife verarbeiten (die Syntax weiß ich im Moment nicht).Sonst bleibt der Cursor offen und kann im Wiederholungsfall nicht noch mal verwendet werden.Der Return muss also hinter das End!Alternativ geht auch ein "select ... into ....", dann gibt's keinen Cursor.

Das End ist da schon richtig. Das Return steht hinter dem "end for"Irgendwie verhuddelt mein Firefox auf der Arbeit die Text und nimmt alle Leerzeichen etc. raus. Zuhause der Firefox läßt alles so wie es ist. Ich bin noch nicht hinter dieses nervige Geheimnis gekommen. Evt versuche ich es mal mit dem IE obwohl ich den nicht mag..GG

[KingofKning]

... das mit der Auslagerung von Teilabfragen in eine UDF ist frei nach Fred Feuerstein Dummfug von der Dummfugigsten Sorte, damit schickt man jede Query Engine in den tiefsten Wald.
Wenn es denn die Summe nicht tut, kann man das auch in subselects auflösen.
In etwa so:
select knd
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201301)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201302)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201303)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201304)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201305)
...
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201404)
from planz p1

geht sicher noch eleganter, indem man die festen Monate rausnimmt, aber für einen ertsen Test, ob das brummt, reicht das sicherlich aus. (Selbstredend braucht man dann einen Index über Kunde und Monat).
wenn Du dann die leeren noch raus und das ganze in einem string haben willst, packst Du die Stringeritis noch drumherum.

D*B

Hallo, ich wollte das heute mal umsetzen, bekomme aber einen:

Unterbrechungspunkt unüberwachter Nachrichten anzeigen

Anweisung/Instruktion . . . . . . . . . : /0533
Programm . . . . . . . . . . . . . . . : QDBGETM
Rekursionsebene . . . . . . . . . . . . : 1

Funktionsfehler X'1716' in Maschineninstruktion. Interne Speicherauszugs-ID

zurück.
Das Statement lautet:
Aktuelle Verbindung besteht zur relationalen Datenbank S65FFEB1.
select knd
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201301)
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201302)
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201303)
from planz p2, adr01pf p1

Im Dump kann ich nichts erkennen.

SPEICHERAUSZUG FÜR NICHT-ÜBERWACHTE DURCHBRUCHNACHRICHT
.NACHRICHTEN-ID MCH3203
.NACHRICHTENDATEI- QCPFMSG BIBLIOTHEK- *LIBL
.WERTIGKEIT- 60
.NACHRICHTENART- 0F
.SENDE-
..PROGRAMM- #dbreseq
BIBLIOTHEK-
..INSTRUKTION- 001D40
.EMPFANGS-
..PROGRAMM- QDBGETM BIBLIOTHEK- QSYS
..INSTRUKTION- 0533
.NACHRICHT-
Funktionsfehler X'1716' in Maschineninstruktion. Interne Speicherauszugs-ID .
.NACHRICHTENDATEN
000000 99D4A028 DCD1F000 1716 *rMµ }J0 *
.NR. NACHRICHTENDATENFELD 01
000000 F1F2F6F0 F5F1F4F1 F0F4F2F5 F2 *1260514104252 *

Was mache ich falsch?

GG

BTW. Mit IE sieht der Text so aus wie ich ihn geschrieben habe.

[Fuerchau]

Sowas deutet eher auf fehlende PTF's hin.