DB2 Server for VSE: 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 24.

Table 24. 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 25.

Table 25. 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 member

to specify your datetime defaults, then start the DBS utility, specifying the ARISDTM member as the control file.

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:

• When you convert the external form to an internal form:
  • When datetime data is entered by INSERT or UPDATE statements, or by the DATALOAD commands of the DBS utility, or by the INPUT command of ISQL.
  • When a constant or host variable is compared with a DATE or TIME column. The constant can be converted during preprocessing time.
  • When the DATE or TIME scalar functions are used with a string representation of a date or time.

  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.

• When you convert the internal form to an external form:
  • When data is retrieved from the column by SELECT or FETCH statements, or by the DATAUNLOAD commands in the DBS utility, and the default format is local.
  • When the CHAR scalar function is used with the LOCAL format specification.

  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.

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 26 shows what is in the parameter list used by the date and time exits (see Register 1).

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

• The Eye-catcher and Length of list is initialized by the database manager.
• The Function Number is a full word number describing the function to be performed, as follows:

• The EXIT Global Area is not used. Both values are set to zero.
• The length of the ISO Date and Time Areas are 10 bytes and 8 bytes, respectively.
• The length of the LOCAL Date and Time Areas are as defined in the SYSTEM.SYSOPTIONS catalog table. The pointer to the length of the area points to a fullword that contains the value in this table.
• The User Work Area is a 512-byte area.
• The Environment Dependent Area is a 40-byte area. For the datetime exits, only byte 2 is used. It contains D for VSE, and V for VM.
• The Exit Return Code Area is a full word to be set by the exit to the return code.

  The possible return codes are:

  -1

  The exit supplied by the database manager has not been replaced by a user exit. The database manager then sets SQLCODE to -185.

  0

  The function has been performed.

  4

  Invalid date or time value. The database manager then sets SQLCODE to -181.

  8

  Date or time value not in valid format. The database manager then sets SQLCODE to -180.

  Other

  Error in exit. The function number of the exit will be stored in SQLERRD5, and the return code in SQLERRD1. The database manager then sets SQLCODE to -816.

  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.

Coding Your Own Datetime Exit

User-coded exits must conform to the following:

• The installation replaceable exits must be coded in Assembler language.
• The exits must be reentrant; they must save registers at entry and restore them before exit.
• The exits (and any of the modules they call) must not use any DB2 Server for VSE facilities.
• The exits must not use the return code -1.
• When formatting ISO datetime to LOCAL datetime, the user is responsible for formatting the full buffer (the number of bytes equal to the length of the local datetime as defined in the SYSTEM.SYSOPTIONS catalog table).
• The exits must support 31-bit addressing (AMODE 31) and be loaded below 16 megabytes (RMODE 24)

In an online environment, imbedded CICS commands (EXEC CICS) are not allowed.

Figure 110 shows the IBM-supplied ARIUXDT module, which is an A-type source member. You need to modify this source code to support your local date format requirements.

Figure 110. 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 111 shows the IBM-supplied ARIUXTM module. This module is an A-type source member named ARIUXTM. You can modify this source code to support your local time format requirements.

Figure 111. 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

After assembling your program, you must catalog the ARIUXDT TEXT (or ARIUXTM TEXT) file into your private sublibrary. (Your assembled version of ARIUXDT or ARIUXTM must be named ARIUXDT TEXT or ARIUXTM TEXT.) Then link-edit the exit router component ARIXSXR.

The ARIXSXR component contains the IBM-supplied version of ARIUXDT (or ARIUXTM). The link book name is ARISLKXD. When doing the link-edit, specify your private sublibrary ahead of the DB2 Server for VSE sublibrary on the LIBDEF statement that defines the search order. The link-edit will then replace the IBM-supplied version with your version.

An example of job control to install a user version of ARIUXDT or ARIUXTM is shown in Figure 112. Here, it is assumed that the ARIUXDT OBJ (or ARIUXTM OBJ) file is in a sublibrary called LIB.USER, and the ARIXSXR component will be replaced in PRD2.DB2710.

Figure 112. Example Job Control to Install ARIUXDT or ARIUXTM

// JOB INSTALL USER // LIBDEF *,SEARCH=(LIB.USER,PRD2.DB2710),CATALOG=PRD2.DB2710 * **************************************************************** * INSTALL ARIUXIT USER EXIT ROUTINE * * **************************************************************** // OPTION CATAL INCLUDE ARISLKXD // EXEC LNKEDT,PARM='MSHP' /* /&

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 member

to specify your datetime defaults, then call the DBS utility, specifying the ARISDTM member as the control file.