In SQL UDF auf gleiche Tabelle in verschiedenen Libs zugreifen?

[Nils_V]

Hallo,

ich habe eine SQL UDF geschrieben, in der ich in Abhängigkeit von einem Parameter auf Tabellen mit gleichem Namen und Aufbau in verschiedenen Libs zugreifen will. Ich möchte nicht von der *LIBL abhängig sein.
Ich habe versucht, sowohl mit SET SCHEMA als auch mit SET PATH als auch mit beiden den Zugriffspfad auf die Tabelle zu beeinflussen - leider ohne Erfolg.
Ausserdem habe ich das Problem, dass ich bisweilen während der Tests offenbar die UDF im Cache aufrufe, und die Änderung gar nicht wirksam ist. Ich ändere dann die SELECT Liste des Test-Statements, um den Optimizer auszutricksen. Gibts ne Möglichkeit, das Caching auszuschalten?

Hier der Code:

create function mylib/getHist_WP (
MyLib varchar (20),
KennNummer varchar (7)
)

returns varchar (40)
language sql
reads sql data
no external action
returns null on null input

begin
declare ResultHis varchar (30);

-- PATH und SCHEMA setzen
set path = MyLib;
set schema = MyLib;

select Feld
into ResultHis
from MyTable
where KennNr = KennNummer;

-- Alte Werte wieder einstellen
set path = *LIBL;
set schema = default;

return ResultHis;
end


Danke vorab für Hinweise!

Nils

[Fuerchau]

Gecached wird da eigentlich nichts.
Nach dem DROP FUNCTION musst du nur ggf. eine RCLACTGRP durchführen.

Um variabel auf Tabellen zuzugreifen musst du einen OVRDBF vor dem SELECT aufrufen (kann per CALL QCMDEXC gemacht werden), DLTOVR nicht vergessen.

Allerdings wird die Performance hier stark nachlassen !!!!

[Nils_V]

Danke für die Hinweise!

Kannst Du mir mit der Syntax für das CALL QCMDEXC innerhalb der UDF weiterhelfen?

Schade um die Performance-Einbusse... schon ärgerlich, dass die LIB nicht anders parametrierbar ist!
(Eigentlich möchte ich das SQL Statement dynamisch zusammensetzen, inklusive der FROM clause; das scheitert aber daran, das ich das Ergebnis in eine Hostvariable schreiben muss, denn ich will es ja zurückgeben).

Nils

[Fuerchau]

The CURRENT PATH special register is used to resolve user-defined distinct types and functions in
dynamic SQL statements.

Statische SQL's werden also nicht berücksichtigt.

Der OVRDBF muss in einer Zeichen-Hostvariablen zusammengebaut werden, der 2. Paramter im Format DEC(15, 5) gibt die Länge der Hostvariaben an:

declare QCMD char(256);
declare QCMDL dec(15, 5);

set QCMD = 'OVRDBF FILE(MYFILE) TOFILE(' concat MYLIB concat '/MYFILE) OVRSCOPE(*JOB)';
set QCMDL = 256;
call QCMDEXC (QCMD, QCMDL);

select ... from MYFILE;

set QCMD = 'DLTOVR MYFILE';
call QCMDEXC (QCMD, QCMDL);

Das Problem mit dem OVRDBF kann sein, dass ggf. der Cursor des Select's geöffnet bleibt.

Ich denke am Besten funktioniert das mit einem CASE-Verteiler, der abhängig von der LIB qualifizierte Select's abgibt.
Bei einer neuen Lib (die gibts ja nicht so häufig) muss die Funktion eben ergänzt werden.

[dholtmann]

Wenn du das SQL sowieso dynamisch zusammenstellst, kannst du dann nicht
"select * from meinelib.meinedatei" schreiben,
statt
"select * from meinedatei"?
Oder irre ich?

[dschroeder]

Wenn du das SQL sowieso dynamisch zusammenstellst, kannst du dann nicht
"select * from meinelib.meinedatei" schreiben,
statt
"select * from meinedatei"?
Oder irre ich?

Genau, das würde ich auch vorschlagen. Du kannst dann zwar das Ergebnis nicht direkt in eine Hostvariable einlesen, aber du kannst es per Cursor auslesen.
Dieter

NACHTRAG: Ich habe gerade bemerkt, dass du das vollständig in SQL machen möchtest. Ich weiß gar nicht, ob man das Statement da dynamisch zusammenbauen kann. Ich hatte angenommen, dass du das mit embedded SQL in RPG machen möchtest. Also kannst du meinen Vorschlag wahrscheinlich vergessen.

[Fuerchau]

Auch mit dynamischem SQL kann ich den Fetch in Hostvariablen durchführen.
Ich kann nur die Parameter nicht als Hostvariable angeben.
Der Fetch prüft zur Compilezeit nicht, ob die Variablen zum Select passen. Dies muss nur zur Laufzeit stimmen.
Allerdings empfielt sich bei dynamischem SQL kein "Select *", da sich die Ergebnisliste ja ändern kann.
Beim embedded SQL wird der "Select *" auf die Spalten zur Compilezeit bechränkt.

[B.Hauser]

