This page (revision-31) was last changed on 26-Nov-2021 10:22 by Kevin Higgs

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
31 26-Nov-2021 10:22 13 KB Kevin Higgs to previous
30 26-Nov-2021 10:22 14 KB Karen Parrott to previous | to last
29 26-Nov-2021 10:22 14 KB Karen Parrott to previous | to last
28 26-Nov-2021 10:22 13 KB Kevin Higgs to previous | to last
27 26-Nov-2021 10:22 13 KB kparrott to previous | to last
26 26-Nov-2021 10:22 14 KB kparrott to previous | to last
25 26-Nov-2021 10:22 13 KB kparrott to previous | to last
24 26-Nov-2021 10:22 13 KB kparrott to previous | to last
23 26-Nov-2021 10:22 13 KB kparrott to previous | to last
22 26-Nov-2021 10:22 13 KB kparrott to previous | to last
21 26-Nov-2021 10:22 12 KB kparrott to previous | to last

Page References

Incoming links Outgoing links
LMTD
LMTD

Version management

Difference between version and

At line 1 added one line
[{TableOfContents }]
At line 3 added one line
At line 4 changed one line
There are two methods of loading external data using the LMTD function; Table Data Loader logic (no interface) and DIF Data Loader logic (with interface).
There are two methods of loading external data using the LMTD function; DIF Data Loader logic (with interface definition) and Table Data Loader logic (no interface).
\\ \\
%%info
__NOTE:__ As of the 5.04.00.03 Release all bypassed records and duplicated records are no longer considered errors. They are printed in bypassed file and on the report output if trace level is set to 6 or greater. \\
\\
Exception Level 1 will show only report summary. (#records processed, bypassed and duplicated and # record inserted/updated).%%\\
At line 6 changed 2 lines
!Loading External Data without an Interface
The technology used by LMTD when an interface format has not been defined is known as Table Data Loader. This technology reads table data from a comma-delimited text file and inserts or updates valid records in the file into the destination table in the database.
!!Load External Data with an Interface
The technology used by LMTD when an Interface format is used is called Data Transfer Facility (DTF). This is a sophisticated program, which enables you to transfer data between different databases by extracting data from one database and loading it into another. The DTF is fully capable of multiple table load/extracts and foreign key resolution.
At line 9 changed one line
To run the Table Data Loader logic using LMTD, values for the source file name, destination table and character delimiter should be provided. The interface code field should be left blank. When loading simple, single tables without the need for Foreign Key resolution, this method is recommended.
The DTF is comprised of two main components: the Data Extractor and the Data Loader. The Data Extractor, as the name suggests, extracts table data based on a pre-defined extract configuration from a database and writes the data to either a binary or human-readable XML file. The Data Loader processes these extracted files and loads the table data by inserting or updating records into the appropriate tables in the database.
At line 11 changed one line
The drawback of not using an interface format method is that the Table Data Loader technology is not able to transfer data from one database to another as conflicting ID values cause foreign keys to not be found. If foreign keys are involved in the data you wish to load, it is recommended to use the LMTD interface format method instead. This method is described below.
The DIF Data Loader uses the Data Loader component of the DTF in order to take advantage of its sophisticated multiple table load/extract and foreign key resolution capabilities. The DIF Data Loader emulates the functionality of the DTF Data Extractor. Instead of extracting table data from a database, the DIF Data Loader extracts table data, based on an interface format, from a source file and passes the data onto the DTF Data Loader to be processed.
At line 13 changed one line
The following will review the necessary set up and process flow for running the LMTD to load data using a source file to load directly into a table.
The following will review the necessary steps required to use LMTD to load data using a source file based on an interface format.
At line 15 changed 2 lines
;Step 1 - Create File
;:The first line of the file must contain the header of column names that identifies what is to be loaded. The order of data within the file must match the order in the header record. The column names can be in lower or upper case.
;Step 1 - Define Interface Format in [IDIF]
The source file layout needs to be defined as an interface format in [IDIF]. The [DATA LOADER IDIF FORMAT] page outlines what is required to be defined in [IDIF]. Each line of the source file is read and divided into field values based on the field information table in this screen.
At line 25 added one line
At line 19 changed one line
;:Within the IMST screen, the administrator must define where the Extract and Load files are located and where the load functions are to write the exception files to. These locations are used by the functions to create and retrieve information. The customer can modify these values to represent any directory on their system, which is convenient.
Within the [IMST] screen, the administrator must define where the Extract and Load files are located and where the load functions are to write the exception files to. These locations are used by the functions to create and retrieve information. The customer can modify these values to represent any directory on their system, which is convenient.
At line 21 changed one line
;:The extract directory is defined using the [EXTRCT DATA FLDR] preference. The load directory is defined using the [LOAD DATA FLDR] preference. The [ATTACHMENTS FLDR] must be specified so the load functions know where to write the report exceptions log to.
The extract directory is defined using the [EXTRCT DATA FLDR|EXTRCT DATA FLDR(System_Preference)] preference. The load directory is defined using the [LOAD DATA FLDR|LOAD DATA FLDR(System_Preference)] preference. The [ATTACHMENTS FLDR|ATTACHMENTS FLDR(System_Preference)] must be specified so the load functions know where to write the report exceptions log to.
At line 23 changed one line
;:If the full path is not specified here, then the path used by the HL Application Directory will apply. The user who is running the LMTD must have Read/Write/Create access to this directory on the server.
If the full path is not specified here, then the path used by the HL Application Directory will apply. The user who is running the LMTD must have Read/Write/Create access to this directory on the server.
At line 34 added one line
Launch the LMTD function specifying the appropriate parameters for the DIF Data Loader as indicated below.
At line 36 added one line
At line 28 changed one line
;:During the load process, all non-fatal exceptions as well as the number of insertions and updates (by table) are logged in the execution run logs. After the load process completes, a report is written to a text file indicating the number of records read, number of records bypassed, number of records processed, number of records that were bypassed because an identical one already existed in the table, and number of records updated/inserted by table. The report will display all non-fatal business exceptions encountered during the load with a record number indicating for which record the exception occurred. The report will also display the amount of time that was taken for the load process to complete.
During the load process, all non-fatal exceptions as well as the number of insertions and updates (by table) are logged in the execution run logs. After the load process completes, a report is written to a text file indicating the number of records read, number of records bypassed, number of records processed, number of records that were bypassed because an identical one already existed in the table, and number of records updated/inserted by table. The report will display all non-fatal business exceptions encountered during the load with a record number indicating for which record the exception occurred. The report will also display the amount of time that was taken for the load process to complete.
At line 30 changed one line
;:There are 2 tiers of exceptions that can be generated during the load process. The first tier involves extracting and building data from the source file and the second involves processing the extracted data. However, there will only be ONE exception for each failed load. Basically, if the record fails to build data correctly, the program will not bother loading it so you will never see 2 exceptions for a single failed record load.
There are 2 tiers of exceptions that can be generated during the load process. The first tier involves extracting and building data from the source file and the second involves processing the extracted data. However, there will only be ONE exception for each failed load. Basically, if the record fails to build data correctly, the program will not bother loading it so you will never see 2 exceptions for a single failed record load.
At line 32 changed one line
;:Exceptions generated while extracting and building data from the source file will indicate that the exception occurred while "reading data from file". Exceptions generated while processing the extracted data will indicate that the exception occurred while "loading data into the DB".
Exceptions generated while extracting and building data from the source file will indicate that the exception occurred while "reading data from file". Exceptions generated while processing the extracted data will indicate that the exception occurred while "loading data into the DB".
At line 34 changed one line
;:When a Null Pointer Exception (NPE) occurs during the load process, the entire stack trace to the NPE is captured and displayed as part of the exception message in the load report.
When a Null Pointer Exception (NPE) occurs during the load process, the entire stack trace to the NPE is captured and displayed as part of the exception message in the load report.
At line 36 changed one line
;:The report text file will be located in the directory which was specified in the [IMST] preference [LOAD DATA FLDR].
The report text file will be located in the directory which was specified in the [IMST] preference [LOAD DATA FLDR|LOAD DATA FLDR(System_Preference)].
At line 39 changed one line
;:If there are no exceptions in the report, you should navigate to the table where the data was loaded and verify that the load was done correctly.
If there are no exceptions in the report, you should navigate to the table(s) where the data was loaded and verify that the load was done correctly.
At line 42 changed 2 lines
!Load External Data with an Interface
The technology used by LMTD when an Interface format is used is called Data Transfer Facility (DTF). This is a sophisticated program, which enables you to transfer data between different databases by extracting data from one database and loading it into another. The DTF is fully capable of multiple table load/extracts and foreign key resolution.
!!Loading External Data without an Interface
The technology used by LMTD when an interface format has not been defined is known as Table Data Loader. This technology reads table data from a comma-delimited text file and inserts or updates valid records in the file into the destination table in the database.
At line 45 changed one line
The DTF is comprised of two main components: the Data Extractor and the Data Loader. The Data Extractor, as the name suggests, extracts table data based on a pre-defined extract configuration from a database and writes the data to either a binary or human-readable XML file. The Data Loader processes these extracted files and loads the table data by inserting or updating records into the appropriate tables in the database.
To run the Table Data Loader logic using LMTD, values for the source file name, destination table and character delimiter should be provided. The interface code field should be left blank. When loading simple, single tables without the need for Foreign Key resolution, this method is recommended.
At line 47 changed one line
The DIF Data Loader uses the Data Loader component of the DTF in order to take advantage of its sophisticated multiple table load/extract and foreign key resolution capabilities. The DIF Data Loader emulates the functionality of the DTF Data Extractor. Instead of extracting table data from a database, the DIF Data Loader extracts table data, based on an interface format, from a source file and passes the data onto the DTF Data Loader to be processed.
The drawback of not using an interface format method is that the Table Data Loader technology is not able to transfer data from one database to another as conflicting ID values cause foreign keys to not be found. If foreign keys are involved in the data you wish to load, it is recommended to use the LMTD interface format method instead. This method is described below.
At line 49 changed one line
The following will review the necessary steps required to use LMTD to load data using a source file based on an interface format.
The following will review the necessary set up and process flow for running the LMTD to load data using a source file to load directly into a table.
At line 51 changed 2 lines
;Step 1 - Define Interface Format in [IDIF]
;:The source file layout needs to be defined as an interface format. The interface format is created and modified in the [IDIF] screen. Each line of the source file is read and divided into field values based on the field information table in this screen.
;Step 1 - Create File
The first line of the file must contain the header of column names that identifies what is to be loaded. The order of data within the file must match the order in the header record. The column names can be in lower or upper case.
At line 54 removed 3 lines
;:For this method of LMTD, only the 'Rec#', 'Field#', 'Name', 'Beg Posn' and 'End Posn' columns are used. The 'Name' column of the table specifies the table and column (or foreign key column) while the 'Beg Posn' and 'End Posn' columns determine which characters of the line in the file represent the field value.
;:The [LMTD INTERFACE FORMAT] page outlines what is required to be defined in [IDIF].
At line 58 changed one line
;:Within the IMST screen, the administrator must define where the Extract and Load files are located and where the load functions are to write the exception files to. These locations are used by the functions to create and retrieve information. The customer can modify these values to represent any directory on their system, which is convenient.
Within the IMST screen, the administrator must define where the Extract and Load files are located and where the load functions are to write the exception files to. These locations are used by the functions to create and retrieve information. The customer can modify these values to represent any directory on their system, which is convenient.
At line 60 changed one line
;:The extract directory is defined using the [EXTRCT DATA FLDR] preference. The load directory is defined using the [LOAD DATA FLDR] preference. The [ATTACHMENTS FLDR] must be specified so the load functions know where to write the report exceptions log to.
The extract directory is defined using the [EXTRCT DATA FLDR|EXTRCT DATA FLDR(System_Preference)] preference. The load directory is defined using the [LOAD DATA FLDR|LOAD DATA FLDR(System_Preference)] preference. The [ATTACHMENTS FLDR|ATTACHMENTS FLDR(System_Preference)] must be specified so the load functions know where to write the report exceptions log to.
At line 62 changed one line
;:If the full path is not specified here, then the path used by the HL Application Directory will apply. The user who is running the LMTD must have Read/Write/Create access to this directory on the server.
If the full path is not specified here, then the path used by the HL Application Directory will apply. The user who is running the LMTD must have Read/Write/Create access to this directory on the server.
At line 72 added one line
Launch the LMTD function specifying the appropriate parameters for the Table Data Loader as indicated below.
At line 67 changed one line
;:During the load process, all non-fatal exceptions as well as the number of insertions and updates (by table) are logged in the execution run logs. After the load process completes, a report is written to a text file indicating the number of records read, number of records bypassed, number of records processed, number of records that were bypassed because an identical one already existed in the table, and number of records updated/inserted by table. The report will display all non-fatal business exceptions encountered during the load with a record number indicating for which record the exception occurred. The report will also display the amount of time that was taken for the load process to complete.
During the load process, all non-fatal exceptions as well as the number of insertions and updates (by table) are logged in the execution run logs. After the load process completes, a report is written to a text file indicating the number of records read, number of records bypassed, number of records processed, number of records that were bypassed because an identical one already existed in the table, and number of records updated/inserted by table. The report will display all non-fatal business exceptions encountered during the load with a record number indicating for which record the exception occurred. The report will also display the amount of time that was taken for the load process to complete.
At line 69 changed one line
;:There are 2 tiers of exceptions that can be generated during the load process. The first tier involves extracting and building data from the source file and the second involves processing the extracted data. However, there will only be ONE exception for each failed load. Basically, if the record fails to build data correctly, the program will not bother loading it so you will never see 2 exceptions for a single failed record load.
There are 2 tiers of exceptions that can be generated during the load process. The first tier involves extracting and building data from the source file and the second involves processing the extracted data. However, there will only be ONE exception for each failed load. Basically, if the record fails to build data correctly, the program will not bother loading it so you will never see 2 exceptions for a single failed record load.
At line 71 changed one line
;:Exceptions generated while extracting and building data from the source file will indicate that the exception occurred while "reading data from file". Exceptions generated while processing the extracted data will indicate that the exception occurred while "loading data into the DB".
Exceptions generated while extracting and building data from the source file will indicate that the exception occurred while "reading data from file". Exceptions generated while processing the extracted data will indicate that the exception occurred while "loading data into the DB".
At line 73 changed one line
;:When a Null Pointer Exception (NPE) occurs during the load process, the entire stack trace to the NPE is captured and displayed as part of the exception message in the load report.
When a Null Pointer Exception (NPE) occurs during the load process, the entire stack trace to the NPE is captured and displayed as part of the exception message in the load report.
At line 75 changed one line
;:The report text file will be located in the directory which was specified in the [IMST] preference [LOAD DATA FLDR].
The report text file will be located in the directory which was specified in the [IMST] preference [LOAD DATA FLDR|LOAD DATA FLDR(System_Preference)].
At line 78 changed one line
;:If there are no exceptions in the report, you should navigate to the table(s) where the data was loaded and verify that the load was done correctly.
If there are no exceptions in the report, you should navigate to the table where the data was loaded and verify that the load was done correctly.
At line 81 removed one line
At line 86 changed 9 lines
||Report Parameters||
|Server File| This field provides the name of the file that has been placed on the server in the \\directory specified in the [IMST] record [LOAD DATA FLDR].
|Local File| This field allows you to upload a local source file for loading into the database \\(similar to that of WW Reports in EP). To do this, you must populate the 'Local File'\\ field by selecting the local source file using the file chooser. When selecting a local\\source file to load, the 'Server File' field MUST be blank.
|Allow Update|Lexicon value of YES or NO\\ Selecting "NO" disallows the updating of existing records in the database. If there \\are new records in the load file, the program will still perform an insertion of these \\records into the destination table. In short, selecting "NO" for Allow Update does \\not allow you to run the program in trial as it only prevents existing records in the\\database from being updated. \\Selecting "YES" will update existing records in the database with changes found in \\the load file.
|Allow Insert| Lexicon value of YES or NO\\By selecting "NO", LMTD will not load any new records into the specified table.\\By selecting "YES", new records found in the load file will be inserted into the\\specified table.
|Exception Level| Lexicon value of 0 to 9. \\This field allows you to specify the level of trace you wish to see in the report.
|Interface Code| If using the Table Data Loader logic (no interface), leave this blank. \\If using the DIF Data Loader logic (with interface) an interface must be specified to load a source file.\\You can select multiple interfaces if you have to run a particular source file multiple\\times using different interfaces. If the source file has thousands of records\\suggested to run the LMTD multiple times selecting only one interface code each\\time otherwise there may be an impact on performance.
|File Format| If using the Table Data Loader (no interface) the field defaults to Character-delimited as the Table\\ Data Loader method only supports Character-delimited files. \\If using the DIF Data Loader (interface format) the user may select either Character-delimited or Fixed Position.
|Destination Table| This field identifies the table that will be inserted or updated. This is a mandatory \\parameter for the Table Data Loader method.\\When loading a file using the DIF Data Loader (interface format) the destination \\table MUST be left BLANK.
||Report Criteria||
|__Source File__
|Server File| This field provides the name of the file that has been placed on the server in the directory specified in the [IMST] record [LOAD DATA FLDR|LOAD DATA FLDR(System_Preference)].
|Local File| This field allows you to upload a local source file for loading into the database (similar to that of WW Reports in Personality). \\To do this, you must populate the 'Local File' field by selecting the local source file using the file chooser. When selecting a local source file to load, the 'Server File' field MUST be blank.
|__Load Permissions__
|Allow Update|Lexicon value of YES or NO\\ Selecting "NO" disallows the updating of existing records in the database. If there are new records in the load file, the program will still perform an insertion of these records into the destination table. \\In short, selecting "NO" for Allow Update does not allow you to run the program in trial as it only prevents existing records in the database from being updated. \\Selecting "YES" will update existing records in the database with changes found in the load file.
|Allow Insert| Lexicon value of YES or NO\\By selecting "NO", LMTD will not load any new records into the specified table.\\By selecting "YES", new records found in the load file will be inserted into the specified table.
|Truncate before load| Lexicon value of YES or NO\\__On tables without dependencies__, when this is selected as "Yes", the values will be dropped before the load.\\Example: Loading onto IPTL - There are values from the last load. Setting this to "Yes" will drop those values before doing the load. Other sample screens that allow this option, can include the [IULF] or any of the conversion screens (IVXXX).
|Maximum Exceptions| This is a value that can be set to limit the number of exceptions that are encountered before the function terminates. \\If this is not supplied, the default value is 50.
|Exception Level| Lexicon value of 0 to 9. \\This field allows you to specify the level of trace you wish to see in the report.\\Note that if ‘0-Exceptions only’ is chosen, you will see NO messages, counts, etc. It is suggested that you run at a minimum of ‘1-User Trace’.
|__File Layout__
|__Interface__
|Interface Code| If using the Table Data Loader logic (no interface), leave this blank. \\If using the DIF Data Loader logic (with interface) an interface must be specified to load a source file. You can select multiple interfaces if you have to run a particular source file multiple times using different interfaces. If the source file has thousands of records suggested to run the LMTD multiple times selecting only one interface code each time, otherwise there may be an impact on performance.
|File Format| If using the Table Data Loader (no interface) the field defaults to Character-delimited as the Table Data Loader method only supports Character-delimited files. \\If using the DIF Data Loader (interface format) the user may select either Character-delimited or Fixed Position.
At line 109 added 18 lines
|__Column Header__
|Destination Table| This field identifies the table that will be inserted or updated. This is a mandatory parameter for the Table Data Loader method.\\When loading a file using the DIF Data Loader (interface format) the destination table MUST be left BLANK.
----
!!Business Scenarios for LMTD
Here are a few examples of interface formats used to load data into various tables. Please note that these may not include all available columns on the table, just a few that were selected to update or insert.
#[Loading values into IPTL|LMTD LOADING TO IPTL]
#[Loading transactions into IPTL|LMTD LOADING TRANSACTIONS TO IPTL]
#[Loading sundries into IPSN|LMTD LOADING SUNDRIES]
#[Loading complex multi deduction file into IPSN|LMTD LOADING COMPLEX SUNDRY FILE]
#[Loading time exceptions|LMTD LOADING TIME EXCEPTIONS]
#[Loading date splits onto a data effective table|LMTD LOADING DATE EFFECTIVE SPLITS]
----
![Notes|Edit:Internal.LMTD]
[{InsertPage page='Internal.LMTD' default='Click to create a new notes page'}]
This page (revision-31) was last changed on 26-Nov-2021 10:22 by Kevin HiggsTop