Re: File Key

As a general rule, we look at the "L01" logical for the Primary key that
is closest to third form normal for any given table. For example, ECH,
the Order Header file: the ECHL01 logical is keyed by HORD, the order
number. The HORD column is a unique identifier for the order and the ECH
is where the HORD data element enters the system.

The oral history I heard about the unkeyed physicals is that there was a
period of time where using a primary key logical was faster on the
System/38. But the rest of the manuscript referred to some dude ripping
a monster's arm off, so I could be wrong...

-----Original Message-----
From: bpcs-l-bounces+wallyc=bergquistcompany.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+wallyc=bergquistcompany.com@xxxxxxxxxxxx] On
Behalf Of rob@xxxxxxxxx
Sent: Tuesday, May 25, 2010 7:39 AM
To: BPCS ERP System
Subject: Re: [BPCS-L] File Key

If you are wanting to know where BPCS stores your software license key
then call BPCS, tell them you thinking about becoming a MIMIX (or other

HA software) shop and you are running on two different boxes and you
want
to ensure that you do not replicate the software key from the primary to

the backup or the software will not run.
Or, you can google the snot out of various combinations of the above
until
you find a hit. (bpcs, mimix, key, ...)

If the other poster was right,...
BPCS doesn't key their physical files, that might start using database
integrity and that goes against their grain. Actually I think they are
stuck in a stone age mentality that said "I heard that back on (some
forerunner to the original ENIAC) that you shouldn't key physical files
because if something gets corrupted then it's not as easy to recover as
if
you only used logical file keys."
But getting to the point...
If you've ever used MS SQL Server or any other database package then
this
next is going to sound familiar.
Query the file SYSINDEXES and then SYSKEYS.
This is where you get slapped. The logical files used mainly by BPCS do

not qualify for a true sql 'index' and therefore do not appear in these
views.

SELECT COLUMN_COUNT, SYSTEM_INDEX_NAME, SYSTEM_INDEX_SCHEMA,
SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA
FROM sysINDEXES
WHERE SYSTEM_TABLE_SCHEMA ='GDIDIVF'
AND SYSTEM_TABLE_NAME ='IIM'
....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
..+....8....+....9....
COLUMN_COUNT SYSTEM_INDEX_NAME SYSTEM_INDEX_SCHEMA
SYSTEM_TABLE_NAME
SYSTEM_TABLE_SCHEMA
3 IIMI24 GDIDIVF IIM
GDIDIVF

2 IIMI01 GDIDIVF IIM
GDIDIVF

******** End of data ********

SELECT COLUMN_POSITION, ORDINAL_POSITION, ORDERING,
SYSTEM_COLUMN_NAME, SYSTEM_INDEX_NAME, SYSTEM_INDEX_SCHEMA,
COLUMN_IS_EXPRESSION, EXPRESSION_HAS_UDF
FROM syskeys
WHERE SYSTEM_INDEX_SCHEMA ='GDIDIVF'
AND SYSTEM_INDEX_NAME LIKE 'IIM%'
....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
..+....8....+....9....+...10...
COLUMN_POSITION ORDINAL_POSITION ORDERING SYSTEM_COLUMN_NAME
SYSTEM_INDEX_NAME SYSTEM_INDEX_SCHEMA
1 1 A IID
IIMI01
GDIDIVF
2 2 A IPROD
IIMI01
GDIDIVF
1 1 A IID
IIMI24
GDIDIVF
16 2 A IITYP
IIMI24
GDIDIVF
2 3 A IPROD
IIMI24
GDIDIVF

There's other ways to get all the file keys by using various api's, let
me
know if that's what you are looking for.

Rob Berendt