What are the benefits and drawbacks of using dml labels in multiload scripts?

1 What are dml labels?

Dml labels are names that you can give to different dml statements in your multiload script, such as insert, update, delete, or upsert. You can use them to group and organize your dml statements, and to reference them later in other parts of your script. For example, you can use dml labels to specify which dml statements should be applied to which input data sources, or to control the order of execution of your dml statements. You can also use dml labels to define error tables and error handling logic for your multiload script.

2 How do dml labels work?

To use dml labels in your multiload script, you need to follow a few rules and conventions. First, you need to declare your dml labels in the layout section of your script, using the .LAYOUT statement. You can declare up to 16 dml labels per layout. Second, you need to assign your dml labels to your dml statements in the dml section of your script, using the .DML LABEL statement. You can assign one or more dml labels to each dml statement, separated by commas. Third, you need to reference your dml labels in the import section of your script, using the .APPLY statement. You can reference one or more dml labels per import source, separated by commas. You can also use the keyword ALL to apply all the dml labels in the current layout.

3 What are the benefits of using dml labels?

Using dml labels in your multiload script can have several benefits, depending on your use case and requirements. For example, you can gain flexibility by using dml labels to perform different types of operations on different input data sources, such as inserting, updating, deleting, or performing upserts (insert or update). Additionally, you can reuse the same dml statements and error tables for different input data sources, as well as improve the readability and maintainability of your multiload script with meaningful names and organization of the dml statements.

4 What are the drawbacks of using dml labels?

Using dml labels in your multiload script can also have some drawbacks, depending on your use case and requirements. You must be careful and consistent when using dml labels, as they can introduce complexity and potential errors. For example, you must ensure that the dml labels are unique within a layout, match the data types and formats of your input data sources, do not conflict with other keywords or reserved words, and do not exceed the maximum length of 30 characters. Additionally, you should be aware of the performance implications of using dml labels in your multiload script, as they can affect the speed and efficiency of your data loading process. For example, using multiple dml labels per import source can increase the number of passes and reads that multiload has to perform on your input data, which can slow down your multiload operation. Furthermore, using different dml labels for different input data sources can prevent multiload from applying some optimizations such as block-level compression or parallel processing, which can reduce the throughput and scalability of your multiload operation.

5 How to use dml labels effectively?

To use dml labels effectively in your multiload script, you need to consider the pros and cons of using them and follow some best practices and tips. You should only use them when necessary, such as when you need to perform different operations on different data sources or control the order of execution. Additionally, you should use descriptive and consistent names for your dml labels to help improve readability and maintainability. Furthermore, try to use fewer and simpler dml labels that minimize the number of passes and reads that multiload has to perform on your input data. Lastly, avoid using complex expressions or conditions as they can affect performance and accuracy.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?