The stored procedure
MIGRATE_DATE_TYPES automatically scans base views, interface views and complex types and updates them to change columns of type
date (deprecated) to one of the datetime types that was added in Denodo 7.0.
GET_VIEWS ( input_database_name : text , input_process_base_views : boolean , input_process_interface_views : boolean , input_process_complex_types : boolean )
If you provide a non null value for
input_database_nameit will only scan for elements in that database. Please note that updating an element in a database may propagate changes to element in other databases. If you pass
nullfor this parameter it will update all elements in the server.
You can set
input_process_complex_typesto false to skip processing that kind of element.
The procedure changes the fields with type
dateto a new type in the following way:
For JDBC base views, Virtual DataPort looks at the field “sourceTypeName” of the statement
CREATE WRAPPER JDBCof the base view, and the adapter of the data source of the base view. With this information, it will export the date fields with one of the new types.
For non-JDBC base views or derived views, it looks into the property “sourceTypeId” of the field of the
If the property “sourceTypeId” is
DATE, the field is updated to
localdate, which is the name of the type in Denodo to represent date values without time (equivalent to type DATE of SQL).
If it is
TIME, the field is updated to
If it is
TIMESTAMP, the field will remain as
date(deprecated). There is not enough information to know if the field is timestamp or timestamptz.
If the field does not have a source type, the field is remains as
If there is not enough information to export the field with one of the new date types, the field is exported as
date. This will occur in non-JDBC base views whose fields do not have the source type id defined, or for fields of derived views that are the result of applying a function.
Each row returned by the procedure has these fields:
database_name: name of database of the element described in the row.
element_name: name of the element being updated.
element_type: type of the element being updated. “BASE”, “INTERFACE” or “REGISTER”.
column_name: name of the field with
datetype being updated..
column_new_type: the new type assigned to the field.
status: a message describing the result of the process of migrating that field. For example it may just show “OK”, or list the views modified due to changes propagation, or show an error.
The user that runs this procedure must have administration privileges on all the databases that will be processed.
SELECT * FROM MIGRATE_DATE_TYPES() WHERE input_process_base_views = true AND input_process_interface_views = true AND input_process_complex_types = true
Scans all databases to search base views, interface views and complex types with fields with
date and updates them according to the rules described above. Returns a row per column with
SELECT database_name, element_name, element_type, nest(column_name, column_new_type, status) FROM MIGRATE_DATE_TYPES() WHERE input_process_base_views = true AND input_process_interface_views = true AND input_process_complex_types = true GROUP BY database_name, element_name, element_type
Same as before but returning only one row per element. Information about columns of each view / register will be nested in an array.