[DB2] No SQL-Delete possible. Need help! Getting sick!

[Robert Schneider]

Hi to all,
I don't understand that: I try to delete a record via JDBC. But I always
get the error SQL7008 with the error code 3. It seems that this has
something to do with journaling, since the table from which I want to
delete has two foreign keys that references two other tables and it is
also referenced by another table. But this shouldn't be a problem, since I
set the commit mode to none (or *none) at all places where this makes
sense: connection.setA utoCommit(true) ,
connection.setT ransactionIsola tion(NONE), as a parameter within the url
for the driver, as a SQL-Statement 'set transaction isolation level no
commit'. And the table preferences in the iNavigator says also
commit=*none. Where is the problem? Is a deletion with a normal
SQL-Statement impossible when the table has some references or is
referenced? Maybe I should also mention (but I don't think that this
important) that the table is empty, that no record in the other table does
reference a record in this table and because the table is empty there is
obviously no reference to the other two mentioned tables.

What am I doing wrong?
Must I use journals? Is it not possible without?

Please help me! I don't have any clue where the problem could be.
Thanks,
Robert



--
Erstellt mit M2, Operas revolutionärem E-Mail-Modul:

http://www.opera.com/m2/

[Serge Rielau]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Hi Robert,

What's your platform?
I don't think your problem has anything to do with DELETE.

Here is what I get for an explanation for SQL7008:
db2 => ? SQL7008;


SQL7008N REXX variable "<variable> " contains inconsistent
data.

Explanation:

A variable that contained inconsistent data was passed to REXX.

The command cannot be processed.

User Response:

If the variable is an SQLDA, verify that the data and length
fields have been assigned correctly. If it is a REXX variable,
verify that the type of data is appropriate to the command where
it is being used.


I'm a bit confused by the REXX part.. pelase verify the correctness of
the SQLCODE....


--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Robert Schneider]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Hi Serge,
first I have to say that I'm new to DB2. My db is on an AS400. And I'm
also new to this too. This doesn't makes it easier for me...

There error message is not same as you have posted. It's the typical 'xxx
in yyybibl for operation invalid' (translated from german). I've seen this
message in a lot of postings. But I could only find the instruction to set
the commit somehow to none or set the transaction level somehow to none.
But as I have written I have done this in several kind of ways without
success. I have to admit that I'm a little bit confused about all the
possibilties (and I have to admit that this might has something to do with
my English skills...)

Robert



Am Thu, 19 Feb 2004 10:58:43 -0500 hat Serge Rielau
<srielau@ca.e ye-be-em.com> geschrieben:
[color=blue]
> Hi Robert,
>
> What's your platform?
> I don't think your problem has anything to do with DELETE.
>
> Here is what I get for an explanation for SQL7008:
> db2 => ? SQL7008;
>
>
> SQL7008N REXX variable "<variable> " contains inconsistent
> data.
>
> Explanation:
>
> A variable that contained inconsistent data was passed to REXX.
>
> The command cannot be processed.
>
> User Response:
>
> If the variable is an SQLDA, verify that the data and length
> fields have been assigned correctly. If it is a REXX variable,
> verify that the type of data is appropriate to the command where
> it is being used.
>
>
> I'm a bit confused by the REXX part.. pelase verify the correctness of
> the SQLCODE....
>
>[/color]



--
Erstellt mit M2, Operas revolutionärem E-Mail-Modul:

http://www.opera.com/m2/

[PM \(pm3iinc-nospam\)]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Problem description and error message provided don't fit...

Can you provide
SQL7008 with the error code 3's text you have.
client, server, driver versions.
code page settings (contraction/expansion)
size of table (is empty, ok), log parameters

Something like : db2 get snapshot for all on dbname | findstr /i /C:"log
SPACE" /C:"SECONDARY"
to see if someone else is using all the logs.

commit mode None = auto commit ON ?

