PDA

View Full Version : Aplha2 - DB2 CK - PRIMARY_INDEX vs PRIMARY_KEY mismatch ?



Samuel Pizarro
8-Mar-2017, 08:11 PM
I have a DB2 standard table (non-recnum), with 2 indexes defined on it. Both of them could be used to uniquely identify the record. (actually, almost every index created for DF apps could be used for that, as they need to be UNIQUE, but that is a topic for some other day conversation. :))

Ok. Index-1 the real PK, was properly identified by CK as the Primary-Key .

But the CK insist in setting the PRIMARY_INDEX table attribute to index 2. :(

I can force it to be Index-1, and DF makes the proper modification to the int file. But if I perform a simple refresh on this table (not a full Connect), the PRIMARY_INDEX table attribute is set back to 2 again.

10715

The Index-1 properly identified as the PK.
10716

Table's INT file:


DRIVER_NAME DB2_DRV
SERVER_NAME DFCONNID=RVMEDREP_ConnID
DATABASE_NAME GROUPMENURIGHT
SCHEMA_NAME APPCTRL

RECNUM_TABLE NO
PRIMARY_INDEX 2
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES
IDENTITY_CACHE_SIZE 0

FIELD_NUMBER 1
FIELD_INDEX 1
FIELD_RELATED_FILE 6
FIELD_RELATED_FIELD 1

FIELD_NUMBER 2
FIELD_INDEX 1
FIELD_RELATED_FILE 5
FIELD_RELATED_FIELD 1

INDEX_NUMBER 1
INDEX_NAME GROUPMENURIGHT_PK

INDEX_NUMBER 2
INDEX_NAME GROUPMENURIGHT002


Shouldn't be the case to have some kind of consistence between them ?

Regards

Martin Moleman
9-Mar-2017, 03:46 AM
Looks strange indeed. Not sure why it behaves like this.
What is the definition of index 2?
Can you show a create table script for this table?

Samuel Pizarro
9-Mar-2017, 07:19 AM
Hi Martin

I'm not with my dev stuff today, and can't give you the exactly real script. But I can try to give some tips until I get at home tonight where I will be able to provide the full script.

The Table primary-key is a compound one, based on the 1st two columns. as you can see in the 1st picture.

So the PK index-1 would be (Col1 ASC, Col2 ASC)

And the 2nd index, is to enforce to be used by the foreign-key, as the col2 is related to a parent table.
The Index-2 would be (Col2 ASC, Col1 ASC) . same columns as the PK, but with inverted segment order.

Let me know if this helps, otherwise I will provide the full real script tonight when I get home.

Regards

Stephen W. Meeley
9-Mar-2017, 09:14 AM
Samuel,

I don't have DB2 handy but I essentially created a similar table in MSSQL (with index 1 and 2 in reverse order and a primary key set to column 1 and 2) and everything seem to be fine. I don't have index 2 set as a foreign key (I don't have the related table structure) so if you so that may play a role.



DRIVER_NAME MSSQLDRV
SERVER_NAME DFCONNID=Chinook
DATABASE_NAME GroupMenu
SCHEMA_NAME dbo

RECNUM_TABLE NO
PRIMARY_INDEX 1
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES

FIELD_NUMBER 1
FIELD_INDEX 1

FIELD_NUMBER 2
FIELD_INDEX 1

INDEX_NUMBER 1
INDEX_NAME PK_GroupMenu

INDEX_NUMBER 2
INDEX_NAME GroupMenu002

Martin Moleman
9-Mar-2017, 09:52 AM
Samuel,

I can't reproduce it, so a script would be good.

Samuel Pizarro
9-Mar-2017, 10:36 AM
Thanks for the effort Martin

Will provide the script here tonight!

Samuel Pizarro
9-Mar-2017, 10:38 AM
Thanks for the effort and feedback Stephen!

Will try that out later with a simpler table without the FKs.

Samuel Pizarro
9-Mar-2017, 06:10 PM
Hi Martin

Here is a script that I can replicate the issue here. I have simplified it, removing the relationships (foreign-keys) and either this way I still can see the mismatch after connecting to this table.

db2 DDL:


--<ScriptOptions statementTerminator=";"/>

CREATE TABLE APPCTRL.GROUPMENURIGHT (
IRN_WEBAPPMENU SMALLINT NOT NULL,
IRN_USERGROUP INTEGER NOT NULL,
ICD_RIGHT SMALLINT NOT NULL DEFAULT 0
)
ORGANIZE BY ROW
DATA CAPTURE NONE
COMPRESS NO;



CREATE UNIQUE INDEX APPCTRL.GROUPMENURIGHT_PK
ON APPCTRL.GROUPMENURIGHT
(IRN_WEBAPPMENU ASC,
IRN_USERGROUP ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;

ALTER TABLE APPCTRL.GROUPMENURIGHT ADD CONSTRAINT GROUPMENURIGHT_PK PRIMARY KEY
(IRN_WEBAPPMENU,
IRN_USERGROUP);

CREATE UNIQUE INDEX APPCTRL.GROUPMENURIGHT002
ON APPCTRL.GROUPMENURIGHT
(IRN_USERGROUP ASC,
IRN_WEBAPPMENU ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;


COMMENT ON COLUMN APPCTRL.GROUPMENURIGHT.ICD_RIGHT IS
'Bit composition of User Rights against View/MenuItem

0 - Nothing ;
1 - Read Only ;
2 - Can Add Records ;
4 - Can Change/Update Recoreds ;
8 - Can Delete Records ;

The Final value will be the sum of all possible choices. ';



And follow the INT generated, after "Connecting" to it from DF Studio:


DRIVER_NAME DB2_DRV
SERVER_NAME DFCONNID=RVMEDREP_ConnID
DATABASE_NAME GROUPMENURIGHT
SCHEMA_NAME APPCTRL

RECNUM_TABLE NO
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES
PRIMARY_INDEX 2

INDEX_NUMBER 1
INDEX_NAME GROUPMENURIGHT_PK

INDEX_NUMBER 2
INDEX_NAME GROUPMENURIGHT002



Regards

Martin Moleman
13-Mar-2017, 10:39 AM
Samuel,

I still can't reproduce. Maybe its because I use a newer build.
Can you just wait for the next public build (should be there soon) and try again?

Samuel Pizarro
13-Mar-2017, 11:08 AM
sure!

I will try that on next build release!

Thanks

Stephen W. Meeley
14-Mar-2017, 07:23 AM
Samuel,

The Beta 1 build has been posted.

Samuel Pizarro
30-Mar-2017, 04:43 PM
Hi Martin

I have just tested this with Beta-2, and I still see the issue. Index.1 flagged as PK, but PRIMARY_INDEX set to 2 .

I am using win10 with DB2 11.1 (but don't know if that will matter)

Regards

Samuel Pizarro
30-Mar-2017, 04:45 PM
Hi Stephen

I created a new simple table today, without any relationships, and used Beta-2 to connect to it, and still see the same issue.

So, Relationships is not playing a role.
Thks

Martin Moleman
4-Apr-2017, 04:47 AM
Samuel,

I tried again but am unable to reproduce. Primary_Index always remains at 1 in my case.
This is what I tried:



Created the table in DB2 with your script
With SQL Connect/Repair wizard: Connect New.
This created the following INT (With primary_index 1)


DRIVER_NAME DB2_DRV
SERVER_NAME DFCONNID=DB2Order190ID
DATABASE_NAME GROUPMENURIGHT
SCHEMA_NAME DB2ADMIN

RECNUM_TABLE NO
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES
PRIMARY_INDEX 1

INDEX_NUMBER 1
INDEX_NAME GROUPMENURIGHT_PK

INDEX_NUMBER 2
INDEX_NAME GROUPMENURIGHT002

Refresh table: The primary_index is still 1
SQL Connect/Repair wizard: Repair existing: The primary_index is still 1


Did I miss something?

Samuel Pizarro
4-Apr-2017, 03:49 PM
Nope

There must be something different between our 2 setups.

I wonder why your SCHEMA_NAME is DB2AMIN, if the DDL I provided creates the table under APPCTRL schema ?

Compare the INT I shared in past threads and you will see the schema is different.

Just some other observations.
My system locale is PT-BR
My DF-Collate is Portuguese. (I know this should not interfere, but ....)
My DB2 DB Collate is not the standard. I use a special collation to make it CASE-INSENSITIVE



Database territory = BR
Database code page = 1208
Database code set = UTF-8
Database country/region code = 55
Database collating sequence = CLDR181_LROOT_AN_CX_EX_FX_HX_NX_S1
Alternate collating sequence (ALT_COLLATE) =


Can you try with a DB created with those settings ?

Regards

Martin Moleman
5-Apr-2017, 07:06 AM
Aha, I can now reproduce it. You were kind of right. The collating sequence changes the ordering of the indexes.

Indexes are sorted alphabetically by index name. With your collating the index names sorts as
GROUPMENURIGHT002
GROUPMENURIGHT_PK

With my collating it sorts as
GROUPMENURIGHT_PK
GROUPMENURIGHT002

(The underscore sorts different when using your collating sequence.)

The connect wizard gets confused when it does not receive the PK as the first index (alphabetically). That is the real issue here. I need to look further at that, but at least I can now reproduce it.

This sort order issue also caused the problems in the other thread.

Samuel Pizarro
5-Apr-2017, 10:48 AM
On db2, you have also the concept of index ID, per table. Indes 1, index 2, etc...
defined per order of creation.

Maybe that could help in. Just some info that might could help. Don't really know as. Db2 does not imposes a rule that the PK index hast to be the first one created for a particular table.

Thanks for the feedback

Samuel Pizarro
26-May-2017, 08:10 AM
Hi Martin

I see the SQL driver version for beta3 is still the same as for beta2 (6.2.0.33) so I guess nothing was changed/fixed yet right ?

Do you expect to have this sorted out for 19.0 Release candidate ?

Thanks

Stephen W. Meeley
26-May-2017, 09:30 AM
Samuel,

Actually that was my bad. There were new drivers that should have been in Beta 3 and I accidentally left them out. Perhaps I can email you the latest drivers by themselves and you can give them a try?

Samuel Pizarro
26-May-2017, 10:25 AM
Sure. I can try that.

samuel @ pizarros.com.br

thanks

Stephen W. Meeley
26-May-2017, 10:32 AM
Sent