Testdatengenerator 1/24 Realitätsnahe Testdaten generieren Anwendungen besser testen Testdatengenerator V1 Dokumentation Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 2/24 Inhalt Inhalt ...................................................................................................................................................... 2 Testdatengenerator - Dokumention ....................................................................................................... 3 Die Arbeitsumgebung des Generators ........................................................................................... 4 Die Bestandteile des Generators ................................................................................................... 4 Die ausgelieferten Skripte und Daten ............................................................................................ 6 Die Installation der Skripte und Daten ............................................................................................ 6 Die Vorgehensweise bei der Erstellung von Testdaten ................................................................. 8 Erstellen von Werteliste-Tabellen aus den operativen Daten ...................................................... 16 Mit ausgelieferte Werteliste-Tabellen ........................................................................................... 17 Verwendung des Testdatengenerators mit OWB (beliebige Version) ......................................... 18 Plan für Testdatengenerator Version 2 ........................................................................................ 20 Anhang ............................................................................................................................................. 21 Syntax-Beschreibung der Basisroutinen ...................................................................................... 22 Version 1, Stand Januar 2011 Der hier dokumentierte Testdatengenerator kann kostenfrei genutzt werden. Daraus ergeben sich keine Ansprüche gegenüber dem Ersteller der Routinen. Der Code der Routinen wird mit ausgeliefert und kann je nach Bedarf angepasst werden. Kommentare, Ergänzungen und Fehlerkorrekturen: Bitte an: Alfred Schlaucher, Oracle Deutschland 040/89091-132 [email protected] Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 3/24 Testdatengenerator - Dokumentation Der hier vorgestellte Testdatengenerator hat die Aufgabe beliebig strukturierte und anonymisierte Daten in beliebiger Menge und Form für Testzwecken in Datenbanktabellen bereitzustellen. Der Generator unterstützt Entwickler auch die nicht naheliegenden Werte und Wertekombinationen für neu entwickelte Anwendungen zu testen: Der Testdatengenerator liefert beliebige Kombinationen in zufälliger Form, so dass auch diejenigen Zeichen und Werte vorhanden sind, an die man nicht denkt. Nicht immer stehen Echtdaten für Testzwecke zur Verfügung. Der Testdatengenerator erstellt Daten mit einer zu den Echtdaten analogen Struktur, aber dennoch zufällig, so dass Bezüge zu den Echtdaten nicht hergestellt werden können. Data Warehouse – Systeme reizen häufig Systemgrenzen aus, sei es bei der Performance oder der Speicherkapazität. Der Testdatengenerator produziert Daten beliebiger Menge und dies sehr realitätsnah genau in der Struktur, in der auch das Warehouse aufgebaut ist. Das Testen von Anwendungen muss nachvollziehbar sein. D. h. eine getestete Anwendung sollte gemeinsam mit den Testdaten dokumentiert und archiviert sein. Das ist bei sehr großen Data Warehouse-Datenbeständen aber selten möglich. Stattdessen ist es sinnvoll TestdatenProfile vorzuhalten, über die ein Testdaten-Set wieder hergestellt werden kann. Die Kombination eines Datenbank - nahen ETL - Werkzeuges mit diesem Testdatengenerator, der aus offen zugänglichen Oracle PL/SQL -Routinen besteht, ist besonders geeignet, die Produktivität des Entwicklers zu steigern. Datenqualität und Testdaten Gerade Data Warehouse Systeme leiden oft unter der schlechten Datenqualität der zuliefernden Vorsysteme. Das führt dazu, dass ein erheblicher Anteil der Entwicklungskosten für neue WarehouseSysteme, aber auch für deren Weiterentwicklung, in das Testen von Prüfroutinen zur Vermeidung von Datenqualitätsfehlern gesteckt wird. Das Testen der Warehouse-Routinen und das ständige Bemühen um bessere Qualität der Warehouse-Daten gehören zusammen. Ein vorgelagerter Schritt zur Erstellung der Testdaten ist die Analyse zur gewünschten Struktur der Testdaten. Diese Information erhält man aus dem Profiling der Echtdaten der operativen Systeme. Die notwendigen Wertebereiche, Feldtypen, Zeichenkombinationen, Mengen, die Proportionen der Werte untereinander oder eine notwendige Eindeutigkeit lassen sich nur über Profiling-Verfahren mit der nötigen Sicherheit feststellen. Zusammenhang zwischen Profiling und Testdatenerzeugung Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 4/24 Die Arbeitsumgebung des Generators Der Testdatengenerator ist mit Oracle PL/SQL erstellt und erzeugt über Insert-Statements Oracle Datenbanktabellen. In der jetzigen Form werden die Routinen über SQLPlus aufgerufen. die Steuerparameter stehen in Profiltabellen. Der SQLDeveloper hilft bei der Definition der Profiltabellen. Es ist auch möglich die Sourcen des Testdatengenerators in OWB zu laden. Weiter unten finden Sie hierzu ein Beispiel. Die Bestandteile des Generators Die wichtigsten Bestandteile neben der Generatorprozedur sind Basisroutinen und Steuertabellen. Basisroutinen: Dies sind eine Reihe einfacher Funktionen, die Parameter-gesteuert einzelne Zufallswerte zurückliefern. Für jeden Wertetyp (numerisch, alphanumerisch, Buchstabenkombinationen, Wörter usw.) gibt es eine spezielle Funktion. Steuertabellen: Über Steuertabellen definiert man die Form einer gewünschten Zieltabelle. Steuertabellen haben eine vorgegebene Struktur. Über diese Struktur legt man die gewünschten Spalten, die Werteinhalte dieser Spalten und auch die Proportionen dieser Spalten fest. Im Detail: Basisroutinen: Dies sind eine Reihe einfacher Funktionen, die Parameter-gesteuert einzelne Zufallswerte zurückliefern. Für jeden Wertetyp (numerisch, alphanumerisch, Buchstabenkombinationen, Wörter usw.) gibt es eine spezielle Funktion. Diese Funktionen nutzt man entweder direkt in selbst geschrieben SQL-Statements oder lässt sie von der Testdatengenerator-Prozedur TG verwenden. Es gibt: Ran_m_n(m,n) numerische Werte (von - bis) Ran_Values(‘xy:?z‘,n) Anzahl n Zeichen von xy:?z… Ran_Words(‘/‘,‘Wort1/Wort2/Wort3‘) Wörter Ran_Dates(m,n) Datum von (sysdate - m) bis (sysdate - n) Ran_ABC123(n) Anzahl n Buchstaben+Zahlen Ran_ABC_Mixed(n) Anzahl n Groß-/Kleinbuchstaben Ran_ABC_UPPER(n) Anzahl n Großbuchstaben Ran_list(list_name) Einzelwert aus Liste Ran_Digits(n) Anzahl n Zahlenwerte Profiltabellen Über Profiltabellen definiert man die Form einer gewünschten Zieltabelle. Profiltabellen haben eine vorgegebene Struktur. Über diese Struktur legt man die gewünschten Spalten, die Werteinhalte dieser Spalten und auch die Proportionen dieser Spalten für die gewünschte Zieltabelle fest. WertelisteTabellen Tabellen mit Wertelisten, z. B, Ortsnamen, Artikelnamen, Titel usw.. Das sind Hilfstabellen, die man selbst erstellt. Über sie kann man dem Testdatengenerator eine Auswahlmenge mit echten Werten für die Generierung von Spaltenwerten anbieten. Man erstellt sie entweder über External Tables aus Textdateien oder aus den bestehenden Spalten von operativen Tabellen mit Hilfe einer unten beschriebenen Funktion. Eine Generierungsroutine (TG) Diese Routine mit dem Namen TG liest eine Steuertabelle ein und erstellt daraus ein DDL-Skript, über das dann eine Zieltabelle erzeugt werden kann. Hilfsroutinen Das sind Table Functions, die im Verlauf der Arbeit mit dem Testdatengenerator sehr hilfreich sein können: TF_LT Damit erstellt man Wertelisten-Tabellen aus operativen Daten. TF_LFD_NR Diese Table Function kann als Tabellenersatz bei der Erstellung Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 5/24 von neuen Sätzen genutzt werden. Komponentenübersicht Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 6/24 Die ausgelieferten Skripte und Daten Ausgeliefert werden eine Reihe von Skripten und ein Datenbank-Dump (Export-File). SQL-Skripte Basisroutinen Hilfsroutinen für Testdatengenerator den RAN_ABC123_Mixed RAN_ABC_MIXED RAN_ABC_UPPER RAN_DATE_N RAN_DIGITS RAN_LIST RAN_M_N RAN_VALUES RAN_WORDS Diese Funktionen können auch unabhängig von dem Testdatengenerator (TG) genutzt werden RAN_DATES RAN_LISTS RAN_NUMBERS Diese Funktionen werden nur dem Testdatengenerator (TG) genutzt. Testdatengenerator TG Table Functions TF_LFD_NR TF_LT Hilfsroutinen Dump mit Wertelisten-Tabellen Wertelisten.DMP (Beschreibung siehe weiter unten). Die Installation der Skripte und Daten Erstellen Sie sich ein separates Datenbankschema mit den Standard-Rechten in Ihrer OracleUmgebung. Damit separieren Sie die neuen Objekte von Ihren bestehenden Daten. Z. B. CREATE USER TD IDENTIFIED BY TD. Connecten Sie sich in dieses Schema (z. B. mit SQLPlus) und laden Sie die Skripte entsprechend Ihrer Präfix-Nummerierung in das Schema: (Die Reihenfolge ist wichtig, weil einige Routinen andere voraussetzen) SQL> START [Laufwerk/Pfad]01_basis SQL> START [Laufwerk/Pfad]02_Zwischen SQL> START [Laufwerk/Pfad]03_tf SQL> START [Laufwerk/Pfad]04_rest Der Ladevorgang sollte ohne Fehlermeldung durchführbar sein. Die Wertelisten-Tabellen importieren Sie als Dump in das gleiche Schema. Auf Betriebssystem-Ebene: IMP TD/TD Die Daten sind aus einem Schema mit dem Namen TD exportiert worden. Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 7/24 Laden Sie die Daten entsprechend dieser Darstellung: C:\>imp td/td Import: Release 11.2.0.1.0 - Production on Wed Jan 5 11:19:43 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Import data only (yes/no): no > Import file: EXPDAT.DMP > g:\wertelisten Enter insert buffer size (minimum is 8192) 30720> Export file created by EXPORT:V11.02.00 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > Ignore create error due to object existence (yes/no): no > Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no > Username: td Enter table(T) or partition(T:P) names. Null list means all tables for user Enter table(T) or partition(T:P) name or . if done: . importing TD's objects into TD . importing TD's objects into TD . . importing table "LS_BUNDESLAND_LISTE" 13 rows imported . . importing table "LS_HAUPTSTAEDTE_LISTE" 203 rows imported . . importing table "LS_KREIS_LISTE" 342 rows imported . . importing table "LS_NACHNAMEN_LISTE" 100 rows imported . . importing table "LS_ORTE_LISTE" 12010 rows imported . . importing table "LS_STAATEN_LISTE" 207 rows imported . . importing table "LS_TOP500_FIRMEN_DE_LISTE" 500 rows imported . . importing table "LS_TIER_LISTE" 951 rows imported . . importing table "LS_US_STATE_LISTE" 52 rows imported Import terminated successfully without warnings. C:\> Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 8/24 Die Vorgehensweise bei der Erstellung von Testdaten 1. Die einfache Verwendung der Basisroutinen Die Basisfunktionen können auch als einzelne PL/SQL-Funktionen in SELECT-Statements benutzt werden. Sie sind überall dort einsetzbar, wo nur ein Rückgabe-Wert weiterverarbeitet werden kann. Die folgenden Beispiele zeigen die Verwendung: Anforderung Beispiel Es werden Zahlen von 500 - 999 benötigt. SQL> select ran_m_n(500,999) from dual; RAN_M_N(500,999) ---------------878 Es werden Zahlen mit 2 Nachkommastellen in dem Bereich von 10 bis 20 benötigt. SQL> select ran_m_n(10,20,2) from dual; RAN_M_N(10,20,2) ---------------19.12 Es wird ein Datum zwischen 1.1.1980 und 31.12.2010 gebraucht. (Annahme aktuelles Jahr ist 2011) SQL> select ran_date_n(31,1) from dual; RAN_DATE_ --------24-FEB-90 Es werden Werte aus einer Gruppe von Wörtern/Begriffen benötigt. SQL> select ran_words('/','Hund/Katze/Pferd/Esel/Vogel/') Tier from dual; TIER -------------------------------------------------------------------------Hund Gleiche Anforderung wie zuvor. Ein Wort soll jedoch mit einer Häufigkeit von 50% vorkommen. (Das Ergebnis wirkt erst, wenn die Abfrage mehrfach abgesetzt wird. Wenn z. B. 1000 Sätze einer Tabelle zu füllen sind, dann sollen in 500 Sätzen ein bestimmter Wert vorhanden sein.) SQL> select ran_words('/', 'Hund/ Hund/ Hund/ Hund/ Hund/Katze/Pferd/Esel/Vogel/') Tier from dual; Es wird ein 5-Zeichen-String benötigt. SQL> select ran_values(5,'[]}{&%$§²@ 123456789') Zeichen from Dual; ZEICHEN --------------------------------------------------------------------7&&]% Es sind Werte von folgendem Typ zu erzeugen: ABC-12345 SQL> Select ran_ABC_UPPER(3)||'-'||ran_digits(5) Wert from dual; TIER -------------------------------------------------------------------------Hund WERT ----------------------------------------------------------------STA-58679 Es sollen Zufallswerte aus einer bestehenden Tabelle heraus genutzt werden. Stand/letzte Änderung 08.04.2017 1. Aus der Tabellen-Spalte, die für die Werte in Frage kommt, muss eine Werteliste-Tabelle (siehe weiter unten, dazu gibt es eine Hilfsroutine) erstellt werden. 582616100 Testdatengenerator 9/24 2. SQL> select ran_list('LS_Orte_Liste') Ort from dual; ORT -----------------------------------------------------Pessin 2. Die Verwendung der Basisroutinen zum Schreiben von vielen Sätzen Die einzelnen Aufrufe aus dem Abschnitt zuvor, setzt man sinnvoll in einer Kombination ein. Beispiel: Es soll eine Tabelle mit folgender Struktur und gefüllt mit 1000 Sätzen erzeugt werden: Create Table TB_Tiere ( Tier_nr number, Tier_name varchar2(100), Tier_art varchar2(100), Geb_am date, Geb_Ort varchar2(100), MeldeNr varchar2(20)); Folgendes Statement erstellt und füllt die Tabellen SQL> Create table TB_Tiere as select Ran_M_N(1,10000) substr(Ran_ABC_Mixed(10),1,100) substr(ran_words('/','Hund/Katze/Pferd/Esel/Vogel/'),1,100) ran_date_n(10,1) substr(ran_list('LS_Orte_Liste'),1,100) substr(ran_ABC_UPPER(3)||'-'||ran_digits(5),1,100) from dual connect by level < 1001; Table created. Tier_nr, Tier_name, Tier_art, Geb_am, Geb_Ort, MeldeNr SQL> desc tb_tiere Name Null? Type ----------------------------------------- -------- ---------------------------TIER_NR NUMBER TIER_NAME VARCHAR2(100) TIER_ART VARCHAR2(100) GEB_AM DATE GEB_ORT VARCHAR2(100) MELDENR VARCHAR2(100) SQL> select count(*) from tb_tiere; COUNT(*) ---------1000 Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 10/24 Die Tabelle, die in der Datenbank entstanden ist sieht so aus: Die Ergebnistabelle Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 11/24 3. Effizenteres Erstellungsverfahren mit Table Function als „Trigger“ Bei der zuvor beschriebenen Lösung wurden die Testdaten über ein SQL mit der Form erstellt: CREATE TABLE zieltabelle AS SELECT RAN_function1, RAN_function2. ….. FROM DUAL CONNECT BY LEVEL < n Das ist eine sehr einfache und schnelle Schreibweise. Sie hat aber den Nachteil, dass sie aufgrund einer möglichen Hauptspeicherbegrenzung nicht in unbegrenzter Menge Sätze liefert. Bei wenigen Millionen Sätzen ist das Ende erreicht, weil die Verarbeitung komplett im Hauptspeicher stattfindet. Außerdem erhält man keinerlei nutzbare Information aus der DUAL-Dummy-Tabelle. Um dies zu umgehen, wird eine Table Function TF_LFD_NR(n) mit ausgeliefert. Table Functions haben die Eigenschaft, dass sie wie eine echte Tabelle Sätze an ein aufrufendes SQL-Select-Statement liefern. Die Anzahl der Sätze kann dabei unbegrenzt sein. Die Sätze werden in dem PIPE-Verfahren weitergereicht, d. h. die Verarbeitung erfolgt dann, wenn auch Ressourcen (z. B. Speicher) vorhanden ist. Das was sie liefert kann man durch Programmierung beeinflussen. Die Table Function TF_LFD_NR(n) liefert eine aufsteigend laufende Nummer. Das hat zudem den Vorteil, dass man diese Nummer z. B. zum Durchnummerieren der Testsätze nutzen kann, oder man baut damit einen eindeutigen Schlüssel (Unique Key) für die Testsätze auf. Die Verwendung sieht so aus: CREATE TABLE zieltabelle AS SELECT RAN_function1, RAN_function2. ….. FROM TABLE (TF_LFD_NR(n)) Als Parameter n gibt man der Table Function eine Zahl mit. Damit legt man die Menge der zu erzeugende Sätze der Zieltabelle fest. Zur Anschauung dient dieser Test in dem 20.000.000 Sätze in eine Zieltabelle geschrieben werden sollen. Die Lösung mit CONNECT BY bricht nach 16 Minuten ab, Der Test mit der Table Function läuft durch. (Der Test wurde auf einem Laptop mit 3GB Hauptspeicher durchgeführt). SQL> create table xyz as select 2 ran_M_n(1,10) A, 3 ran_M_n(1,10) B, 4 ran_M_n(1,10) C, 5 ran_M_n(1,10) D 6 FROM DUAL connect by level < 20000000 7 / FROM DUAL connect by level < 20000000 * ERROR at line 6: ORA-30009: Not enough memory for CONNECT BY operation Elapsed: 00:16:20.71 Stand/letzte Änderung 08.04.2017 SQL> create table abc as select 2 ran_M_n(1,10) A, 3 ran_M_n(1,10) B, 4 ran_M_n(1,10) C, 5 ran_M_n(1,10) D 6 FROM table (tf_lfd_nr(20000000)) 7 / Table created. Elapsed: 00:59:15.78 582616100 Testdatengenerator 12/24 4. Die Verwendung von Steuertabellen zur Erstellung von Profilen Will man bei der Erstellung von Testdaten systematisch vorgehen und die Art der Erstellung dokumentieren, so kann man Profiltabellen anlegen. Über Profiltabellen legt man fest: Den Namen einer zu erstellenden Zieltabelle Die Spaltenstruktur Die vorkommende Wertestruktur pro Column Die Proportionen von Werten innerhalb einer Spalte Eine Profiltabelle hat einen frei wählbaren Namen und immer eine feste Struktur: Diese Struktur muss passen, weil sich die verarbeitende Routine auf bestimmte Eintragungen verlässt. Festlegen der Spaltennamen und der Struktur der Spalten: Jede Zeile in der Profiltabelle beschreibt einen Wert für eine bestimmte Ziel-Column in der zu generierenden Zieltabelle. Sollen unterschiedliche Werte oder unterschiedliche Wertebereiche in einer Ziel-Column vorkommen, dann gibt es für diese Ziel-Column zwei oder mehr Eintragungen in der Profiltabelle. Festlegen von Namen und Anzahl Sätze der zu generierenden Tabelle: Es muss eine Eintragszeile in der Profiltabelle geben, über die der Name der zu generierenden Tabelle (Zieltabelle) und die Anzahl der Sätze festgelegt werden. In diesem einen Satz muss unter Typ der Wert „TAB“ stehen. Der Zieltabellenname wird unter Spalte erfasst. (Beispiel siehe unten). Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 13/24 Es bedeuten: Spaltenname Bedeutung Mögliche Werte Spalte Wenn in dem Feld TYP die Werte CHAR, DAT, NUM, LIST stehen, ist das der Name der Zielspalte. CHAR DAT NUM LIST TAB Gesamt_werte Wenn in dem Feld TYP der Wert TAB steht, ist das der Name der Zieltabelle. Wenn in dem Feld TYP die Werte CHAR, DAT, NUM, LIST stehen, kann das Feld ignoriert werden. Wenn in dem Feld TYP der Wert TAB steht, ist das die Anzahl der Sätze in der Zieltabelle Typ Laenge Wert Verteilung CHAR für feste Werte, die unter WERT benannt sind. - Ein beliebiger Zahlenwert 1-n CHAR DAT für Datumswert in einem vorgegebenen Bereich, wie er unter WERT benannt ist. DAT LIST für Werte, die aus einer Werteliste-Tabelle gelesen werden sollen. Der Name der Werteliste-Tabelle steht unter WERT. LIST NUM für Zahlen in einem vorgegebenen Bereich, wie er unter WERT benannt ist. Enthält die Länge der Ziel-Column in Byte Gibt Angaben über die jeweilige Ausprägung in der Ziel-Column. Eintrag ist abhängig von dem Eintrag unter Typ (siehe auch separate Spalte in dieser Darstellung) Gibt die relative Menge der vorkommenden Werte an. NUM Stand/letzte Änderung 08.04.2017 Korrespondierende Eintragungen in Feld WERT NULL Für jeden vorkommenden Wert gibt es einen konkreten Eintrag. Von - Bis Jahresangaben. Angabe in Anzahl Jahren vom Tagesdatum aus zurückgerechnet. Name der Werteliste-Tabelle Ein Zahlenbereich n-m - 582616100 Testdatengenerator 14/24 Ein Beispiel: Beispiel für eine Profiltabelle Die zu erstellende Tabelle wird den Namen T_30 haben. In der letzten Zeile steht unter TYP der Eintrag TAB. Damit wurde festgelegt, dass der Eintrag T_30 unter SPALTE der Tabellenname ist. Damit ist auch die Anzahl der Zeilen der neuen Zieltabelle klar: 20. Die neuen Tabelle wird die Columns Beruf Geb_Dat Name SP2 haben. In diesen Zeilen ist unter TYP kein Eintrag TAB zu finden, sondern nur die definierten Werte CHAR, DAT, LIST, NUM. Die Column-Namen ergeben sich aus den Eintragungen unter SPALTE. Für die Column „Beruf“ wird es genau die Werte geben: „Baecker“, „Mueller“, „Bauer“. Und zwar in den Proportionen 5, 4 und 7. Umgerechnet auf einen Prozentwert ergibt das 31% Anteile den Wert „Baecker“, 25% Anteile den Wert „Mueller“ und 44% Anteile den Wert „Bauer“. Sollen gleiche Mengenverhältnisse hergestellt werden, dann trägt man die Zahl 1 unter VERTEILUNG für alle Werte ein. Für die Column „Name“ wird ein Zufallswert aus der Werteliste-Tabelle „LAST_NAME“ gelesen. Die Eintragung „2“ unter Verteilung ergibt keinen Sinn. Sie wird ignoriert. Für die Column SP1 werden Zahlen aus den beiden Bereichen 1-10 und 100-200 generiert und zwar in dem Verhältnis von 1 zu 4. Ergebnis des Generierungslaufes Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 15/24 Der Aufruf des Testdatengenerators Um die Profiltabelle zu verwenden und um daraus eine Zieltabelle zu generieren, ist ein Aufruf der PL/SQL - Prozedur TG notwendig. Diese Prozedur wird über SQLPlus in dem jeweiligen Datenbankschema gestartet, in dem auch bereits alle anderen Funktionen installiert wurden. Die Prozedur erhält den Namen der Profiltabelle als Parameter übergeben. EXEC[UTE] TG(‘profiltabelle‘); Die Prozedur wird nicht die Zieltabelle selbst erstellen. Sie wird statt dessen einen Text-Output erzeugen, der dem weiter oben beschrieben Beispiel entspricht. Der Output hat die Form: Dieser Output ist dann anschließend auszuführen. Damit man den Output erhält muss set serveroutput on in der Session gesetzt sein. Die einzelnen Zeilen können sehr breit sein, deswegen sollte man die LINESIZE in SQLPlus sehr Hoch setzen, um unnötige Zeilenumbrüche zu vermeiden. z. B. set linesize 1000 Man kann auch direkt ein Text-Skript auf Betriebssystemebene erzeugen, indem man den Output mit Spool in eine Datei umlenkt um diese dann anschließend zu starten. Spool c:\Skriptname.sql EXEC TG(‘profiltabelle‘); SPOOL OFF Start c:\Skriptname Für das oben gezeigte Beispiel sieht die Ausführung und das generierte Ergebnis so aus: SQL> exec tg('VT'); CREATE TABLE T_30 AS SELECT substr(ran_WORDS('/','Baecker/Baecker/Baecker/Baecker/Baecker/Mueller/Mueller/Mueller/Mueller/B auer/Bauer/Bauer/Bauer/Bauer/Bauer/Bauer/'),1,10) Beruf , substr(ran_dates('/','5-3/5-3/10-8/10-8/10-8/10-8/10-8/'),1,20) Geb_Dat , substr(ran_lists('/','LAST_NAME/LAST_NAME/'),1,20) Name , substr(ran_NUMBERS('/','1-10/100-200/100-200/100-200/100-200/'),1,20) SP2 from dual connect by level < 20 PL/SQL procedure successfully completed. Die daraus entstandene Zieltabelle ist weiter oben schon abgedruckt. Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 16/24 Erstellen von Werteliste-Tabellen aus den operativen Daten Oft will man seine eigenen operativen Daten für Testzwecke nutzen, z. B. Artikelnamen, Wohnorte von Kunden, Liste von Geschäftsfelder oder Verkaufsgebieten. Über die vorbereitete Table Function TF_LT kann man aus operativen Tabellen einzelne Spalten herauslesen und daraus eine Werteliste-Tabelle machen, die der Testdatengenerator dann nutzt, um Zufallswerte zu generieren. Eine Werteliste-Tabelle hat immer nur zwei Spalten: NR Value number varchar2 Die erste Spalte muss eine aufsteigend laufende Nummer von 1 bis n enthalten. In der zweiten Spalte sollten die Werten stehen. Hier können beliebige Informationen versammelt sein. Es muss in ein varchar-Feld passen. Beispiel einer Werteliste-Tabelle Eine solche Tabelle kann manuell erstellt werden. Um den Vorgang zu vereinfachen, wird mit dem Testdatengenerator eine Table Funktion mitgeliefert, über die man aus bestehenden Tabellen die interessanten Collumns herauslesen kann. Die Werte sind anschließend mit DISTINCT bearbeitet. Dadurch sind alle Werte in der Werteliste-Tabelle nur einmal vorhanden. Die Nummernspalte mit dem laufenden Zähler wird hinzu generiert. Beispiel: Es gibt eine Tabelle ORTE mit der folgenden Struktur: SQL> desc Orte Name Null? Type ----------------------------------------------------- -------- ------------ORT_NR NUMBER ORT VARCHAR2(40) KREIS_NR NUMBER KREIS VARCHAR2(40) BUNDESLAND_ID NUMBER BUNDESLAND VARCHAR2(40) REGION_ID NUMBER REGION VARCHAR2(5) Daraus interessiert die Spalte ORT aus der eine Werteliste-Tabelle mit dem Namen LS_Orte erstellt werden soll. Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 17/24 Der Aufruf hierzu lautet: create table LS_Orte_Liste as select * from table( TF_LT('orte','ort')); Erstellung einer Werteliste-Tabelle Mit ausgelieferte Werteliste-Tabellen Um ein Grundset an deutschsprachigen Daten nutzen zu können, sind mehrere Wertelisten-Tabellen vorbereitet worden. Sie befinden sich in dem mit ausgelieferten Datenbank-Dump. Wertelisten-Tabelle LS_BUNDESLAND_LISTE LS_HAUPTSTAEDTE_LISTE LS_KREIS_LISTE LS_NACHNAMEN_LISTE LS_ORTE_LISTE LS_STAATEN_LISTE LS_TOP500_FIRMEN_DE_LISTE LS_US_STATE_LISTE Anzahl Sätze 16 203 342 100 12010 207 500 52 Weitere Wertelisten Tabellen können Sie bequem mit Hilfe der Table Function TF_LT erstellen. (Siehe Beschreibung weiter oben) Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 18/24 Verwendung des Testdatengenerators mit OWB (beliebige Version) Oracle Warehouse Builder eignet sich gut als Rahmen für den Testdatengenerator. Alle ProgrammSourcen können an einer Stelle zusammengehalten werden, die jeweiligen Sourcen-Typen, die Funktionen, Prozeduren, Table Functions, Object Types, Table Types aber auch die WertelistenTabellen und die Profiltabellen lassen sich in einer Projekt-Tree-Darstellung sortiert darstellen. Alle Objekte des Testdatengenerators an einer Stelle Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 19/24 Ein Beispiel-Mapping Ein Beispiel-Mapping erläutert exemplarisch die Verwendung der Random-Funktionen. In dem Mapping ist eine Kundentabelle mit beliebig vielen Sätzen zu erstellen. Die verschiedenen Columns der Kundentabelle erfordern unterschiedliche Wertestrukturen. Die Generierung der Werte für die einzelnen Columns erfolgt entweder über direkte Funktionsaufrufe oder als Expression-Operatoren. Die eigentliche Herausforderung für die Erstellung von Testdaten in einem Warehouse BuilderMapping ist die Tatsache, dass es in diesem Fall keine Quelltabellen gibt aus denen Daten gelesen werden können. Ein normales SQL-Select-Statement hat eine FROM-Klausel hinter der sich die Quelltabellen befinden müssten. Wenn keine Quelltabellen existieren, greift die FROM-Klause ins Leere. Die Lösung in dem folgenden Mapping besteht in einer Table Function die über ihre PIPE-RückgabeFunktion laufende Nummern über die FROM-Klausel zurückgibt und zwar so viele, wie der Table Function über einen Aufrufparameter mitgegeben werden. Beispiel-Mapping zur Erstellung von Testdaten. Die Table Function TF_LFD_NR dient als TriggerTabelle über die die Anzahl der Sätze in der Zieltabelle bestimmt wird. Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 20/24 Plan für Testdatengenerator Version 2 Diese Version des Testdatengenerators ist noch nicht perfekt und hat Lücken. Er wird aber weiter entwickelt. Und es gibt auch schon Pläne für eine Version 2. Folgende Punkte sind hier geplant: Angabe konkreter Jahreszahlen bei Generieren von Datumswerten. Über Profiltabellen auch andere Arten von Zeichen generieren können, nicht nur CHAR, DAT, NUM, LIST. Spaltenübergreifende Abhängigkeiten von Werten. APEX-Oberfläche. Verwendung einer Table Function beim Abarbeiten der Profile, um die Speicherbegrenzung aufzuheben. Erzeugen echter Tabellen anstatt Screen-Output. Wenn Sie ebenfalls Vorschläge für die Weiterentwicklung haben: Schicken Sie eine Mail an [email protected] Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 21/24 Anhang Die ausgelieferten Routinen Name Verwendung RAN_ABC123_Mixed.txt Basisroutine RAN_ABC_Mixed.txt Basisroutine RAN_ABC_UPPER.txt Basisroutine RAN_Date_N.txt Basisroutine RAN_Digits.txt Basisroutine RAN_list.txt Basisroutine RAN_lists.txt Basisroutine RAN_M_N.txt Basisroutine RAN_Dates.txt Hilfsroutine für TG RAN_Numbers.txt Hilfsroutine für TG RAN_Values.txt Hilfsroutine für TG RAN_words.txt Basisroutine und Hilfsroutine für TG tf_LT.txt Table Function mit Zusatz-Definitionen zum erzeugen von WertelisteTabellen aus Spalten andere Tabellen tg.txt Testdatengenerator Tf_lfd_nr Table Function zum Erzeugen einer laufenden Nummer als Ersatz für eine Quelltabelle in der FROM-Klausel Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 22/24 Syntax-Beschreibung der Basisroutinen Function RAN_M_N Parameter MIN_N, MAX_N, number of decimals(optional) Beschreibung Returns a random number value between parameter1 and parameter2 The optional third parameter indicates the number of digits behind the decimal point. Rules: If the second parameter has a lower value than the first the first parameter will be ignored and set to zero. Maximum 7 digits RAN_ABC_MIXED N(optional) Example: select ran_M_N(1000,2000) from dual Returns a number (parameter1) of letters in mixed cases. Parameter1 is optional. Max number of letters is 256 Ran_digits N(optional) (You get only upper or only lower cases with the SQL-upper/lower function around this function.) Returns a number (parameter1) of digits. Parameter1 is optional. Max number of digits is 256 RAN_ABC123_MIXED N(optional) select ran_digits(5) from dual; Returns a number (parameter1) of letters or digits in mixed cases. Parameter1 is optional. Max number of letters is 256 select ran_abc123_MIXED(5) from dual; (You get only upper or only lower cases with the SQL-upper/lower function around this function.)´ RAN_ABC_UPPER N(optional) select upper(ran_abc123_MIXED(5)) from dual; select lower(ran_abc123_MIXED(5)) from dual; Returns a number (parameter1) of upper letters. Parameter1 is optional. Max number of letters is 256 Example: Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 23/24 select ran_abc_upper(5) from dual; RAN_DATE_N RANGE_FROM RANGE_TO Returns any date between Parameter1 and Parameter2 Parameter1, Parameter2 are numbers and mean the number of years. Parameter1 has to be higher than Parameter2 Example: Assumed this Day is 16-12-02 select ran_date_n(50,20) from dual --> returns any date between 16-12-52 and 16-12-82 select ran_date_n(10,0) from dual --> returns any date between 16-12-92 and today RAN_ VALUES N, values Returns a number (parameter1) of values of parameter2. With first parameter you specify the number of values with the second one you can specify the values itsself. Example: Select ran_values(5,'.$567{Karl Gustav}') from dual; Returns: 7 {al a$va . su76 Max number of letters is 256. RAN_WORDS WORD_DELIMITER, Words (You get upper or lower cases only with the SQL-upper/lower function around this function. ) Returns a word (parameter2) of range of words (parameter2). With first parameter you specify a seperator for the list of word of second paramter. With second you specify a list of words seperated by the separator sign defined in first parameter (last sign must be the seperator itsself). Example: Stand/letzte Änderung 08.04.2017 582616100 Testdatengenerator 24/24 ran_WORDS('/','retail/IT/Medical/finance') Ran_List Tab_name returns one of the words. Returns a value of a table with name tab_name with the column value. The table tab_name must have the form: Create table tab_name (nr number, Value varchar2()); Example: Select ran_Ran_List(‘tab_name') from dual; Stand/letzte Änderung 08.04.2017 582616100