-
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 ?
-
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.
-
Zitat von Fuerchau
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
-
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.
-
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.
-
Wie gesagt, ein Trigger wäre an dieser Stelle sicherer und unabhängig von irgendwelchen Programmen (ODBC/JDBC...).
-
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
-
Zitat von loeweadolf
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
-
- 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
-
Nur ein paar Infos am Rande ...
Zitat von BenderD
- 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 von BenderD
- 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
-
Wie gesagt, ich habe eine Fehlermeldung bekommen wenn ich beim Insert die Spalte verwende.
Mehr behaupte ich ja gar nicht.
-
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.
Similar Threads
-
By dibe in forum IBM i Hauptforum
Antworten: 11
Letzter Beitrag: 11-11-16, 09:59
-
By Tonazzo in forum NEWSboard Programmierung
Antworten: 5
Letzter Beitrag: 15-10-15, 22:36
-
By KingofKning in forum NEWSboard Programmierung
Antworten: 2
Letzter Beitrag: 18-05-15, 13:09
-
By Willi1 in forum IBM i Hauptforum
Antworten: 1
Letzter Beitrag: 03-05-02, 08:38
-
By lorenzen in forum IBM i Hauptforum
Antworten: 4
Letzter Beitrag: 21-03-01, 13:44
Berechtigungen
- Neue Themen erstellen: Nein
- Themen beantworten: Nein
- You may not post attachments
- You may not edit your posts
-
Foren-Regeln
|
Erweiterte Foren Suche
Google Foren Suche
Forum & Artikel Update eMail
AS/400 / IBM i
Server Expert Gruppen
Unternehmens IT
|
Kategorien online Artikel
- Big Data, Analytics, BI, MIS
- Cloud, Social Media, Devices
- DMS, Archivierung, Druck
- ERP + Add-ons, Business Software
- Hochverfügbarkeit
- Human Resources, Personal
- IBM Announcements
- IT-Karikaturen
- Leitartikel
- Load`n`go
- Messen, Veranstaltungen
- NEWSolutions Dossiers
- Programmierung
- Security
- Software Development + Change Mgmt.
- Solutions & Provider
- Speicher – Storage
- Strategische Berichte
- Systemmanagement
- Tools, Hot-Tips
Auf dem Laufenden bleiben
|
Bookmarks