If you delete, even if you specify an isolation level such as UR, there will
be
internal isolation level upgrade to cs scemantics or higher for updateable
statements (insert, delete, update).


See : Note that Connection.TRAN SACTION_NONE ...
setTransactionI solation
public void setTransactionI solation(int level)
throws SQLExceptionAtt empts to change the
transaction isolation level for this Connection object to the one given. The
constants defined in the interface Connection are the possible transaction
isolation levels.
Note: If this method is called during a transaction, the result is
implementation-defined.


Parameters:
level - one of the following Connection constants:
Connection.TRAN SACTION_READ_UN COMMITTED,
Connection.TRAN SACTION_READ_CO MMITTED,
Connection.TRAN SACTION_REPEATA BLE_READ, or
Connection.TRAN SACTION_SERIALI ZABLE. (Note that Connection.TRAN SACTION_NONE
cannot be used because it specifies that transactions are not supported.)
Throws:
SQLException - if a database access error occurs or the given parameter
is not one of the Connection constants

PM

"Robert Schneider" <nospamplease@i gs.at> a écrit dans le message de
news:opr3mi98na hf6ur3@news.aon .at...[color=blue]
> Hi to all,
> I don't understand that: I try to delete a record via JDBC. But I always
> get the error SQL7008 with the error code 3. It seems that this has
> something to do with journaling, since the table from which I want to
> delete has two foreign keys that references two other tables and it is
> also referenced by another table. But this shouldn't be a problem, since I
> set the commit mode to none (or *none) at all places where this makes
> sense: connection.setA utoCommit(true) ,
> connection.setT ransactionIsola tion(NONE), as a parameter within the url
> for the driver, as a SQL-Statement 'set transaction isolation level no
> commit'. And the table preferences in the iNavigator says also
> commit=*none. Where is the problem? Is a deletion with a normal
> SQL-Statement impossible when the table has some references or is
> referenced? Maybe I should also mention (but I don't think that this
> important) that the table is empty, that no record in the other table does
> reference a record in this table and because the table is empty there is
> obviously no reference to the other two mentioned tables.
>
> What am I doing wrong?
> Must I use journals? Is it not possible without?
>
> Please help me! I don't have any clue where the problem could be.
> Thanks,
> Robert
>
>
>
> --
> Erstellt mit M2, Operas revolutionärem E-Mail-Modul:
> http://www.opera.com/m2/[/color]

[Karl Hanson]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Serge Rielau wrote:[color=blue]
> Hi Robert,
>
> What's your platform?
> I don't think your problem has anything to do with DELETE.
>[/color]
<snip>[color=blue]
>[/color]

Pretty sure it is iSeries (iNavigator was the tip)

Message ID . . . . . . . . . : SQL7008
Message . . . . : &1 in &2 not valid for operation.

Cause . . . . . : The reason code is &3. Reason codes are:

1 -- &1 has no members.

2 -- &1 has been saved with storage free.

3 -- &1 not journaled, or no authority to the journal. Files with
an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be
journaled to the same journal.


Your foreign keys may have defined constraints, that in turn may require
the journal for recovery of dependent rows. The text below was copied
from the iSeries InfoCenter (apologies for the bulk). On an iSeries
command line, try using the DSPFD command with TYPE(*CST) to view
constraint info.

<Start of text copied from InfoCenter>
DB2 UDB for iSeries Database Programming V5R2

Enforcement of delete rules
When you delete a record from a parent file, the system checks the
dependant file for any dependent records (matching non-null foreign key
values). If it finds any dependent records, the delete rule determines
the action that is taken:

No Action--if the system finds any dependent records, it returns a
constraint violation and does not delete records.
Cascade--the system deletes dependent records that its finds in the
dependent file.
Set Null--the system sets null capable fields in the foreign key to null
in every dependent record that it finds.
Set Default--the system sets all fields of the foreign key to their
default value when it deletes the matching parent key.
Restrict--same as no action except that enforcement is immediate.
If part of the delete rule enforcement fails, the entire delete
operation fails and all associated changes are rolled back. For example,
a delete cascade rule causes the database to delete ten dependent
records, but a system failure occurs while deleting the last record. The
database will not allow deletion of the parent key record, and the
deleted dependent records are re-inserted.

