Content Manager OnDemand V9.0 new date types for Date, Date/Time and Date/Time (TZ)

Content Manager OnDemand V9.0 new date types were introduced in order to allow for a broader range of values supported by the underlying database for Date, Date/Time and Date/Time (TZ) types.

In order to use the new types, you must be using Content Manager OnDemand V9.0 or later of the OnDemand clients, for example: Windows client, arsdoc, Content Manager OnDemand Web Enablement Kit (ODWEK), CICS.

The previous types for Date, Date/Time, and Date/Time (TZ) were renamed as Date (old style), Date/Time (old style), Date/Time (TZ) (old style). Although these types can still be used, they are no longer recommended.

The valid ranges for the Date/Time fields are as follows:

Date (old style) Date Date/Time (old style) Date/Time Date/Time (TZ) (old style) Date/Time (TZ)

1970-01-01 0001-01-01 1970-01-01 00:00:00 0001-01-01 00:00:00 1970-01-01 00:00:00 0001-01-01 00:00:00.000000

2058-12-31 9999-12-31 2037-12-31 23:59:59 9999-12-31 23:59:59 2037-12-31 23:59:59 9999-12-31 23:59:59.999999

Note:
The Time field has been renamed Time (old style) in the OnDemand Administrator client, but there is no new Time field type. The Time (old style) data type is only available for existing application groups. A new application group cannot be created with a Time (old style) data type. The OnDemand Administrator client provides three ways to create an application group: add, copy, export. The Time (old style) data type isn't available when using the add function regardless of the version of the server (V8.5 or V9). When an application group is copied with a Time field, the Time (old style) data type is shown for reference but it must be changed before the new application group can be added. When an application group with a Time (old style) field is exported, the export fails and an error message is displayed. The Time (old style) field type has been removed because of its limited granularity.

The old date/time data types in the batch administration program (ARSXML) have not been renamed. Instead, new date/time data types have been added called Date (native), Date/Time (native), and Date/Time (TZ) (native). These are the same as Date, Date/Time and Date/Time (TZ) in the OnDemand Administrator client. If an application group is exported using arsxml with a Time field, the resulting XML file contains "Time" as the value for the dataType attribute. The XML file can be used to update the existing application group but it cannot be used to create a new application group.

As can be seen, the new field Date/Time (TZ) now has extended ability to store up to 6 digits of precision (microsecond) – which is now used by the Content Manager OnDemand System Log and System Load application groups in order to represent more granular timing of activity on the system. In order to use these new types for the System Log/Load facility, use the –u option to the arssyscr command to update the existing application group(s). Although it is recommended that customers use these new fields, it is not required to update these application groups immediately upon installing Content Manager OnDemand V9.0 – the update of System Log/Load can happen at any time.

You can create folder fields of type Date (old style) and map them to an application group field of type Date (old style) and/or Date. You can also create a folder field of type Date and map to an application group field of type Date (old style) and/or Date. It should also be noted that the only difference between the folder field types are that the minimum/maximum and default values that can be placed into those types must fall within the allowed range for that type. Same is true for Date/Time and Date/Time (old style) as well as Date/Time (TZ) and Date/Time (TZ) (old style).

What about existing application groups?

In Content Manager OnDemand V9.0, the OnDemand Administrator client as well as the ARSXML command both support a way in which to update an existing application group from using old style formats to the new style formats. In the OnDemand Administrator client, under the Update an Application Group, under the General tab, under the Advanced button, there is an option Create/Use new style date/time field types in place of existing old style date/time field types. Selecting this check box causes the application group to be modified with new application group date/time fields. The equivalent approach with ARSXML is to specify the attribute createAndUseNewDates="Yes" for the application group to be updated. This does not modify the previously defined old style field types, nor does it modify any previously loaded data to that application group.

However, it does cause the following actions:

1. The application group is updated with the new date/time fields (could be 1 or more depending on the application group definitions). You will not ‘see’ the new field types defined in the application group. They are hidden to simplify the definitions. If you had only a single Date value in the application group, you will continue to see only a single Date value defined using the Admin, but looking at the application group data table directly you would see two columns; one for the Date (old style) and one for the new Date field.
2. The existing application group data tables are altered such that a new column (with a value of NULL) is added for each new date/time field. The new column name takes the existing date/time database field name and adds _dt or _dt<num> to the end of the name.
3. All existing application group data tables are closed, so that no further data can be loaded into them. On zOS it is possible to create an application group such that only a single application group data table is generated, however with these new fields types, the old data table is closed and a new table created.
4. Once new data is attempted to be stored into the updated application group, a new table will be created and all new metadata values will be stored into the new date/time fields. The old style date/time field(s) will be stored with a value of -1.
5. Using any Content Manager OnDemand V9.0 or later client guarantees that any query will transparently and appropriately be made across any application group data tables regardless of the updated field types. See the next section to understand the new Content Manager OnDemand date/time query syntax.

In Content Manager OnDemand V9.0 we have introduced a new syntax, making it easier to create a query that includes date values. This new query syntax is also used to ensure that any updated application group is properly queried regardless of the existing or new date/time data in the application group. Previously, the SQL syntax for Content Manager OnDemand date/time resulted in a query which used the database field name and the Content Manager OnDemand internal date values.

For example, prior to Content Manager OnDemand V9.0 to query for a date based on 2012/09/17, the query would look like:

• WHERE crd_date = 15601

• with Content Manager OnDemand V9.0 or later, the same query will look like:

• WHERE ODDAT_crd_date = ‘2012-09-17’

A query between two date ranges:

• WHERE crd_date BETWEEN 15570 AND 15601
  
  with Content Manager OnDemand V9.0 or later:
  
  WHERE ODDAT_crd_date BETWEEN ‘2012-08-17’ AND ‘2012-09-17’




 

Using Content Manager OnDemand tokenized query:

• 2;crd_date,crd_date; 15570,15601;WHERE crd_date BETWEEN ? AND ?
  
  with Content Manager OnDemand V9.0 or later:
  
  2;ODDAT_crd_date,ODDAT_crd_date; ‘2012-08-17’, ‘2012-09-17’;WHERE ODDAT_crd_date BETWEEN ? AND ?

Regardless if the application group has old date types, new date types, or updated date types, the Content Manager OnDemand server modifies the SQL such that it maps to the correct value(s). The key changes that the SQL syntax requires:
 

1. ODDAT_ gets prefixed to the database field name
2. There should always be a space between the field name, the operator and the value.
3. Expected value format
   • Date and/or Date (old style):
     ‘YYYY-MM-DD’
   • Date/Time and /or Date/Time (old style):
     ‘YYYY-MM-DD HH:MM:SS’
   • Date/Time (TZ) and /or Date/Time (TZ) (old style):
     ‘YYYY-MM-DD HH:MM:SS.FFFFFF’ where FFFFFF consists of 6 fractional digits

If this syntax is not properly followed it is possible for the SQL to not get correctly modified by the Content Manager OnDemand server which might result in either invalid SQL getting passed to the database, or a successful query but not including all possible results. Content Manager OnDemand modifies the new syntax such that any query made based on an old style date/time field is converted to the Content Manager OnDemand internal date/time value. For new style date/time, Content Manager OnDemand ensures the syntax matches the expectations of the underlying database.

For Date/Time (TZ) or Date/Time (TZ) (old style), the value(s) must always be in Coordinated Universal Time (UTC), sometimes known as GMT, as all values in the database are always in UTC.