Kbase 21477: How to change the primary index of the table on AS/400
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/17/2004 |
|
Status: Verified
GOAL:
How to change the primary index of a table on AS/400?
FACT(s) (Environment):
Progress/400 DataServer
FIX:
It's an easy task when using Progress/400 Data Dictionary. However it's much more complicated when the schema modifications and the database maintenance is performed on AS/400 itself. Progress recommends consistent attitude towards database maintenance: natively on AS/400 or through the progress client. Therefore this Knowledge Base will explain how it should look like while the database schema modifications are performed on the AS/400 natively. Such a situation is very frequent in cases when "native" AS/400 HLL programs access the same database files concurrently with Progress 4GL clients and the customer has chosen the AS/400 maintenance method to modify the database schema.
Progress indexes usually represent AS/400 logical files that are being generated with relation to existing physical files (tables). However the primary index of each table is deployed as a keyed (indexed) physical file. This means that there is no logical file for it - instead, the key information is included into appropriate physical file description. It makes the task of changing it more complex as modifying the physical file will be required.
Progress/400 DataServer uses externally described files. Each created table or index should have a corresponding DDS description. Usually it may be found in P__DDS source file in the Dictionary Library.
Prior to performing any further steps it is advisable that at least the files to be modified should be backed up or copied aside. The main task is to modify appropriate DDS description to reflect required changes. The way of doing this is described below.
1. Make backup copies of all the objects involved in this process.
2. The physical file's DDS description is to be modified to include new key fields (taken from the logical file description of the previous secondary index)
3. The logical file's DDS description needs to be changed with ex-primary index definition. It also could be worthy to change its name to the one used for previous primary index, particularly for Progress clients
4. In the Dictionary Library there is an old compiled logical file that is no longer needed. It can be safely removed.
5. In order to preserve the data in the physical file we shouldn't create a new one but change the existing object with a command CHGPF specifying corresponding DDS source. The object will be changed preserving existent records. It has to be kept in mind that the data in the existing file is converted to the new format based on field names. If the name of a field is changed, its existing data is lost.
6. Then it is sufficient to compile a DDS description for a new logical file, which will be a secondary index from now on. The CRTLF (Create Logical File) command should be used for this purpose (or option 14 in WRKMBRPDM menu).
7. When the database schema modification is completed it's ready to be used for any native AS/400 programs but it's still not ready for the Progress 4GL clients. All these changes have to be reflected in the server schema of the Progress/400 DataServer. In order to pull appropriate information into server schema files CHGPRODCT command must be processed. It is sufficient to provide the changed physical file name as a FRMFILLST parameter. PROATR should be set to *SAVE if this file was originally created from the Progress client.
8. The last point is to synchronize the schema holder(s) of the Progress client. It may happen that Progress 4GL application depends on index names. After the changes the primary index will be named after physical file's name. This case can be resolved by modifying index name through the Progress/400 Data Dictionary and repeat the synchronization process.
In order to perform above tasks one has to be have appropriate object management authority or object alter authority for the file and execute authority to the library. An exclusive-no-read lock is required, which means no one can be using the file for any purpose. This particularly applies to th.e use of CHGPF command..