If a referential constraint enforcement causes a change to a record, the
associated journal entry will have an indicator value noting that a
referential constraint caused the record change. For example, a
dependent record that is deleted by a delete cascade rule will have a
journal entry indicator which indicates that the record change was
generated during referential constraint enforcement.
<End of text copied from InfoCenter>

--
Karl Hanson

[Robert Schneider]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

I was just writing when I saw your posting. Yes iSeries (is this not the
same as the AS400?).
Maybe you could have a look at my create-Statements after the error
message. I have not used NO ACTION or something like this. So I use the
default behaviour. But maybe this is the problem.

Unfortunalty the error message is in german. I get it when I try to
execute the DELETE-Statement in the SQL editor in the iNavigator:

[SQL7008] ANNOT in IGSMTDIDX für Operation ungültig. Ursache . . . . :
Der Ursachencode ist 3. Ursachencodes und ihre Bedeutung:1 -- ANNOT hat
keine Teildateien2 -- ANNOT wurde mit freiem Speicherplatz gesichert3 --
ANNOT nicht im Journal aufgezeichnet/keine Berechtigung für Journal.
Dateien mit RI-Integritätsbedi ngungsaktion CASCADE, SET NULL od. SET
DEFAULT müssen im selben Journal aufgezeichnet werden.4 und 5 -- ANNOT in
Prod.bibl. gespeichert/erstellt, aber Benutzer in Debug-Modus
UPDPROD(*NO).6 -- Datensammlung wird erstellt, aber Benutzer in
Debug-Modus UPDPROD(*NO).7 -- Basistabelle zum Erstellen der Sicht
ungültig; ist programmbeschri eben oder in einer temporären Bibliothek.8 --
Benutzer versuchte, ein Objekt zu erstellen. Basistabelle ist aber in ASP,
der nicht der ASP ist, in dem Objekt erstellt wird.9 -- Index ist z. Z.
angehalten/ungültig.10 -- Integritätsbed./Auslöser wird zu ungültiger
Tabelle hinzugefügt. Tabelle ist in QTEMP, hat unterschiedlich e ASPs, ist
keine extern beschriebene Datei oder kann nicht
überschrieben/aktualisiert/gelöscht werden. Für eine Integritätsbed. ist
Tabelle eine Quellendatei oder Elterndatei hat andere Teildatei. Max.
Anzahl Auslöser in einem Auslöser erreicht.11 -- Verteilte Tabelle wird in
Bibl. QTEMP erstellt/Sicht wird für mehrere verteilte Tabellen erstellt.12
-- Tabelle konnte nicht in QTEMP, QSYS, QSYS2 erstellt werden, da sie eine
DATALINK-Spalte mit Option FILE LINK CONTROL enthält.13 -- Tabelle konnte
nicht in Datensammlung erstellt werden, die ein Datenverzeichni s enthält.
Tabelle enthält DATALINK- oder LOB-Spalte, die mit Datenverz. in Konflikt
tritt.14 -- DATALINK-/LOB-Spalte konnte nicht hinzugefügt werden; Datei
ist keine SQL-Tabelle.15 -- Versuch, Objekt mit COMMIT-Definition in
anderem ASP zu erstellen/ändern.Fehlerbe seitigung: Je nach Ursachencode
einen der folgenden Schritte durchführen:1 -- Teildatei zu ANNOT
hinzufügen (ADDPFM).2 -- ANNOT zurückspeichern (RSTOBJ).3 --
Journalfunktion für ANNOT starten (STRJRNPF)/Zugriffsberecht igung für
Journal einholen.4, 5 oder 6 -- Befehl CHGDBG mit UPDPROD(*YES)
ausführen.7 -- Tabellennamen entfernen, die Dateien in
QTEMP/programmbeschri ebene Dateien angeben.8 -- Tabellen im selben ASP
verwenden.9 -- Mit EDTRBDAP Folge des Zugriffspfads von HELD in 1-99 od.
*OPN ändern od. eindeutigen Index/eindeutige Integritätsbedi ngung erneut
erstellen/löschen.10 -- Gültige Tabellen für Integritätsbed. oder Auslöser
angeben.11 -- Andere Bibl. als QTEMP angeben/Sicht für nur 1 verteilte
Tabelle erstellen.12 -- Andere Bibl. als QTEMP, QSYS, QSYS2 oder SYSIBM
angeben.13 -- Bibl. ohne Datenverzeichni s angeben od. alle DATALINK- und
LOB-Spalten löschen.14 -- SQL-Tabelle zum Hinzufügen der DATALINK oder
LOB-Spalte angeben.15 -- Objekt im selben ASP wie aktuelle
COMMIT-Definition angeben oder diese beenden.





