SQLCODE +535 for positioned update of primary key

Don Fleisher

SQLCODE +535 for positioned update of primary key
I have a COBOL/DB2 program which performs positioned update of one of the
primary key columns in table (table1). The cursor declaration and
statements used to perform the UPDATE are listed below. The table being
updated does not have any RI, is neither a parent nor a child and does not
contain self referencing relationships. The version of DB2 is V7.

The table is in a partitioned tablespace and I have performed updates to
the primary key columns successfully using SPUFI. When a BIND is performed
for the program with positioned update to primary keys, DB2 issues a
SQLCODE +535/SQLSTATE 01591 and the BIND job ends with RC=04.

Is there any way to code UPDATE statements to change values of primary key
columns and not receive a SQLCODE +535 (besides DELETE and INSERT) ?

Thanks for your thought on this.



The columns that comprise the primary key for table1 are :
pkcol1
PKCOL2
pkcol3
pkcol4

DECLARE cursor1 CURSOR
FOR SELECT PKCOL2
FROM table1
WHERE pkcol1 = :hv1
AND PKCOL2 = 'literal2'
AND pkcol3 = :hv3
FOR UPDATE OF PKCOL2

OPEN cursor1

UPDATE table1
SET PKCOL2 = :hv2
WHERE CURRENT OF cursor1

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dale Smock

Re: SQLCODE +535 for positioned update of primary key
(in response to Don Fleisher)
If it is a partitioned table that you are updating the partitioning key,
there is a zparm parameter PARTKEYU of Yes, No, or Same to allow or disallow
updates of partitioning key values.

Dale Smock
Bertelsmann

-----Original Message-----
From: Don Fleisher [mailto:[login to unmask email]
Sent: Friday, December 12, 2003 12:08 PM
To: [login to unmask email]
Subject: SQLCODE +535 for positioned update of primary key


I have a COBOL/DB2 program which performs positioned update of one of the
primary key columns in table (table1). The cursor declaration and
statements used to perform the UPDATE are listed below. The table being
updated does not have any RI, is neither a parent nor a child and does not
contain self referencing relationships. The version of DB2 is V7.

The table is in a partitioned tablespace and I have performed updates to
the primary key columns successfully using SPUFI. When a BIND is performed
for the program with positioned update to primary keys, DB2 issues a
SQLCODE +535/SQLSTATE 01591 and the BIND job ends with RC=04.

Is there any way to code UPDATE statements to change values of primary key
columns and not receive a SQLCODE +535 (besides DELETE and INSERT) ?

Thanks for your thought on this.



The columns that comprise the primary key for table1 are :
pkcol1
PKCOL2
pkcol3
pkcol4

DECLARE cursor1 CURSOR
FOR SELECT PKCOL2
FROM table1
WHERE pkcol1 = :hv1
AND PKCOL2 = 'literal2'
AND pkcol3 = :hv3
FOR UPDATE OF PKCOL2

OPEN cursor1

UPDATE table1
SET PKCOL2 = :hv2
WHERE CURRENT OF cursor1

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: SQLCODE +535 for positioned update of primary key
(in response to Dale Smock)
Don,
You are recieving a positive SQLcode which is a warning to you. It does not fail the bind, as you stated below you recieved a condition code pf 04. Take a look in your job's output to see if the words bind sucessful exist or not. From the book +535 THE RESULT OF THE POSITIONED UPDATE OR DELETE MAY DEPEND ON THE
ORDER OF THE ROWS
Means that you have to ensure your SQL is written correctly to ensure after you change the key you don't see the row in your cursor again.


Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/12/03 12:08:24 PM >>>
I have a COBOL/DB2 program which performs positioned update of one of the
primary key columns in table (table1). The cursor declaration and
statements used to perform the UPDATE are listed below. The table being
updated does not have any RI, is neither a parent nor a child and does not
contain self referencing relationships. The version of DB2 is V7.

The table is in a partitioned tablespace and I have performed updates to
the primary key columns successfully using SPUFI. When a BIND is performed
for the program with positioned update to primary keys, DB2 issues a
SQLCODE +535/SQLSTATE 01591 and the BIND job ends with RC=04.

Is there any way to code UPDATE statements to change values of primary key
columns and not receive a SQLCODE +535 (besides DELETE and INSERT) ?

Thanks for your thought on this.



The columns that comprise the primary key for table1 are :
pkcol1
PKCOL2
pkcol3
pkcol4

DECLARE cursor1 CURSOR
FOR SELECT PKCOL2
FROM table1
WHERE pkcol1 = :hv1
AND PKCOL2 = 'literal2'
AND pkcol3 = :hv3
FOR UPDATE OF PKCOL2

OPEN cursor1

UPDATE table1
SET PKCOL2 = :hv2
WHERE CURRENT OF cursor1

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm