| Title: | DB Integrator Public Conference |
| Notice: | Database Integration - today! Kit/Doc info see note 36 |
| Moderator: | BROKE::ABUGOV |
| Created: | Mon Sep 21 1992 |
| Last Modified: | Fri Jun 06 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 1171 |
| Total number of notes: | 5187 |
Hi,
I have a customer who is using DBI Gateway for RMS V3.0 and SQL V6.0-1
running VMS V6.2 on an Alpha, and is experiencing performance problems.
He claims that it is taking far to long to perform the SQL when
inserting into a file.
The file he is currently using is approx 2 Meg in size, but he plans to
use files up to 100 Meg in the future.
Is this a performance problem, is the customer doing something wrong,
or is it normal for it to take this long.
I have very few skills in this area. Can anyone help in any way.
Attached below is a brief description of the situation as supplied by
the customer.
Thanks,
Mario Vassil
Digital CSC
Sydney
=========================================
4000 blocks of indexed file. !! L1_record_type is NOT in any index
however so the read accesses are presumably sequential and non optimised.
SQL to select certain types and insert into extract file. This is
because the input file is not normalised and I want to split it up to make
a nice structured arrangement.
Running on 64M 2100 AXP. Having masses of diskquota,IOlim, pgfquo etc
makes no difference.
* Performing the SQL below takes 11..12 mins when inserting into a
file.
* performing the SELECT only to the screen with display turned off by
CTRL-O reduces the time to 3 seconds !!!
* performing the SELECT only from a batch job that logs SYS$OUTPUT
completes in 2 minutes and produces a log file of 8000 blocks as
might be expected.
=> It appears that the INSERT is consuming a lot of the time. Is it
perhaps opening the file for each row to be inserted.
This could be a major problem with users as is the SQL I have used not
perhaps typical of what they might want to do in reality.
========================================
** DCL follows
****************************************************************
$
$ SQL
SQL> attach 'filename NSDS$lis01201';
SQL> insert into normalised_lis01201_l1
_SQL> select * from lis01201_l1 where l1_record_type = 'L1' ;
****Database .CDO follows
*****************************************************
DEFINE FIELD L1_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD Lx_date
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD Lx_time
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD Lx_user_ident
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD Lx_system_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD Lx_sub_system_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
DEFINE FIELD Lx_client_ref_no
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 20 CHARACTERS.
DEFINE FIELD Lx_contact_initials
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L1_last_date
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD L1_last_time
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD L1_logoff_flag
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 1 CHARACTERS.
DEFINE FIELD L1_maynelaw_session_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L1_maynelaw_rate_per_minute
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L1_elapsed_minutes
DESCRIPTION IS /* */
DATATYPE IS signed word .
DEFINE FIELD L1_filler_1
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 80 CHARACTERS.
DEFINE FIELD L1_filler_2
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 15 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE RECORD LIS01201_l1_RECORD .
L1_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l1_last_date.
l1_last_time.
l1_logoff_flag.
l1_maynelaw_session_charge.
l1_maynelaw_rate_per_minute.
l1_elapsed_minutes.
l1_filler_1.
l1_filler_2.
END.
DEFINE RMS_DATABASE LIS01201_l1_STORAGE.
RECORD LIS01201_l1_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l1_STORAGE.
RECORD LIS01201_l1_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE DATABASE LIS01201_L1
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l1_stoRAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE normalised_LIS01201_L1
USING normalised_LIS01201_l1_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L1.DAT .
** File .FDL for input & output file follows
**********************************
IDENT " 6-FEB-1996 15:55:02 VAX-11 FDL Editor"
SYSTEM
SOURCE "VAX/VMS"
FILE
ORGANIZATION indexed
RECORD
CARRIAGE_CONTROL carriage_return
FORMAT fixed
SIZE 174
AREA 0
ALLOCATION 612
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 156
AREA 1
ALLOCATION 12
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 6
AREA 2
ALLOCATION 357
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 3
EXTENSION 96
KEY 0
CHANGES no
DATA_AREA 0
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 1
NAME "l1_record_key"
PROLOG 3
SEG0_LENGTH 14
SEG0_POSITION 2
TYPE string
KEY 1
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_user_ident"
SEG0_LENGTH 10
SEG0_POSITION 16
TYPE string
KEY 2
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_system_code"
SEG0_LENGTH 2
SEG0_POSITION 26
TYPE string
KEY 3
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_sub_system_code"
SEG0_LENGTH 3
SEG0_POSITION 28
TYPE string
KEY 4
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_client_ref_no"
SEG0_LENGTH 20
SEG0_POSITION 31
TYPE string
KEY 5
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_contact_initials"
SEG0_LENGTH 3
SEG0_POSITION 51
TYPE string
* end
*************************************************************************
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 1054.1 | Any journaling involved? | BROKE::GREEN | Thu May 02 1996 18:31 | 6 | |
Hi Mario,
Do either of the RMS files use RMS Journaling?
Thanks,
Don
| |||||
| 1054.2 | No journaling involved | ORAREP::GIDDAY::VASSIL | Fri May 03 1996 03:26 | 25 | |
Hi Don,
Thanks for your response.
>> Do either of the RMS files use RMS Journaling?
No, the files do not use RMS Journaling.
Apparently the customer was getting the following error message
which kept causing his process to fail.
%RDB-F-SYS_REQUEST, error from system services request
-NSDS-E-WRITERELFAIL, Failed to insert, delete, or update data relation
-RMS-F-FAC, record operation not permitted by specified file access (FAC)
As a result of this error mesage the customer set up the
following logical to bypass RMS Journaling.
$ DEFINE/SYS NSDS$_BYPASS_JOURNALING "T"
The customer only recently installed DBI/RMS and SQL and this
is the first time that he has fully utilised it.
Thanks,
Mario.
| |||||
| 1054.3 | Can we get the files? | BROKE::GREEN | Fri May 03 1996 11:42 | 10 | |
Hi Mario,
Could you make a saveset with both RMS files and the metadata files and
copy them to ORAREP"":: so that we can test this here. Let me know what
the saveset name is and I'll watch for it.
Thanks,
Don
| |||||
| 1054.4 | Can we get the files? | ORAREP::GIDDAY::VASSIL | Mon May 06 1996 04:05 | 14 | |
Hi Don, I have spoken to my customer in regards to obtaining the files, he will supply me with the metadata files, but he can not supply me with the RMS files as the information with in them is regarded as confidential. He will however supply me with the FDL's with which to build the files. Will this be sufficient enough for you to test? I will create a saveset and copy the files into ORAREP"":: as soon as I receive them. Thanks, Mario. | |||||
| 1054.5 | Do the best we can | BROKE::GREEN | Mon May 06 1996 11:16 | 15 | |
Hi Mario,
I was running some performance tests here using two RMS files which
each had 6 keys like yours do. Running a similar kind of query I was
seeing performance times of 11-12 seconds to do the inserts. Rather
than keep coming back to you with more questions as they arise I felt
that using the customer's files could save time. For instance, my tests
were inserting just 20 rows out of a possible 20,000 and I didn't know
just how many rows you were actually inserting. I also wondered if file
size could be an issue, number of actual rows in both files etc.
Sure we'll try our best with blank files based on your FDL's, but it
would be great to test with the actual files.
Don
| |||||
| 1054.6 | Customer Info | ORAREP::GIDDAY::VASSIL | Thu May 09 1996 05:12 | 1117 | |
Hi Don,
Sorry about the delay in getting back to you, I only just received the
info from my customer.
Unfortunately this is the best he can supply me with, even though I
explained
the importance of testing his RMS files.
Please let me know if this is satisfactory or not.
Cheers,
Mario.
*******************************************************************************
**********************************************
LDEVL1-[ANDY]> dir 120*
Directory GRP$MDEV0:[ANDY]
1201.CDO;2 34/36
1-MAY-1996 16:22:44.10 (RWED,RWED,RWED,RWED)
1201.CDO;1 30/32
26-APR-1996 14:10:16.32 (RWED,RWED,RWED,RWED)
1201.COM;1 1/4
2-MAY-1996 13:18:30.03 (RWED,RWED,RWED,RWED)
1201.DAT;1 4072/4072
2-MAY-1996 11:55:55.26 (RWED,RWED,RWED,RWED)
1201.FDL;2 5/8
2-MAY-1996 10:31:04.58 (RWED,RWED,RWED,RWED)
1201.FDL;1 5/8
11-APR-1996 09:17:27.67 (RWED,RWED,RWED,RWED)
1201.LOG;1 7399/7400
2-MAY-1996 13:19:23.25 (RWED,RWED,RE,)
1201.REC;2 54/56
11-APR-1996 09:35:23.98 (RWED,RWED,RWED,RWED)
1201.SUB;1 1/4
2-MAY-1996 13:19:18.52 (RWED,RWED,RWED,RWED)
1201_INPUT.FDL;1 13/16
6-MAY-1996 16:04:45.17 (RWED,RWED,RWED,RWED)
1201_L1.DAT;2 2312/2312
2-MAY-1996 12:15:36.62 (RWED,RWED,RWED,RWED)
1201_L1.DAT;1 2312/2312
2-MAY-1996 11:57:06.29 (RWED,RWED,RWED,RWED)
1201_L2.DAT;1 984/984
1-MAY-1996 16:49:18.88 (RWED,RWED,RWED,RWED)
1201_L3.DAT;1 984/984
1-MAY-1996 16:49:41.94 (RWED,RWED,RWED,RWED)
1201_L4.DAT;1 1140/1140
1-MAY-1996 16:49:45.86 (RWED,RWED,RWED,RWED)
1201_L6.DAT;1 984/984
1-MAY-1996 16:49:50.58 (RWED,RWED,RWED,RWED)
1201_L7.DAT;1 984/984
1-MAY-1996 16:49:54.56 (RWED,RWED,RWED,RWED)
1201_L8.DAT;1 984/984
1-MAY-1996 16:49:59.30 (RWED,RWED,RWED,RWED)
1201_MALS.DAT;1 4071/4072
6-FEB-1996 20:39:22.00 (RWED,RWED,RWED,RWED)
Total of 19 files, 26369/26392 blocks.
******************************************************************************
***********************************************
LDEVL1-[ANDY]> anal /rms /fdl 1201.dat
LDEVL1-[ANDY]> dir 1201*.fdl
Directory GRP$MDEV0:[ANDY]
1201.FDL;3 13/16
6-MAY-1996 16:13:07.16 (RWED,RWED,RWED,RWED)
1201.FDL;2 5/8
2-MAY-1996 10:31:04.58 (RWED,RWED,RWED,RWED)
1201.FDL;1 5/8
11-APR-1996 09:17:27.67 (RWED,RWED,RWED,RWED)
1201_INPUT.FDL;1 13/16
6-MAY-1996 16:04:45.17 (RWED,RWED,RWED,RWED)
Total of 4 files, 36/48 blocks.
LDEVL1-[ANDY]> ren 1201.fdl;3 1201_input.fdl;2
LDEVL1-[ANDY]> ren 1201.fdl;3 1201_input.fdl;2
LDEVL1-[ANDY]> dir 1201*.fdl
Directory GRP$MDEV0:[ANDY]
1201.FDL;2 5/8
2-MAY-1996 10:31:04.58 (RWED,RWED,RWED,RWED)
1201.FDL;1 5/8
11-APR-1996 09:17:27.67 (RWED,RWED,RWED,RWED)
1201_INPUT.FDL;2 13/16
6-MAY-1996 16:13:07.16 (RWED,RWED,RWED,RWED)
1201_INPUT.FDL;1 13/16
6-MAY-1996 16:04:45.17 (RWED,RWED,RWED,RWED)
Total of 4 files, 36/48 blocks.
******************************************************************************
***********************************************
LDEVL1-[ANDY]> type 1201_input.fdl;2
IDENT " 6-MAY-1996 16:13:07 OpenVMS ANALYZE/RMS_FILE Utility"
SYSTEM
SOURCE OpenVMS
FILE
ALLOCATION 4072
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
CLUSTER_SIZE 4
CONTIGUOUS no
EXTENSION 156
FILE_MONITORING no
GLOBAL_BUFFER_COUNT 0
NAME "GRP$MDEV0:[ANDY]1201.DAT;1"
ORGANIZATION indexed
OWNER [MDEV,MDEV_ANDY]
PROTECTION (system:RWED, owner:RWED, group:RWED,
world:RWED)
RECORD
BLOCK_SPAN yes
CARRIAGE_CONTROL carriage_return
FORMAT fixed
SIZE 174
AREA 0
ALLOCATION 2640
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 156
AREA 1
ALLOCATION 18
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 6
AREA 2
ALLOCATION 1413
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 3
EXTENSION 96
KEY 0
CHANGES no
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DATA_AREA 0
DATA_FILL 100
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 1
NAME "l1_record_key"
NULL_KEY no
PROLOG 3
SEG0_LENGTH 14
SEG0_POSITION 2
TYPE string
KEY 1
CHANGES no
DATA_KEY_COMPRESSION yes
DATA_AREA 2
DATA_FILL 100
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_user_ident"
NULL_KEY no
SEG0_LENGTH 10
SEG0_POSITION 16
TYPE string
KEY 2
CHANGES no
DATA_KEY_COMPRESSION no
DATA_AREA 2
DATA_FILL 100
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_system_code"
NULL_KEY no
SEG0_LENGTH 2
SEG0_POSITION 26
TYPE string
KEY 3
CHANGES no
DATA_KEY_COMPRESSION no
DATA_AREA 2
DATA_FILL 100
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_sub_system_code"
NULL_KEY no
SEG0_LENGTH 3
SEG0_POSITION 28
TYPE string
KEY 4
CHANGES no
DATA_KEY_COMPRESSION yes
DATA_AREA 2
DATA_FILL 100
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_client_ref_no"
NULL_KEY no
SEG0_LENGTH 20
SEG0_POSITION 31
TYPE string
KEY 5
CHANGES no
DATA_KEY_COMPRESSION no
DATA_AREA 2
DATA_FILL 100
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_contact_initials"
NULL_KEY no
SEG0_LENGTH 3
SEG0_POSITION 51
TYPE string
ANALYSIS_OF_AREA 0
RECLAIMED_SPACE 0
ANALYSIS_OF_AREA 1
RECLAIMED_SPACE 0
ANALYSIS_OF_AREA 2
RECLAIMED_SPACE 0
ANALYSIS_OF_KEY 0
DATA_FILL 98
DATA_KEY_COMPRESSION 48
DATA_RECORD_COMPRESSION 61
DATA_RECORD_COUNT 16452
DATA_SPACE_OCCUPIED 2622
DEPTH 2
INDEX_COMPRESSION 24
INDEX_FILL 58
INDEX_SPACE_OCCUPIED 18
LEVEL1_RECORD_COUNT 437
MEAN_DATA_LENGTH 174
MEAN_INDEX_LENGTH 16
ANALYSIS_OF_KEY 1
DATA_FILL 77
DATA_KEY_COMPRESSION -4
DATA_RECORD_COUNT 133
DATA_SPACE_OCCUPIED 300
DEPTH 1
DUPLICATES_PER_SIDR 123
INDEX_COMPRESSION 24
INDEX_FILL 31
INDEX_SPACE_OCCUPIED 3
LEVEL1_RECORD_COUNT 51
MEAN_DATA_LENGTH 878
MEAN_INDEX_LENGTH 12
ANALYSIS_OF_KEY 2
DATA_FILL 93
DATA_KEY_COMPRESSION 0
DATA_RECORD_COUNT 88
DATA_SPACE_OCCUPIED 243
DEPTH 1
DUPLICATES_PER_SIDR 186
INDEX_COMPRESSION 0
INDEX_FILL 3
INDEX_SPACE_OCCUPIED 3
LEVEL1_RECORD_COUNT 9
MEAN_DATA_LENGTH 1313
MEAN_INDEX_LENGTH 4
ANALYSIS_OF_KEY 3
DATA_FILL 93
DATA_KEY_COMPRESSION 0
DATA_RECORD_COUNT 154
DATA_SPACE_OCCUPIED 246
DEPTH 1
DUPLICATES_PER_SIDR 106
INDEX_COMPRESSION 0
INDEX_FILL 8
INDEX_SPACE_OCCUPIED 3
LEVEL1_RECORD_COUNT 23
MEAN_DATA_LENGTH 753
MEAN_INDEX_LENGTH 5
ANALYSIS_OF_KEY 4
DATA_FILL 88
DATA_KEY_COMPRESSION 58
DATA_RECORD_COUNT 1016
DATA_SPACE_OCCUPIED 279
DEPTH 1
DUPLICATES_PER_SIDR 15
INDEX_COMPRESSION 44
INDEX_FILL 48
INDEX_SPACE_OCCUPIED 3
LEVEL1_RECORD_COUNT 60
MEAN_DATA_LENGTH 124
MEAN_INDEX_LENGTH 22
ANALYSIS_OF_KEY 5
DATA_FILL 85
DATA_KEY_COMPRESSION 0
DATA_RECORD_COUNT 218
DATA_SPACE_OCCUPIED 270
DEPTH 1
DUPLICATES_PER_SIDR 74
INDEX_COMPRESSION 0
INDEX_FILL 14
INDEX_SPACE_OCCUPIED 3
LEVEL1_RECORD_COUNT 42
MEAN_DATA_LENGTH 533
MEAN_INDEX_LENGTH 5
*******************************************************************************
**********************************************
!!! I used $ create file.dat /fdl=1201.fdl
!!! to make the 'empty' 1201_Ln.DAT files that
!!! the SQL insert is applied to.
LDEVL1-[ANDY]> type 1201.fdl
IDENT " 6-FEB-1996 15:55:02 VAX-11 FDL Editor"
SYSTEM
SOURCE "VAX/VMS"
FILE
ORGANIZATION indexed
RECORD
CARRIAGE_CONTROL carriage_return
FORMAT fixed
SIZE 174
AREA 0
ALLOCATION 612
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 156
AREA 1
ALLOCATION 12
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 6
AREA 2
ALLOCATION 357
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 3
EXTENSION 96
KEY 0
CHANGES no
DATA_AREA 0
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 1
NAME "l1_record_key"
PROLOG 3
SEG0_LENGTH 14
SEG0_POSITION 2
TYPE string
KEY 1
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_user_ident"
SEG0_LENGTH 10
SEG0_POSITION 16
TYPE string
KEY 2
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_system_code"
SEG0_LENGTH 2
SEG0_POSITION 26
TYPE string
KEY 3
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_sub_system_code"
SEG0_LENGTH 3
SEG0_POSITION 28
TYPE string
KEY 4
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_client_ref_no"
SEG0_LENGTH 20
SEG0_POSITION 31
TYPE string
KEY 5
CHANGES no
DATA_AREA 2
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 2
NAME "l1_contact_initials"
SEG0_LENGTH 3
SEG0_POSITION 51
TYPE string
****************************************************************
*************************************************************
LDEVL1-[ANDY]> type 1201.cdo
!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
! !
! done for l1 l2 l3 l4 l6 l7 l8 !
! .. !
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L1_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L2_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L3_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L4_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L6_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L7_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L8_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD Lx_RECORD_TYPE
DESCRIPTION IS /* Logon/Logoff record type "L1" */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD Lx_date
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD Lx_time
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD Lx_user_ident
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD Lx_system_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD Lx_sub_system_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
DEFINE FIELD Lx_client_ref_no
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 20 CHARACTERS.
DEFINE FIELD Lx_contact_initials
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L1_last_date
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD L1_last_time
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD L1_logoff_flag
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 1 CHARACTERS.
DEFINE FIELD L1_maynelaw_session_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L1_maynelaw_rate_per_minute
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L1_elapsed_minutes
DESCRIPTION IS /* */
DATATYPE IS signed word .
DEFINE FIELD L1_filler_1
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 80 CHARACTERS.
DEFINE FIELD L1_filler_2
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 15 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L2_string
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 80 CHARACTERS.
DEFINE FIELD L2_module
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 40 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L3_authority_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD L3_account_name
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 40 CHARACTERS.
DEFINE FIELD L3_items_or_pea_no
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD L3_form_number
DESCRIPTION IS /* */
DATATYPE IS signed word .
DEFINE FIELD L3_maynelaw_form_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L3_authority_form_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L3_crr_no
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD L3_form_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L3_request_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
DEFINE FIELD L3_broker_form_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L3_retrieved
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 1 CHARACTERS.
DEFINE FIELD L3_filler
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 34 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L4_form_number
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L4_ticket_number
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L4_lto_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L4_maynelaw_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L4_description
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 40 CHARACTERS.
DEFINE FIELD L4_form_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L4_request_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
DEFINE FIELD L4_exp_no_prop
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 1 CHARACTERS.
DEFINE FIELD L4_filler
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 58 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L6_last_date
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 6 CHARACTERS.
DEFINE FIELD L6_last_time
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD L6_cac_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L6_maynelaw_rate_per_minute
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L6_elapsed_bintim
DESCRIPTION IS /* */
DATATYPE IS signed QUADWORD .
DEFINE FIELD L6_filler
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 90 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L7_asc_charge
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L7_broker_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L7_lawpoint_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L7_narrative
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 40 CHARACTERS.
DEFINE FIELD L7_variant
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 68 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DEFINE FIELD L8_reference_no
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 8 CHARACTERS.
DEFINE FIELD L8_lawpoint_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_authority_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_authority_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD L8_stationer_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_connect_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_broker_fee
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_broker_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE FIELD L8_description
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 40 CHARACTERS.
DEFINE FIELD L8_form_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L8_request_type
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 3 CHARACTERS.
DEFINE FIELD L8_sbn_service_code
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 2 CHARACTERS.
DEFINE FIELD L8_form_number
DESCRIPTION IS /* */
DATATYPE IS signed longword .
DEFINE FIELD L8_filler
DESCRIPTION IS /* */
DATATYPE IS TEXT SIZE IS 21 CHARACTERS.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!DEFINE FIELD DATE_DOM
! DESCRIPTION IS /* standard definition for complete dates */
! DATATYPE IS DATE.
!
!DEFINE FIELD SALARY_DOM
! DESCRIPTION IS /* standard definition of salary */
! DATATYPE IS SIGNED LONGWORD.
!
!DEFINE FIELD YEAR_DOM
! DESCRIPTION IS /* standard definition for year-only date values
*/
! DATATYPE IS SIGNED WORD.
!DEFINE FIELD YEAR_GIVEN BASED ON YEAR_DOM.
!
!DEFINE FIELD LAST_NAME_DOM
! DESCRIPTION IS /* standard definition of last name */
! DATATYPE IS TEXT SIZE IS 14 CHARACTERS.
!DEFINE FIELD LAST_NAME datatype is text size is 14 BASED ON
LAST_NAME_DOM.
!
!DEFINE FIELD SALARY_START
! DESCRIPTION IS /* starting date, is null if = 17-NOV-1858
00:00:00.00 */
! BASED ON DATE_DOM.
DEFINE RECORD LIS01201_l1_RECORD .
L1_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l1_last_date.
l1_last_time.
l1_logoff_flag.
l1_maynelaw_session_charge.
l1_maynelaw_rate_per_minute.
l1_elapsed_minutes.
l1_filler_1.
l1_filler_2.
END.
DEFINE RECORD LIS01201_L2_RECORD .
L2_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
L2_string.
L2_module.
END.
DEFINE RECORD LIS01201_L3_RECORD .
L3_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
L3_authority_code.
L3_account_name.
l3_items_or_pea_no.
l3_form_number.
l3_maynelaw_form_charge.
l3_authority_form_charge.
l3_crr_no.
l3_form_type.
l3_request_type.
l3_broker_form_charge.
l3_retrieved.
l3_filler.
END.
DEFINE RECORD LIS01201_L4_RECORD .
L4_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l4_form_number .
l4_ticket_number .
l4_lto_charge .
l4_maynelaw_fee .
l4_description .
l4_form_type .
l4_request_type .
l4_exp_no_prop .
l4_filler .
END.
DEFINE RECORD LIS01201_L6_RECORD .
L6_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l6_last_date .
l6_last_time .
l6_cac_charge .
l6_maynelaw_rate_per_minute .
l6_elapsed_bintim .
l6_filler .
END.
DEFINE RECORD LIS01201_L7_RECORD .
L7_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l7_asc_charge .
l7_broker_fee .
l7_lawpoint_fee .
l7_narrative .
l7_variant .
END.
DEFINE RECORD LIS01201_L8_RECORD .
L8_RECORD_TYPE .
lx_date .
lx_time.
lx_user_ident.
lx_system_code.
lx_sub_system_code.
lx_client_ref_no.
lx_contact_initials.
l8_reference_no .
l8_lawpoint_fee .
l8_authority_fee .
l8_authority_code .
l8_stationer_fee .
l8_connect_fee .
l8_broker_fee .
l8_broker_code .
l8_description .
l8_form_type .
l8_request_type .
l8_sbn_service_code .
l8_form_number .
l8_filler .
END.
DEFINE RMS_DATABASE LIS01201_l1_STORAGE.
RECORD LIS01201_l1_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l2_STORAGE.
RECORD LIS01201_l2_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l3_STORAGE.
RECORD LIS01201_l3_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l4_STORAGE.
RECORD LIS01201_l4_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l6_STORAGE.
RECORD LIS01201_l6_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l7_STORAGE.
RECORD LIS01201_l7_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE LIS01201_l8_STORAGE.
RECORD LIS01201_l8_RECORD.
FILE_DEFINITION
! ORGANIZATION SEQUENTIAL
ORGANIZATION indexed
! FORMAT VARIABLE.
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l1_STORAGE.
RECORD LIS01201_l1_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l2_STORAGE.
RECORD LIS01201_l2_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l3_STORAGE.
RECORD LIS01201_l3_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l4_STORAGE.
RECORD LIS01201_l4_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l6_STORAGE.
RECORD LIS01201_l6_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l7_STORAGE.
RECORD LIS01201_l7_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE RMS_DATABASE normalised_LIS01201_l8_STORAGE.
RECORD LIS01201_l8_RECORD.
FILE_DEFINITION
ORGANIZATION indexed
FORMAT fixed .
END.
DEFINE DATABASE LIS01201_L1
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l1_stoRAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L2
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l2_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L3
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l3_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L4
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l4_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L6
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l6_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L7
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l7_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE LIS01201_L8
DESCRIPTION IS /* NEW LIS01201 */
USING LIS01201_l8_STORAGE
ON GRP$MDEV0:[ANDY]1201.DAT .
DEFINE DATABASE normalised_LIS01201_L1
USING normalised_LIS01201_l1_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L1.DAT .
DEFINE DATABASE normalised_LIS01201_L2
USING normalised_LIS01201_l2_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L2.DAT .
DEFINE DATABASE normalised_LIS01201_L3
USING normalised_LIS01201_l3_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L3.DAT .
DEFINE DATABASE normalised_LIS01201_L4
USING normalised_LIS01201_l4_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L4.DAT .
DEFINE DATABASE normalised_LIS01201_L6
USING normalised_LIS01201_l6_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L6.DAT .
DEFINE DATABASE normalised_LIS01201_L7
USING normalised_LIS01201_l7_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L7.DAT .
DEFINE DATABASE normalised_LIS01201_L8
USING normalised_LIS01201_l8_stoRAGE
ON GRP$MDEV0:[ANDY]1201_L8.DAT .
DEFINE FIELD f1
DATATYPE IS TEXT SIZE IS 10 CHARACTERS.
DEFINE RECORD r1 .
f1 .
end.
DEFINE RMS_DATABASE rmsdb1 .
RECORD r1 .
FILE_DEFINITION
ORGANIZATION SEQUENTIAL
FORMAT fixed .
END.
DEFINE DATABASE t1
USING rmsdb1
ON GRP$MDEV0:[ANDY]t1.DAT .
LDEVL1-[ANDY]> lo
MDEV_ANDY logged out at 6-MAY-1996 16:16:40.11
******************************************************************************
***********************************************
| |||||
| 1054.7 | Some things I saw & some things to try | BROKE::GREEN | Thu May 09 1996 18:06 | 35 | |
Hi Mario, Your situation is different than what I was testing. When you say that an insert takes 11-12 minutes, do you mean just a L1 insert? Or do you mean all of the inserts combined takes 11-12 minutes? My files used 6 keys but my keys and record sizes were smaller than yours, and I used no index compression on my keys. Regarding index compression, your input file has 6 keys and 3 of them use compression, the larger keys. Based on the ANALYZE/RMS/FDL analysis the index compression on key 1 (lx_user_ident) should be removed. You can see this by the negative value associated with data_key_compression in the Analysis of Key 1 section of your ANALYZE/RMS/FDL file. Refer to page FDL-4 of the VMS File Definition Language Facility Manual as this is where I saw this mentioned. Your output files use index compression everywhere. Some of these keys are only 2 and 3 bytes in length. I don't think that they need compression. This looks like unnecessary overhead to me. Indexes with RMS are like Rdb in that they are great for read operations, not so great for insertions. Are 6 keys really necessary? Are they all being used on a regular basis? The record compression looks like it's helping you. It probably really helps with those large filler fields you have at the end of each record. I'd keep this attribute enabled. Could you please run a performance test for us? Make the output files sequential with no compression and tell us how long it takes. We need to see this information before offering suggestions. Thanks, Don | |||||
| 1054.8 | Seems to have worked | ORAREP::GIDDAY::VASSIL | Tue May 14 1996 03:54 | 14 | |
Hi Don,
Sorry about the delay in getting back to you, the customer only just
completed his performance test.
By making his output files sequential with no compression seems, to
have solved his problem. He now says that his performance has increased
to what he deems an acceptable level.
Many thanks for your help Don,
Cheers,
Mario.
| |||||