NovoRoi Systems DokuWiki

User Tools

Site Tools

Trace:
novo_motion_schema_editor

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
novo_motion_schema_editor [2021/02/03 16:58] – dlmnovo_motion_schema_editor [2024/05/13 16:08] (current) – dlm
Line 1: Line 1:
 +[[main_page|NovoMotion Outbound Menu]]
  
 ====== NovoMotion Schema Editor ====== ====== NovoMotion Schema Editor ======
Line 10: Line 11:
  
  
-{{:novoschema1.jpg?800|}} 
  
 +{{:file:novoschema1.jpg|}}
  
  
Line 54: Line 55:
  
  
-  * U2 dictionary is the name of the base dictionary item (for the selected file) within the U2 environment. (I-descriptors are also supported. I-descriptors that use the SUBR keyword or that execute a TRANS to translate to another file should be used with caution. Warnings are sent to the user if they chooses an I-descriptor that uses one of these types.) Note that there are also a number of keywords associated with this column: +  * U2 dictionary is the name of the base dictionary item (for the selected file) within the U2 environment. (I-descriptors are also supported. I-descriptors that use the SUBR keyword or that execute a TRANS to translate to another file should be used with caution. Warnings are sent to users if they choose an I-descriptor that uses one of these types.) Note that there are also a number of keywords associated with this column: 
-       * @MULTIVALUEID - Current suggestion is to use multiple primary keys instead of this. However, it is used to explode the controlling attribute of a multi-valued set @MULTIVALUEID:part1:part2   + 
-       * @ORDINAL - Assigns a numeric sequence to the value - @ORDINAL:mvinfo. See Ordinal considerations below.  +^ **Key Word**^ **Description** | 
-       * @ACTION - Used to specify a delete or insert event   +|@MULTIVALUEID |Current suggestion is to use multiple primary keys instead of this. However, it is used to explode the controlling attribute of a multi-valued set @MULTIVALUEID:part1:part2  | 
-       * @LITERAL - Used to pass static data through the schema - @LITERAL:1  +|@ORDINAL |Assigns a numeric sequence to the value - @ORDINAL:mvinfo. See Ordinal considerations below. | 
-       * @FIELD - Field based on info  - @FIELD:info:delimiter:occurrence:numberOfOccurrences +|@ACTION | Used to specify a delete or insert event  | 
-       * @TM - Allows selection of a specific table master - @TM:tablenbr  +|@LITERAL | Used to pass static data through the schema - @LITERAL:1 | 
-       * @CHANGE - Used to change a specific string from one literal to another - @CHANGE:info:from:to   +|@FIELD | Field based on info  - @FIELD:info:delimiter:occurrence:numberOfOccurrences| 
-       * @CONVERT - Used to change multiple characters from one character to another -  @CONVERT:info:from:to  +|@FIELDS | Field for multi-value array.  Same as @FIELD | 
-       * @ICONV - Used to convert a string to internal format  - @ICONV:mvinfo:conversion +|@TM | Allows selection of a specific table master - @TM:tablenbr | 
-       * @OCONV - Used to convert a string to external format  - @OCONV:mvinfo:conversion +|@CHANGE | Used to change a specific string from one literal to another - @CHANGE:info:from:to |  
-       * @FMT - Used to format a string  - @FMT:mvinfo:statment +|@CONVERT | Used to change multiple characters from one character to another -  @CONVERT:info:from:to | 
-       * @NOT - The reverse of a string evaluation - @NOT:mvinfo  +|@ICONV | Used to convert a string to internal format  - @ICONV:mvinfo:conversion| 
-       * @TRIM - Removes errant spaces from a string  - @TRIM:mvinfo  +|@OCONV | Used to convert a string to external format  - @OCONV:mvinfo:conversion| 
-       * @NULL - Returns a null character - @NULL +|@FMT | Used to format a string  - @FMT:mvinfo:statment| 
 +|@NOT | The reverse of a string evaluation - @NOT:mvinfo | 
 +|@TRIM | Removes errant spaces from a string  - @TRIM:mvinfo | 
 +|@NULL | Returns a null character - @NULL | 
 +|@BLOAT | Use to expand (bloat) a dictionary to the value mark count dictated by another dictionary - @BLOAT:DictOne:BoatDict|  
 +|@USER | Sends the U2 user ID who caused the record change event, or load event | 
 +|@SWAP | Used to change string from one string to another -  @SWAP:info:from:to | 
 +|@CAT | Use U2 CATS to combine two arrays of data together - @CAT:info1:info2 | 
 +|@TIMESTAMP | transmit a record timestamp for when change / load event happened in U2. This is both date and time in SQL. | 
 + 
 + 
 +  * In SWAP CONVERT etc above, @AM @VM @SVM can be specified as IN or OUT strings.
  
   * Ordinal considerations: The precision and scale assigned to the ordinal are taken from the mvinfo dictionary. Because an ordinal is a number, the precision and scale should be changed to numeric with a scale of 3 or 4.    * Ordinal considerations: The precision and scale assigned to the ordinal are taken from the mvinfo dictionary. Because an ordinal is a number, the precision and scale should be changed to numeric with a scale of 3 or 4. 
