The database manager supports many datetime formats. This section describes the datetime formats and how you can add your own by coding your own exit.
The database manager supports DATE, TIME, and TIMESTAMP data types and operations. You can enter a date or a time using many different formats.
Dates can be entered in any of the formats shown in Table 26.
Format Name | Abbreviation | Date Format | Example |
---|---|---|---|
International Standards Organization | ISO | yyyy-mm-dd | 1993-12-31 |
IBM USA standard | USA | mm/dd/yyyy | 12/31/1993 |
IBM European standard | EUR | dd.mm.yyyy | 31.12.1993 |
Japanese Industrial Standard Christian Era | JIS | yyyy-mm-dd | 1993-12-31 |
Site-defined | LOCAL | Any site-defined form | -- |
Times can be entered in any of the formats shown in Table 27.
Format Name | Abbreviation | Time Format | Example |
---|---|---|---|
International Standards Organization | ISO | hh.mm[.ss] | 13.30.05 |
IBM USA standard | USA | hh:mm AM or PM | 1:30 PM |
IBM European standard | EUR | hh.mm[.ss] | 13.30.05 |
Japanese Industrial Standard Christian Era | JIS | hh:mm[:ss] | 13:30:05 |
Site-defined | LOCAL | Any site-defined form | -- |
To define the LOCAL format, you have to code your own date or time exit. For information about coding your own datetime exit, see Coding Your Own Datetime Exit.
When the database manager is installed, the default date and time formats are both ISO. To change them, you must change the entry in the SYSTEM.SYSOPTIONS table. You must have DBA authority to do this.
For example, to specify that the date output format is USA, enter:
UPDATE SYSTEM.SYSOPTIONS - SET VALUE='USA' WHERE SQLOPTION='DATE'
Similarly, to specify that the time output format is JIS, you enter:
UPDATE SYSTEM.SYSOPTIONS - SET VALUE='JIS' WHERE SQLOPTION='TIME'
Alternatively, you can update the SYSTEM.SYSOPTIONS table by modifying the IBM-supplied ARISDTM MACRO
to specify your datetime defaults, then start the DBS utility, specifying the ARISDTM MACRO file as the control file. To modify the ARISDTM MACRO, use the VMSES/E full part replacement local modification procedure. For details, see the appendix in the DB2 Server for VM Program Directory describing local modifications.
Two datetime installation replaceable exits are provided to
allow you to convert datetime values in any installation-defined format into ISO format, or from ISO format into any installation-defined format. These exits which are link-edited into the exit router component ARIXSXR, are called ARIUXDT and ARIUXTM for date and time, respectively.
When the database manager is installed, ARIXSXR is loaded and addressability to the exits is set.
The entries in the SYSTEM.SYSOPTIONS catalog table are used by the database manager to determine the default datetime format for output.
If SYSTEM.SYSOPTIONS indicates that local datetime exits are present, the exits are called during SQL statement processing when conversion between internal and external forms is required.
The product-supplied exits return a -1 return code, meaning the exits have not been replaced by the user exits. If a user program issues an SQL statement that calls the exits, SQLCODE -185 is returned. Therefore, if the user is to replace the exits, the -1 return code must not be used.
If a program has been preprocessed with the LOCAL format, or if the installation default is LOCAL, then the datetime exits are called before any interpretation of the datetime data values. Otherwise, the database manager attempts to interpret the datetime data values first. In this situation, it calls the local exit only if it does not recognize the datetime value.
The datetime exits are called at the following times:
The exit should then convert the installation-defined format into ISO format. The ISO format is then validated and converted into an internal format to be entered into the column or used in comparisons. If the column is a key column for an index, the index entry is made in an internal format.
At this point, the exit should convert the value from ISO format into installation-defined format; then the database manager returns the converted value. In this situation, the exit is called after any edit routine or sort.
The exits are called in the AMODE that the database manager is running in. If the exits do not support 31-bit addressing, the database manager must be started with the AMODE(24) parameter. If the database manager is running in AMODE(24), the load module for the exit must be generated with RMODE 24.
When the exits are called, the registers are set as follows:
Registers 2--13 must be saved and restored by the exit. If this is not done, the results will be unpredictable.
Table 28 shows what is in the parameter list used by the date and time exits (see Register 1).
Table 28. Parameter List Used by Date and Time Exits
Length | Description |
---|---|
2 words 1 word |
Eye-catcher: ARIUXDT or ARIUXTM Length of parameter list |
1 word 1 word |
Pointer to Function Number Pointer to length of Function Number |
1 word 1 word |
Pointer to Exit Global Area Pointer to length of Exit Global Area |
1 word 1 word |
Pointer to ISO Datetime Area Pointer to length of ISO Datetime Area |
1 word 1 word |
Pointer to LOCAL Datetime Area Pointer to length of LOCAL Datetime Area |
1 word 1 word |
Pointer to User Work Area Pointer to length of User Work Area |
1 word 1 word |
Pointer to Environment Dependent Area Pointer to length of Environment Dependent Area |
1 word 1 word |
Pointer to Exit Return Code Area Pointer to length of Exit Return Code Area |
Each area in the parameter list is described below.
Number | Function |
---|---|
00000004 00000008 |
DATE Functions: Convert DATE from LOCAL format to ISO format. Convert DATE from ISO format to Installation format. |
00000004 00000008 |
TIME Functions: Convert TIME from LOCAL format to ISO format. Convert TIME from ISO format to Installation format. |
The possible return codes are:
The exit name, function code and return code are set up as message tokens in SQLERRM; they are used when the message associated with SQLCODE -816 is displayed, for example, by the DBS utility and ISQL.
If a program has been preprocessed with the LOCAL format, or if the installation default is LOCAL, then the database manager evaluates the output of the datetime exit if the return code is either 0 or 8. Otherwise, the output is evaluated only if the return code is 0.
User-coded exits must conform to the following:
Figure 113 shows the IBM-supplied ARIUXDT module, which is on the service disk. Its file name and file type are ARIUXDT MACRO. You need to modify this source code to support your local date format requirements.
Figure 113. IBM-Supplied Version of ARIUXDT
Figure 114 shows the IBM-supplied ARIUXTM module. This module is on the service disk. Its file name and file type are ARIUXTM MACRO. You can modify this source code to support your local time format requirements.
Figure 114. IBM-Supplied Version of ARIUXTM
After the program is coded, assemble it as you would any other program.
During customization of DB2 Server for VM, you may have to modify TEXT files that are serviced as full part replacement (for example, for user exits, such as ARIUXDT and ARIUXTM). The source is shipped as a MACRO file.
Use the following steps to create and build the affected objects with the new parts. These steps use the VMSES/E local modification procedure.
Stop the application server using your normal operating procedures.
Log on to the installation or service user ID, 5697F42S.
Establish the access order.
vmfsetup 5697F42S {DB2VM|DB2VMSFS}
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
If you have your own PPF override, then substitute that name for the 5697F42S name shown in this command and any following commands.
Copy the MACRO source code to the local modification disk as ASSEMBLE. This only needs to be done the first time you apply local modifications to this part; otherwise, you will delete your version of the ASSEMBLE file.
vmfrepl {ariuxdt|ariuxtm} macro 5697F42S {DB2VM|DB2VMSFS} (filetype assemble outmode localsam
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
Edit the ARIUXDT ASSEMBLE or the ARIUXTM ASSEMBLE file on the local modification disk (2C2) and make your changes.
Update the local version vector table for the assembled TEXT file.
vmfrepl {ariuxdt|ariuxtm} text 5697F42S {DB2VM|DB2VMSFS} (logmod modid nocopy
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
modid is the new local modification identifier for the part (for example, L0001).
Use the VMFHLASM command to assemble ARIUXDT or ARIUXTM.
vmfhlasm {ariuxdt|ariuxtm} 5697F42S {DB2VM|DB2VMSFS} (nockgen $select outmode localsam
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
Notes:
Build your new local modification on the test build disks.
vmfbld ppf 5697F42S {DB2VM|DB2VMSFS} (status vmfbld ppf 5697F42S {DB2VM|DB2VMSFS} (serviced
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
After you issue the VMFBLD command with the STATUS option, you can use the VMFVIEW BUILD command to view the $VMFBLD $MSGLOG file to see which objects will be built.
Use ARISINST to place the new local modiciation into production.
arisinst c 5697F42S {DB2VM|DB2VMSFS}
Use DB2VM for installing on minidisks or DB2VMSFS for installing in Shared File System directories.
The C function of ARISINST copies the test service and production build disks to the database machine (SQLMACH) production service and production build disks.
Restart the applicaton server in multiple user mode with the required PROTOCOL parameter.
You need to update the SYSTEM.SYSOPTIONS catalog table to specify the length of your local datetime format.
If you installed a local date or time format, you can update the local date or time length by using the database manager. For example, if the length of your local date format is 10 bytes, enter:
UPDATE SYSTEM.SYSOPTIONS - SET VALUE = '10' - WHERE SQLOPTION = 'LDATELEN'
The local date length specified must be greater than 9 and less than 255.
If the length of your local time format is 8 bytes, enter:
UPDATE SYSTEM.SYSOPTIONS - SET VALUE = '8' - WHERE SQLOPTION = 'LTIMELEN'
The local time length specified must be greater than 7 and less than 255.
The changes will be in effect the next time the application server is started.
You can also update the SYSTEM.SYSOPTIONS table by modifying the IBM-supplied ARISDTM MACRO
to specify your datetime defaults, then call the DBS utility, specifying the ARISDTM MACRO file as the control file. To modify the ARISDTM MACRO, use the VMSES/E full part replacement local modification procedure. For details on local modifications, see the appendix in the DB2 Server for VM Program Directory.