Re: duplicate key error in identity column

Granted my reply is a little dated but you may want to take another look
at your DDL. Instead of creating a logical which omits that column, or by
using an O spec to omit that column, there is an alternative.

NOT HIDDEN
Indicates the column is included in implicit references to the table
in SQL statements. This is the default.
IMPLICITLY HIDDEN
Indicates the column is not visible in SQL statements unless it is
referred to explicitly by name. For example, SELECT * does not include any
hidden columns in the result. A table must contain at least one column
that is not IMPLICITLY HIDDEN.

However, this is not 'implicitly hidden' from RLA like it is with SQL.

Let's look at this table:
create table rob.mytable (
QTID INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )
implicitly hidden,
mychar char(5))
rcdfmt MYTABLER

INSERT INTO ROB.MYTABLE (MYCHAR) VALUES('HI')

select * from rob.mytable
does not show any implicitly hidden columns
....+.
MYCHAR
HI

You have to ask for them, like:
select qtid,mychar from rob.mytable
....+....1....+....2..
QTID MYCHAR
1 HI

When we process it with SQL we see:
ctl-opt dftactgrp(*no) actgrp(*caller) debug(*yes);
dcl-ds mytableDS extname('MYTABLE');
end-ds;

*inlr=*on;
// exec sql select * into :mytableDS // results in SQLSTATE=22018 bad
character
exec sql select * into :mychar
from rob.mytable
fetch first row only;
dump;
return;

From the dump:

MYTABLEDS DS
MYCHAR CHAR(5) 'HI ' 'C8C9404040'X
QTID BIN(9,0) 077952576. '40404040'X


But when we process it RLA we see:
ctl-opt dftactgrp(*no) actgrp(*caller);
dcl-f mytable disk(*ext) usage(*update);
*inlr=*on;
read mytable;
dsply qtid; // displays the value. Darn, does not hide implictly
return;

So, who cares enough to submit a DCR?


Rob Berendt