DB2 Server for VM: System Administration


Defining Your Own Datetime Format

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.

Datetime Formats

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.

Table 26. Date Formats
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.

Table 27. Time Formats
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.

Default Output Format

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.

How Datetime Exits Work

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.

When Date and Time Exits are Called (Exit Points)

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

Register 0
Undefined.

Register 1
Points to a pointer to the parameter list for ARIUXDT (or ARIUXTM). The format of the parameter list is discussed below. The first field in it is an eye-catcher value.

Register 2--12
Undefined.

Register 13
Points to a standard register save area.

Register 14
Contains the return address.

Register 15
Contains the entry point of the user installation routine.

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.

Coding Your Own Datetime Exit

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

 
         TITLE ' ARIUXDT'
***********************************************************************
* ARIUXDT USER DATE CONVERSION ROUTINE                                *
*     REGISTER ASSUMPTIONS:                                           *
*         R1  -> PARMLIST                                             *
*         R13 -> SAVE AREA                                            *
*         R14 -> RETURN ADDRESS                                       *
*         R15 -> ENTRY POINT                                          *
*                                                                     *
* ALTHOUGH PROVIDED IN A GENERAL INTERFACE LIBRARY, ARIUXIT IS NOT TO *
* BE USED AS A GENERAL PROGRAMMING INTERFACE.  REFER TO PRODUCT       *
* DOCUMENTATION TO DETERMINE INTENDED USAGE.                          *
***********************************************************************
ARIUXDT  CSECT ,
ARIUXDT  AMODE 31
ARIUXDT  RMODE 24
         USING *,R15                   ESTABLISH TEMP ADDRESSABILITY
         B     PROLOG                  BRANCH TO START OF PROGRAM
         DC    C'ARIUXDT'
         DROP  R15                     DROP R15 AND USE OWN ADDRESSABIL-
*                                      ITY
PROLOG   STM   R14,R12,12(R13)         SAVE REGS IN CALLER'S AREA
         LR    R12,R15                 SAVE BASE REGISTER
PSTART   EQU   ARIUXDT                 START OF PROGRAM
         USING PSTART,R12              SET UP BASE REGISTER
         L     R1,0(R1)                POINT TO THE PARAMETER LIST
         USING PARMLIST,R1             ADDRESSABILITY FOR INPUT PARMS
         L     R2,FNPTR                POINT TO FUNCTION TYPE
***********************************************************************
*  M A I N L I N E
***********************************************************************
MAINLINE DS    0H                      START OF CODE
         SPACE
         SR    R15,R15                 INITIALIZE RETURN CODE TO ZERO
***********************************************************************
* HERE YOU WOULD PLACE CODE THAT GETS AND VERIFIES YOUR
* INPUT DATE AND CONVERTS IT TO EITHER TO LOCAL FORMAT OR ISO FORMAT
* A RETURN CODE OF -1 MEANS AN EXIT IS NOT PROVIDED
* A RETURN CODE OF 0 MEANS CONVERSION WAS SUCCESSFUL
* A RETURN CODE OF 4 MEANS THAT THE DATE VALUE WAS OUT OF RANGE
* A RETURN CODE OF 8 MEANS THAT THE DATE WAS INVALID
***********************************************************************
         BCTR  R15,R0                  EXIT NOT PROVIDED
         B     RETURN                  CONVERSION COMPLETE
 
***********************************************************************
*  RETURN TO CALLER
***********************************************************************
RETURN   DS    0H                      RETURN POINT
         L     R2,RETPTR               LOAD RETCODE PTR
         ST    R15,0(R2)               STORE EXIT RETURN CODE
         L     R14,12(,R13)            RESTORE R14
         LM    R0,R12,20(R13)          RESTORE REST OF CALLER'S REGS
         BR    R14                     RETURN TO CALLER
         EJECT
