[[main_page|NovoMotion Outbound Menu]]

====== NovoMotion Schema Editor ======


The NovoMotion Schema Editor is used to create or maintain the definition of the elements of a schema generator used with the the SQL environment.  Once the user has logged in through the [[novo_motion_u2_login|NovoMotion U2 Login]], the user will access the Schema Editor through the U2 Options view.

\\

==== NovoMotion Schema Editor - U2 Options Tab - SA Map Example ====



{{:file:novoschema1.jpg|}}


The U2 Options tab allows for the following information to be entered:



  * The description is a free-form character string and can contain any description that is pertinent.


  * The Unidata File Name is the name of the file from which the data will be extracted.   Virtual attributes ( I-descriptors) on that file are also available to the schema editor.  NOTE: 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. 


  * The Schema Active flag is used to flag those schemas that are currently in use.  


  * The Collect Schema Matrix Audit Data flag is used to store data in the BCI.AUDIT file. 


  * The Schema Subroutine contains the name of the program to apply to this schema. It is used in conjunction with the Execute Before Matrix and Execute After Matrix flags to indicate when the subroutine should be applied. 


  * The schema allows definition of the following data types: 


^ **Data Type**^ **Description** |
| Variable Character| used for characters with a length greater than 1000 |
| Character| used for any string of data up to 1000 characters |
| Date| used for any date field |
| Integer| used for any numeric field that does not contain decimals |
| Numeric| used for most non-integer numbers |
| Decimal| inherently the same as numeric |
| Floating Point| used when the precision and scale are not distinct |




  * Precision represents the maximum length of the table column data. It is analogous to the dictionary length of a U2 dictionary.


  * Scale represents the number of decimal places contained in the data. It is analogous to the masked decimal (MD) conversion on the dictionary in the U2 environment. 


  * 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:

^ **Key Word**^ **Description** |
|@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. |
|@ACTION | Used to specify a delete or insert event  |
|@LITERAL | Used to pass static data through the schema - @LITERAL:1 |
|@FIELD | Field based on info  - @FIELD:info:delimiter:occurrence:numberOfOccurrences|
|@FIELDS | Field for multi-value array.  Same as @FIELD |
|@TM | Allows selection of a specific table master - @TM:tablenbr |
|@CHANGE | Used to change a specific string from one literal to another - @CHANGE:info:from:to | 
|@CONVERT | Used to change multiple characters from one character to another -  @CONVERT:info:from:to |
|@ICONV | Used to convert a string to internal format  - @ICONV:mvinfo:conversion|
|@OCONV | Used to convert a string to external format  - @OCONV:mvinfo:conversion|
|@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 | TBD | 
|@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. 


  * SQL Column is the title of the column within the SQL table. 


  * The Primary Key checkbox is used to identify the data element that is guaranteed unique. 


  * The Data Required for Insert checkbox is used to identify elements necessary prior to update or insert into the SQL table. If there is a null value in a data element for which this flag is set, the row update or insert is not performed. 


  * The Detail column will show the user the U2 information about the U2 dictionary selected.  The display will resemble the one below.


{{:file:novo_schema2.jpg|}}



  * The user may check the Bypass Dictionary Checks checkbox.  This is used to suppress the messages generated when a user selects an I descriptor that uses either the SUBR or the TRANS keyword. 


  * Suppress Output is used when the U2 dictionary is used to select records, but the data is not needed in SQL.


  * The SQL Default is used to control what data is sent to SQL when the U2 Dictionary element contains no data.The SQL Default can be used to specify a default value for a SQL data type when null is an invalid data element. For example, small time date, numeric, etc. When null is a valid construct in SQL (varchar, char) this default is ignored. Uses of this column could include a zero in a numeric field or GetDate() for a date field.




==== NovoMotion Schema Editor - SQL Options tab - SA Map Example ====


{{:file:novoschema3.jpg|}}



Additionally, the SQL Options tab allows the following information to be entered:



  * The name of the table on the SQL server. 


  * The name of the database source from which the data is gathered.

 
  * The SQL server login for use by the processes. 


  * The SQL server password associated with the login.




==== NovoMotion Schema Editor - Post Job Options Tab - SA Map Example ====



{{:novoschema4.jpg|}}



The Post Job Options tab allows the user to enter the following information:



  * The SQL Job name is the name of the SQL process to run after the data is updated - if desired. This is often useful if the data is distributed from one SQL table to others. 


  * The ODBC database name is the name of the database connection to use for this process.

 
  * The login is the name of the login to use to access that database.

 
  * The password is the password used on the specified ODBC database for the entered login. 


==== NovoMotion Schema Editor - Drop Down Menus ====


=== File ===
 



^ **Action **^ **Description** |
| New| used to create a new schema |
| New from Template| applies a previously saved template for ease of entry |
| Open| used to access an existing schema - including one that was auto-generated |
| Save| save changes to a schema - using the existing schema name |
| Save As| save changes to a schema - using a new schema name |
| Save As Template| save current schema as a template for later use |
| Import Schema| used to import a schema that was generated on another system |
| Export Schema| used to prepare a schema for distribution to another system |
| Delete| remove the current schema |
| Close| close the current schema |
| Exit| leave the NovoMotion Schema Editor |
| Logout| return to the U2 Login screen |




=== Edit ===



^ **Action **^ **Description** |
| Add Action Row| allows user to automatically add the @ACTION to a schema - @ACTION is used for tables that require row replacement |
| Apply Template| apply a previously saved template to the existing schema |




=== Tools ===


^ **Action **^ **Description** |
| Generate SQL Script| used to create the SQL script |
| Validate Schema| checks the schema for valid login information |
| Apply Schema to selected Schema| apply a previously saved template to the existing schema |
| Launch Interface Control| starts the Interface Control for the existing U2 Login |
| Launch File Loader| starts the File Loader - see screen shot below |




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|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.


=== Help ===


^ **Action **^ **Description** |
| About Schema Editor| used to check the version and build of the editor |


\\
[[main_page|NovoMotion Outbound  Menu]]