Index on tables

Hi List,

There is a strange problem thats occuring .

When a generate table (F55TEST1)command is given it generates the table and indices on the database.

however the indices are created as F55TEST1_1,F55TE00001,F55TE00002 and F55TE00003

the sqls generated being

CREATE UNIQUE INDEX PRODDTAPM/F55TES53_1 ON PRODDTAPM/F55TES53 (SDOBNM ASC, SDVERS ASC, SDUKID ASC)

CREATE INDEX PRODDTAPM/F55TEST1_10 ON PRODDTAPM/F55TES53 (SDOBNM ASC, SDVERS ASC, SDSEQ ASC)

CREATE INDEX PRODDTAPM/F55TEST1_11 ON PRODDTAPM/F55TES53 (SDOBNM ASC, SDVERS ASC, SDSDB ASC, SDTYDT ASC, SDLNID ASC, SDSEQ ASC)

CREATE INDEX PRODDTAPM/F55TEST1_12 ON PRODDTAPM/F55TES53 (SDOBNM ASC, SDVERS ASC, SD55ZSDBCR ASC, SDTYDT ASC, SDLNID ASC, SDSEQ ASC)

this is happening 3 different tables.


This is creating problems since it just wudnt work fine in the applications when the table is being called.

Ideally it should get it created as F55TEST1_1, F55TEST1_2,F55TEST1_3 and F55TEST1_4

why is JDE choosing to create _10,_11 and _12 index rather than _2,_3,_4 through sql statements.

This is with DB2/400 as database with ent svr on As400 . Since the tables and indices are getting created, i am sure that the authorities given are fine too .

any help would be appreciated.. (XE, SP23 , As400)

thanks

Hi Freddy

You say this is happening on XeSP23 ? What one-off ?

I1

If you look at those file names (F55TEST1_10, etc.), they are going over the AS/400 display limit of 10 characters. That's why they are showing up as F55TE00001 and so on. That's how the AS/400 shows a "long" file name.

As to why it's going as _10, _11, and _12 instead of _2, _3, and _4 ... I can only guess. Have you deleted and recreated or modified these indices during your development process? The Table Development tool tends to number each creation with a higher number, rather than just using the number of indices there are. So if you've worked on those indices a number of times, it will have caused the numbering to go that high.

Just a guess ...

well that should be sumthing I shud ask the development team

however if i copy this table to another one and generate it too generates the indices _10,_11,_12.. thats a one shot direct attempt..


will come back after asking the dev team

if I open table through UTB i find that on the table DDPKEYH.DDB the indexes are created as 1, 10, 11 and 12.
Is there a way i can change these ..?

Regards,
Freddy

I don't know of a way, short of deleting the object, and recreating it from scratch.

This might be a more apprpriate question for a developer.

Freddy,

In my experience what you are seeing is standard behavior. On the AS/400 platform JDE does not control the naming convention of the underlying logical file that supports the index. JDE only controls the name of the index in the DB2 catalog. When the length of this name exceeds the "standard" AS/400 naming convention of 10 characters the underlying logical file name will be generated by the system to something that is unique and fits within the 10 character limit.

Here is an example:

create table QGPL/F55ABCDEFG
(
COL1 CHAR(1)
)

create index QGPL/F55ABCDEFG_1 ON QGPL/F55ABCDEFG
(
COL1 ASC
)
Index F55ABCDEFG_1 created in QGPL on table F55ABCDEFG in QGPL.

The length of the index name is 12 characters.

Now, query the DB2 catalog via QSYS2/SYSINDEXES:

SELECT INDEX_NAME, SYSTEM_INDEX_NAME FROM QSYS2/SYSINDEXES
WHERE TABLE_NAME LIKE 'F55ABCDEFG'
AND INDEX_SCHEMA LIKE 'QGPL'

The results:

INDEX_NAME SYSTEM_INDEX_NAME
F55ABCDEFG_1 F55AB00001

Note that the SYSTEM_INDEX_NAME will match the name of the logical file created in QGPL to support the index.

You state that it wouldn't work through the application. Are you actually experiencing a problem? You shouldn't be. JDE can access these indexes just fine despite them having a system index name that does not match the SQL index name.

Freddy,
If you look at the .h file created under B7/DV73333/include directory with the table name you will see what numbers 'should' be used for AS400 indexes.

Here's an example from one of our custom tables.

/* PRIMARY INDEX */
#define ID_F59PT000_PROPERTY_NUMBER 1L

typedef struct
{
MATH_NUMERIC qa59ptpty; /* 0 to 48 */
} KEY1_F59PT000, FAR *LPKEY1_F59PT000;

#define ID_F59PT000_BUSINESS_UNIT 2L

typedef struct
{
char qamcu[13]; /* 49 to 61 */
} KEY2_F59PT000, FAR *LPKEY2_F59PT000;

#define ID_F59PT000_VENDOR_ROLL_NBR_PROP_TYPE 3L

typedef struct
{
MATH_NUMERIC qaan8; /* 62 to 110 */
char qatx2[21]; /* 111 to 131 */
char qa59ptpt; /* 132 to 132 */
} KEY3_F59PT000, FAR *LPKEY3_F59PT000;

#define ID_F59PT000_BUSINESS_CATEGORY___PROP_NBR 4L

typedef struct
{
char qabncc[5]; /* 133 to 137 */
MATH_NUMERIC qa59ptpty; /* 138 to 186 */
} KEY4_F59PT000, FAR *LPKEY4_F59PT000;

#endif

So the #DEFINE blahblah....xL
indicates the number where x is.

As JEMiller pointed out if your table name + underscore + number is greater than 10, the 400 will definitely do odd things. But that doesn't look like the case here.

I imagine if you delete all the h files (deployment server too) and resave the file, it 'may' renumber them. You can look at the .h file before you gen the indexes to see what it will do beforehand.

Hope this helps.

Sue
Xe SP23J1 coexistent

[ QUOTE ]
I imagine if you delete all the h files (deployment server too) and resave the file, it 'may' renumber them. You can look at the .h file before you gen the indexes to see what it will do beforehand.

[/ QUOTE ]

JDE gets the next number of the index from the F98712 file in Central Objects from the TPINID field by adding one to the highest value I believe, so I think the only way to start the numbering sequence over again is if you delete the file completely from your Central Objects location and recreate it. Deleting the .h files alone won't do anything for this situation if memory serves me right.