Maybe that gives you a hint.

What makes me really sick is that I've created a very simple db design.
Nothing special. I have just use the key words create table ... primary
key ... foreign key and some indexes.

<************** *************** *************** *************** *************** ************>

<* references Annot *>
CREATE TABLE AnnotVal (

DocID NUMERIC(15) NOT NULL,
ValueID NUMERIC(10) NOT NULL,
FieldID NUMERIC(3) NOT NULL,
SeqNum NUMERIC(3) NOT NULL,

PRIMARY KEY(DocID, SeqNum, ValueID, FieldID),

FOREIGN KEY (ValueID) REFERENCES FieldVal,
FOREIGN KEY (DocID, SeqNum) REFERENCES Annot)

<************** *************** *************** *************** *************** ************>

<* this is the table where I want to delete *>
CREATE TABLE Annot (

DocID NUMERIC(15) NOT NULL,
SeqNum NUMERIC(3) NOT NULL,
AttSeqNum NUMERIC(3) NOT NULL,
AttMimeType VARCHAR(30),
< and some other columns >,

PRIMARY KEY(DocID, SeqNum),

FOREIGN KEY(DocID) REFERENCES Doc (DocID),
FOREIGN KEY(DocID, AttSeqNum, AttMimeType) REFERENCES Attach(DocID,
SeqNum, MimeType))

<************** *************** *************** *************** *************** ************>

<* referenced by Annot *>
CREATE TABLE Doc(

DocID NUMERIC(15) GENERATED BY DEFAULT AS IDENTITY (START WITH 1,
INCREMENT BY 1),
< and some other columns >,

PRIMARY KEY(DocID))

<************** *************** *************** *************** *************** ************>

<* referenced by Annot *>
CREATE TABLE Attach (

DocID NUMERIC(15) NOT NULL,
SeqNum NUMERIC(3) NOT NULL,
MimeType VARCHAR(30) NOT NULL,
< and some other columns >,

PRIMARY KEY(DocID, SeqNum, MimeType),

FOREIGN KEY(DocID) REFERENCES Doc (DocID))

<************** *************** *************** *************** *************** ************>
Hope that is not too confusing to you.
And my DELETE-Statement is:
DELETE FROM myBibl.annot WHERE DocId = 123;



These are the concerned tables. What do I have to change now? I have seen
the documentation where you have got this bulk ;) from (DB2 UDB for
iSeries Database Programming V5R2). But I don't get it what to use. What I
need is to control updating, deletion and insertion by myself (maybe this
is not the best way, but for my first steps I would like to solve it this
way).

Thanks in advance. Now I have to go home to my wife and my son (and my
dinner, yum!). I will have a look tomorrow at this thread.

Thanks,
Robert

[PM \(pm3iinc-nospam\)]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Sorry, i have no idea how to work with journaling on AS-400/I-Series.

For translations, i use http://www.google.ca/language_tools?hl=en
It's not perfect but it can help.