Line 85: Line 97:
  
  
-{{file_novo_schema2.jpg}} +{{:file:novo_schema2.jpg|}}
  
  
Line 100: Line 112:
  
  
-==== NovoMotion Schema Editor - SQL Options tab - SA map example ====+==== NovoMotion Schema Editor - SQL Options tab - SA Map Example ====
  
  
- +{{:file:novoschema3.jpg|}}
-{{file_novo_schema3.jpg}} +
  
  
Line 126: Line 137:
  
  
-==== NovoMotion Schema Editor - Post Job Options tab - SA Map example ====+==== NovoMotion Schema Editor - Post Job Options Tab - SA Map Example ====
  
  
  
-{{file_novo_schema4.jpg}} +{{:novoschema4.jpg|}}
  
  
Line 150: Line 161:
  
  
-The function contains the following drop down menus:+==== NovoMotion Schema Editor - Drop Down Menus ====
  
  
-  * **File**+=== File === 
 +  
  
  
Line 173: Line 186:
  
  
-  * **Edit**+=== Edit === 
  
  
Line 183: Line 197:
  
  
-  * **Tools**+=== Tools ===
  
  
Line 196: Line 210:
  
  
-**Note:**  The Generate SQL Script is used when one wants to generate script to create the SQL table (See the Creating a SQL script information) to avoid the necessity of creating the SQL table by hand.  This is often used multiple times during the development of new schema to perfect the information between U2 and SQL.+Note:  The Generate SQL Script is used when one wants to generate script to create the SQL table (see [[creating_a_sql_script|Creating a SQL Script]]) to avoid the necessity of creating the SQL table by hand.  This is often used multiple times during the development of new schema to perfect the information between U2 and SQL.
  
  
-The SQL script can be pasted into SQL Management Studio and executed. It will drop any existing SQL table of the name defined in the schema and re-create it with the columns defined in the schema. It does this for both the base SQL table and - if an @ACTION row exists - for the parallel SQL trigger table.  See the [[sql_server_management_studio_example]] if you would like to review notes about the SQL Server tasks. +The SQL script can be pasted into SQL Management Studio and executed. It will drop any existing SQL table of the name defined in the schema and re-create it with the columns defined in the schema. It does this for both the base SQL table and - if an @ACTION row exists - for the parallel SQL trigger table.  See the [[sql_server_management_studio_example|SQL Server Management Studio Example]] if you would like to review notes about the SQL Server tasks. 
  
 +If a SQL table has already been established and the schema needs to change, the user has the following options:
 +  - Generate the SQL script through the Schema Editor, copy it, paste it into SQL Management Studio, and execute the script.  This will drop the existing table  and replace the existing SQL column definitions.  Then use [[load_bci.action|LOAD.BCI.ACTION]]  to reload the data into the SQL table.
 +  - Change, add, or delete the desired information in the schema via the Schema Editor, then make the same changes to the SQL table via SQL tools.  Next use [[load_bci.action|LOAD.BCI.ACTION]]  to reload the data into the SQL table.  This can be used when the SQL table has data that cannot be regenerated from the U2 server because the data has already been purged from that server or because the table is large and the data being added is new.
  
-**Additional Usage Notes:**  If a SQL table has already been established and the schema needs to change, the user has the following options: 
-  - Generate the SQL script through the Schema Editor, copy it, paste it into SQL Management Studio, and execute the script.  This will drop the existing table  and replace the existing SQL column definitions.  Then use [[load_bci.action]]  to reload the data into the SQL table. 
-  - Change, add, or delete the desired information in the schema via the Schema Editor, then make the same changes to the SQL table via SQL tools.  Next use [[load_bci.action]] to reload the data into the SQL table.  This can be used when the SQL table has data that cannot be regenerated from the U2 server because the data has already been purged from that server or because the table is large and the data being added is new. 
  
- +=== Help ===
-  * **Help**+
  
  
Line 214: Line 227:
  
  
- +\\ 
 +[[main_page|NovoMotion Outbound  Menu]]
novo_motion_schema_editor.1612389526.txt.gz · Last modified: 2021/02/03 16:58 by dlm