BIND PACKAGE RUNS INTO A MYSTERIOUS -151 SQLCODE - The proble m is solved - Thanks to e

Georg Peter

BIND PACKAGE RUNS INTO A MYSTERIOUS -151 SQLCODE - The proble m is solved - Thanks to e
>>>> This restriction (multirow UPDATE of primary key) is removed in V5.

THAT'S IT ! All our IT centers are running DB2 for OS/390 Version 5 except
the center where the -151 SQLCODE appears............

Thanks to everybody who helps me !

With kind regards - mit freundlichen Grüssen,
G e o r g H . P e t e r
DB2 Data Base Administrator c/o
------------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Department Product Support
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany
Phone 0049-711-8108-271
PC-Fax 004971189696071
------------------------------------------------------------------------

-----Ursprüngliche Nachricht-----
Von: Friemel, Norbert [mailto:[login to unmask email]
Gesendet am: Freitag, 9. Juni 2000 16:14
An: [login to unmask email]
Betreff: Re: BIND PACKAGE RUNS INTO A MYSTERIOUS -151 SQLCODE

> Any idea why this simple Bind Package runs into a SQLCODE -151 ?
>

... aah, that application! ;-))

Column DEW23_AGS is part of the primary key (and part of a foreign key) and
the search-condition selects more then one row (specifies only 1 column of
the 5-column primary key)

From SC26-3270-00 (DB2 for MVS/ESA V4 SQL Reference):
<snip>
The search condition is applied to each row of the table or view and the
updated rows are those for which the result of the search-condition is true.
If a column to be updated is part of a primary key, the number of rows
selected for update must not be greater than one.
<snip>

This restriction (multirow UPDATE of primary key) is removed in V5.

Norbert Friemel


visit the





JOHN G. MATTHEWS

-151
(in response to Georg Peter)
Programmer received the following error on some new SQL UPDATing a
table in a partitioned TS. The PARTKEYU parameter is SAME and the
SQL didn't change the partition# of any row anyway. The UPDATES
column in SYSIBM.SYSCOLUMNS for the offending column is 'N' which
I assume is there from the days when you could not change a column
in a partitioned table. This SQL ran against the test table which
is also partitioned and has UPDATES = 'N' for the column. This is
a table and not a view. The column is a TIMESTAMP, not a ROWID.

Any ideas why the -151 here and not on test table?

UPDATE CL.TCRORDRE SET COLLECTION_DTTME =
COLLECTION_DTTME + 1 DAYS
WHERE SPECIMEN_ID >= 'A000000000000'
AND SPECIMEN_ID <= 'A999999999999';
QLERROR ON UPDATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -151, ERROR: THE UPDATE STATEMENT IS INVALID BECAUS
CATALOG DESCRIPTION OF COLUMN CL.TCRORDRE.
COLLECTION_DTTME INDICATES THAT IT CANNOT BE UPDATED
DSNT418I SQLSTATE = 42808 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODML SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -400 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE70' X'00000000' X'00000000' X'FFFFFFFF
INFORMATION



Amit Sinha

Re: -151
(in response to JOHN G. MATTHEWS)
I remember encountering this problem. Even when the PTF for updating the
partitioning key was applied on the subsystem, the update column in the
SYSIBM.SYSCOLUMNS had 'N' in it. But if you drop and recreate the table the
update column in the SYSIBM.SYSCOLUMS will change to 'Y', and then you
should be able to update it.
About the update happening in test table don't know ?

HTH

Amit Sinha

Tel:414-931-4815




-----Original Message-----
From: JOHN G. MATTHEWS [mailto:HC#[login to unmask email]
Sent: Wednesday, December 13, 2000 6:03 PM
To: [login to unmask email]
Subject: -151


Programmer received the following error on some new SQL UPDATing a
table in a partitioned TS. The PARTKEYU parameter is SAME and the
SQL didn't change the partition# of any row anyway. The UPDATES
column in SYSIBM.SYSCOLUMNS for the offending column is 'N' which
I assume is there from the days when you could not change a column
in a partitioned table. This SQL ran against the test table which
is also partitioned and has UPDATES = 'N' for the column. This is
a table and not a view. The column is a TIMESTAMP, not a ROWID.

Any ideas why the -151 here and not on test table?

UPDATE CL.TCRORDRE SET COLLECTION_DTTME =
COLLECTION_DTTME + 1 DAYS
WHERE SPECIMEN_ID >= 'A000000000000'
AND SPECIMEN_ID <= 'A999999999999';
QLERROR ON UPDATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -151, ERROR: THE UPDATE STATEMENT IS INVALID BECAUS
CATALOG DESCRIPTION OF COLUMN CL.TCRORDRE.
COLLECTION_DTTME INDICATES THAT IT CANNOT BE UPDATED
DSNT418I SQLSTATE = 42808 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODML SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -400 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE70' X'00000000' X'00000000' X'FFFFFFFF
INFORMATION