PARMLIST DSECT ,                       INPUT PARAMETER LIST
EYECATCH DS    CL8                     EYECATCHER
PLEN     DS    F                       LENGTH OF PARAMETER LIST
FNPTR    DS    AL4                     POINTER TO FUNCTION TYPE
FNLENP   DS    AL4                     LENGTH OF FUNCTION TYPE
GLBPTR   DS    AL4                     POINTER TO GLOBAL EXIT AREA
GLBLENP  DS    AL4                     LENGTH OF GLOBAL EXIT AREA
ISOPTR   DS    AL4                     POINTER TO ISO DATETIME AREA
ISOLENP  DS    AL4                     LENGTH OF ISO DATETIME AREA
LOCPTR   DS    AL4                     POINTER TO LOCAL DATETIME AREA
LOCLENP  DS    AL4                     LENGTH OF LOCAL DATETIME AREA
WORKPTR  DS    AL4                     POINTER TO USER WORK AREA
WORKLENP DS    AL4                     LENGTH OF USER WORK AREA
ENVPTR   DS    AL4                     POINTER TO ENVIR. DEPENDANT AREA
ENVLENP  DS    AL4                     LENGTH OF ENVIR. DEPENDANT AREA
RETPTR   DS    AL4                     POINTER TO RETURN CODE AREA
RETLENP  DS    AL4                     LENGTH OF RETURN CODE AREA
         EJECT
ARIUXDT  CSECT ,
R0       EQU   00                      EQUATES FOR REGISTERS 0-15
R1       EQU   01
R2       EQU   02
R3       EQU   03
R4       EQU   04
R5       EQU   05
R6       EQU   06
R7       EQU   07
R8       EQU   08
R9       EQU   09
R10      EQU   10
R11      EQU   11
R12      EQU   12
R13      EQU   13
R14      EQU   14
R15      EQU   15
         END   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

         TITLE ' ARIUXTM'
 
***********************************************************************
* ARIUXTM USER TIME CONVERSION ROUTINE                                *
*     REGISTER ASSUMPTIONS:                                           *
*         R1  -> PARMLIST                                             *
*         R13 -> SAVE AREA                                            *
*         R14 -> RETURN ADDRESS                                       *
*         R15 -> ENTRY POINT                                          *
*                                                                     *
* ALTHOUGH PROVIDED IN A GENERAL INTERFACE LIBRARY, ARIUXIT IS NOT TO *
* BE USED AS A GENERAL PROGRAMMING INTERFACE.  REFER TO PRODUCT       *
* DOCUMENTATION TO DETERMINE INTENDED USAGE.                          *
***********************************************************************
ARIUXTM  CSECT ,
ARIUXTM  AMODE 31
ARIUXTM  RMODE ANY
         USING *,R15                   ESTABLISH TEMP ADDRESSABILITY
         B     PROLOG                  BRANCH TO START OF PROGRAM
         DC    C'ARIUXTM'
         DROP  R15                     DROP R15 AND USE OWN ADDRESSABIL-
*                                      ITY
PROLOG   STM   R14,R12,12(R13)         SAVE REGS IN CALLER'S AREA
         LR    R12,R15                 SAVE BASE REGISTER
PSTART   EQU   ARIUXTM                 START OF PROGRAM
         USING PSTART,R12              SET UP BASE REGISTER
         L     R1,0(R1)                POINT TO PARAMETER LIST
         USING PARMLIST,R1             ADDRESSABILITY FOR INPUT PARMS
         L     R2,FNPTR                POINT TO FUNCTION TYPE
***********************************************************************
*  M A I N L I N E
***********************************************************************
MAINLINE DS    0H                      START OF CODE
         SPACE
         SR    R15,R15                 INITIALIZE RETURN CODE TO ZERO
***********************************************************************
* HERE YOU WOULD PLACE CODE THAT GETS AND VERIFIES YOUR
* INPUT TIME AND CONVERTS IT TO EITHER TO LOCAL FORMAT OR ISO FORMAT
* A RETURN CODE OF -1 MEANS AN EXIT IS NOT PROVIDED
* A RETURN CODE OF 0 MEANS CONVERSION WAS SUCCESSFUL
* A RETURN CODE OF 4 MEANS THAT THE TIME VALUE WAS OUT OF RANGE
* A RETURN CODE OF 8 MEANS THAT THE TIME WAS INVALID
***********************************************************************
         BCTR  R15,R0                  EXIT NOT PROVIDED
         B     RETURN                  CONVERSION COMPLETE
 
