db2utils¶

Prototypes¶

ASSERT_EQUALS(A INTEGER, B INTEGER)
ASSERT_EQUALS(A DOUBLE, B DOUBLE)
ASSERT_EQUALS(A TIMESTAMP, B TIMESTAMP)
ASSERT_EQUALS(A TIME, B TIME)
ASSERT_EQUALS(A VARCHAR(4000), B VARCHAR(4000))

RETURNS INTEGER

Description¶

Raises the ASSERT_FAILED_STATE state if A does not equal B. The function is overloaded for most common types and generally should not need CASTs for usage. The return value in the case that the values are equal is arbitrary.

Parameters¶

A

The first value to compare

B

The value to compare to A

Examples¶

Test that TIMESTAMP is constant within an expression:

VALUES ASSERT_EQUALS(CURRENT TIMESTAMP, CURRENT TIMESTAMP);

1
-----------
          0

Test an obvious failure:

VALUES ASSERT_EQUALS(1, 2);

1
-----------
SQL0438N  Application raised error or warning with diagnostic text: "1 does
not equal 2".  SQLSTATE=90001

See Also¶

• Source code
• ASSERT_NOT_EQUALS scalar function
• ASSERT_IS_NULL scalar function
• ASSERT_IS_NOT_NULL scalar function
• ASSERT_FAILED_STATE

Prototypes¶

ASSERT_IS_NOT_NULL(A INTEGER)
ASSERT_IS_NOT_NULL(A DOUBLE)
ASSERT_IS_NOT_NULL(A TIMESTAMP)
ASSERT_IS_NOT_NULL(A TIME)
ASSERT_IS_NOT_NULL(A VARCHAR(4000))

RETURNS INTEGER

Description¶

Raises the ASSERT_FAILED_STATE state if A is NULL. The function is overloaded for most common types and generally should not need CASTs for usage. The return value in the case that the value is not NULL is arbitrary.

Parameters¶

A

The value to check for NULL.

Examples¶

Test an obvious tautology:

VALUES ASSERT_IS_NOT_NULL(1);

1
-----------
          0

Test that the DATE scalar function function returns NULL on NULL input:

VALUES ASSERT_IS_NOT_NULL(DATE(2000, 1, NULL));

1
-----------
SQL0438N  Application raised error or warning with diagnostic text: "Value
is NULL".  SQLSTATE=90001

See Also¶

• Source code
• ASSERT_IS_NULL scalar function
• ASSERT_NOT_EQUALS scalar function
• ASSERT_EQUALS scalar function
• ASSERT_FAILED_STATE

Prototypes¶

ASSERT_IS_NULL(A INTEGER)
ASSERT_IS_NULL(A DOUBLE)
ASSERT_IS_NULL(A TIMESTAMP)
ASSERT_IS_NULL(A TIME)
ASSERT_IS_NULL(A VARCHAR(4000))

RETURNS INTEGER

Description¶

Raises the ASSERT_FAILED_STATE state if A is not NULL. The function is overloaded for most common types and generally should not need CASTs for usage. The return value in the case that the value is NULL is arbitrary.

Parameters¶

A

The value to check for NULL.

Examples¶

Test an obvious failure:

VALUES ASSERT_IS_NULL(1);

1
-----------
SQL0438N  Application raised error or warning with diagnostic text: "1 is
non-NULL".  SQLSTATE=90001

Test that the DATE scalar function function returns NULL on NULL input:

VALUES ASSERT_IS_NULL(DATE(2000, 1, NULL));

1
-----------
          0

See Also¶

• Source code
• ASSERT_IS_NOT_NULL scalar function
• ASSERT_NOT_EQUALS scalar function
• ASSERT_EQUALS scalar function
• ASSERT_FAILED_STATE

Prototypes¶

ASSERT_NOT_EQUALS(A INTEGER, B INTEGER)
ASSERT_NOT_EQUALS(A DOUBLE, B DOUBLE)
ASSERT_NOT_EQUALS(A TIMESTAMP, B TIMESTAMP)
ASSERT_NOT_EQUALS(A TIME, B TIME)
ASSERT_NOT_EQUALS(A VARCHAR(4000), B VARCHAR(4000))

RETURNS INTEGER

Description¶

Raises the ASSERT_FAILED_STATE state if A equals B. The function is overloaded for most common types and generally should not need CASTs for usage. The return value in the case that the values aren’t equal is arbitrary.

Parameters¶

A

The first value to compare

B

The value to compare to A

Examples¶

Test that the LEFT function works:

VALUES ASSERT_NOT_EQUALS('AAA', LEFT('AAA', 1));

1
-----------
          0

Test an obvious failure:

VALUES ASSERT_NOT_EQUALS(1, 1);

1
-----------
SQL0438N  Application raised error or warning with diagnostic text: "
Values are both 1".  SQLSTATE=90001

See Also¶

• Source code
• ASSERT_EQUALS scalar function
• ASSERT_IS_NULL scalar function
• ASSERT_IS_NOT_NULL scalar function
• ASSERT_FAILED_STATE

Prototypes¶

