Es gibt Momente, da liebe ich meinen Job. Mittwoch war einer dieser Tage. Alles was ich wollte, war per Stored Procedure ein paar Zufallsdaten innerhalb der Datenbank erzeugen. Das Thema Zufallsdaten scheiterte an vielen Dingen. Heute berichte ich nur über RANDOM()/RAND(), NOW() und TIMESTAMP in MaxDB. Mehr halten meine Nerven nicht aus.
Inhalt
- RAND()/RANDOM() per Stored Function simulieren
- NOW(), TIMESTAMP() und TIMESTAMP
- Variablen vs. CURSORS in MaxDB
- AK Catalog information not found
- English language version
RAND()/RANDOM() per Stored Function simulieren
MaxDB stellt keine Funktion bereit, um Zufallszahlen zu erzeugen. RAND() oder RANDOM() existieren nicht. Offensichtlich konnte man sich bei der Implementierung niemand vorstellen, daß RAND()/RANDOM() und Verwandte dazu benutzt werden können, um Tabellen mit Zufallsdaten zu befüllen oder jeden Tag eine andere, zufällige Auswahl von Tagessprüchen zu präsentieren, ohne hierfür eine Programmiersprache zu bemühen.
Eine Programmiersprache wollte ich nicht bemühen, um Zufallsdaten zu erzeugen. Also mußte ich RAND()/RANDOM() in MaxDB per benutzerdefinierter Funktion mit CREATE FUNCTION RANDOM() simulieren. Mit den Zufallszahlen kann ich dann auch zufällige Strings von zufälliger Länge erzeugen.
So machte ich mich schlau wie Zufallszahlen erzeugt werden. Naja ich versuchte es, und am Ende fand ich etwas über Lineare Kongruenzgeneratoren. Hinter dem Wortungetüm verbirgt sich eine einfache Rechenanweisung die Zahlenreihenfolgen erzeugt, deren Werte “zufällig” erscheinen. Da sich die Werte in einer solchen Zahlenreihenfolge, in einem solchen Generator irgendwann wiederholen, wählt man Generatoren mit einer sehr großen Periode. Im Normalfall wird der Generator einmal mit einem Wert initialisiert. Anschließend berechnet man aus diesem den Folgewert, erzeugt aus dem Folgewert einen Folge-Folgewert und so weiter und so fort.
Ich wollte jedoch nicht die Folgewerte speichern, weshalb ich kurzerhand immer wieder neue initiale Werte verwendet habe. Das mag sich negativ auf die Wertestreung und “Zufälligkeit” auswirken, aber ich wollte jetzt endlich weiterkommen. Doch welchen “zufälligen” Anfangswert kann eine Datenbank liefern? Eine Datenbank liefert kaum eine zufällige, physikalische Größen außer der Zeit. Diese wollte ich dann auch verwenden in Form von Mikrosekunden, die der MySQL Server bis heute leider immer noch nicht zur Verfüngung stellt. Aber MaxDB kann das. Es blieb also nur die Frage, wie man in MaxDB an einen Timestamp kommt.
NOW(), TIMESTAMP() und TIMESTAMP
Ich wußte, daß es DEFAULT TIMESTAMP gibt. Ich zitiere aus dem Handbuch.
Specifications of Values (extended_value_spec) Locate the document in the library structure Values are specified (extended_value_spec) using one of the keywords DEFAULT or STAMP or by specifying the values (value spec: Literals, parameter specifications, key words and so on). Syntax <extended_value_spec> ::= DEFAULT | STAMP | <value_spec> </value_spec><value_spec> ::= <literal> | <parameter_spec> | NULL | USER | USERGROUP | SYSDBA | CURRENT_SCHEMA | UID | [<schema_name>.]<sequence_name>.NEXTVAL | [<schema_name>.]<sequence_name>.CURRVAL | <table_name>.CURRVAL | DATE | TIME | TIMESTAMP | UTCDATE | TIMEZONE | UTCDIFF | TRUE | FALSE | TRANSACTION Explanation DEFAULT DEFAULT identifies the default value for the column in a CREATE TABLE statement or ALTER TABLE statement. If one of these values is not defined, DEFAULT cannot be used to specify values. The DEFAULT keyword can be used in the following SQL statements: INSERT statement, UPDATE statement The DEFAULT keyword can be used in a DEFAULT predicate.
Und ich wußte, daß es eine Funktion TIMESTAMP() gibt. Und natürlich gibt es auch noch einen Datentyp TIMESTAMP, aber der liefer mir ja keinen Wert, den ich in einer benutzerdefinierten Funktion hätte benutzen können.
TIMESTAMP(a,b) Locate the document in the library structure TIMESTAMP(a,b) is a function (extraction) that calculates from the expressions a and b a time stamp value comprising a date value, time value, and 0 microseconds. Result of TIMESTAMP(a,b) Function TIMESTAMP(a) a must be a time stamp value or an alphanumeric value that matches the current time stamp format The result is this time stamp value. TIMESTAMP(a,b) a must be a date value and b a time value (or alphanumeric value that matches the current format for date and time values). The result is a time stamp value calculated from a date value, time value, and 0 microseconds. a or b is the NULL value NULL Value
Hmm, TIMESTAMP taucht nur in Verbindung mit DEFAULT im Handbuch auf und TIMESTAMP() ist nur in Verbindung mit Parametern beschrieben. Wie komme ich denn jetzt an einen Timestamp in einer benutzerdefinierten Funktion ohne einen Datensatz in einer Tabelle einzufügen?
Irgendwann probierte ich NOW() aus, wie von ott vorgeschlagen. NOW() ist nicht in der Liste der Datenbankfunktionen von MaxDB enthalten. Es steht dort nicht, weil es nur im Rahmen des ODBC-/SQLDBC-Interface implementiert ist. Es ist mir schleierhaft wieso man diese Funktion nicht auf dem Level des Kerns implementiert hat. Aber mir war es gleich, ich benutzte das SQL Studio, welches (wie sqlcli) per ODBC mit MaxDB kommuniziert. Meine Implementierung von RAND()/RANDOM() war jetzt endlich möglich unter Benutzung von NOW() und einer Stored Function.
CREATE FUNCTION SHOP.RANDOM RETURNS FIXED(38, 37) AS
VAR randval FIXED(38, 37);
DECLARE randresult CURSOR FOR
SELECT ((397204094 * MICROSECOND(NOW())) MOD 2147483647) / 2147483646
FROM DBADMIN.DUAL;
FETCH FIRST randresult INTO :randval;
CLOSE randresult;
RETURN randval;
Moooooment – wieso funktioniert das? Wenn NOW() eine ODBC-/SQLDBC-Funktion ist, wieso kann man es in einer benutzerdefinierten Funktion verwenden? Eine benutzerdefinierte Funktion (Stored Function) wird innerhalb der Datenbank gespeichert und ausgeführt. An der Ausführung des Funktionskörpers ist kein Client beteiligt. Nun, da sind ja noch die symbolischen Konstanten von MaxDB, die im Handbuch gekonnt unter dem [E]BNF-Syntaxbaustein extended_value_spec versteckt werden. NOW() läßt sich in TIMESTAMP übersetzen und das steht zur Verfügung.
SELECT
NULL,
USER,
USERGROUP,
SYSDBA,
CURRENT_SCHEMA,
UID,
DATE,
TIME,
TIMESTAMP,
UTCDATE,
TIMEZONE,
UTCDIFF,
TRUE,
FALSE,
TRANSACTION
FROM
DUAL
Ich gebe ja gerne zu, daß ich das Handbuch nicht verstanden habe und das Feature dokumentiert ist. Aber ich fürchte, ich bin nicht anders als der Durschnittsbenutzer. Und vielleicht hat der Durschnittsbenutzer ähnliche Probleme wie ich.
Kleiner Nachtrag vom 23.11.: NOW() wird bei der semantischen Prüfung akzeptiert, weil es eine ODBC-Funktion ist. Im Katalog der Datenbank wird die angelegte Funktion mitsamt dem NOW() Aufruf so gespeichert, wie es definiert wurde. Bei der ersten Ausführung der Funktion wird NOW() gewandelt und das Compilat gecacht.
Variablen vs. CURSORS in MaxDB
Doch weiter im Text. Ich hatte mich jetzt bis zu dem Punkt durchgekämpft bei dem ich für jeden Aufruf eines SELECT eine “Zufallszahl” erhielt. Im obigen Beispiel habe ich einen Cursor verwendet, um die Zufallszahl abzugreifen. Man kann jedoch alternativ auch SELECT ... INTO ... FROM verwenden.
DROP FUNCTION RANDOM
//
CREATE FUNCTION RANDOM RETURNS FIXED(38,37) AS
VAR randval FIXED(38,37);
SELECT ((134775818 * MICROSECOND(NOW()) + 387) MOD 231) / 230
INTO :randval
FROM DBADMIN.DUAL;
RETURN randval;
//
SELECT NULL, RANDOM() FROM DUAL
AK Catalog information not found
Damit wäre mein Tag fast gerettet gewesen, wenn das Logfile des SQL Studio nicht gelegentlich folgendes gezeigt hätte.
++++ Execute +++++++++++++++++++++++++++++++
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
DROP FUNCTION RANDOM
Statement successfully executed. No Result
Execution Time: 20:36:30.965 - 20:36:30.965 (00.000 sec)
++++ Execute +++++++++++++++++++++++++++++++
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
CREATE FUNCTION RANDOM RETURNS FIXED(38,37) AS
VAR randval FIXED(38,37);
SELECT
((134775818 * MICROSECOND(NOW()) + 387) MOD 231) / 230
INTO :randval
FROM DBADMIN.DUAL;
RETURN randval;
Statement successfully executed. No Result
Execution Time: 20:36:30.995 - 20:36:31.004 (00.009 sec)
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;-9205 POS(1) System error: AK Catalog information not found:45000000000004F0001800
SELECT NULL, RANDOM() FROM DUAL
Was soll ich sagen? Nach der Ursache wird gerade gesucht. Bitte keine Bugreports einschicken. Ich werde bei Gegelenheit vom Ausgang berichten.
Nachtrag: http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1136664 beschreibt den -9205 Fehler und schlägt einen Workaround vor. Gelöst in 7.6.00.15.
English language version?
If you’re looking for an english language summary of this blog entry, leave a comment.
Pingback: Internet Super Hero » Blog Archive » MaxDB: Working around the missing RANDOM function