This article is an overview of AutoGen and explanation of what objects can be made in SQL with the Automation Engine, how those events are triggered, and any settings that can be applied to modify what is created.
NOTE: This article applies to DSP 6.1 and above.
This article contains the following sections:
- What is AutoGen?
- AutoGen Objects Processes
- Alter Settings for SQL AutoGen Objects
- Set the Design Status for a Target
- Alter Settings for Construct AutoGen
- View SQL AutoGen Transaction Status
- Automation Engine Actions
What is AutoGen?
AutoGen is the name of a dspMigrate™ WebApp that governs the execution of events that automatically create SQL and/or Data Services objects. AutoGen uses the data defined in Target Design and Map to create target tables, source tables, source rules, and target rules for the data migration process.
There are two kinds of AutoGen:
- SQL AutoGen: Driven from Map and used to create objects needed for the migration project, such as target tables, source tables, and SQL rules based on the following action types: Copy, Default, Rule, Xref, RuleXref, and Construction.
- Data Services AutoGen – Driven from the Target Design and provides Data Services code that enriches the target table data with Basic Rules defined in Target Design. Data Services AutoGen also runs a set of standard validations (format checks, target value mapping checks and field status checks) against the target table to avoid needing to write these validation rules in SQL.
AutoGen is often confused with different things that have a similar name in the DSP®. To understand AutoGen it is important to know the difference between these three things:
- Automate is the name of a platform WebApp that schedules various events to run at specific times or frequencies.
- AutoGen is the name of a dspMigrate WebApp that governs the execution of events that automatically create SQL and/or Data Services objects.
- The Automation Engine is a component of the Common platform WebApp. It is often referred to as the AE and it is the engine that AutoGen uses to automatically create SQL objects (and run some other tasks). For more information about using the Automation Engine, see the Automation Engine Developer Guide.
AutoGen Objects/Processes
Navigation | Settings that Modify What is Created |
---|---|
{Quick Panel} > Automation tab > Create Target Table or Create All Objects icons | See Alter Settings for SQL AutoGen Objects section below table. |
{Quick Panel} > Automation tab > Sources icon > Create Source Table icon
OR {Quick Panel} > Automation tab > Create All Objects icon |
See Alter Settings for SQL AutoGen Objects section below table. |
{Quick Panel} > Automation tab > Create All Rules or Create All Objects icons OR {Quick Panel} > Automation tab > Sources icon > Create Source Table icon OR {Quick Panel} > Automation tab > Sources icon > Update Row Sources icon > Create Source Table icon |
See Alter Settings for SQL AutoGen Objects section below table. |
{Quick Panel} > Automation tab > Build Reports or Create All Objects icons | See Alter Settings for SQL AutoGen Objects section below table. |
{Quick Panel} > Automation tab > Build Reports with Remediation icon | If the Build Remediation Rule Reports checkbox on the Map tab of the Parameters page in Console (dspMigrate > Advanced Configuration > Parameters) is checked, Map builds Transform Remediation Rule reports to display data audit updates during the Transform Automation process.
NOTE: Enabling this feature may affect performance. If unchecked, Map generates the remediation rule value to process, but does not build the new view to generate the remediation report. |
{Quick Panel} > Automation tab > Mapping icon > Reset icon OR dspMigrate > Map > Targets icon > Mappings icon > Reset icon |
What happens when a user clicks the Reset icon for a field mapping on the Field Mappings page in Map or the Automation SQL Field Mappings page in SQL AutoGen depends on what option has been selected in the Reset Field Mapping field on the Map tab of the Parameters page (dspMigrate > Advanced Configuration > Parameters). Options are:
|
dspMigrate > Map > Targets icon > Mappings icon > Submit icon | See Alter Settings Construct AutoGen section below table. |
dspMigrate > Map > Targets icon > Mappings icon > Submit All icon | See Alter Settings for SQL AutoGen Objects section below table. |
dspMigrate > Map > Targets icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Mappings icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Sources icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Design Status icon > Complete icon OR dspMigrate > Map > Targets icon > Design Status icon > Design Finished icon OR dspMigrate > Design > Targets icon > Sync to Map icon |
See Alter Settings for SQL AutoGen Objects section below table. See Set the Design Status for a Target section below table. |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Source Config icon > Tables icon > Refresh icon OR dspMigrate > Map > Configuration > Wave Lookup Table Refresh > Refresh Legacy Values icon |
If the Build Xref Target Rules checkbox on the Map tab of the Parameters page (dspMigrate > Advanced Configuration > Parameters) is unchecked, Map builds cross reference rules at the source level for those field mappings with an action of Xref or RuleXref. If checked, Map builds cross reference rules at the target level for those field mappings with an action of Xref or RuleXref. By default, the check box is unchecked. |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Source Config icon > Tables icon > Remove Values icon |
|
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Remove Values icon |
|
dspMigrate > Map > Targets icon > Sources icon > Auto Map icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Auto Map icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Secondary Source icon > Auto Map icon |
|
dspMigrate > Map > Targets icon > Sources icon > Auto Update Not Used icon |
If an Add Row or Update Row Source is mapped to another Add Row or Update Row Source in Map, the Auto Set Not Used for Multiple Sources setting on the Parameters page in Console controls how Map updates an action that is not set. Map updates the ACTION field on the Field Mappings page once the mapping is saved. The Auto Set Not Used for Multiple Sources list box has three options:
NOTE: An action of NotUsed can be updated after it has been set. |
dspMigrate > Map > Value Mapping > Auto Map by Description icon |
|
dspMigrate > Map > Value Mapping > Auto Map by Value icon |
|
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Target Table Import icon |
|
dspMigrate > Map > Configuration > Wave Lookup Table Refresh > Refresh Target Values icon |
|
dspMigrate > Design > Targets icon > Sources icon > Drop And Build Data Construction Table And Page icon | See Alter Settings for SQL AutoGen Objects section below table. |
dspMigrate > Design > Targets icon > Sources icon > Update Stats icon | See Alter Settings for SQL AutoGen Objects section below table. |
NOTE: To see a list of AE (Automation Engine) actions associated with each navigation in the table above, see the Automation Engine Actions table at the end of this article.
Alter Settings for SQL AutoGen Objects
Tables, Views, and Stored Procedures
A setting on the Automation page’s Vertical View configures if and how SQL Objects are auto generated in the database and registered in Transform.
NOTE: ALTER table commands are used to add missing columns. Rules only create views and stored procedures that are missing. They do not update any views or stored procedures.
The Auto Gen Levels are:
- New Rules - Alter Table and Build New Views and Stored Procedures Only – When autogenerating, a table is updated and only new views and stored procedures are created. Existing views and stored procedures are not updated.
- Off - No Generating Any SQL Objects – AutoGen is not used.
NOTE: Rules for mappings with the Copy, Default, Rule, XRef, and Xref actions are not built. For mappings with the Construction action (as in, mappings that use Enhanced Construction), the table and field are not created in the target database and a page is not created in Construct. Full Construction objects are built via the Sync in Target Design. The rules must be generated on the Automation SQL Field Mappings page. Refer to Create Rules using SQL AutoGen in the online help for more information.
- On - Drop & Rebuild Table, Views and Stored Procedures – Any SQL objects previously created for the target are dropped and a new table, SQL views and stored procedures with the same name are added.
- Rebuild - Alter Table and Drop & Rebuild Views And Stored Procedures – The table is updated. Views and stored procedures previously created for the target are dropped and new SQL views and stored procedures with the same name are added.
Business User Reports
A setting on the Automation page’s Vertical View configures if and how SQL Objects are auto generated in the database and registered in Transform.
Options for how business user reports are generated for the target with SQL AutoGen are:
- On - Drop & Rebuild Report Views and Registrations – Any SQL views previously created for the business user reports in the target are dropped and new SQL views with the same name are added.
- Off - No Report Generation – AutoGen is not used.
- New - Add new reports without modifying existing report views or registrations – When autogenerating, only new views are created. Existing views are not updated.
Set the Design Status for a Target
The Design Status for a Target indicates its stage in the design process. The Target’s Design Status determines if AutoGen is active. To set the Design Status, click the link in the DESIGN STATUS column for the Target on the Targets page.
Values are:
- In Design — A Target is in this Design Status when it has been added and while the Target is being configured. All functionality that can be performed on a Target is available and the Sync to Map icon is enabled.
- Design Finished — When a Target is in this status, the design is locked down, and no changes are allowed. A Target can only move to this status if it is in sync with Map. The Sync to Map icon is enabled. AutoGen in Data Services and in SQL is allowed.
- Completed — When a Target is in this status, the design is locked down, and no changes are allowed. A Target can only move to this status if it is in sync with Map. AutoGen in Data Services and in SQL is not allowed. The Sync to Map icon is disabled.
- Inactive — A Target in Inactive status is not pushed to Map for mapping and is not available in Transform. When a Target in this status is selected, the Sync to Map icon is disabled.
Alter Settings for Construct AutoGen
Construct AutoGen Level
To begin Full Construction in Target Design, add a Target Source as {Full Construction}. After saving a {Full Construction} Target Source, tables, indexes, views (page views and validation views), WebApp page, page properties, CranPort package and Collect registrations are automatically created for the Construct page.
To set the Construct AutoGen Level, which controls whether the Construct page is updated or overwritten, go to the Vertical View of the Target Sources page and update the Construct AutoGen Level Field.
Values are:
- Off - Construct objects and pages are not generated.
- New - Adds new fields to the page table and new column properties to page. Does not rebuild page views.
- Rebuild - Drops and recreates all views and column properties and adds new fields to the page table.
NOTE: This field defaults to Rebuild.
Construct AutoGen Build View
To set the Construct AutoGen Build View setting, go to the Vertical View of the Target Sources page and update the Construct AutoGen Build View field. This option controls how the Construct page is built with Horizontal and Vertical Views.
Values are:
- Horizontal Only – All fields are displayed and editable on the Horizontal View.
- Horizontal/Vertical Mix – Only required fields are displayed and editable on the Horizontal View. All other fields are on the Vertical View.
- Vertical Only – Only required fields are displayed on the Horizontal View. All fields are edited on the Vertical View.
The tabs' creation and field sorting are controlled by the Application Screen, Field Order, and Key fields on the Target Fields page’s Vertical View.
View SQL AutoGen Transaction Status
A Migration Developer can track transaction status as target tables, source tables and rules are built during SQL AutoGen.
The AutoGen SQL Log page displays messages about success or failure and detailed information to troubleshoot issues so that users can build objects more efficiently.
If AutoGen transactions have generated successfully (for example, the target table, source table or rules have been built successfully), the icon is a green check mark. If transactions have failed, a red icon displays.
If the Log icon is disabled, targets, sources or rules have not been generated.
To view the log in AutoGen, click the Log icon on the following pages:
- Automation
- Automation SQL Target Source
- Automation SQL Target Source (Update Row)
- Automation SQL Field Mappings
Automation Engine Actions
Navigation | AE (Automation Engine) Action |
---|---|
{Quick Panel} > Automation tab > Create Target Table or Create All Objects icons | TaskGroup – cMap Build Target Table TaskGroup – AutoGen Build Staging Tables |
{Quick Panel} > Automation tab > Sources icon > Create Source Table icon OR {Quick Panel} > Automation tab > Create All Objects icon |
TaskGroup – cMap Build Source Table and Insert and Delete Rules |
{Quick Panel} > Automation tab > Create All Rules or Create All Objects icons OR {Quick Panel} > Automation tab > Sources icon > Create Source Table icon OR {Quick Panel} > Automation tab > Sources icon > Update Row Sources icon > Create Source Table icon |
TaskGroup – cMap Build Rules (Target Source Rules and Target Rules) |
{Quick Panel} > Automation tab > Build Reports or Create All Objects icons | TaskGroup – cMap Build Target Reports |
{Quick Panel} > Automation tab > Build Reports with Remediation icon | Taskroup – cMap Build Target Reports with Remediation |
{Quick Panel} > Automation tab > Mapping icon > Reset icon OR dspMigrate > Map > Targets icon > Mappings icon > Reset icon |
Task – cMap Target Field Mapping Reset Action |
dspMigrate > Map > Targets icon > Mappings icon > Submit icon | TaskGroup – cMap Submit Rules and Build Construction |
dspMigrate > Map > Targets icon > Mappings icon > Submit All icon | TaskGroup – cMap Submit All Rules and Build Construction |
dspMigrate > Map > Targets icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Mappings icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Sources icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Sync From Target Design icon OR dspMigrate > Map > Targets icon > Design Status icon > Complete icon OR dspMigrate > Map > Targets icon > Design Status icon > Design Finished icon OR dspMigrate > Design > Targets icon > Sync to Map icon |
Task - Target Design Reset Inactive Fields Task - Target Design Full Construction Table |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Source Config icon > Tables icon > Refresh icon OR dspMigrate > Map > Configuration > Wave Lookup Table Refresh > Refresh Legacy Values icon |
TaskGroup - cMap Value Mapping Source Value Pull |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Source Config icon > Tables icon > Remove Values icon |
Task – cMap Value Mapping Delete by Source Field |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Remove Values icon |
Task – cMap Value Mapping Delete by Source |
dspMigrate > Map > Targets icon > Sources icon > Auto Map icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Auto Map icon OR dspMigrate > Map > Targets icon > Sources icon > Update Row Sources icon > Secondary Source icon > Auto Map icon |
Task – cMap Auto Map Source Table |
dspMigrate > Map > Targets icon > Sources icon > Auto Update Not Used icon |
Task – cMap Target Source Update Non-Mapped Fields to NOT USED |
dspMigrate > Map > Value Mapping > Auto Map by Description icon |
Task – cMap Auto Value Map by Description between Legacy and Target |
dspMigrate > Map > Value Mapping > Auto Map by Value icon |
Task – cMap Auto Value Map by Value between Legacy and Target |
dspMigrate > Map > Configuration > Value Mapping (Config) > Sources icon > Target Table Import icon |
TaskGroup – cMap Reset Mappings Values from SDB and Map Values from Target Table |
dspMigrate > Map > Configuration > Wave Lookup Table Refresh > Refresh Target Values icon |
TaskGroup – cMap Value Mapping Target Configuration Pull |
dspMigrate > Design > Targets icon > Sources icon > Drop And Build Data Construction Table And Page icon |
Task – Target Design Full Construction Table |
dspMigrate > Design > Targets icon > Sources icon > Update Stats icon |
Task – Target Design Construction Count Updates |