PDA

View Full Version : Alpha2: Primary Key not being recognized by Studio/CK ?



Samuel Pizarro
21-Feb-2017, 01:36 PM
Hi

After connecting to some DB2 tables, I noticed that Studio is not recognizing the Primary-Key definitions properly.

One Example:
This is how the table is defined in DB2.
10658

But After "connecting to" this table from Studio, I can't see the "key" icon in the 1st column.
10659

Despite the "Primary Index" table property (PRIMARY_INDEX int keyword) has been set properly to 1. The "key" icon does not appear in front of the columns in the grid.

And, looking at the Index-1 definition, "sql primary key' is marked as False, but it should be True I guess
10660

If I try to change this index attribute to True, when I try to save the table, I thought it would only make some changed in the .int file as the index already exist in the back-end. but actually DF tries to make some backend modification and fails with SQL0669N error bellow:


Studio Error
Table changes for (USERGROUP) were not saved.
Generic error 42917 (-699) -- [IBM][CLI Driver][DB2/NT64] SQL0669N A system required index cannot be dropped explicitly. SQLSTATE=42917
Table = USERGROUP (5), Source = DB2_DRV.Structure_End.DropIndexes

It looks DF/Studio/CK tried to drop the index, but it couldn't as the index is used as PK.

Regards




Regards

Martin Moleman
22-Feb-2017, 03:15 AM
I just tried to connect to a DB2 table with a primary key and that worked for me.

Can you show the complete Create Table script for this table?

Also do you happen to know what is meant by 'A system required index' ? That might have something to do with it?

Samuel Pizarro
22-Feb-2017, 05:42 AM
Hi Martin

Here is the ddl to re-create such table.


CREATE TABLE "APPCTRL"."USERGROUP" (
"IRN_USERGROUP" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE ORDER ),
"SDC_GROUPNAME" VARCHAR(200) NOT NULL DEFAULT ''
)
DATA CAPTURE NONE
COMPRESS NO;

CREATE UNIQUE INDEX "APPCTRL"."USERGROUP001"
ON "APPCTRL"."USERGROUP"
("IRN_USERGROUP" ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;

CREATE UNIQUE INDEX "APPCTRL"."USERGROUP002"
ON "APPCTRL"."USERGROUP"
("SDC_GROUPNAME" ASC)
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;

ALTER TABLE "APPCTRL"."USERGROUP" ADD CONSTRAINT "USERGROUP_PK" PRIMARY KEY
("IRN_USERGROUP");


Basically, the index that composes the PK segments already exist before defining the PK constraint in the table.

So, a 'System required index' is the index associated with a PK or FK definition. So you can't just drop the index, and the PK or FK depends on it. on DB2 you would have to fisrt drop the FK or PK constraint, and then later you would be able to drop the index it self.
SQL0669N (http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00669n.html)

Thanks in advance!

Martin Moleman
22-Feb-2017, 08:06 AM
Samuel,

Doesn't the name of the index have to be the same as the name of the constraint?
So the index should also be named "USERGROUP_PK" instead of "USERGROUP001".

Apparently the names can be different in DB2, but this explains why you are having problems. The Connectivity Kit just assumes the names must be the same.

Samuel Pizarro
22-Feb-2017, 10:33 AM
Hi Martin

Indeed on DB2 you don't need to have both with the same name. When creating the PK constraint db2 checks if it has already an unique index tha satisties the pk uniqueness rule (segmentes) and uses it as the PK.

This is a pretty common usage on DB2 world.

I will try to rename the index to match the pk name. But it would be nice if CK could detect situations like this in the future.

Will reply back with the results.

Thanks

Samuel Pizarro
24-Feb-2017, 08:20 PM
Hi Martin

I was able to change the index names to match with the PK name constraint, and now DF Studio/CK are recognizing the PK as you said.

The query bellow shows the correlation between the primary-Key constraint name and the index used by DB2 to enforce the PK rule.



SELECT SUBSTR(RTRIM(I.TABSCHEMA) || '.' || RTRIM(I.TABNAME), 1, 40) TABNAME, SUBSTR(I.INDNAME, 1, 30) INDNAME, UNIQUERULE, SUBSTR(C.CONSTNAME, 1, 40) CONSTNAME, C.TYPE
FROM SYSCAT.INDEXES I INNER JOIN SYSCAT.TABCONST C
ON I.TABSCHEMA = C.TABSCHEMA AND I.TABNAME = C.TABNAME AND I.UNIQUERULE = C.TYPE
WHERE I.TABSCHEMA = 'APPCTRL' AND C.TYPE = 'P'

TABNAME INDNAME UNIQUERULE CONSTNAME TYPE
---------------------------------------- ------------------------------ ---------- ---------------------------------------- ----
APPCTRL.GROUPMEMBERS GROUPMEMBER001 P GROUPMEMBER_PK P
APPCTRL.GROUPMENURIGHT GROUPMENURIGHT001 P GROUPMENURIGHTS_PK P
APPCTRL.INTERFACELANGUAGE INTERFACELANGUAGE001 P LANGUAGE_PK P
APPCTRL.TRANSLATIONS TRANSLATIONS001 P TRANSLATIONS_PK P
APPCTRL.USERGROUP USERGROUP_PK P USERGROUP_PK P
APPCTRL.WEBAPPMENU WEBAPPMENU001 P WEBAPPMENU_PK P
APPCTRL.WEBAPP_SESSION WEBAPP_SESSION001 P WEBAPP_SESSION P
APPCTRL.WEBAPP_USER WEBAPP_USER001 P WEBAPP_USER P



Something similar could be used to determine the proper Index name used by DB2 as the PK.

Thanks

Martin Moleman
27-Feb-2017, 03:33 AM
Thanks, That is helpful. I will take another look at this.

Samuel Pizarro
27-Feb-2017, 08:18 PM
Yrw and Thank you!