DB2 DDL

[bsanders1979]

Hello, No issue really...just wanted to provide the DDL for creating SCHEDULED_TASKS in db2. I basically took one of the already provided scripts, ran it thru ChatGPT and then made the necessary adjustments.

CREATE TABLE SCHEDULED_TASKS (
  TASK_NAME VARCHAR(100) NOT NULL,
  TASK_INSTANCE VARCHAR(100) NOT NULL,
  TASK_DATA BLOB,
  EXECUTION_TIME TIMESTAMP NOT NULL,
  PICKED CHAR(1) NOT NULL,
  PICKED_BY VARCHAR(50),
  LAST_SUCCESS TIMESTAMP,
  LAST_FAILURE TIMESTAMP,
  CONSECUTIVE_FAILURES INT,
  LAST_HEARTBEAT TIMESTAMP,
  VERSION BIGINT NOT NULL
);

ALTER TABLE SCHEDULED_TASKS ADD PRIMARY KEY (TASK_NAME, TASK_INSTANCE);

CREATE INDEX SCHEDULED_TASKS_EXECUTION_TIME_IDX ON SCHEDULED_TASKS (EXECUTION_TIME ASC);

CREATE INDEX SCHEDULED_TASKS_LAST_HEARTBEAT_IDX ON SCHEDULED_TASKS (LAST_HEARTBEAT ASC);

[kagkarlsson]

Hi! Thank you for contributing! Is there a TIMESTAMP type that also persists time zone? That would be preferrable I think.

[bsanders1979]

Hello, Apparently, some variants of db2 do support "timestamp with time zone". But, it seems the one I use (11.5 Linux) does not.

Granted, this statement works...

values (CURRENT TIMESTAMP, CURRENT TIMEZONE, CURRENT TIMESTAMP - CURRENT TIMEZONE);

But this does not... space or not separating TIMEZONE...

CREATE TABLE tz(tstz TIMESTAMP WITH TIME ZONE);

[kagkarlsson]

You can go with timezone, but you should use setting .alwaysPersistTimestampInUTC()

[kagkarlsson]

After you have successfully tested the ddl-script, would you open a PR and submit it?