PM

[Karl Hanson]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Robert Schneider wrote:[color=blue]
> I was just writing when I saw your posting. Yes iSeries (is this not the
> same as the AS400?).[/color]

iSeries is the newer name for AS/400.
[color=blue]
> Maybe you could have a look at my create-Statements after the error
> message. I have not used NO ACTION or something like this. So I use the
> default behaviour. But maybe this is the problem.
>
> Unfortunalty the error message is in german. I get it when I try to
> execute the DELETE-Statement in the SQL editor in the iNavigator:
>
> [SQL7008] ANNOT in IGSMTDIDX für Operation ungültig. Ursache . . . . :[/color]
<snip>[color=blue]
>
> Maybe that gives you a hint.
>
> What makes me really sick is that I've created a very simple db design.
> Nothing special. I have just use the key words create table ... primary
> key ... foreign key and some indexes.
>[/color]
<snip>

When you use the SQL CREATE SCHEMA (or CREATE COLLECTION) statement, a
library is created and a journal is automatically created (QSQJRN) into
the library. When you subsequently use CREATE TABLE and specify that
schema (implicitly or explicitly), the created table is automatically
journaled to QSQJRN in the schema. The SQL7008 message indicates the
table resides in IGSMTDIDX. My guess: IGSMTDIDX is a pre-existing
library, not created via SQL CREATE SCHEMA (so it has no journal).

Some links that may help clarify:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstxdbterms.htm

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymst16.htm

Also see "Qualificat ion of Unqualified Object Names" here:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst39.htm

Is it a problem to use CREATE SCHEMA and get the journal automatically?
You could also set up journaling using CL, but it is simpler to have SQL
do it (by default).

--
Karl Hanson

[Robert Schneider]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

> Sorry, i have no idea how to work with journaling on AS-400/I-Series.

Thanks anyway.
[color=blue]
> For translations, i use http://www.google.ca/language_tools?hl=en
> It's not perfect but it can help.[/color]

Okay, I will try to keep this in mind and hopefully use it next time.

Robert

[Robert Schneider]

Re: [DB2] No SQL-Delete possible. Need help! Getting sick!

Hi Karl Hanson,

I think that has helped me.
[color=blue]
> When you use the SQL CREATE SCHEMA (or CREATE COLLECTION) statement, a
> library is created and a journal is automatically created (QSQJRN) into
> the library. When you subsequently use CREATE TABLE and specify that
> schema (implicitly or explicitly), the created table is automatically
> journaled to QSQJRN in the schema. The SQL7008 message indicates the
> table resides in IGSMTDIDX. My guess: IGSMTDIDX is a pre-existing
> library, not created via SQL CREATE SCHEMA (so it has no journal).[/color]

You're right: The library has already existed when I called the CREATE
TABLE statements. And it is a good idea to use CREATE SCHEMA - I will use
it (probably the normal way, but if someone doesn't know that this command
exists...).

I conclude (with my gathered knowledge) that if there are tables with a
foreign keys I must have journals (or a journal) if I want to delete some
records. And it seemes, to me because of some trials, that it is possible
to delete records from a table that is not a parent table. If it is one I
get the error.
[color=blue]
> Some links that may help clarify:
> http://publib.boulder.ibm.com/iserie...stxdbterms.htm
> http://publib.boulder.ibm.com/iserie...rbafymst16.htm
> Also see "Qualificat ion of Unqualified Object Names" here:
> http://publib.boulder.ibm.com/iserie...rbafzmst39.htm[/color]

Yes, this helps me too. This is what I like at IBM. For developers there
is a lot of good documentations, which are very comprehensive too. Just to
find them, to know where to look first is a little problem.
But I should spend more time to read the docs more thorough instead of
searching only the key words and find out what I may do with them.

Thank you once more,
Robert


--
Erstellt mit M2, Operas revolutionärem E-Mail-Modul:

http://www.opera.com/m2/