The second global update definition allows you to extract
(selected items from) records in one database and append them to another
database.
Extract Data into which Database Number
The database selected within the ProFiler menu is the
database records are to be extracted from. Here you are selecting the
database you wish to add the records to.The database you select must be one of the eight files within the
ProFiler application (and can’t be the extract ‘from’ file); <F4-Select>
is available if required.
Append Records
Set whether the new records are to be appended after existing records. If ‘N’ then existing records are cleared on the ‘to’ file before the extract begins.
Next you match the data items to be extracted. The screen is similar to the first update definition type, however it is simpler definition with you matching the items on the ‘from’ database with those on the ‘to’ database. Data item must be consistent in type and size.
As with the first update you can define up to 160 entries on
the definition, 12 per screen. Once the
screen is completed (either because the 12th item is set or via use
of the <End> or <Page-Down> key you will be prompted for the entry
number to edit next. This defaults to
the next entry but can be amended as required.<Esc> completes the data matching process and moves to the next
section of the update definition.
Item Name to Flag Record as Updated?
Select a data item from the input file, X-type or ?-type, that
is to be updated with a ‘Y’ to show the record has been processed. This is then used, by default, in subsequent
runs of the global update to not process the record again
Omit File Lock on Database
When running a global update the system locks out the database preventing other operators accessing it whilst the update is proceeding to ensure data integrity. As an alternative to a file lock when a database is in general use you can apply a record lock, the system then proceeds on a record-by-record basis locking a record, updating the changes, releasing the record lock and then moving on to the next record. Locking individual records will however increase the time taken to process the global update.
If record locking you should use the ‘flag’ option above to
identify whether a record has been processed or not (the update may be rejected
for individual records if another operator is using them at the time).
Omit File Lock on Output Database
Only applicable if appending records to the database
on the extract this option applies record locking to the output file as records
are added. Note again that this will
increase the time the update takes.
Omit Disk Flushing on Input Database
Only applicable if using record locking. During the update is technically possible for
a record to be changed by another operator in between the system deciding
whether a record can be extracted (based on selection criteria) and then
locking it for the update. To compensate
the system ‘flushes’ the record from memory and re-reads the record after it
has been locked. However, this increases
the time taken for the update as each record is read twice and, depending on
the database being updated from, maybe unnecessary for the update definition
(the values that can be changed not being extracted in this definition). Set to omit the record ‘flush’ as required.
Maintain Links with Other Database
Only applicable if appending records and then only if the ProFiler Plus accessory is use.
When adding records to a database the ‘to’ database may be
the detail file for another database.This option allows you to maintain the master / detail record pointers
for two alternate master databases. If this
option is set, at the end of this parameter screen, a pop-up screen will allow
you to define the relationship to both files.
Link to Database No (1-8)
Select the database which is to be used as the ‘alternate’ master records database.<F4-Select> shows the list of defined databases.
Input Item for Lookup
Select the data item on the ‘extract to’ file which contains the index value for the alternate master database – for example, the account code.
Lock Out at Record Level
Set to apply record locks on the alternate master file on a record-by-record basis. Leave blank to exclusively lock the file.
Link DB: First Pointer
Link DB: Last Pointer
Link DB: Entry Count
Reference the data items on the alternate master file that contain the first/last detail record pointers to the ‘extract-to’ file. The entry count parameter is optional and would hold the number of detail records attached. The first/last pointer fields are Y-type (usually six characters); the entry count is usually an N-type field.
New DB: Link Recno
New DB: Next Pointer
New DB: Prev Pointer
New DB: Entry Number
Reference the data items on the extract-to file that maintain the record pointers to the alternate master file. ‘Link Recno’, ‘Next Pointer’ and ‘Prev Pointer’ are Y-type data items (again usually six characters), and the ‘Entry Number’ is an N-type field.
Copy Down: From / To
This option allows you to define up to twelve copy items from the alternate master file to the ‘extract-to’ file. Data item types must be compatible.
Copy Up: From / To
This option allows you to define up to twelve copy items to the alternate master file from the ‘extract-to’ file. Again data item types must be compatible.
Add: From / To
This option allows you to accumulate values from the ‘extract-to’ detail database to the alternate master database. Update items are restricted to K, L and M item types.
Subtract: From / To
This option allows you to subtract values recorded on the
‘extract-to’ detail database to the alternate master database. Again update items are restricted to K, L and
M item types.
Notes
After defining the relationship for the first alternate master database a second screen is displayed with identical options to the above to define any relationship to a second alternate master database.
Explode Assemblies
into Full Detail Records
Again, this option is only applicable when appending records and the ProFiler Plus accessory is in use.
This utility is of use where the extract-from database contains details for assembly items and you require the extract-to database to contain details from the components related to these assemblies. You may use this to generate component requirements for stock ordering (for example).
If this option is set, at the end of this parameter screen, a pop-up screen will allow you to define the relationship to the assembly and component files.
Assembly HDR DB No
Assembly DET DB No
Enter the file numbers within the application location of files for the stock assembly and component databases.
Input Item for Assembly Code
Select the data item on the extract-from database that holds the assembly code to be exploded.
Input Item for Quantity
Select the data item on the extract-from database that holds the quantity of the assembly required.This is used in determining the quantity required of the component.
Assembly HDR Item: First
Assembly DET Item: Stock
Assembly DET Item: Qty
Assembly DET Item: Next
These parameters ask you to reference the data items on the assembly and component files. On the assembly file you are asked for the first-pointer, on the component file you are asked for the stock code, quantity and next-pointer.
Output Item for Stock
Select the data item on the extract-to database that is to be updated with the component stock record.
Output Item for Quantity
Select the data item on the extract-to database that is to be updated with the quantity of component required (input item qty x assembly detail item qty).
Custom Point No / Parameter
Leave as 0 unless otherwise advised.
Notes
This procedure only supports one level of assembly.
The extract-to database can contain many records for each extract-from database – the standard extract ‘copy’ items will be applied to each of the ‘to’ records.
Custom Point No (if applicable)
Enter custom parameter number as advised by your Datafile reseller otherwise leave blank. (This parameter applies to the extract definition as a whole as opposed to the above which only applies to the stock assembly explosion).
The next section allows you to set and use record number
‘slots’ within the update. These ‘slots’
are counters stored against the database and can be used to increase the speed
of an update, so historical records aren’t reprocessed and to speed subsequent
follow-on procedures.
Each database has 10 slots that can be used – but note that the standard
applications will be using these slot numbers also (the sales transaction file
for example uses slots 1-8 as part of general processing) so care should be
taken when using this option on standard application databases.
Store Highest Record Number Updated in Which Slot (1-10)
Set the slot number to be updated with the highest record processed. Once saved this can be used in the next prompt as the starting point for the next update run.
Start from Record Number in which Slot Number (1-10)
Set the slot number for which to start processing the update from (in fact it starts from this value plus ‘1’ to start on the next record). It is usual to set both this and the previous parameter to the same slot number where no subsequent procedures are to use the same set of records.
Store First Record Processed in Slot Number (1-10)
When running a series of procedures that are going to be
reporting/updating the same set of data it can speed subsequent processes if
you record the first record processed on the update into the slot numbers. This can then be used as the starting point
in subsequent procedures.
Run-Time Prompts
The next section allows three-lines of text that are to be displayed to the operator recording the procedure that is to be run and show any required warning messages.
The final section allows you to define criteria that are to
be applied to the update as a whole.
Specify Search Criteria
Set up to six lines of criteria that are to be applied to
the update definition – no record will be processed that doesn’t match the
criteria specified here. The criteria
are defined in the normal manner.
Allow Runtime Changes to Search
Set if the operator is to be allowed to change criteria at
run-time
Save Any Changes
If the operator is allowed to make changes to the criteria set to save these changes for use on the next update run.
After the above are confirmed you are prompted
Respond ‘No’ to save the update definition, ‘Yes’ to go back to the data items to amend the definition or <Esc> to abandon without saving.
Article ID: 1206
Created On: Thu, Jun 28, 2012 at 3:52 PM
Last Updated On: Tue, Jun 20, 2023 at 5:32 PM
Online URL: https://kb.datafile.co.uk/article/g2-â€-extract-records-between-database-files-1206.html