***********************************************************************
*  RETURN TO CALLER
***********************************************************************
RETURN   DS    0H                      RETURN POINT
         L     R2,RETPTR               LOAD RETCODE PTR
         ST    R15,0(R2)               STORE EXIT RETURN CODE
         L     R14,12(,R13)            RESTORE R14
         LM    R0,R12,20(R13)          RESTORE REST OF CALLER'S REGS
         BR    R14                     RETURN TO CALLER
         EJECT
PARMLIST DSECT ,                       INPUT PARAMETER LIST
EYECATCH DS    CL8                     EYECATCHER
PLEN     DS    F                       LENGTH OF PARAMETER LIST
FNPTR    DS    AL4                     POINTER TO FUNCTION TYPE
FNLENP   DS    AL4                     LENGTH OF FUNCTION TYPE
GLBPTR   DS    AL4                     POINTER TO GLOBAL EXIT AREA
GLBLENP  DS    AL4                     LENGTH OF GLOBAL EXIT AREA
ISOPTR   DS    AL4                     POINTER TO ISO DATETIME AREA
ISOLENP  DS    AL4                     LENGTH OF ISO DATETIME AREA
LOCPTR   DS    AL4                     POINTER TO LOCAL DATETIME AREA
LOCLENP  DS    AL4                     LENGTH OF LOCAL DATETIME AREA
WORKPTR  DS    AL4                     POINTER TO USER WORK AREA
WORLENP  DS    AL4                     LENGTH OF USER WORK AREA
ENVPTR   DS    AL4                     POINTER TO ENVIR. DEPENDANT AREA
ENVLENP  DS    AL4                     LENGTH OF ENVIR. DEPENDANT AREA
RETPTR   DS    AL4                     POINTER TO RETURN CODE AREA
RETLENP  DS    AL4                     LENGTH OF RETURN CODE AREA
         EJECT
ARIUXTM  CSECT ,
R0       EQU   00                      EQUATES FOR REGISTERS 0-15
R1       EQU   01
R2       EQU   02
R3       EQU   03
R4       EQU   04
R5       EQU   05
R6       EQU   06
R7       EQU   07
R8       EQU   08
R9       EQU   09
R10      EQU   10
R11      EQU   11
R12      EQU   12
R13      EQU   13
R14      EQU   14
R15      EQU   15
         END   ARIUXTM

After the program is coded, assemble it as you would any other program.

Installing Your Version of ARIUXDT or ARIUXTM

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.

Step 1. Stop the Application Server

Stop the application server using your normal operating procedures.

Step 2. Log on to the Installation or Service User ID

Log on to the installation or service user ID, 5697F42S.

Step 3. Establish the Minidisk or SFS Directory Order

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.

Step 4. Copy the MACRO Source Code (First Time Only)

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.

Step 5. Edit the ASSEMBLE File

Edit the ARIUXDT ASSEMBLE or the ARIUXTM ASSEMBLE file on the local modification disk (2C2) and make your changes.

Step 6. Update the Local Version Vector Table

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

Step 7. Assemble the File

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:

  1. Other options are available for the assemble commands. Consult the VM/ESA: VMSES/E Introduction and Reference for additional information.

  2. We recommend that you use the VMFHLASM command supplied by VMSES/E. Also available are the VMFHASM and VMFASM commands.

  3. If the assemble function is successful, the ARIUXDT TXTLnnnn file or the ARIUXTM TXTLnnnn file is placed on the LOCALSAM 2C2(E) disk.

Step 8. Build your New Local Modification

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.

Step 9. Place the New Local Modification into Production

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.

Step 10. Restart the Application Server

Restart the applicaton server in multiple user mode with the required PROTOCOL parameter.

Updating the SYSTEM.SYSOPTIONS Catalog Table

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.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]