Home » Categories » Solutions by Business Process » Profiler

G2 – Extract Records between Database Files

Datafile Software

G2 – Extract Records between Database Files

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.

Custom Fields
  • Release ID: Standard
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Copy Items Back to Lookup File (9902)
Viewed 1232 times since Fri, Jun 29, 2012
General Details
Viewed 1212 times since Thu, Jun 28, 2012
G - Global Update
Viewed 1133 times since Thu, Jun 28, 2012
User-Defined Reports - Default Printer Settings
Viewed 1301 times since Mon, Jun 11, 2012
User Defined Reports - Email Links
Viewed 1188 times since Wed, Jun 13, 2012
Disk Utilities
Viewed 1223 times since Thu, Jun 28, 2012
R – Rebuild Indexes
Viewed 694 times since Fri, Jun 29, 2012
Database Utilities
Viewed 1261 times since Fri, Jun 29, 2012
EDI Messages and Files
Viewed 1406 times since Thu, Jul 5, 2012
User Defined Report – Omit Print of Item
Viewed 781 times since Wed, Jun 13, 2012