AUTH_DIFF(SOURCE VARCHAR(128), SOURCE_TYPE VARCHAR(1), DEST VARCHAR(128), DEST_TYPE VARCHAR(1), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTH_DIFF(SOURCE VARCHAR(128), DEST VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTH_DIFF(SOURCE VARCHAR(128), DEST VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1))

RETURNS TABLE(
  OBJECT_TYPE VARCHAR(18),
  OBJECT_ID VARCHAR(262),
  AUTH VARCHAR(140),
  SUFFIX VARCHAR(20),
  LEVEL SMALLINT
)

Description¶

This utility function determines the difference in authorizations held by two different entities (as determined by AUTHS_HELD table function). Essentially it takes the authorizations of the SOURCE entity and “subtracts” the authorizations of the DEST entity, the result being the authorizations that need to be granted to DEST to give it the same level of access as SOURCE. This is used in the definition of the COPY_AUTH procedure routine.

Parameters¶

SOURCE

The name to check for existing authorizations.

SOURCE_TYPE

The type of the SOURCE parameter. Specify 'U', 'G', or 'R' for User, Group or Role respectively. If this parameter is omitted, the type will be determined by the AUTH_TYPE scalar function function.

DEST

The intended destination for the authorizations held by SOURCE.

DEST_TYPE

The type of the DEST parameter. Takes the same values as SOURCE_TYPE. If omitted, the type will be determined by the AUTH_TYPE scalar function function.

INCLUDE_COLUMNS

If this parameter is 'Y', column level authorizations will be included.

INCLUDE_PERSONAL

If this parameter is 'Y', and SOURCE identifies a user, then authorizations for the source user’s personal schema will be included in the result. This parameter defaults to 'N' when omitted.

Returns¶

See the AUTHS_HELD table function documentation for a description of the columns of the returned table (this routine is essentially a “subtraction” of two AUTHS_HELD calls hence the output structure is identical).

Examples¶

Show the authorizations directly granted to the DB2INST1 user which the currently logged on user does not possess.

SELECT * FROM TABLE(AUTH_DIFF('DB2INST1', USER, 'N'));

OBJECT_TYPE OBJECT_ID                 AUTH        SUFFIX               LEVEL
----------- ------------------------- ----------- -------------------- ------
PACKAGE     NULLID.POLYH03            CONTROL                               0
INDEX       SYSTOOLS.ATM_UNIQ         CONTROL                               0
INDEX       SYSTOOLS.HI_OBJ_UNIQ      CONTROL                               0
TABLE       SYSTOOLS.HMON_ATM_INFO    CONTROL                               0
TABLE       SYSTOOLS.HMON_COLLECTION  CONTROL                               0
TABLE       SYSTOOLS.POLICY           CONTROL                               0
INDEX       SYSTOOLS.POLICY_UNQ       CONTROL                               0
TABLE       SYSTOOLS.HMON_ATM_INFO    ALTER       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  ALTER       WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           ALTER       WITH GRANT OPTION         1
PACKAGE     NULLID.POLYH03            BIND        WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    DELETE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  DELETE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           DELETE      WITH GRANT OPTION         1
PACKAGE     NULLID.POLYH03            EXECUTE     WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    UPDATE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  UPDATE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           UPDATE      WITH GRANT OPTION         1
TABLESPACE  SYSTOOLSPACE              USE         WITH GRANT OPTION         1
TABLESPACE  SYSTOOLSTMPSPACE          USE         WITH GRANT OPTION         1

See Also¶

• Source code
• AUTH_TYPE scalar function
• AUTHS_HELD table function
• COPY_AUTH procedure
• MOVE_AUTH procedure
• REMOVE_AUTH procedure

Prototypes¶

AUTH_TYPE(AUTH_NAME VARCHAR(128))
RETURNS VARCHAR(1)

Description¶

This is a utility function used by the COPY_AUTH procedure procedure, and other associated procedures. Given an authorization name, this scalar function returns 'U', 'G', or 'R' to indicate that AUTH_NAME is a user, group, or role respectively (based on the content of the system catalog tables). If the name is undefined, 'U' is returned, unless the name is PUBLIC in which case 'G' is returned (for consistency with the catalog tables). If the name represents multiple authorization types, SQLSTATE 21000 is raised.

Parameters¶

AUTH_NAME

The authorization name to test for type.

Examples¶

Show the type of the PUBLIC authorization name.

VALUES AUTH_TYPE('PUBLIC');

1
-
G

Show the type of the authorization name of the currently logged on user.

VALUES AUTH_TYPE(CURRENT USER);

1
-
U

See Also¶

• Source code
• AUTHS_HELD table function
• AUTH_DIFF table function
• COPY_AUTH procedure
• MOVE_AUTH procedure
• REMOVE_AUTH procedure

Prototypes¶

AUTHS_HELD(AUTH_NAME VARCHAR(128), AUTH_TYPE VARCHAR(1), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTHS_HELD(AUTH_NAME VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTHS_HELD(AUTH_NAME VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1))

RETURNS TABLE(
    OBJECT_TYPE VARCHAR(18),
    OBJECT_ID VARCHAR(262),
    AUTH VARCHAR(140),
    SUFFIX VARCHAR(20),
    LEVEL SMALLINT
)

Description¶

This is a utility function used by COPY_AUTH procedure, and other associated procedures, below. Given an authorization name and type, and a couple of flags, this table function returns the details of all the authorizations held by that name. The information returned is sufficient for comparison of authorizations and generation of GRANT/REVOKE statements.

Parameters¶

AUTH_NAME

The authorization name to query authorizations for.

AUTH_TYPE

The type of the authorization name. Use 'U' for users, 'G' for groups, or 'R' for roles. If this parameter is omitted the type will be determined by calling the AUTH_TYPE scalar function function.

INCLUDE_COLUMNS

If this is 'Y' then include column-level authorizations for relations (tables, views, etc). This is useful when generating REVOKE statements from the result (as column level authorizations cannot be revoked directly in DB2).

INCLUDE_PERSONAL

This parameter controls whether, in the case where AUTH_NAME refers to a user (as opposed to a group or role), authorizations associated with the user’s personal schema are included in the result. If set to 'Y', personal schema authorizations are included. Defaults to 'N' if omitted.

Returns¶

The function returns one row per authorization found in the system catalogs for the specified authorization name. Each row contains the following columns:

OBJECT_TYPE

This column typically contains a string indicating the type of object identified by the OBJECT_ID column. However, given that this routine’s primary purpose is to aid in the generation of GRANT and REVOKE statements, and given the inconsistencies in the numerous GRANT and REVOKE syntaxes employed by DB2, this column is blank for certain object types (roles and security labels), and misleading for others (e.g. 'TABLE' is returned for all relation types including views).

OBJECT_ID

The identifier of the object the authorization was granted upon. This will be the schema-qualified name for those objects that reside in a schema, and will be properly quoted (if required) for inclusion in generated SQL.

AUTH

The name of the authority granted upon the OBJECT_ID. For example, if OBJECT_TYPE is 'DATABASE' this might be 'BINDADD' or 'IMPLICIT_SCHEMA'. Alternatively, if OBJECT_TYPE is 'TABLE' this could be 'SELECT' or 'ALTER'. As the function’s purpose is to aid in generating GRANT and REVOKE statements, the name of the authority is always modelled after what would be used in the syntax of these statements.

SUFFIX

Several authorizations can be granted with additional permissions. For example in the case of tables, SELECT authority can be granted with or without the GRANT OPTION (the ability for the grantee to pass on the authority to others), while roles can be granted with or without the ADMIN OPTION (the ability for the grantee to grant the role to others). If such a suffix is associated with the authority, this column will contain the syntax required to grant that option.

LEVEL

This is a numeric indicator of the “level” of a grant. As discussed in the description of the SUFFIX column above, authorities can sometimes be granted with additional permissions. In such cases this column is a numeric indication of the presence of additional permissions (for example, a simple SELECT grant would be represented by 0, with SELECT WITH GRANT OPTION would be 1). This is used by COPY_AUTH procedure when comparing two sets of authorities to determine whether a grant needs “upgrading” (say from SELECT to SELECT WITH GRANT OPTION).

Examples¶

Show the authorizations held by the PUBLIC group, limiting the results to 10 authorizations per object type (otherwise the results are huge!).

WITH T AS (
  SELECT
    ROW_NUMBER() OVER (
      PARTITION BY OBJECT_TYPE
      ORDER BY OBJECT_ID
    ) AS ROWNUM,
    T.*
  FROM
    TABLE (AUTHS_HELD('PUBLIC', 'N')) AS T
)
SELECT
  T.OBJECT_TYPE,
  T.OBJECT_ID,
  T.AUTH,
  T.SUFFIX,
  T.LEVEL
FROM
  T
WHERE
  T.ROWNUM <= 10

OBJECT_TYPE        OBJECT_ID                                  AUTH                 SUFFIX               LEVEL
------------------ ------------------------------------------ -------------------- -------------------- ------
DATABASE                                                      BINDADD                                        0
DATABASE                                                      CONNECT                                        0
DATABASE                                                      CREATETAB                                      0
DATABASE                                                      IMPLICIT_SCHEMA                                0
PACKAGE            NULLID.AOTMH00                             BIND                                           0
PACKAGE            NULLID.AOTMH00                             EXECUTE                                        0
PACKAGE            NULLID.ATSH04                              BIND                                           0
PACKAGE            NULLID.ATSH04                              EXECUTE                                        0
PACKAGE            NULLID.DB2XDBMI                            BIND                                           0
PACKAGE            NULLID.DB2XDBMI                            EXECUTE                                        0
PACKAGE            NULLID.PRINTSG                             BIND                                           0
PACKAGE            NULLID.PRINTSG                             EXECUTE                                        0
PACKAGE            NULLID.REVALH03                            BIND                                           0
PACKAGE            NULLID.REVALH03                            EXECUTE                                        0
PROCEDURE          SYSIBM.*                                   EXECUTE                                        0
SCHEMA             DAVE                                       CREATEIN                                       0
SCHEMA             NULLID                                     CREATEIN                                       0
SCHEMA             SQLJ                                       CREATEIN                                       0
SCHEMA             SYSPUBLIC                                  CREATEIN                                       0
SCHEMA             SYSPUBLIC                                  DROPIN                                         0
SCHEMA             SYSTOOLS                                   CREATEIN                                       0
SCHEMA             UTILS                                      CREATEIN                                       0
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_CONTACTGROUPS            EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_CONTACTS                 EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_DBP_MEM_USAGE            EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_DBP_MEM_USAGE_AP         EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO      EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_INDEX_INFO               EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_MSGS                     EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO        EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97    EXECUTE              WITH GRANT OPTION         1
SPECIFIC FUNCTION  SYSPROC.ADMIN_GET_TAB_INFO                 EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.DB2_INSTALL_JAR                       EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.DB2_INSTALL_JAR2                      EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.DB2_REPLACE_JAR                       EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.DB2_UPDATEJARINFO                     EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.RECOVERJAR                            EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.REFRESH_CLASSES                       EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.REMOVE_JAR                            EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SQLJ.REMOVE_JAR2                           EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SYSFUN.GET_SAR                             EXECUTE              WITH GRANT OPTION         1
SPECIFIC PROCEDURE SYSFUN.GET_SAR4PARM                        EXECUTE              WITH GRANT OPTION         1
TABLE              SYSCAT.ATTRIBUTES                          SELECT                                         0
TABLE              SYSCAT.AUDITPOLICIES                       SELECT                                         0
TABLE              SYSCAT.AUDITUSE                            SELECT                                         0
TABLE              SYSCAT.BUFFERPOOLDBPARTITIONS              SELECT                                         0
TABLE              SYSCAT.BUFFERPOOLNODES                     SELECT                                         0
TABLE              SYSCAT.BUFFERPOOLS                         SELECT                                         0
TABLE              SYSCAT.CASTFUNCTIONS                       SELECT                                         0
TABLE              SYSCAT.CHECKS                              SELECT                                         0
TABLE              SYSCAT.COLAUTH                             SELECT                                         0
TABLE              SYSCAT.COLCHECKS                           SELECT                                         0
TABLESPACE         SYSTOOLSTMPSPACE                           USE                                            0
TABLESPACE         USERSPACE1                                 USE                                            0
WORKLOAD           SYSDEFAULTUSERWORKLOAD                     USAGE                                          0

See Also¶

• Source code
• AUTH_TYPE scalar function
• AUTH_DIFF table function
• COPY_AUTH procedure
• MOVE_AUTH procedure
• REMOVE_AUTH procedure

Prototypes¶

DATE(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER)
DATE(AYEAR INTEGER, ADOY INTEGER)

RETURNS DATE

Description¶

Returns the DATE value with the components specified by AYEAR, AMONTH and ADAY, or alternatively AYEAR and ADOY the latter of which is the day of year to construct a DATE for.

Parameters¶

AYEAR

Specifies the year component of the resulting date.

AMONTH

If provided, specifies the month component of the resulting date.

ADAY

If provided, specifies the day (of month) component of the resulting date.

ADOY

If provided, specifies the day of year from which the month and day components of the resulting date will be calculated (the first day of a year is numbered 1).

Examples¶

Construct a date for first day in February, 2010:

VALUES DATE(2010, 2, 1);

1
----------
2010-02-01

Construct a date for the 180th day of 2009:

VALUES DATE(2009, 180);

1
----------
2009-06-29

See Also¶

• Source code
• TIME scalar function
• DATE (built-in function)

Prototypes¶

DATE_RANGE(START DATE, FINISH DATE, STEP DECIMAL(8, 0))
DATE_RANGE(START DATE, FINISH TIMESTAMP, STEP DECIMAL(8, 0))
DATE_RANGE(START TIMESTAMP, FINISH DATE, STEP DECIMAL(8, 0))
DATE_RANGE(START TIMESTAMP, FINISH TIMESTAMP, STEP DECIMAL(8, 0))
DATE_RANGE(START DATE, FINISH VARCHAR(26), STEP DECIMAL(8, 0))
DATE_RANGE(START VARCHAR(26), FINISH DATE, STEP DECIMAL(8, 0))
DATE_RANGE(START VARCHAR(26), FINISH VARCHAR(26), STEP DECIMAL(8, 0))
DATE_RANGE(START TIMESTAMP, FINISH VARCHAR(26), STEP DECIMAL(8, 0))
DATE_RANGE(START VARCHAR(26), FINISH TIMESTAMP, STEP DECIMAL(8, 0))
DATE_RANGE(START DATE, FINISH DATE)
DATE_RANGE(START DATE, FINISH TIMESTAMP)
DATE_RANGE(START TIMESTAMP, FINISH DATE)
DATE_RANGE(START TIMESTAMP, FINSIH TIMESTAMP)
DATE_RANGE(START DATE, FINISH VARCHAR(26))
DATE_RANGE(START VARCHAR(26), FINISH DATE)
DATE_RANGE(START VARCHAR(26), FINISH VARCHAR(26))
DATE_RANGE(START TIMESTAMP, FINISH VARCHAR(26))
DATE_RANGE(START VARCHAR(26), FINISH TIMESTAMP)

RETURNS TABLE(
  D DATE
)

Description¶

DATE_RANGE generates a range of dates from START to FINISH inclusive, advancing in increments given by the date duration STEP. Date durations are DECIMAL(8,0) values structured as YYYYMMDD (in DB2 they are typically derived as the result of subtracting two DATE values). Hence, the following call would generate all dates from the 1st of January 2006 to the 31st of January 2006.

DATE_RANGE('2006-01-01', '2006-01-31', 1)

Alternatively, the following call can be used to generate the 1st day of each month in the year 2006:

DATE_RANGE('2006-01-01', '2006-12-01', 100)

Note that 100 does not mean increment by 100 days each time, but by 1 month each time because the digit 1 falls in the MM part of YYYYMMDD. If STEP is omitted it defaults to 1 day.

Parameters¶

START

The date (specified as a DATE, TIMESTAMP, or VARCHAR(26)) from which to start generating dates. START will always be part of the resulting table.

FINISH

The date (specified as a DATE, TIMESTAMP, or VARCHAR(26)) on which to stop generating dates. FINISH may be part of the resulting table if iteration stops on FINISH. However, if the specified STEP causes iteration to overshoot FINISH, it will not be included.

STEP

If provided, the duration by which to increment each row of the output. Specified as a date duration; a DECIMAL(8,0) value formatted as YYYYMMDD (numebr of years, number of months, number of days).

Returns¶

D

The function returns a table with a single column simply named D which contains the dates generated.

Examples¶

Generate all days in the first month of 2010:

SELECT D
FROM TABLE(
  DATE_RANGE(MONTHSTART(2010, 1), MONTHEND(2010, 1))
);

D
----------
2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13
2010-01-14
2010-01-15
2010-01-16
2010-01-17
2010-01-18
2010-01-19
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24
2010-01-25
2010-01-26
2010-01-27
2010-01-28
2010-01-29
2010-01-30
2010-01-31

Generate the first day of each month in 2010:

SELECT D
FROM TABLE(
  DATE_RANGE(YEARSTART(2010), YEAREND(2010), 100)
);

D
----------
2010-01-01
2010-02-01
2010-03-01
2010-04-01
2010-05-01
2010-06-01
2010-07-01
2010-08-01
2010-09-01
2010-10-01
2010-11-01
2010-12-01

Generate the last day of each month in 2010:

SELECT MONTHEND(D) AS D
FROM TABLE(
  DATE_RANGE(YEARSTART(2010), YEAREND(2010), 100)
);

D
----------
2010-01-31
2010-02-28
2010-03-31
2010-04-30
2010-05-31
2010-06-30
2010-07-31
2010-08-31
2010-09-30
2010-10-31
2010-11-30
2010-12-31

Calculate the number of days in each quarter of 2010 (this is a crude and inefficient method, but it serves to demonstrate the ability to aggregate result sets over date ranges):

SELECT QUARTER(D) AS Q, COUNT(*) AS DAYS
FROM TABLE(
  DATE_RANGE(YEARSTART(2010), YEAREND(2010))
)
GROUP BY QUARTER(D);

Q           DAYS
----------- -----------
          1          90
          2          91
          3          92
          4          92

See Also¶

• Source code
• DATE scalar function
• DATE (built-in function)
• DAYS (built-in function)

Prototypes¶

EXPORT_SCHEMA(ASCHEMA VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_SCHEMA(INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_SCHEMA()

RETURNS TABLE(
    TABSCHEMA VARCHAR(128),
    TABNAME VARCHAR(128),
    SQL VARCHAR(8000)
)

Description¶

This table function can be used to generate a script containing EXPORT commands for all tables (not views) in the specified schema or the current schema if the ASCHEMA parameter is omitted. This is intended to be used in scripts for migrating databases or generating ETL scripts.

The generated EXPORT commands will target an IXF file named after the table, e.g. if ASCHEMA is DATAMART, and the table is COUNTRIES the file would be named "DATAMART.COUNTRIES.IXF". The export command will explicitly name all columns in the table. Likewise, LOAD_SCHEMA table function generates LOAD commands with explicitly named columns. This is to ensure that if the target database’s tables are not declared in exactly the same order as the source database, the transfer will still work if, for example, columns have been added to tables in the source but in the table declaration, they were not placed at the end of the table.

If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns will be included, otherwise they are excluded. GENERATED BY DEFAULT columns are always included. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns will be included, otherwise they are excluded.

Parameters¶

ASCHEMA

If provided, the schema containing the tables to generate EXPORT commands for. If omitted, defaults to the value of the CURRENT SCHEMA special register.

INCLUDE_GENERATED

If this parameter is 'Y' then any columns defined as GENERATED in the source tables will be included in the result. Contrariwise, if 'N', generated columns will be excluded. Defaults to 'Y' if omitted.

INCLUDE_IDENTITY

If this parameter is 'Y' (and INCLUDE_GENERATED is 'Y' given that identity columns are by definition generated) then any columns defined as IDENTITY in the source tables will be included in the result. Contrariwise, if 'N', identity columns will be excluded (regardless of the value of INCLUDE_GENERATED). Defaults to 'Y' if omitted.

Returns¶

The function returns one row per table present in the source schema. Note that the function does not filter out invalidated or inoperative tables. The result table contains three columns:

TABSCHEMA

Contains the name of the schema containing the table named in TABNAME.

TABNAME

Contains the name of the table that will be exported by the command in the SQL column.

SQL

Contains the text of the generated EXPORT command.

The purpose of including the (otherwise redundant) TABSCHEMA and TABNAME columns is to permit the result to be filtered further without having to dissect the SQL column.

Examples¶

Generated EXPORT commands for all tables in the current schema, excluding all generated columns:

SELECT SQL FROM TABLE(EXPORT_SCHEMA('N', 'N'))

SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPORT TO "DB2INST1.CL_SCHED.IXF" OF IXF SELECT CLASS_CODE,DAY,STARTING,ENDING FROM DB2INST1.CL_SCHED
EXPORT TO "DB2INST1.DEPARTMENT.IXF" OF IXF SELECT DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION FROM DB2INST1.DEPARTMENT
EXPORT TO "DB2INST1.ACT.IXF" OF IXF SELECT ACTNO,ACTKWD,ACTDESC FROM DB2INST1.ACT
EXPORT TO "DB2INST1.EMPLOYEE.IXF" OF IXF SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM FROM DB2INST1.EMPLOYEE
EXPORT TO "DB2INST1.EMP_PHOTO.IXF" OF IXF SELECT EMPNO,PHOTO_FORMAT,PICTURE FROM DB2INST1.EMP_PHOTO
EXPORT TO "DB2INST1.EMP_RESUME.IXF" OF IXF SELECT EMPNO,RESUME_FORMAT,RESUME FROM DB2INST1.EMP_RESUME
EXPORT TO "DB2INST1.PROJECT.IXF" OF IXF SELECT PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF,PRSTDATE,PRENDATE,MAJPROJ FROM DB2INST1.PROJECT
EXPORT TO "DB2INST1.PROJACT.IXF" OF IXF SELECT PROJNO,ACTNO,ACSTAFF,ACSTDATE,ACENDATE FROM DB2INST1.PROJACT
EXPORT TO "DB2INST1.EMPPROJACT.IXF" OF IXF SELECT EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE FROM DB2INST1.EMPPROJACT
EXPORT TO "DB2INST1.IN_TRAY.IXF" OF IXF SELECT RECEIVED,SOURCE,SUBJECT,NOTE_TEXT FROM DB2INST1.IN_TRAY
EXPORT TO "DB2INST1.ORG.IXF" OF IXF SELECT DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION FROM DB2INST1.ORG
EXPORT TO "DB2INST1.STAFF.IXF" OF IXF SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM FROM DB2INST1.STAFF
EXPORT TO "DB2INST1.SALES.IXF" OF IXF SELECT SALES_DATE,SALES_PERSON,REGION,SALES FROM DB2INST1.SALES
EXPORT TO "DB2INST1.STAFFG.IXF" OF IXF SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM FROM DB2INST1.STAFFG
EXPORT TO "DB2INST1.EMPMDC.IXF" OF IXF SELECT EMPNO,DEPT,DIV FROM DB2INST1.EMPMDC
EXPORT TO "DB2INST1.PRODUCT.IXF" OF IXF SELECT PID,NAME,PRICE,PROMOPRICE,PROMOSTART,PROMOEND,DESCRIPTION FROM DB2INST1.PRODUCT
EXPORT TO "DB2INST1.INVENTORY.IXF" OF IXF SELECT PID,QUANTITY,LOCATION FROM DB2INST1.INVENTORY
EXPORT TO "DB2INST1.CUSTOMER.IXF" OF IXF SELECT CID,INFO,HISTORY FROM DB2INST1.CUSTOMER
EXPORT TO "DB2INST1.PURCHASEORDER.IXF" OF IXF SELECT POID,STATUS,CUSTID,ORDERDATE,PORDER,COMMENTS FROM DB2INST1.PURCHASEORDER
EXPORT TO "DB2INST1.CATALOG.IXF" OF IXF SELECT NAME,CATLOG FROM DB2INST1.CATALOG
EXPORT TO "DB2INST1.SUPPLIERS.IXF" OF IXF SELECT SID,ADDR FROM DB2INST1.SUPPLIERS
EXPORT TO "DB2INST1.PRODUCTSUPPLIER.IXF" OF IXF SELECT PID,SID FROM DB2INST1.PRODUCTSUPPLIER

Generate EXPORT commands for all tables in the DB2INST1 schema whose names begin with 'EMP', including generated columns which aren’t also identity columns:

SELECT SQL
FROM TABLE(EXPORT_SCHEMA('DB2INST1', 'Y', 'N'))
WHERE TABNAME LIKE 'EMP%'

SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPORT TO "DB2INST1.EMPLOYEE.IXF" OF IXF SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM FROM DB2INST1.EMPLOYEE
EXPORT TO "DB2INST1.EMPMDC.IXF" OF IXF SELECT EMPNO,DEPT,DIV FROM DB2INST1.EMPMDC
EXPORT TO "DB2INST1.EMPPROJACT.IXF" OF IXF SELECT EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE FROM DB2INST1.EMPPROJACT
EXPORT TO "DB2INST1.EMP_PHOTO.IXF" OF IXF SELECT EMPNO,PHOTO_FORMAT,PICTURE FROM DB2INST1.EMP_PHOTO
EXPORT TO "DB2INST1.EMP_RESUME.IXF" OF IXF SELECT EMPNO,RESUME_FORMAT,RESUME FROM DB2INST1.EMP_RESUME

See Also¶

• Source code
• EXPORT_TABLE scalar function
• LOAD_TABLE scalar function
• LOAD_SCHEMA table function
• LOAD (built-in command)
• EXPORT (build-in command)

Prototypes¶

EXPORT_TABLE(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_TABLE(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_TABLE(ATABLE VARCHAR(128))

RETURNS VARCHAR(8000)

Description¶

This function generates an EXPORT command for the specified table in the specified schema or the current schema if ASCHEMA is omitted. If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns will be included, otherwise they are excluded. GENERATED BY DEFAULT columns are always included. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns will be included, otherwise they are excluded.

See EXPORT_SCHEMA table function for more information on the generated command.

Parameters¶

ASCHEMA

If provided, the schema containing the table to generate an EXPORT command for. If omitted, defaults to the value of the CURRENT SCHEMA special register.

ATABLE

The name of the table to generate an EXPORT command for.

INCLUDE_GENERATED

If this parameter is 'Y' then any columns defined as GENERATED in the source table will be included in the export. Contrariwise, if 'N', generated columns will be excluded from the command. Defaults to 'Y' if omitted.

INCLUDE_IDENTITY

If this parameter is 'Y' (and INCLUDE_GENERATED is 'Y' given that identity columns are by definition generated) then any columns defined as IDENTITY in the source table will be included in the export. Contrariwise, if 'N', identity columns will be excluded from the command (regardless of the value of INCLUDE_GENERATED). Defaults to 'Y' if omitted.

Examples¶

Generate an EXPORT command for the EMPLOYEE table in the standard SAMPLE database:

VALUES EXPORT_TABLE('EMPLOYEE')

1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------...
EXPORT TO "DB2INST1.EMPLOYEE.IXF" OF IXF SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM FROM DB2INST1.EMPLOYEE

Generate an EXPORT command for the PEOPLE table (DDL included) excluding IDENTITY columns:

CREATE TABLE PEOPLE (
    ID      INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    NAME    VARCHAR(100) DEFAULT '' NOT NULL,
    GENDER  CHAR(1) NOT NULL,
    DOB     DATE NOT NULL,
    TITLE   VARCHAR(10) NOT NULL GENERATED ALWAYS AS (
        CASE GENDER
            WHEN 'M' THEN 'Mr.'
            WHEN 'F' THEN 'Ms.'
        END
    ),
    CONSTRAINT GENDER_CK CHECK (GENDER IN ('M', 'F'))
);

VALUES EXPORT_TABLE('PEOPLE', 'Y', 'N');

1
----------------------------------------------------------------------------------------...
EXPORT TO "DB2INST1.PEOPLE.IXF" OF IXF SELECT NAME,GENDER,DOB,TITLE FROM DB2INST1.PEOPLE

See Also¶

• Source code
• EXPORT_SCHEMA table function
• LOAD_TABLE scalar function
• LOAD_SCHEMA table function
• LOAD (built-in command)
• EXPORT (build-in command)

Prototypes¶

HOUREND(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER)
HOUREND(ATIMESTAMP TIMESTAMP)
HOUREND(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the last microsecond of AHOUR in the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the end of the hour. Either AYEAR, AMONTH, ADAY, and AHOUR, or ATIMESTAMP must be provided.

Examples¶

Calculate the last microsecond of the specified hour:

VALUES HOUREND('2010-01-23 04:56:00');

1
--------------------------
2010-01-23-04.59.59.999999

Calculate the end of the first working day in 2011:

VALUES HOUREND(2011, 1, DAY(
  CASE WHEN DAYOFWEEK(YEARSTART(2011)) IN (1, 7)
    THEN NEXT_DAYOFWEEK(YEARSTART(2011), 2)
    ELSE YEARSTART(2011)
  END), 4);

1
--------------------------
2011-01-03-04.59.59.999999

See Also¶

• Source code
• HOURSTART scalar function
• HOUR (built-in function)

Prototypes¶

HOURSTART(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER)
HOURSTART(ATIMESTAMP TIMESTAMP)
HOURSTART(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the first microsecond of AHOUR in the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the start of the hour. Either AYEAR, AMONTH, ADAY, and AHOUR, or ATIMESTAMP must be provided.

Examples¶

Truncate the specified timestamp to the nearest hour:

VALUES HOURSTART('2010-01-23 04:56:00');

1
--------------------------
2010-01-23-04.00.00.000000

Calculate the start of the first working day in 2011:

VALUES HOURSTART(2011, 1, DAY(
  CASE WHEN DAYOFWEEK(YEARSTART(2011)) IN (1, 7)
    THEN NEXT_DAYOFWEEK(YEARSTART(2011), 2)
    ELSE YEARSTART(2011)
  END), 9);

1
--------------------------
2011-01-03-09.00.00.000000

See Also¶

• Source code
• HOUREND scalar function
• HOUR (built-in function)

Prototypes¶

LOAD_SCHEMA(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
LOAD_SCHEMA(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
LOAD_SCHEMA(ATABLE VARCHAR(128))

RETURNS TABLE(
    TABSCHEMA VARCHAR(128),
    TABNAME VARCHAR(128),
    SQL VARCHAR(8000)
)

Description¶

This table function can be used to generate a script containing LOAD commands for all tables (not views) in the specified schema or the current schema if the ASCHEMA parameter is omitted. This is intended to be used in scripts for migrating the database.

This function is the counterpart of EXPORT_SCHEMA table function. See EXPORT_SCHEMA table function and LOAD_TABLE scalar function function for more information on the commands generated.

Parameters¶

ASCHEMA

If provided, the schema containing the tables to generate LOAD commands for. If omitted, defaults to the value of the CURRENT SCHEMA special register.

INCLUDE_GENERATED

If this parameter is 'Y' then the routine assumes generated columns are included in the source files, and the LOAD commands will include the GENERATEDOVERRIDE modifier. Otherwise, if 'N', the GENERATEDMISSING modifier will be used instead. Defaults to 'Y' if omitted.

INCLUDE_IDENTITY

If this parameter is 'Y' then the routine assumes identity columns are included in the source files, and the LOAD commands will include the IDENTITYOVERRIDE modifier. Otherwise, if 'N', the IDENTITYMISSING modifier will be used instead. Defaults to 'Y' if omitted.

Returns¶

The function returns one row per table present in the source schema. Note that the function does not filter out invalidated or inoperative tables. The result table contains three columns:

TABSCHEMA

Contains the name of the schema containing the table named in TABNAME.

TABNAME

Contains the name of the table that will be loaded by the command in the SQL column.

SQL

Contains the text of the generated LOAD command.

The purpose of including the (otherwise redundant) TABSCHEMA and TABNAME columns is to permit the result to be filtered further without having to dissect the SQL column.

Examples¶

Generated LOAD commands for all tables in the current schema, excluding all generated columns:

SELECT SQL FROM TABLE(LOAD_SCHEMA('N', 'N'))

SQL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOAD FROM "DB2INST1.CL_SCHED.IXF" OF IXF METHOD N (CLASS_CODE,DAY,STARTING,ENDING) REPLACE INTO DB2INST1.CL_SCHED (CLASS_CODE,DAY,STARTING,ENDING)
LOAD FROM "DB2INST1.DEPARTMENT.IXF" OF IXF METHOD N (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION) REPLACE INTO DB2INST1.DEPARTMENT (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION)
LOAD FROM "DB2INST1.ACT.IXF" OF IXF METHOD N (ACTNO,ACTKWD,ACTDESC) REPLACE INTO DB2INST1.ACT (ACTNO,ACTKWD,ACTDESC)
LOAD FROM "DB2INST1.EMPLOYEE.IXF" OF IXF METHOD N (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) REPLACE INTO DB2INST1.EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)
LOAD FROM "DB2INST1.EMP_PHOTO.IXF" OF IXF METHOD N (EMPNO,PHOTO_FORMAT,PICTURE) REPLACE INTO DB2INST1.EMP_PHOTO (EMPNO,PHOTO_FORMAT,PICTURE)
LOAD FROM "DB2INST1.EMP_RESUME.IXF" OF IXF METHOD N (EMPNO,RESUME_FORMAT,RESUME) REPLACE INTO DB2INST1.EMP_RESUME (EMPNO,RESUME_FORMAT,RESUME)
LOAD FROM "DB2INST1.PROJECT.IXF" OF IXF METHOD N (PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF,PRSTDATE,PRENDATE,MAJPROJ) REPLACE INTO DB2INST1.PROJECT (PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF,PRSTDATE,PRENDATE,MAJPROJ)
LOAD FROM "DB2INST1.PROJACT.IXF" OF IXF METHOD N (PROJNO,ACTNO,ACSTAFF,ACSTDATE,ACENDATE) REPLACE INTO DB2INST1.PROJACT (PROJNO,ACTNO,ACSTAFF,ACSTDATE,ACENDATE)
LOAD FROM "DB2INST1.EMPPROJACT.IXF" OF IXF METHOD N (EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE) REPLACE INTO DB2INST1.EMPPROJACT (EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE)
LOAD FROM "DB2INST1.IN_TRAY.IXF" OF IXF METHOD N (RECEIVED,SOURCE,SUBJECT,NOTE_TEXT) REPLACE INTO DB2INST1.IN_TRAY (RECEIVED,SOURCE,SUBJECT,NOTE_TEXT)
LOAD FROM "DB2INST1.ORG.IXF" OF IXF METHOD N (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) REPLACE INTO DB2INST1.ORG (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION)
LOAD FROM "DB2INST1.STAFF.IXF" OF IXF METHOD N (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM) REPLACE INTO DB2INST1.STAFF (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM)
LOAD FROM "DB2INST1.SALES.IXF" OF IXF METHOD N (SALES_DATE,SALES_PERSON,REGION,SALES) REPLACE INTO DB2INST1.SALES (SALES_DATE,SALES_PERSON,REGION,SALES)
LOAD FROM "DB2INST1.STAFFG.IXF" OF IXF METHOD N (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM) REPLACE INTO DB2INST1.STAFFG (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM)
LOAD FROM "DB2INST1.EMPMDC.IXF" OF IXF METHOD N (EMPNO,DEPT,DIV) REPLACE INTO DB2INST1.EMPMDC (EMPNO,DEPT,DIV)
LOAD FROM "DB2INST1.PRODUCT.IXF" OF IXF METHOD N (PID,NAME,PRICE,PROMOPRICE,PROMOSTART,PROMOEND,DESCRIPTION) REPLACE INTO DB2INST1.PRODUCT (PID,NAME,PRICE,PROMOPRICE,PROMOSTART,PROMOEND,DESCRIPTION)
LOAD FROM "DB2INST1.INVENTORY.IXF" OF IXF METHOD N (PID,QUANTITY,LOCATION) REPLACE INTO DB2INST1.INVENTORY (PID,QUANTITY,LOCATION)
LOAD FROM "DB2INST1.CUSTOMER.IXF" OF IXF METHOD N (CID,INFO,HISTORY) REPLACE INTO DB2INST1.CUSTOMER (CID,INFO,HISTORY)
LOAD FROM "DB2INST1.PURCHASEORDER.IXF" OF IXF METHOD N (POID,STATUS,CUSTID,ORDERDATE,PORDER,COMMENTS) REPLACE INTO DB2INST1.PURCHASEORDER (POID,STATUS,CUSTID,ORDERDATE,PORDER,COMMENTS)
LOAD FROM "DB2INST1.CATALOG.IXF" OF IXF METHOD N (NAME,CATLOG) REPLACE INTO DB2INST1.CATALOG (NAME,CATLOG)
LOAD FROM "DB2INST1.SUPPLIERS.IXF" OF IXF METHOD N (SID,ADDR) REPLACE INTO DB2INST1.SUPPLIERS (SID,ADDR)
LOAD FROM "DB2INST1.PRODUCTSUPPLIER.IXF" OF IXF METHOD N (PID,SID) REPLACE INTO DB2INST1.PRODUCTSUPPLIER (PID,SID)

Generate LOAD commands for all tables in the DB2INST1 schema whose names begin with 'EMP', including generated columns which aren’t also identity columns:

SELECT SQL
FROM TABLE(LOAD_SCHEMA('DB2INST1', 'Y', 'N'))
WHERE TABNAME LIKE 'EMP%'

SQL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOAD FROM "DB2INST1.EMPLOYEE.IXF" OF IXF METHOD N (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) REPLACE INTO DB2INST1.EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)
LOAD FROM "DB2INST1.EMPMDC.IXF" OF IXF METHOD N (EMPNO,DEPT,DIV) REPLACE INTO DB2INST1.EMPMDC (EMPNO,DEPT,DIV)
LOAD FROM "DB2INST1.EMPPROJACT.IXF" OF IXF METHOD N (EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE) REPLACE INTO DB2INST1.EMPPROJACT (EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE)
LOAD FROM "DB2INST1.EMP_PHOTO.IXF" OF IXF METHOD N (EMPNO,PHOTO_FORMAT,PICTURE) REPLACE INTO DB2INST1.EMP_PHOTO (EMPNO,PHOTO_FORMAT,PICTURE)
LOAD FROM "DB2INST1.EMP_RESUME.IXF" OF IXF METHOD N (EMPNO,RESUME_FORMAT,RESUME) REPLACE INTO DB2INST1.EMP_RESUME (EMPNO,RESUME_FORMAT,RESUME)

See Also¶

• Source code
• EXPORT_TABLE scalar function
• EXPORT_SCHEMA table function
• LOAD_TABLE scalar function
• LOAD (built-in command)
• EXPORT (built-in command)

Prototypes¶

LOAD_TABLE(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
LOAD_TABLE(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
LOAD_TABLE(ATABLE VARCHAR(128))

RETURNS VARCHAR(8000)

Description¶

This function generates a LOAD command for the specified table in the specified schema or the current schema if ASCHEMA is omitted. If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns are assumed to be included in the source file, and the LOAD command will utilize GENERATEDOVERRIDE, otherwise the LOAD command will utilize GENERATEDMISSING. GENERATED BY DEFAULT columns are treated as ordinary columns. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns are assumed to be included in the source file, and the LOAD command will utilize IDENTITYOVERRIDE, otherwise the LOAD command will utilize IDENTITYMISSING.

See EXPORT_SCHEMA table function for more information on the generated command.

Parameters¶

ASCHEMA

If provided, the schema containing the table to generate a LOAD command for. If omitted, defaults to the value of the CURRENT SCHEMA special register.

ATABLE

The name of the table to generate a LOAD command for.

INCLUDE_GENERATED

If this parameter is 'Y' then the routine assumes generated columns are included in the source file, and the LOAD command will include the GENERATEDOVERRIDE modifier. Otherwise, if 'N', the GENERATEDMISSING modifier will be used instead. Defaults to 'Y' if omitted.

INCLUDE_IDENTITY

If this parameter is 'Y' then the routine assumes identity columns are included in the source file, and the LOAD command will include the IDENTITYOVERRIDE modifier. Otherwise, if 'N', the IDENTITYMISSING modifier will be used instead. Defaults to 'Y' if omitted.

Examples¶

Generate a LOAD command for the EMPLOYEE table in the standard SAMPLE database:

VALUES LOAD_TABLE('EMPLOYEE')

1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------...
LOAD FROM "DB2INST1.EMPLOYEE.IXF" OF IXF METHOD N (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) REPLACE INTO DB2INST1.EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)

Generate a LOAD command for the PEOPLE table (DDL included) excluding IDENTITY columns:

CREATE TABLE PEOPLE (
    ID      INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    NAME    VARCHAR(100) DEFAULT '' NOT NULL,
    GENDER  CHAR(1) NOT NULL,
    DOB     DATE NOT NULL,
    TITLE   VARCHAR(10) NOT NULL GENERATED ALWAYS AS (
        CASE GENDER
            WHEN 'M' THEN 'Mr.'
            WHEN 'F' THEN 'Ms.'
        END
    ),
    CONSTRAINT GENDER_CK CHECK (GENDER IN ('M', 'F'))
);

VALUES LOAD_TABLE('PEOPLE', 'Y', 'N');

1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOAD FROM "DB2INST1.PEOPLE.IXF" OF IXF MODIFIED BY GENERATEDOVERRIDE,IDENTITYMISSING METHOD N (NAME,GENDER,DOB,TITLE) REPLACE INTO DB2INST1.PEOPLE (NAME,GENDER,DOB,TITLE)

See Also¶

• Source code
• EXPORT_TABLE scalar function
• EXPORT_SCHEMA table function
• LOAD_SCHEMA table function
• LOAD (built-in command)
• EXPORT (build-in command)

Prototypes¶

MINUTEEND(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER, AMINUTE INTEGER)
MINUTEEND(ATIMESTAMP TIMESTAMP)
MINUTEEND(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the last microsecond of AMINUTE in AHOUR on the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

AMINUTE

If provided, the minute component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the end of the minute. Either AYEAR, AMONTH, ADAY, AHOUR, and AMINUTE, or ATIMESTAMP must be provided.

Examples¶

Round the specified timestamp up to one microsecond before the next minute:

VALUES MINUTEEND('2010-01-23 04:56:12');

1
--------------------------
2010-01-23-04.56.59.999999

Generate a timestamp at the end of a minute with the specified fields:

VALUES MINUTEEND(2010, 2, 14, 9, 30);

1
--------------------------
2010-02-14-09.30.59.999999

See Also¶

• Source code
• MINUTESTART scalar function
• MINUTE (built-in function)

Prototypes¶

MINUTESTART(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER, AMINUTE INTEGER)
MINUTESTART(ATIMESTAMP TIMESTAMP)
MINUTESTART(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the first microsecond of AMINUTE in AHOUR on the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

AMINUTE

If provided, the minute component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the start of the minute. Either AYEAR, AMONTH, ADAY, AHOUR, and AMINUTE, or ATIMESTAMP must be provided.

Examples¶

Truncate the specified timestamp to the nearest minute:

VALUES MINUTESTART('2010-01-23 04:56:12');

1
--------------------------
2010-01-23-04.56.00.000000

Generate a timestamp at the start of a minute with the specified fields:

VALUES MINUTESTART(2010, 2, 14, 9, 30);

1
--------------------------
2010-02-14-09.30.00.000000

See Also¶

• Source code
• MINUTEEND scalar function
• MINUTE (built-in function)

Prototypes¶

MONTH_CLAIM(ADATE DATE)
MONTH_CLAIM(ADATE TIMESTAMP)
MONTH_CLAIM(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the month of ADATE, according to the CLAIM calendar. ADATE can be expressed as a DATE value, a TIMESTAMP, or a VARCHAR containing a valid string representation of a date or timestamp. If ADATE is NULL, the result is NULL. Otherwise, the result will be in the range 1-12.

Parameters¶

ADATE

The date to calculate the month of, according to the CLAIM calendar.

Examples¶

Calculate the CLAIM month for the 31st of January, 2010:

VALUES MONTH_CLAIM(DATE(2010, 1, 31));

1
------
     2

Calculate the length of all CLAIM months in 2010:

SELECT MONTH_CLAIM(D) AS MONTH, COUNT(*) AS DAYS
FROM TABLE(DATE_RANGE(YEARSTART_CLAIM(2010), YEAREND_CLAIM(2010)))
GROUP BY MONTH_CLAIM(D);

MONTH  DAYS
------ -----------
     1          28
     2          28
     3          35
     4          28
     5          35
     6          28
     7          28
     8          35
     9          28
    10          28
    11          35
    12          28

See Also¶

• Source code
• MONTH (built-in function)
• DAY_CLAIM
• WEEK_CLAIM scalar function
• QUARTER_CLAIM scalar function
• YEAR_CLAIM scalar function

Prototypes¶

MONTHEND(AYEAR INTEGER, AMONTH INTEGER)
MONTHEND(ADATE DATE)
MONTHEND(ADATE TIMESTAMP)
MONTHEND(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the last day of AMONTH in AYEAR, or the last day of the month of ADATE depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AMONTH for which to return the ending date.

AMONTH

If provided, the month for which to return to the ending date.

ADATE

If provided the date in the month for which to return the ending date. Either AYEAR and AMONTH, or ADATE must be specified.

Examples¶

Calculate the ending date of the second month of 2010:

VALUES MONTHEND(2010, 2);

1
----------
2010-02-28

Calculate the ending date for the 28th of January, 2009:

VALUES MONTHEND('2009-01-28');

1
----------
2009-01-31

See Also¶

• Source code
• MONTHSTART scalar function
• MONTH (built-in function)

Prototypes¶

MONTHEND_CLAIM(AYEAR INTEGER, AMONTH INTEGER)
MONTHEND_CLAIM(ADATE DATE)
MONTHEND_CLAIM(ADATE TIMESTAMP)
MONTHEND_CLAIM(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the last day of AMONTH in AYEAR, or the last day of the month of ADATE depending on the variant of the function that is called, according to the CLAIM calendar.

Parameters¶

AYEAR

If provided, the year of AMONTH for which to return the ending date.

AMONTH

If provided, the month for which to return to the ending date.

ADATE

If provided the date in the month for which to return the ending date. Either AYEAR and AMONTH, or ADATE must be specified.

Examples¶

Calculate the end of the second month of 2010, according to the CLAIM calendar:

VALUES MONTHEND_CLAIM(2010, 2);

1
----------
2010-02-19

Calculate the CLAIM month ending date for the 28th of January, 2009 (which is actually in February according to the CLAIM calendar):

VALUES MONTHEND_CLAIM('2009-01-28');

1
----------
2009-02-20

See Also¶

• Source code
• MONTHSTART_CLAIM scalar function

Prototypes¶

MONTHSTART(AYEAR INTEGER, AMONTH INTEGER)
MONTHSTART(ADATE DATE)
MONTHSTART(ADATE TIMESTAMP)
MONTHSTART(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the first day of AMONTH in AYEAR, or the first day of the month of ADATE depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AMONTH for which to return the starting date.

AMONTH

If provided, the month for which to return to the starting date.

ADATE

If provided the date in the month for which to return the starting date. Either AYEAR and AMONTH, or ADATE must be specified.

Examples¶

Calculate the starting date of the second month in 2010:

VALUES MONTHSTART(2010, 2);

1
----------
2010-02-01

Calculate the start of the month for the 28th of January, 2009:

VALUES MONTHSTART('2009-01-28');

1
----------
2009-01-01

See Also¶

• Source code
• MONTHEND scalar function
• MONTH (built-in function)

Prototypes¶

MONTHSTART_CLAIM(AYEAR INTEGER, AMONTH INTEGER)
MONTHSTART_CLAIM(ADATE DATE)
MONTHSTART_CLAIM(ADATE TIMESTAMP)
MONTHSTART_CLAIM(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the first day of AMONTH in AYEAR, or the first day of the month of ADATE depending on the variant of the function that is called, according to the CLAIM calendar.

Parameters¶

AYEAR

If provided, the year of AMONTH for which to return the starting date.

AMONTH

If provided, the month for which to return to the starting date.

ADATE

If provided the date in the month for which to return the starting date. Either AYEAR and AMONTH, or ADATE must be specified.

Examples¶

Calculate the starting date of the second CLAIM calendar month in 2010:

VALUES MONTHSTART_CLAIM(2010, 2);

1
----------
2010-01-23

Calculate the start of the CLAIM calendar month for the 28th of January, 2009 (which actually exists in February, according to the CLAIM calendar):

VALUES MONTHSTART_CLAIM('2009-01-28');

1
----------
2009-01-24

See Also¶

• Source code
• MONTHEND_CLAIM scalar function

Prototypes¶

MONTHWEEK(ADATE DATE)
MONTHWEEK(ADATE TIMESTAMP)
MONTHWEEK(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the month of ADATE, where weeks start on a Sunday. The result will be in the range 1-6 as partial weeks are permitted. For example, if the first day of a month is a Saturday, it will be counted as week 1, which lasts one day. The next day, Sunday, will start week 2.

Parameters¶

ADATE

The date to calculate the week of the month for.

Examples¶

Calculate the week of the month for the 31st of January, 2010:

VALUES MONTHWEEK(DATE(2010, 1, 31));

1
------
     6

Calculate the length of all weeks in January 2010:

SELECT MONTHWEEK(D) AS WEEK_NUM, COUNT(*) AS WEEK_LENGTH
FROM TABLE(DATE_RANGE(MONTHSTART(2010, 1), MONTHEND(2010, 1)))
GROUP BY MONTHWEEK(D);

WEEK_NUM WEEK_LENGTH
-------- -----------
       1           2
       2           7
       3           7
       4           7
       5           7
       6           1

See Also¶

• Source code
• MONTHWEEK_ISO scalar function

Prototypes¶

MONTHWEEK_CLAIM(ADATE DATE)
MONTHWEEK_CLAIM(ADATE TIMESTAMP)
MONTHWEEK_CLAIM(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the month of ADATE, according to the CLAIM calendar. ADATE can be expressed as a DATE value, a TIMESTAMP, or a VARCHAR containing a valid string representation of a date or timestamp. If ADATE is NULL, the result is NULL. Otherwise, the result is a SMALLINT between 1 and 5.

Parameters¶

ADATE

The date to calculate the week of the month for, according to the CLAIM calendar.

Examples¶

Calculate the week of CLAIM month for the 1st of June, 2010:

VALUES MONTHWEEK_CLAIM(DATE(2010, 6, 1));

1
------
     1

Calculate the week of CLAIM month for the 31st of January, 2010:

VALUES MONTHWEEK_CLAIM('2010-01-31');

1
------
     2

See Also¶

• Source code
• DAY_CLAIM
• WEEK_CLAIM scalar function
• MONTH_CLAIM scalar function
• YEAR_CLAIM scalar function

Prototypes¶

MONTHWEEK_ISO(ADATE DATE)
MONTHWEEK_ISO(ADATE TIMESTAMP)
MONTHWEEK_ISO(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the month of ADATE, where weeks start on a Monday. The result will be in the range 1-6 as partial weeks are permitted. For example, if the first day of a month is a Sunday, it will be counted as week 1, which lasts one day. The next day, Monday, will start week 2.

Parameters¶

ADATE

The date to calculate the week of the month for.

Examples¶

Calculate the week of the month for the 31st of January, 2010:

VALUES MONTHWEEK(DATE(2010, 1, 31));

1
------
     5

Calculate the length of all weeks in January 2010:

SELECT MONTHWEEK_ISO(D) AS WEEK_NUM, COUNT(*) AS WEEK_LENGTH
FROM TABLE(DATE_RANGE(MONTHSTART(2010, 1), MONTHEND(2010, 1)))
GROUP BY MONTHWEEK_ISO(D);

WEEK_NUM WEEK_LENGTH
-------- -----------
       1           3
       2           7
       3           7
       4           7
       5           7

See Also¶

• Source code
• MONTHWEEK scalar function

Prototypes¶

NEXT_DAYOFWEEK(ADATE DATE, ADOW INTEGER)
NEXT_DAYOFWEEK(ADATE TIMESTAMP, ADOW INTEGER)
NEXY_DAYOFWEEK(ADATE VARCHAR(26), ADOW INTEGER)
NEXT_DAYOFWEEK(ADOW INTEGER)

RETURNS DATE

Description¶

Returns the specified day of the week following the given date. Days of the week are specified in the same fashion as the built-in DAYOFWEEK function (i.e. 1=Sunday, 2=Monday, ... 7=Saturday). If ADATE is omitted the current date is used.

Parameters¶

ADATE

The date after which to return a specific day of the week. If this parameter is omitted the CURRENT DATE special register is used.

ADOW

The day of the week to find specified as an integer where 1 represents Sunday, 2 is Monday, and so on.

Examples¶

Find the next Monday after the start of 2010:

VALUES VARCHAR(NEXT_DAYOFWEEK(YEARSTART(2010), 2), ISO);

1
----------
2010-01-04

Find the third Thursday in February 2010 (note, the CASE expression is necessary in case February starts on a Thursday, in which case NEXT_DAYOFWEEK will be returning the date of the second Thursday in the month, not the first):

VALUES VARCHAR(NEXT_DAYOFWEEK(MONTHSTART(2010, 2), 5) +
  CASE DAYOFWEEK(MONTHSTART(2010, 2))
    WHEN 5 THEN 7
    ELSE 14
  END DAYS, ISO);

1
----------
2010-02-18

See Also¶

• Source code
• PRIOR_DAYOFWEEK scalar function

Prototypes¶

PCRE_GROUPS(PATTERN VARCHAR(1000), TEXT VARCHAR(4000))

RETURNS TABLE(
  GROUP INTEGER,
  POSITION INTEGER,
  CONTENT VARCHAR(4000)
)

Description¶

PCRE groups table function. Given a regular expression in PATTERN, and some text to search in TEXT, the function performs a search for PATTERN in the text and returns the result as a table containing a row for each matching group (including group 0 which implicitly covers the entire search pattern).

Parameters¶

PATTERN

The Perl-compatible Regular Expression (PCRE) to search for.

TEXT

The text to search within.

Returns¶

GROUP

The index of the capturing group; group 0 represents the portion of TEXT which matched the entire PATTERN.

POSITION

The 1-based position of the group within TEXT.

CONTENT

The content of the matched group.

Examples¶

This example demonstrates how multiple groups are matched and returned by the function:

SELECT
    T.GROUP,
    T.POSITION,
    T.CONTENT
FROM
    TABLE(
        PCRE_GROUPS('(<([A-Z][A-Z0-9]*)[^>]*>)(.*?)(</\2>)', '<B>BOLD!</B>')
    ) AS T

GROUP  POSITION  CONTENT
-----  --------  -------------------------
    0         1  <B>BOLD!</B>
    1         1  <B>
    2         2  B
    3         4  BOLD!
    4         9  </B>

Example demonstrating how unmatched groups are not returned, while groups matching the empty string are:

SELECT
    T.GROUP,
    T.POSITION,
    T.CONTENT
FROM
    TABLE(
        PCRE_GROUPS('(FOO)?(\s?)(BAR)?(\s?)(BAZ)?', 'FOOBAR')
    ) AS T

GROUP  POSITION  CONTENT
-----  --------  -------------------------
    0         1  FOOBAR
    1         1  FOO
    2         4
    3         4  BAR
    4         7

See Also¶

• SQL source code
• C source code
• PCRE_SEARCH scalar function
• PCRE_SUB scalar function
• PCRE_SPLIT table function
• PCRE library homepage
• Wikipedia PCRE article

Prototypes¶

PCRE_SEARCH(PATTERN VARCHAR(1000), TEXT VARCHAR(4000), START INTEGER)
PCRE_SEARCH(PATTERN VARCHAR(1000), TEXT VARCHAR(4000))

RETURNS INTEGER

Description¶

PCRE searching function. Given a regular expression in PATTERN, and some text to search in TEXT, returns the 1-based position of the first match. START is an optional 1-based position from which to start the search (defaults to 1 if not specified). If no match is found, the function returns zero. If PATTERN, TEXT, or START is NULL, the result is NULL.

Parameters¶

PATTERN

The Perl-compatible Regular Expression (PCRE) to search for

TEXT

The text to search within

START

The 1-based position from which to start the search. Defaults to 1 if omitted.

Examples¶

Simple searches showing the return value is a 1-based position or 0 in the case of failure:

VALUES
  (PCRE_SEARCH('FOO', 'FOOBAR')),
  (PCRE_SEARCH('BAR', 'FOOBAR')),
  (PCRE_SEARCH('BAZ', 'FOOBAR'))

1
----------
         1
         4
         0

A search to check whether a value looks vaguely like an IP address; note that the octets are not checked for 0-255 range:

VALUES PCRE_SEARCH('^\d{1,3}(\.\d{1,3}){3}$', '192.168.0.1')

1
----------
         1

A search demonstrating use of back-references to check that a closing tag matches the opening tag:

VALUES PCRE_SEARCH('<([A-Z][A-Z0-9]*)[^>]*>.*?</\1>', '<B>BOLD!</B>')

1
----------
         1

Searches demonstrating negative look-aheads:

VALUES
  (PCRE_SEARCH('Q(?!U)', 'QUACK')),
  (PCRE_SEARCH('Q(?!U)', 'QI'))

1
----------
         0
         1

See Also¶

• SQL source code
• C source code
• PCRE_SUB scalar function
• PCRE_SPLIT table function
• PCRE_GROUPS table function
• PCRE library homepage
• Wikipedia PCRE article

Prototypes¶

PCRE_SPLIT(PATTERN VARCHAR(1000), TEXT VARCHAR(4000))

RETURNS TABLE(
  ELEMENT INTEGER,
  SEPARATOR INTEGER,
  POSITION INTEGER,
  CONTENT VARCHAR(4000)
)

Description¶

PCRE string splitting function. Given a regular expression in PATTERN, and some text in TEXT, the function searches for every occurence of PATTERN in TEXT and breaks TEXT into chunks based on those matches. Each chunk is returned as a row in the result table which details whether or not the chunk was a result of a match, or text between the match.

Parameters¶

PATTERN

The Perl-compatible Regular Expression (PCRE) to search for.

TEXT

The text to search within.

Returns¶

ELEMENT

The 1-based index of the chunk. Note that there are usually two rows for each index, one where SEPARATOR is zero and another where SEPARATOR is one. Therefore, one could consider the key of the result table to be (ELEMENT, SEPARATOR)

SEPARATOR

Contains 1 if the row represents a match for PATTERN, and 0 if the row represents text between matches.

POSITION

The 1-based position of CONTENT within the original TEXT parameter.

CONTENT

The extract from TEXT.

Examples¶

An example demonstrating a simple split. Note that a row is still returned for the “missing” value, albeit with an empty CONTENT value:

SELECT
    T.ELEMENT,
    T.SEPARATOR,
    T.POSITION,
    T.CONTENT
FROM
    TABLE(
       PCRE_SPLIT(':', 'A:B:C::E')
    ) AS T

ELEMENT  SEPARATOR  POSITION  CONTENT
-------  ---------  --------  -------------------
      1          0         1  A
      1          1         2  :
      2          0         3  B
      2          1         4  :
      3          0         5  C
      3          1         6  :
      4          0         7
      4          1         7  :
      5          0         8  E

An example demonstrating a very rudimentary CSV parser. Note that to keep things simple, we actually treat the separator pattern as the data here, filter out the interleaved commas and remove the quotes surrounding delimited values:

SELECT
    T.ELEMENT,
    CASE WHEN LEFT(T.CONTENT, 1) = '"'
        THEN SUBSTR(T.CONTENT, 2, LENGTH(T.CONTENT) - 2)
        ELSE T.CONTENT
    END AS CONTENT
FROM
    TABLE(
        PCRE_SPLIT('([^",][^,]*|"[^"]*")', '"Some",CSV,",data"')
    ) AS T
WHERE
    T.SEPARATOR = 1

ELEMENT  CONTENT
-------  -------------------
      1  Some
      2  CSV
      3  ,data

See Also¶

• SQL source code
• C source code
• PCRE_SEARCH scalar function
• PCRE_SUB scalar function
• PCRE_GROUPS table function
• PCRE library homepage
• Wikipedia PCRE article

Prototypes¶

PCRE_SUB(PATTERN VARCHAR(1000), REPL VARCHAR(4000), TEXT VARCHAR(4000), START INTEGER)
PCRE_SUB(PATTERN VARCHAR(1000), REPL VARCHAR(4000), TEXT VARCHAR(4000))

RETURNS VARCHAR(4000)

Description¶

PCRE substitution function. Given a regular expression in PATTERN, a substitution pattern in REPL, some text to match in TEXT, and an optional 1-based START position for the search, returns REPL with backslash prefixed group specifications replaced by the corresponding matched group, e.g. \0 refers to the group that matches the entire PATTERN, \1 refers to the first capturing group in PATTERN. To include a literal backslash in REPL double it, i.e. \\. Returns NULL if the PATTERN does not match TEXT.

Note that ordinary C-style backslash escapes are not interpreted by this function within REPL, i.e. \n will not be replaced by a newline character. Use ordinary SQL hex-strings for this.

Parameters¶

PATTERN

The Perl-Compatible Regular Expression (PCRE) to search for.

REPL

The replacement pattern to return, after substitution of matched groups (indicated by back-slash prefixed numbers within this string).

TEXT

The text to search within.

START

The 1-based position from which to start the search. Defaults to 1 if omitted.

Examples¶

Simple searches demonstrating extraction of the matched portion of TEXT (if any):

VALUES
  (PCRE_SUB('FOO', '\0', 'FOOBAR')),
  (PCRE_SUB('FOO(BAR)?', '\0', 'FOOBAR')),
  (PCRE_SUB('BAZ', '\0', 'FOOBAR'))

1
-------------------...
FOO
FOOBAR
-

A substitution demonstrating the extraction of an IP address from some text:

VALUES PCRE_SUB('\b(\d{1,3}(\.\d{1,3}){3})\b', '\1', 'IP address: 192.168.0.1')

1
-----------------...
192.168.0.1

A substitution demonstrating the replacement of one HTML tag with another:

VALUES PCRE_SUB('<([A-Z][A-Z0-9]*)[^>]*>(.*?)</\1>', '<I>\2</I>', '<B>BOLD!</B>')

1
------------------...
<I>BOLD!</I>

A substitution demonstrating that look-aheads do not form part of the match:

VALUES PCRE_SUB('Q(?!U)', '\0', 'QI')

1
---------------...
Q

See Also¶

• SQL source code
• C source code
• PCRE_SEARCH scalar function
• PCRE_SPLIT table function
• PCRE_GROUPS table function
• PCRE library homepage
• Wikipedia PCRE article

Prototypes¶

PRIOR_DAYOFWEEK(ADATE DATE, ADOW INTEGER)
PRIOR_DAYOFWEEK(ADATE TIMESTAMP, ADOW INTEGER)
PRIOR_DAYOFWEEK(ADATE VARCHAR(26), ADOW INTEGER)
PRIOR_DAYOFWEEK(ADOW INTEGER)

RETURNS DATE

Description¶

Returns the specified day of the week prior to the given date. Days of the week are specified in the same fashion as the built-in DAYOFWEEK function (i.e. 1=Sunday, 2=Monday, ... 7=Saturday). If ADATE is omitted the current date is used.

Parameters¶

ADATE

The date before which to return a specific day of the week. If this parameter is omitted the CURRENT DATE special register is used.

ADOW

The day of the week to find specified as an integer where 1 represents Sunday, 2 is Monday, and so on.

Examples¶

Find the Monday before the start of 2010:

VALUES VARCHAR(PRIOR_DAYOFWEEK('2010-01-01', 2), ISO);

1
----------
2009-12-28

Find the last Friday in January, 2010:

VALUES VARCHAR(PRIOR_DAYOFWEEK(MONTHEND(2010, 1), 6), ISO);

1
----------
2010-01-29

See Also¶

• Source code
• NEXT_DAYOFWEEK scalar function

Prototypes¶

QUARTER_CLAIM(ADATE DATE)
QUARTER_CLAIM(ADATE TIMESTAMP)
QUARTER_CLAIM(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the quarter of the year of ADATE, according to the CLAIM calendar. ADATE can be expressed as a DATE value, a TIMESTAMP, or a VARCHAR containing a valid string representation of a date or timestamp. If ADATE is NULL, the result is NULL. Otherwise, the result is a SMALLINT between 1 and 4.

Parameters¶

ADATE

The date to calculate the quarter of, according to the CLAIM calendar.

Examples¶

Calculate the CLAIM quarter for the 1st of June, 2010:

VALUES QUARTER_CLAIM(DATE(2010, 6, 1));

1
------
     2

Calculate the CLAIM quarter for the 31st of December, 2010:

VALUES QUARTER_CLAIM('2010-12-31');

1
------
     1

See Also¶

• Source code
• QUARTER (built-in function)
• DAY_CLAIM
• WEEK_CLAIM scalar function
• MONTH_CLAIM scalar function
• YEAR_CLAIM scalar function

Prototypes¶

QUARTEREND(AYEAR INTEGER, AQUARTER INTEGER)
QUARTEREND(ADATE DATE)
QUARTEREND(ADATE TIMESTAMP)
QUARTEREND(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the last day of AQUARTER in AYEAR, or the last day of the quarter of ADATE depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AQUARTER for which to return the ending date.

AQUARTER

If provided, the quarter for which to return to the ending date.

ADATE

If provided the date in the quarter for which to return the ending date. Either AYEAR and AQUARTER, or ADATE must be specified.

Examples¶

Calculate the ending date of the second quarter in 2010:

VALUES QUARTEREND(2010, 2);

1
----------
2010-06-30

Calculate the end date of the quarter containing the first of February, 2010:

VALUES QUARTEREND('2010-02-01');

1
----------
2010-03-31

See Also¶

• Source code
• QUARTERSTART scalar function
• QUARTER (built-in function)

Prototypes¶

QUARTERSTART(AYEAR INTEGER, AQUARTER INTEGER)
QUARTERSTART(ADATE DATE)
QUARTERSTART(ADATE TIMESTAMP)
QUARTERSTART(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the first day of AQUARTER in AYEAR, or the first day of the quarter of ADATE depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AQUARTER for which to return the starting date.

AQUARTER

If provided, the quarter for which to return to the starting date.

ADATE

If provided the date in the quarter for which to return the starting date. Either AYEAR and AQUARTER, or ADATE must be specified.

Examples¶

Calculate the starting date of the second quarter in 2010:

VALUES QUARTERSTART(2010, 2);

1
----------
2010-04-01

Calculate the start date of the quarter containing the first of February, 2010:

VALUES QUARTERSTART('2010-02-01');

1
----------
2010-01-01

See Also¶

• Source code
• QUARTEREND scalar function
• QUARTER (built-in function)

Prototypes¶

QUARTERWEEK(ADATE DATE)
QUARTERWEEK(ADATE TIMESTAMP)
QUARTERWEEK(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the quarter of ADATE, where weeks start on a Sunday. The result will be in the range 1-14 as partial weeks are permitted. For example, if the first day of a quarter is a Saturday, it will be counted as week 1, which lasts one day. The next day, Sunday, will start week 2.

Parameters¶

ADATE

The date to calculate the week of the quarter for.

Examples¶

Calculate the week of the quarter for 31st of January, 2010:

VALUES QUARTERWEEK(DATE(2010, 1, 31));

1
------
     6

Show the number of weeks in all quarters in the years 2007-2010:

SELECT YEAR(D) AS YEAR, QUARTER(D) AS QUARTER, QUARTERWEEK(QUARTEREND(D)) AS WEEKS
FROM TABLE(DATE_RANGE('2007-01-01', '2010-12-31', '300'));

YEAR        QUARTER     WEEKS
----------- ----------- ------
       2007           1     13
       2007           2     13
       2007           3     14
       2007           4     14
       2008           1     14
       2008           2     14
       2008           3     14
       2008           4     14
       2009           1     14
       2009           2     14
       2009           3     14
       2009           4     14
       2010           1     14
       2010           2     14
       2010           3     14
       2010           4     14

See Also¶

• Source code
• QUARTERWEEK_ISO scalar function

Prototypes¶

QUARTERWEEK_ISO(ADATE DATE)
QUARTERWEEK_ISO(ADATE TIMESTAMP)
QUARTERWEEK_ISO(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the quarter of ADATE, where weeks start on a Monday. The result will be in the range 1-14 as partial weeks are permitted. For example, if the first day of a month is a Sunday, it will be counted as week 1, which lasts one day. The next day, Monday, will start week 2.

Parameters¶

ADATE

The date to calculate the week of the quarter for.

Examples¶

Calculate the week of the quarter for 31st of January, 2010:

VALUES QUARTERWEEK_ISO(DATE(2010, 1, 31));

1
------
     5

Show the number of weeks in all quarters in the years 2007-2010:

SELECT YEAR(D) AS YEAR, QUARTER(D) AS QUARTER, QUARTERWEEK_ISO(QUARTEREND(D)) AS WEEKS
FROM TABLE(DATE_RANGE('2007-01-01', '2010-12-31', '300'));

YEAR        QUARTER     WEEKS
----------- ----------- ------
       2007           1     13
       2007           2     14
       2007           3     14
       2007           4     14
       2008           1     14
       2008           2     14
       2008           3     14
       2008           4     14
       2009           1     14
       2009           2     14
       2009           3     14
       2009           4     14
       2010           1     14
       2010           2     14
       2010           3     14
       2010           4     14

See Also¶

• Source code
• QUARTERWEEK scalar function

Prototypes¶

QUOTE_IDENTIFIER(AIDENT(VARCHAR(128))

RETURNS VARCHAR(258)

Description¶

Returns AIDENT surrounded by double quotes if AIDENT contains any characters which cannot appear in an identifier, as defined by the DB2 SQL dialect. Specifically this function is intended for correctly quoting SQL identifiers in generated SQL. Hence if AIDENT contains any lower-case, whitespace or symbolic characters, or begins with a numeral or underscore, it is returned quoted. If AIDENT contains no such characters it is returned verbatim.

Parameters¶

AIDENT

The identifier to quote (if necessary).

Examples¶

Quote a simple identifier:

VALUES QUOTE_IDENTIFIER('MY_TABLE')

1
----------...
MY_TABLE

Quote an identifier containing characters that require quoting:

VALUES QUOTE_IDENTIFIER('MyTable')

1
-----------...
"MyTable"

Quote an identifier containing quotation marks:

VALUES QUOTE_IDENTIFIER('My "Table"')

1
-----------------...
"My ""Table"""

See Also¶

• Source code
• QUOTE_STRING scalar function

Prototypes¶

QUOTE_STRING(ASTRING VARCHAR(4000))

RETURNS VARCHAR(4000)

Description¶

Returns ASTRING surrounded by single quotes and performs any necessary escaping within the string to make it valid SQL. For example, single quotes within ASTRING are doubled, and control characters like CR or LF are returned as concatenated hex-strings.

Parameters¶

ASTRING

The string to enclose in single-quotation marks.

Examples¶

Quote a simple string:

VALUES QUOTE_STRING('A string')

1
---------------...
'A string'

Quote a string containing an apostrophe (the delimiter for SQL strings):

VALUES QUOTE_STRING('Frank''s string')

1
--------------------...
'Frank''s string'

Quote a string containing a control character (in this case a line-feed):

VALUES QUOTE_STRING('A multi' || X'0A' || 'line string')

1
------------------------------------...
'A multi' || X'0A' || 'line string'

See Also¶

• Source code
• QUOTE_IDENTIFIER scalar function

Prototypes¶

SECONDEND(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER, AMINUTE INTEGER, ASECOND INTEGER)
SECONDEND(ATIMESTAMP TIMESTAMP)
SECONDEND(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the last microsecond of ASECOND in AMINUTE in AHOUR on the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

AMINUTE

If provided, the minute component of the resulting timestamp.

ASECOND

If provided, the second component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the end of the second. Either AYEAR, AMONTH, ADAY, AHOUR, AMINUTE, and ASECOND, or ATIMESTAMP must be provided.

Examples¶

Round the specified timestamp up to one microsecond before the next second:

VALUES SECONDEND('2010-01-23 04:56:12.123456');

1
--------------------------
2010-01-23-04.56.12.999999

Generate a timestamp at the end of a second with the specified fields:

VALUES SECONDEND(2010, 2, 14, 9, 30, 44);

1
--------------------------
2010-02-14-09.30.44.999999

See Also¶

• Source code
• SECONDSTART scalar function
• SECOND (built-in function)

Prototypes¶

SECONDS(ATIMESTAMP TIMESTAMP)
SECONDS(ATIMESTAMP DATE)
SECONDS(ATIMESTAMP VARCHAR(26))

RETURNS BIGINT

Description¶

Returns an integer representation of a TIMESTAMP. This function is a combination of the built-in DAYS and MIDNIGHT_SECONDS functions. The result is a BIGINT (64-bit integer value) representing the number of seconds since one day before 0001-01-01 at 00:00:00. The one day offset is due to the operation of the DAYS function.

Parameters¶

ATIMESTAMP

The timestamp to convert to an integer representation. If a DATE is provided, then it will be treated as a TIMESTAMP with the equivalent date portion and a time portion of midnight.

Examples¶

Return an integer representation of the first instant of the year 2010:

VALUES SECONDS(YEARSTART(2010));

1
--------------------
         63397987200

Return the number of seconds in the year 2010:

VALUES SECONDS(YEARSTART(2011)) - SECONDS(YEARSTART(2010));

1
--------------------
            31536000

See Also¶

• Source code
• TIMESTAMP scalar function
• DAYS (built-in function)
• MIDNIGHT_SECONDS (built-in function)

Prototypes¶

SECONDSTART(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER, AMINUTE INTEGER, ASECOND INTEGER)
SECONDSTART(ATIMESTAMP TIMESTAMP)
SECONDSTART(ATIMESTAMP VARCHAR(26))

RETURNS TIMESTAMP

Description¶

Returns a TIMESTAMP value representing the first microsecond of ASECOND in AMINUTE in AHOUR on the date given by AYEAR, AMONTH, and ADAY, or of the timestamp given by ATIMESTAMP depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year component of the resulting timestamp.

AMONTH

If provided, the month component of the resulting timestamp.

ADAY

If provided, the day component of the resulting timestamp.

AHOUR

If provided, the hour component of the resulting timestamp.

AMINUTE

If provided, the minute component of the resulting timestamp.

ASECOND

If provided, the second component of the resulting timestamp.

ATIMESTAMP

If provided, the timestamp from which to derive the start of the second. Either AYEAR, AMONTH, ADAY, AHOUR, AMINUTE, and ASECOND, or ATIMESTAMP must be provided.

Examples¶

Truncate the specified timestamp to the nearest second:

VALUES SECONDSTART('2010-01-23 04:56:12.123456');

1
--------------------------
2010-01-23-04.56.12.000000

Generate a timestamp at the start of a second with the specified fields:

VALUES SECONDSTART(2010, 2, 14, 9, 30, 44);

1
--------------------------
2010-02-14-09.30.44.000000

See Also¶

• Source code
• SECONDEND scalar function
• SECOND (built-in function)

Prototypes¶

TABLE_COLUMNS(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
TABLE_COLUMNS(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
TABLE_COLUMNS(ATABLE VARCHAR(128))

Description¶

This function returns a string containing a comma-separated list of the columns in the specified table in the order that they exist in the table.

If ASCHEMA is omitted it defaults to the value of the CURRENT SCHEMA special register. If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns will be included, otherwise they are excluded. GENERATED BY DEFAULT columns are always included. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns will be included, otherwise they are excluded.

Parameters¶

ASCHEMA

If provided, the schema containing the table for which to return a column list. Defaults to the value of the CURRENT SCHEMA special register if omitted.

ATABLE

The table for which to return a column list.

INCLUDE_GENERATED

If provided, specifies whether to include GENERATED ALWAYS columns in the result. Defaults to 'Y' if omitted.

INCLUDE_IDENTITY

If provided, specifies whether to include IDENTITY columns in the result. Defaults to 'Y' if omitted.

Examples¶

Return a comma-separated list of the columns in the SYSIBM.SYSTABLES table:

VALUES TABLE_COLUMNS('SYSIBM', 'SYSTABLES', 'Y', 'Y');

1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NAME,CREATOR,TYPE,CTIME,REMARKS,PACKED_DESC,VIEW_DESC,COLCOUNT,FID,TID,CARD,NPAGES,FPAGES,OVERFLOW,PARENTS,CHILDREN,SELFREFS,KEYCOLUMNS,KEYOBID,REL_DESC,BASE_NAME,BASE_SCHEMA,TBSPACE,INDEX_TBSPACE,LONG_TBSPACE,KEYUNIQUE,CHECKCOUNT,CHECK_DESC,STATS_TIME,DEFINER,TRIG_DESC,DATA_CAPTURE,STATUS,CONST_CHECKED,PMAP_ID,ENCODING_SCHEME,PCTFREE,ROWTYPESCHEMA,ROWTYPENAME,APPEND_MODE,PARTITION_MODE,REFRESH,REFRESH_TIME,LOCKSIZE,VOLATILE,REMOTE_DESC,CLUSTERED,AST_DESC,DROPRULE,LOGINDEXBUILD,PROPERTY,STATISTICS_PROFILE,COMPRESSION,ACCESS_MODE,ACTIVE_BLOCKS,MAXFREESPACESEARCH,AVGCOMPRESSEDROWSIZE,AVGROWCOMPRESSIONRATIO,AVGROWSIZE,PCTROWSCOMPRESSED,CODEPAGE,PCTPAGESSAVED,LAST_REGEN_TIME,SECPOLICYID,PROTECTIONGRANULARITY,INVALIDATE_TIME,DEFINERTYPE,ALTER_TIME,AUDITPOLICYID,COLLATIONID,COLLATIONID_ORDERBY,ONCOMMIT,ONROLLBACK,LOGGED,LASTUSED

See the implementation of EXPORT_TABLE scalar function for an example of the usage of this function within a stored procedure.

See Also¶

• Source code
• EXPORT_TABLE scalar function
• LOAD_TABLE scalar function
• SYSCAT.COLUMNS (built-in catalogue view)

Prototypes¶

TIME(AHOUR INTEGER, AMINUTE INTEGER, ASECOND INTEGER)
TIME(ASECONDS BIGINT)
TIME(ASECONDS INTEGER)

RETURNS TIME

Description¶

Returns a TIME with the components specified by AHOUR, AMINUTE and ASECOND in the first case. In the second case, returns a TIME ASECONDS after midnight. If ASECONDS represents a period longer than a day, the value used is ASECONDS mod 86400 (the “date” portion of the seconds value is removed before calculation). This function is essentially the reverse of the MIDNIGHT_SECONDS function.

Parameters¶

AHOUR

If provided, specifies the hour component of the resulting TIME.

AMINUTE

If provided, specifies the minute component of the resulting TIME.

ASECONDS

If AHOUR and AMINUTE are provided, specifies the second component of the resulting TIME. Otherwise, specifies the number of seconds after minute from which the hour and minute components will be derived.

Examples¶

Construct a time representing midnight:

VALUES TIME(0);

1
--------
00:00:00

Construct a time representing half past noon:

VALUES TIME(12, 30, 0);

1
--------
12:30:00

See Also¶

• Source code
• DATE scalar function
• TIME (built-in function)
• MIDNIGHT_SECONDS (built-in function)

Prototypes¶

TIMESTAMP(ASECONDS BIGINT)
TIMESTAMP(AYEAR INTEGER, AMONTH INTEGER, ADAY INTEGER, AHOUR INTEGER, AMINUTE INTEGER, ASECOND INTEGER, AMICROSECOND INTEGER)

RETURNS TIMESTAMP

Description¶

The first version of this function returns a TIMESTAMP ASECONDS seconds after 0000-12-31 00:00:00. This function is essentially the reverse of SECONDS scalar function. The ASECONDS value MUST be greater than 86400 (it must include a “date” portion) otherwise the returned value has an invalid year of 0000 and an error will occur.

The second version of this function simply constructs a timestamp from the given integer fields.

Parameters¶

ASECONDS

The number of seconds after the epoch (0000-12-31 00:00:00) which the resulting TIMESTAMP will represent.

AYEAR

The year for the resulting timestamp.

AMONTH

The month for the resulting timestamp (1-12).

ADAY

The day for the resulting timestamp (1-31).

AHOUR

The hours for the resulting timestamp (0-23).

AMINUTE

The minutes for the resulting timestamp (0-59).

ASECOND

The seconds for the resulting timestamp (0-59).

AMICROSECOND

The microseconds for the resulting timestamp (0-999999).

Examples¶

Construct a TIMESTAMP representing the epoch (note that 0 cannot be used due to the offset mentioned in SECONDS scalar function:

VALUES TIMESTAMP(86400);

1
--------------------------
0001-01-01-00.00.00.000000

Calculate a TIMESTAMP 10 seconds before midnight on new year’s day 2000 (admittedly this would be more simply accomplished with TIMESTAMP(YEARSTART(2000)) - 10 SECONDS, but for the sake of demonstration we’re using a round-trip of TIMESTAMP and SECONDS scalar function here):

VALUES TIMESTAMP(SECONDS(YEARSTART(2000)) - 10);

1
--------------------------
1999-12-31-23.59.50.000000

Construct a timestamp from a set of literal values:

VALUES TIMESTAMP(2000, 1, 1, 0, 0, 0, 0);

1
--------------------------
2000-01-01-00.00.00.000000

See Also¶

• Source code
• SECONDS scalar function
• TIMESTAMP (built-in function)

Prototypes¶

TS_FORMAT(AFORMAT VARCHAR(100), ATIMESTAMP TIMESTAMP)
TS_FORMAT(AFORMAT VARCHAR(100), ATIMESTAMP DATE)
TS_FORMAT(AFORMAT VARCHAR(100), ATIMESTAMP TIME)
TS_FORMAT(AFORMAT VARCHAR(100), ATIMESTAMP VARCHAR(26))

RETURNS VARCHAR(100)

Description¶

TS_FORMAT is a reimplementation of C’s strftime() function which converts a TIMESTAMP (or DATE, TIME, or VARCHAR(26) containing a string representation of a TIMESTAMP) into a VARCHAR according to a format string containing %-prefixed templates which will be replaced with components derived from the provided TIMESTAMP. The templates which can be used within the format string are as follows:

Parameters¶

AFORMAT

A string containing the templates to substitute with the fields of ATIMESTAMP.

ATIMESTAMP

A TIMESTAMP, DATE, TIME, or VARCHAR(26) value (containing a string representation of a timestamp) which will be used to calculate the substitutions for the templates in AFORMAT.

Examples¶

Format the 7th of August, 2010 in US style:

VALUES TS_FORMAT('%m/%d/%Y', '2010-08-07');

1
----------------------------------------------------------------------------------------------------
08/07/2010

Construct a sentence describing the week of a given date:

VALUES TS_FORMAT('Week %U of %B, %Y', '2010-01-01');

1
----------------------------------------------------------------------------------------------------
Week 01 of January, 2010

See Also¶

• Source code
• TIMESTAMP_FORMAT (built-in function)

Prototypes¶

WEEK_CLAIM(ADATE DATE)
WEEK_CLAIM(ADATE TIMESTAMP)
WEEK_CLAIM(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the week of the year of ADATE, according to the CLAIM calendar. ADATE can be expressed as a DATE value, a TIMESTAMP, or a VARCHAR containing a valid string representation of a date or timestamp. If ADATE is NULL, the result is NULL. Otherwise, the result is a SMALLINT between 1 and 53.

Parameters¶

ADATE

The date to calculate the week of year for, according to the CLAIM calendar.

Examples¶

Calculate the CLAIM week for the 2nd of January, 2010:

VALUES WEEK_CLAIM(DATE(2010, 1, 2));

1
------
     2

Calculate the CLAIM week for the 31st of December, 2010:

VALUES WEEK_CLAIM('2010-12-31');

1
------
     1

See Also¶

• Source code
• WEEK (built-in function)
• DAY_CLAIM
• MONTH_CLAIM scalar function
• QUARTER_CLAIM scalar function
• YEAR_CLAIM scalar function

Prototypes¶

UNICODE_REPLACE_BAD(SOURCE VARCHAR(4000), REPL VARCHAR(100))
UNICODE_REPLACE_BAD(SOURCE VARCHAR(4000))

RETURNS VARCHAR(4000)

Description¶

Under certain circumstances, DB2 will permit text containing characters invalid in the UTF-8 encoding scheme to be inserted into a column intended to contain UTF-8 encoded data. While this doesn’t cause a problem for DB2 queries, it can cause issues for down-stream appliations. This function provides a means of stripping or replacing such invalid characters.

Parameters¶

SOURCE

The string to search for characters invalid in the UTF-8 encoding scheme.

REPL

The string to replace any invalid sequences with. Defaults to the empty string if omitted.

Examples¶

Replacement of truncated UTF-8 characters:

VALUES
    (UNICODE_REPLACE_BAD('FOO' || X'C2', 'BAR'))

1
--------------------....
FOOBAR

Replacement of invalid characters in the middle of a string:

VALUES
    (UNICODE_REPLACE_BAD('FOO' || X'80' || BAR))

1
--------------------....
FOOBAR

See Also¶

• SQL source code
• C source code
• Wikipedia UTF-8 article

Prototypes¶

WEEKEND(AYEAR INTEGER, AWEEK INTEGER)
WEEKEND(ADATE DATE)
WEEKEND(ADATE TIMESTAMP)
WEEKEND(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the last day of AWEEK in AYEAR, or the last day of the week of ADATE (always a Saturday) depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AWEEK for which to return the ending date.

AWEEK

If provided, the week for which to return to the ending date.

ADATE

If provided the date in the week for which to return the ending date. Either AYEAR and AWEEK, or ADATE must be specified.

Examples¶

Calculate the ending date of the last week in 2010:

VALUES WEEKEND(2010, WEEKSINYEAR(2010));

1
----------
2011-01-01

Calculate the end of the week for the 28th of January, 2009:

VALUES WEEKEND('2009-01-28');

1
----------
2009-01-31

See Also¶

• Source code
• WEEKSTART scalar function
• WEEKEND_ISO scalar function
• WEEK (built-in function)

Prototypes¶

WEEKEND_ISO(AYEAR INTEGER, AWEEK INTEGER)
WEEKEND_ISO(ADATE DATE)
WEEKEND_ISO(ADATE TIMESTAMP)
WEEKEND_ISO(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the last day of AWEEK in AYEAR according to the ISO8601 standard, or the last day of the week of ADATE (always a Sunday) depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AWEEK for which to return the ending date.

AWEEK

If provided, the week for which to return to the ending date.

ADATE

If provided the date in the week for which to return the ending date. Either AYEAR and AWEEK, or ADATE must be specified.

Examples¶

Calculate the ending date of the last week in 2010:

VALUES WEEKEND_ISO(2010, WEEKSINYEAR_ISO(2010));

1
----------
2011-01-02

Calculate the end of the week for the 28th of January, 2009:

VALUES WEEKEND_ISO('2009-01-28');

1
----------
2009-02-01

See Also¶

• Source code
• WEEKSTART_ISO scalar function
• WEEKEND scalar function
• WEEK_ISO (built-in function)

Prototypes¶

WEEKSINMONTH(AYEAR INTEGER, AMONTH INTEGER)
WEEKSINMONTH(ADATE DATE)
WEEKSINMONTH(ADATE TIMESTAMP)
WEEKSINMONTH(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the number of weeks in AMONTH in AYEAR (weeks start on a Sunday, and partial weeks are permitted at the start and end of the month), or the number of weeks in the month that ADATE exists within depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year containing AMONTH for which to calculate the number of weeks.

AMONTH

If provided, the month within AYEAR for which to calculate the number of weeks.

ADATE

If provided, the date within the month for which to calculate the number of weeks. Either AYEAR and AMONTH, or ADATE must be provided.

Examples¶

Calculate the number of weeks in January 2010:

VALUES WEEKSINMONTH(2010, 1);

1
------
     6

Calculate the number of weeks in the months of 2010:

SELECT MONTH(D) AS MONTH, WEEKSINMONTH(D) AS WEEKS
FROM TABLE(DATE_RANGE('2010-01-01', '2010-12-01', 100));

MONTH       WEEKS
----------- ------
          1      6
          2      5
          3      5
          4      5
          5      6
          6      5
          7      5
          8      5
          9      5
         10      6
         11      5
         12      5

See Also¶

• Source code
• WEEKSINMONTH_ISO scalar function
• MONTH (built-in function)
• WEEK (built-in function)

Prototypes¶

WEEKSINMONTH_ISO(AYEAR INTEGER, AMONTH INTEGER)
WEEKSINMONTH_ISO(ADATE DATE)
WEEKSINMONTH_ISO(ADATE TIMESTAMP)
WEEKSINMONTH_ISO(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the number of weeks in AMONTH in AYEAR (weeks start on a Monday, and partial weeks are permitted at the start and end of the month), or the number of weeks in the month that ADATE exists within depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year containing AMONTH for which to calculate the number of weeks.

AMONTH

If provided, the month within AYEAR for which to calculate the number of weeks.

ADATE

If provided, the date within the month for which to calculate the number of weeks. Either AYEAR and AMONTH, or ADATE must be provided.

Examples¶

Calculate the number of weeks in January 2010:

VALUES WEEKSINMONTH_ISO(2010, 1);

1
------
     5

Calculate the number of weeks in the months of 2010:

SELECT MONTH(D) AS MONTH, WEEKSINMONTH_ISO(D) AS WEEKS
FROM TABLE(DATE_RANGE('2010-01-01', '2010-12-01', 100));

MONTH       WEEKS
----------- ------
          1      5
          2      4
          3      5
          4      5
          5      6
          6      5
          7      5
          8      6
          9      5
         10      5
         11      5
         12      5

See Also¶

• Source code
• WEEKSINMONTH scalar function
• MONTH (built-in function)
• WEEK_ISO (built-in function)

Prototypes¶

WEEKSINYEAR(AYEAR INTEGER)
WEEKSINYEAR(ADATE DATE)
WEEKSINYEAR(ADATE TIMESTAMP)
WEEKSINYEAR(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the number of weeks in AYEAR (weeks start on a Sunday, and partial weeks are permitted at the start and end of the year), or the number of weeks in the year that ADATE exists within depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year for which to calculate the number of weeks.

ADATE

If provided, the date in the year for which to calculate the number of weeks. Either AYEAR or ADATE must be specified.

Examples¶

Calculate the number of weeks in the year 2010:

VALUES WEEKSINYEAR(2010);

1
------
    53

Calculate the number of weeks in the first 10 years of the 21st century:

SELECT YEAR(D) AS YEAR, WEEKSINYEAR(D) AS WEEKS
FROM TABLE(DATE_RANGE('2000-01-01', '2010-01-01', 10000));

YEAR        WEEKS
----------- ------
       2000     54
       2001     53
       2002     53
       2003     53
       2004     53
       2005     53
       2006     53
       2007     53
       2008     53
       2009     53
       2010     53

See Also¶

• Source code
• WEEKSINYEAR_ISO scalar function
• WEEK (built-in function)

Prototypes¶

WEEKSINYEAR_ISO(AYEAR INTEGER)
WEEKSINYEAR_ISO(ADATE DATE)
WEEKSINYEAR_ISO(ADATE TIMESTAMP)
WEEKSINYEAR_ISO(ADATE VARCHAR(26))

RETURNS SMALLINT

Description¶

Returns the number of weeks in AYEAR according to the ISO8601 standard (weeks start on a Monday, and overlap calendar year ends to ensure all weeks are “whole”), or the number of weeks in the year that ADATE exists within depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year for which to calculate the number of weeks.

ADATE

If provided, the date in the year for which to calculate the number of weeks. Either AYEAR or ADATE must be specified.

Examples¶

Calculate the number of weeks in the year 2010 according to ISO8601:

VALUES WEEKSINYEAR_ISO(2010);

1
------
    52

Calculate the number of weeks in the first 10 years of the 21st century according to ISO8601:

SELECT YEAR(D) AS YEAR, WEEKSINYEAR_ISO(D) AS WEEKS
FROM TABLE(DATE_RANGE('2000-01-01', '2010-01-01', 10000));

YEAR        WEEKS
----------- ------
       2000     52
       2001     52
       2002     52
       2003     52
       2004     53
       2005     52
       2006     52
       2007     52
       2008     52
       2009     53
       2010     52

See Also¶

• Source code
• WEEKSINYEAR scalar function
• WEEK_ISO (built-in function)

Prototypes¶

WEEKSTART(AYEAR INTEGER, AWEEK INTEGER)
WEEKSTART(ADATE DATE)
WEEKSTART(ADATE TIMESTAMP)
WEEKSTART(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the first day of AWEEK in AYEAR, or the first day of the week of ADATE (always a Sunday) depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AWEEK for which to return the starting date.

AWEEK

If provided, the week for which to return to the starting date.

ADATE

If provided the date in the week for which to return the starting date. Either AYEAR and AWEEK, or ADATE must be specified.

Examples¶

Calculate the starting date of the first week in 2010:

VALUES WEEKSTART(2010, 1);

1
----------
2009-12-27

Calculate the start of the week for the 28th of January, 2009:

VALUES WEEKSTART('2009-01-28');

1
----------
2009-01-25

See Also¶

• Source code
• WEEKEND scalar function
• WEEKSTART_ISO scalar function
• WEEK (built-in function)

Prototypes¶

WEEKSTART_ISO(AYEAR INTEGER, AWEEK INTEGER)
WEEKSTART_ISO(ADATE DATE)
WEEKSTART_ISO(ADATE TIMESTAMP)
WEEKSTART_ISO(ADATE VARCHAR(26))

RETURNS DATE

Description¶

Returns a DATE representing the first day of AWEEK in AYEAR according to the ISO8601 standard, or the first day of the week of ADATE (always a Monday) depending on the variant of the function that is called.

Parameters¶

AYEAR

If provided, the year of AWEEK for which to return the starting date.

AWEEK

If provided, the week for which to return to the starting date.

ADATE

If provided the date in the week for which to return the starting date. Either AYEAR and AWEEK, or ADATE must be specified.

Examples¶

Calculate the starting date of the first week in 2010:

VALUES WEEKSTART_ISO(2010, 1);

1
----------
2010-01-04

Calculate the start of the week for the 28th of January, 2009:

VALUES WEEKSTART_ISO('2009-01-28');

1
----------
2009-01-26

See Also¶

• Source code
• WEEKEND_ISO scalar function
• WEEKSTART scalar function
• WEEK_ISO (built-in function)

Prototypes¶

WORKINGDAY(ADATE DATE, RELATIVE_TO DATE, ALOCATION VARCHAR(10))
WORKINGDAY(ADATE DATE, RELATIVE_TO DATE)
WORKINGDAY(ADATE DATE, ALOCATION VARCHAR(10))
WORKINGDAY(ADATE DATE)

RETURNS INTEGER