1. In der SQL Programmierung kann man wie beim embedded SQL dynamisches SQL verwenden.
2. Was in der SQL-Programmierung nicht geht, ist Datenstrukturen zu verwenden. Wenn also SELECT * FROM verwendet wird (statisch oder dynamisch) spielt dabei keine Rollen, müssen die Spalten einzeln in lokale (Host-)Variablen ausgegeben werden.
3. Wenn der Datei-Aufbau identisch ist, sich lediglich die Bibliothek ändert, sollte die Ausgabe in immer die gleichen Variablen kein Problem sein. Besser wäre allerdings gezielt die benötigten Spalten auszuwählen.
4. Wenn tatsächlich nur eine Variable und eine Zeile ausgelesen werden soll, kann das in der SQL-Programmierung sehr elegant mit dynamischem SQL gelöst werden:

create function HSCOMMON10/getHist_WP (MyLib      varchar (20),
                                       KennNummer varchar (7))

       returns varchar (40)
       language sql
       reads sql data
       no external action
       returns null on null input

       Set Option DbgView = *Source

Begin
   Declare ResultHis varchar (30) Default '';
   Declare CmdString VarChar(256) Default '';

   Set CmdString = 'Values (select Feld From ' concat Trim(MyLib) concat
                                                      '.MyTable ' concat
                              ' where KennNr = ?' concat
                              ' Fetch First Row Only) into ?';

   Prepare DynSQL From CmdString;
   Execute DynSQL Using KennNummer, ResultHis;

   Return ResultHis;
End

Seit den letzten Neuerungen kann man das dynamische SQL-Statement auch direkt beim PREPARE zusammenknüppeln, d.h. die extra Variable ist nicht mehr notwendig

• SQLPATH wird verwendet um unqualifiziert angegebene Stored Procedures, User Defined Functions und User Defined Table Functions zur Laufzeit aufzufinden, wenn SQL-Naming verwendet wird.
• CURRENT_SCHEMA (oder DEFAULT SCHEMA) wird für ünqualifiziert angegebene Tabellen oder Views verwendet unabhängig davon, ob SQL- oder System-Naming verwendet wird.
  Wird bei System-Naming das CURRENT_SCHEMA angegeben wird die Bibliotheksliste ingoriert.
  Wird SQL-Naming verwendet, wird zur Compile-Zeit für statisches SQL die Bibliothek über das CURRENT SCHEMA ermittelt und hart in das Objekt übernommen (alternativ kann die DEFAULT Bibliohtek auch über die Option DFTRDBCOL im SET OPTION Statement fix gesetzt werden.
  Für dynamisches SQL kann beim SQL-Naming über die Option DYNDFTCOL im SET OPTION statement festgelegt werden, ob zur Laufzeit das CURRENT SCHEMA ermittelt wird, oder ob das gleiche Schema wie für das statische SQL verwendet wird.
  Beim System-Naming wird die Bibliotheksliste abgegriffen.

Birgitta

[B.Hauser]

Ich kann nur die Parameter nicht als Hostvariable angeben.

Und warum nicht?

Der Fetch prüft zur Compilezeit nicht, ob die Variablen zum Select passen. Dies muss nur zur Laufzeit stimmen.
Allerdings empfielt sich bei dynamischem SQL kein "Select *", da sich die Ergebnisliste ja ändern kann.

Allerdings nur beim dynamischen SQL, da zu diesem Zeitpunkt ja nicht bekannt ist wie das Statement zur Laufzeit aussehen wird!
Beim Statischen erfolgt eine Prüfung der Host-Variablen.

Beim embedded SQL wird der "Select *" auf die Spalten zur Compilezeit bechränkt.

Jetzt reden wir wieder von statischem SQL, da zur Laufzeit das Statement bekannt ist und damit auch auf welche Tabelle(n), View(s) zugegriffen wird.
Beim dynamischen SQL ist das SQL-Statement zur Compile-Zeit ja noch nicht bekannt!

Diese Regeln gelten unabhängig ob embedded SQL oder reine SQL-Programmierung verwendet wird.

Birgitta

[Fuerchau]

Dann zeige doch bitte mal ein Beispiel, wie ich beim dynamischen SQL (ohne using SQLDA) Parameter verwenden kann.
MyStmt = "Select f1, ... from MyTable where K1=?";
Wie und wann gebe ich für das "?" nun die Hostvariable an?
Dies würde SQL schon vereinfachen, ins besonders bei Zeichenketten mit Hochkommainhalten.

[B.Hauser]

Dann zeige doch bitte mal ein Beispiel, wie ich beim dynamischen SQL (ohne using SQLDA) Parameter verwenden kann.
MyStmt = "Select f1, ... from MyTable where K1=?";
Wie und wann gebe ich für das "?" nun die Hostvariable an?

Die Host-Variablen werden genau wie in meinem Beispiel s.o.! entweder beim EXECUTE oder beim OPEN über USING angegeben.

[Fuerchau]

Wer lesen kann...
Da habe ich noch mal in die Doku von V5R4 geschaut und siehe da, den Using mit Variable statt SQLDA habe ich immer überlesen, tsts...