[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Jul 2003
    Beiträge
    331

    Smile SQL - CREATE TABLE automatisher Satzzähler

    Kann ich im SQL (Create Table) beim Erstellen einer Tabelle ein Datenfeld angeben, welches automatisch bei jedem Hinzufügen zu der Datei um eins erhöht wird, egal ob mit SQL oder RPG hinzugefügt wird ?

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Jein:
    Man kann ein Autoincrement-Feld definieren, dass allerdings nur beim weglassen während des Inserts automatisch gezählt wird (as identitiy ...).
    Hier gibt es 2 Varianten:
    GENERATED BY DEFAULT = Nur beim Insert, wenn ein Default erwartet wird.
    GENERATED ALWAYS = Immer, aber es darf nicht erwähnt werden, wie bei DEFAULT

    M.a.W: Ich darf das Feld beim Insert zwar angeben, es treten dabei allerdings 2 Situationen auf:
    - bei DEFAULT wird der Wert so übernommen, wie angegeben, auch bei NULL
    - bei ALWAYS gibt es einen Fehler (das Feld darf nicht angegeben werden), es sei denn man gibt explizit "OVERRIDE USER VALUES" (so ähnlich) beim Insert an.

    Da es sich hierbei um eine SQL-Funktion handelt, schließt dies RPG/LE (auch CPYF) aus, da man das Feld nicht ausschließen kann.

    Um für alles gewappnet zu sein, hilft hier leider nur ein Trigger, da dieser immer beim Before-Insert einen Wert generieren kann.

    Ich hatte hier auch ein Problem beim Kunden, da eine Schnittstelle extern befüllt wird und das Feld der Autonummer vom externen Dienstleister mit NULL vorbelegt wird. SQL-Server u.ä. behandeln dies dann korrekt. Letztendlich konnte ich dies nur mit Trigger lösen.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Zitat Zitat von Fuerchau Beitrag anzeigen
    J
    Da es sich hierbei um eine SQL-Funktion handelt, schließt dies RPG/LE (auch CPYF) aus, da man das Feld nicht ausschließen kann.
    Wer behauptet denn das schon wieder?
    Wir setzten seit Jahren Identity Columns problemlos in Verbindung mit RPG Native I/O und auch bei CPYF mit *MAP *DROP ein.
    Der Zähler wird automatisch (korrekt!) generiert, wenn die Identity Column mit GENERATED ALWAYS definiert wurde.

    Auch bei native I/O kann man nach dem WRITE den ermittelten Wert der Identity Spalte über die SQL-Funktion IDENTITY_VAL_LOCAL ermitteln.

    Bei GENERATED BY DEFAULT wird nur dann weitergezählt, wenn die Identity Column leer ist oder nicht übergeben wurde. Ansonsten wird der in der Variable hinterlegte Wert für die Identity Column übernommen. Damit kann es bei GENREATED BY DEFAULT u.U. zu doppelten Werten kommen. Irgendwann wurde ein neuer Satz wurde mit einem höheren Zähler eingefügt. Beim nächsten Satz ohne Id-Wert wir der automatische Zähler weitergezählt und irgendwann kommt es dann zu Kolision.

    Aber auch beim GENERATED ALWAYS kann es zu Duplikaten kommen, nämlich dann, wenn man den Zähler beim Überlauf wieder von vorne anfangen lässt.

    Wichtig ist, dass über die Indentity Column entweder ein Key Constraint (Primary oder Unique) oder ein Unique Index definiert wird. Dadurch wird verhindert, dass doppelte Werte eingefügt werden können.

    Beim Insert in SQL lässt man entweder die Identity Spalte aus, oder gibt den Sonderwert DEFAULT an.
    Sofern man die Identity Spalte mit IMPLICITLY HIDDEN definiert, hat man auch beim Insert aus einer anderen Tabelle keine Probleme, da die Identity Spalte übergangen wird.

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Leider ist die Dokumentation diesbezüglich unvollständig, daher kann ich nur berichten, was mir passiert ist.
    Meine Definition GENERATED ALWAYS führt beim Insert halt dazu, dass dieser abgewiesen wird.
    Die SQL-Meldung besagt eben auch, dass entweder die Spalte weggelassen oder OVERRIDING USER VALUES angegeben werden muss.
    Der Sonderwert DEFAULT ist in diesem Zusammenhang auch nicht erklärt.
    Ich kann allerdings einen externen Dienstleister nicht verklickern, dass er die Spalte einfach weglassen oder beim Insert DEFAULT angeben soll, da dieses (wieder mal) nicht standard ist.
    Das Attribut HIDDEN würde dieses zwar lösen, wenn dieses Feld nicht existentiell für die Anwendung wäre.
    OVERRIDING USER VALUES kann ich aber leider nicht bereits beim CREATE TABLE angeben, was mir am meisten Sinn geben würde.

    GENERATED BY DEFAULT habe ich auch versucht. Beim Insert mit NULL gibt es ebenso einen Fehler, auch hier half nur weglassen.

    Da du nun auf den Begriff "DEFAULT" beim Insert hinweist stellt sich mir die Frage, wie ich diesen
    a) im ILE/RPG
    b) beim CPYF
    c) beim simplen "insert into ziel select * from quelle"
    angebe.
    Da ich die Daten auch mal retten und wieder zurückspielen musste konnte ich per
    create table as select * from quelle
    die Daten retten (wobei hier die Definition des Zieles an hand des Ergebnisses und nicht der Quelltabelle verwendet wurde).
    Beim umgekehrten Weg "insert into ziel select * from quelle" bzw. beim CPYF handelte ich mir einen Fehler ein. Es half dann nur der Weg "insert into ziel overriding user values select * from quelle".

    Ich lasse mich gerne überzeugen wenn du mir eine Lösung bringst, die folgende Bedingung erfüllt:

    - sichtbare Identity-Spalte
    - generate always (also unabhängig davon ob das Feld mit oder ohne Inhalt angegeben wurde)

    so dass ich die Tabelle mit einem simplen SQL-Insert, CPYF oder ILE/RPG/COBOL-Write verwenden kann.
    So wie es im Moment definiert ist, muss ich zuviele Klimmzüge machen damit der Wert funktioniert. Da ist der Trigger erheblich einfacher und schneller zu realisieren. Ins besonders, wenn dieser die doppelten Schlüssel bei Überlauf (falls überhaupt) selber behandeln kann und ggf. die Lücken füllt. SQL weist da ganz einfach per Fehler ab wenn ein Unique-Key oder Primary-Key definiert ist.

    Hier das Handbuch V7R1:

    If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is
    specified:
    v A value cannot be specified for a ROWID, identity, or row change timestamp
    column that is defined as GENERATED ALWAYS.
    v A value can be specified for a ROWID, identity, or row change timestamp
    column that is defined as GENERATED BY DEFAULT. If a value is specified
    that value is assigned to the column. However, a value can be inserted into a
    ROWID column defined BY DEFAULT only if the specified value is a valid
    row ID value that was previously generated by DB2 for z/OS or DB2 for i.
    When a value is inserted into an identity or row change timestamp column
    defined BY DEFAULT, the database manager does not verify that the
    specified value is a unique value for the column unless the identity or row
    change timestamp column is the sole key in a unique constraint or unique
    index. Without a unique constraint or unique index, the database manager
    can guarantee unique values only among the set of system-generated values
    as long as NO CYCLE is in effect.
    If a value is not specified the database manager generates a new value.

    von der Verwendung des Begriffes/Wertes "DEFAULT" ist hier nicht die Rede.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  5. #5
    Registriert seit
    Jul 2003
    Beiträge
    331
    Vielen Dank an Euch beide für Eure Mühe. Es hört sich an, dass es nicht ganz sicher ist, ob es immer funktioniert.

    Damit ich weiter komme, habe ich das Problem auf folgende Weise gelöst:
    In zwei Anwendungen (1 x SQL und 1 x RPG) werden Datensätze hinzugefügt zu dieser Datei.
    In einem Programm, welches immer sofort in beiden Fällen anschl. läuft, lese ich alle neuen Sätze, das heisst, alle Datensätze mit Satzzähler = 0, und verpasse denen einen entspr. Satzzähler.
    Die zuletzt vergeben Nr. halte ich in einer anderen Datei fest.

    Ich weiß, dass das bei vielen anderen Anwendungen so nicht reichen würde, aber in diesem speziellen Fall ist das ausreichend. So bin ich hier auf der sicheren Seite.

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Wie gesagt, ein Trigger wäre an dieser Stelle sicherer und unabhängig von irgendwelchen Programmen (ODBC/JDBC...).
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  7. #7
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Ich kann allerdings einen externen Dienstleister nicht verklickern, dass er die Spalte einfach weglassen oder beim Insert DEFAULT angeben soll, da dieses (wieder mal) nicht standard ist.
    Dann leg halt eine View an, in der alle Spalten außer der Identity Spalte und anderen Spalten, die automatisch generiert werden (ab 7.3) definiert sind. Die Inserts erfolgen nicht direkt in die physische Datei/Tabelle (was generell sowieso keine gute Idee ist), sondern über die View.

    Woher weißt Du eigentlich dass der Sonderwert DEFAULT nicht Standard ist, könnte es nicht sein, dass andere Datenbanken-Hersteller den DEFAULT einfach nicht integriert haben, da ihre Datenbanken nicht nach Standard funktionieren?

    Da du nun auf den Begriff "DEFAULT" beim Insert hinweist stellt sich mir die Frage, wie ich diesen
    a) im ILE/RPG
    b) beim CPYF
    Habe ich bereits in meinem 1. Post gesagt!
    Wenn Du einen Satz mit RPG schreibst bleibt das ID-Feld einfach 0 oder wird explizit mit clear initialisiert. Mehr nicht!
    Die folgenden 3 Zeilen sind ein Auszug unserer Anwendung aus einer ganz normalen RPG-Prozedur, in der ein Datensatz in eine Tabelle mit Idenitity Column geschrieben wird. (Die Identitity Spalte ist allerdings NICHT mit implicitly hidden definiert)
    Code:
              Clear GblDSDWACTLOGP.ACID;
              Write  DWACTLOGF GblDSDWACTLOGP;                                   
    
               Exec SQL Set :RtnId = Identity_Val_Local();
    Beim CPYF habe ich bereits gesagt, dass man mit *MAP/*DROP kopiert, also, dass die Spalte erst gar nicht übergeben wird. Sollte man jedoch mit CPYF in eine Datei mit Identity Column kopieren wollen und die Spalte ist vorhanden und man will die Original-Werte beibehalten, führt man zunächst einen ALTER TABLE aus und konvertiert GENERATE ALWAYS in GENERATE BY DEFAULT. Nach dem Kopieren wird zurückgestellt. Man sollte allerdings dann den Anfangs-Wert für die ID im Anschluss einstellen.

    Was die Defintion der Identity Spalte als Implicitly Hidden angeht.
    Führ doch einfach die folgenden Satements aus und schau Dir das Ergebnis an:

    Code:
    Create Table YourSchema.myTable
    (MyInt   Integer Not NULL Default 0,
     MyChar  VarChar(10) not NULL default '')
    ; 
    
    Commit;
    
    Insert into YourSchema.MyTable 
    Values(100, 'AAA'), (200, 'BBB'), (300, 'CCC')
    ;
    
    Commit;
    
    Create Table YourSchema.MyTableX
    (MyInt  Integer      Not NULL Default 0,
     MyId   Integer      Generated Always as Identity IMPLICITLY HIDDEN,
     MyChar VarChar(10)  not NULL Default '');
    
    Commit; 
     
    Select * from YourSchema.Mytablex; 
    Select MyId, a.* from YourSchema.MyTablex a;
      
    
    Insert into YourSchema.MyTableX
    Select * from YourSchema.MyTable;
    
    Commit;
    
    Select MyId, a.* from YourSchema.MyTablex a;
    
    Delete from YourSchema.mytable;
     
    Commit;
    
    Insert into YourSchema.myTable
    
    Commit;
    
    Select * from YourSchema.mytablex;

    Ich lasse mich gerne überzeugen wenn du mir eine Lösung bringst, die folgende Bedingung erfüllt:

    - sichtbare Identity-Spalte
    - generate always (also unabhängig davon ob das Feld mit oder ohne Inhalt angegeben wurde)
    Wie wär's mit ausprobieren?

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  8. #8
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Zitat Zitat von loeweadolf Beitrag anzeigen
    Vielen Dank an Euch beide für Eure Mühe. Es hört sich an, dass es nicht ganz sicher ist, ob es immer funktioniert.
    Nur weil Baldur behauptet, dass es nicht funktioniert, muss das noch lange nicht stimmen.

    Wir setzten diese Technik seit Jahren problemlos in diversen Projekten und bei diversen Firmen mit SQL, native I/O und sogar CPYF ein.

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  9. #9
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    - die SQL Standards sind veröffentlicht, den wesentlichen Teil bekommt man hier:
    http://standards.iso.org/ittf/Public...rds/index.html
    weitere sind kostenpflichtig.
    - wichtiger als der Papier Standard ist oft der reale Standard, sprich: Wie wird das in DB2 mit und ohne Ei, in Oracle, MS SQL Server, Firebird, PostgreSQL, MySQL gehandhabt, da erlebt man so manche Überraschung, wenn ich mir so manches übermittelte Problemchen (ArdGate) anschaue.
    - dass ausgerechnet DB2 in der Eier-Variante Vorreiter des SQL Standards sein soll, halte ich für ein Gerücht.
    - den Work around einen Insert mit einem "leeren" Feld per RLA zu machen und dann per embedded SQL zu ermitteln, was man reingeschrieben hat, da muss man erst mal drauf kommen - bei mir kräuseln sich da die Nackenhaare, sei's drum, die Geschmäcker sind verschieden. Das ist nun allerdings ganz sicher kein auch nur im Ansatz ISO/ANSI SQL verträglicher Ansatz!
    - problematisch wird jedes, wie auch immer, generierte Id Feld, wenn die Sequenz lückenlos sein muss, oder wenn der Schlüsselwert auch als Fremdschlüssel in andere Tabellen wandert, was eher die Regel als die Ausnahme ist.

    D*B
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  10. #10
    Registriert seit
    Aug 2003
    Beiträge
    1.508
    Nur ein paar Infos am Rande ...
    Zitat Zitat von BenderD Beitrag anzeigen
    - dass ausgerechnet DB2 in der Eier-Variante Vorreiter des SQL Standards sein soll, halte ich für ein Gerücht.
    Mit der Version 12c kann Oracle das was das EI schon seit vielen Jahren kann.
    Zitat Zitat von BenderD Beitrag anzeigen
    - den Work around einen Insert mit einem "leeren" Feld per RLA zu machen und dann per embedded SQL zu ermitteln, was man reingeschrieben hat, da muss man erst mal drauf kommen
    Klar, Aus- und Weiterbildung ist das A & O in der gesamten IT!


    lg Andreas

  11. #11
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Wie gesagt, ich habe eine Fehlermeldung bekommen wenn ich beim Insert die Spalte verwende.
    Mehr behaupte ich ja gar nicht.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  12. #12
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Ergänzend zu Birgitta sei noch folgendes hinzugefügt:
    Wie auch von Dieter schon des öfteren erwähnt, verwende ich natürlich kein native IO, wenn ich eine Tabelle mit SQL erstelle. Somit erklärt sich die Diskrepanz zwischen Birgitta und mir:

    Wenn ich die Tabelle mit ILERPG beschreibe, kann ich in das ID-Feld reinschreiben was ich will, es wird immer ein Satz mit der nächsten Nummer erstellt.
    Also muss der Compiler an Hand der Tabelleninformationen ggf. das Id-Feld ausschließen oder SQL prüft bereits vor dem Write die Zulässigkeit der Verwendung dieses Feldes.
    Sowas schließ dann z.B. einen einfachen Insert aus einer Struktur aus, ich muss grundsätzlich mit einer View ohne ID-Feld arbeiten auch wenn ich dann in die tatsächliche Tabelle einfüge (nur wegen der Tipparbeit).

    Nun komme ich aber in meinen Augen zu einem fatalen Fehler:
    Birgitta erwähnte den CPYF mit FMTOPT(*MAP *DROP).
    Auf diese Verwendung wäre ich gar nicht gekommen, da diese ja eigentlich nur bei abweichende Definitionen erforderlich ist.
    Also habe ich dies ausprobiert und ich muss zugeben, es funktioniert.
    Allerdings mit einer fatalen Auswirkung:
    Die Datensätze aus der Quelle werden mit der ID der Quelle übertragen, es wird also keine ID aus dem Ziel neu vergeben.
    Habe ich keinen Unique-Key definert (weil ich dachte, ALWAYS wäre ausreichend) erhalte ich nun Doppelte ID's. Mit einem Unique-Key kann ich die Daten aber nicht kopieren.

    D.h., der CPYF kopiert auf einer tieferen Ebene die Daten als der Zugriff per RPG, da ja hier eine Nummer grundsätzlich vergeben wird.

    Erst wenn ich mir tatsächlich eine View ohne das ID-Feld anlege und dann aus dieser View als Quelle ins Ziel mit *MAP/*DROP kopiere erhalte ich auch tatsächlich neue ID's.

    Also ist trotz der Definition der ID als GENERATE ALWAYS nicht sichergestellt, dass es keine doppelten ID's gibt.

    Von der Manipulation der ID mit Reset würde ich sowieso abraten.
    Bei einer 32-Bit Ganzzahl bekomme ich bei ca. 58000 Sätzen täglich ca. 100 Jahre weit.
    Bei einer 64-Bit Ganzzhal habe ich eine Reserve für die nächsten 100 Jahre von ca. 252 Billionen Sätze pro Tag. Da platzt ja eher die AS/400 als dass es einen Grund zum Rücksetzen der ID gibt. Auch nicht bei einem CLRPFM, denn wer weiß wo die ID schon in irgendeiner Form weitergegeben ist.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

Similar Threads

  1. create or replace table
    By dibe in forum IBM i Hauptforum
    Antworten: 11
    Letzter Beitrag: 11-11-16, 10:59
  2. Frage zu CREATE TABLE
    By Tonazzo in forum NEWSboard Programmierung
    Antworten: 5
    Letzter Beitrag: 15-10-15, 23:36
  3. create table
    By KingofKning in forum NEWSboard Programmierung
    Antworten: 2
    Letzter Beitrag: 18-05-15, 14:09
  4. CREATE TABLE
    By Willi1 in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 03-05-02, 09:38
  5. DATFMT bei CREATE TABLE
    By lorenzen in forum IBM i Hauptforum
    Antworten: 4
    Letzter Beitrag: 21-03-01, 14:44

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •