USER MANUALS

MIGRATE_DATE_TYPES

Description

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

Syntax

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_name it 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 null for this parameter it will update all elements in the server.

  • You can set input_process_base_views, input_process_interface_views and input_process_complex_types to false to skip processing that kind of element.

  • The procedure changes the fields with type date to a new type in the following way:

    • For JDBC base views, Virtual DataPort looks at the field “sourceTypeName” of the statement CREATE WRAPPER JDBC of 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 CREATE TABLE statement:

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

      • 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 date (deprecated).

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

Privileges Required

The user that runs this procedure must have administration privileges on all the databases that will be processed.

Examples

Example 1

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

Example 2

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.

Add feedback