CODEWITHSUNDEEP
sqldb2
Rahul Raj
Rahul Raj

Reputation: 3459

How to change timestamp column size in DB2?

Any idea how to change timestamp column size in DB2?

I tried altering table, drop and then create table. Both didn't work.

Here are the queries I've tried:

alter table clnt_notes alter column lupd_ts set data type timestamp(26)

create table CLNT_NOTES
    (NOTE_ID int not null generated always as identity (start with 1, increment by 1),
     CLNT_ID varchar(10) not null,
     TX varchar(200),
     LUPD_TS timestamp(26) not null)

Upvotes: 1

Views: 7255

Answers (2)

Charles
Charles

Reputation: 23793

It depends on your DB2 platform and version. Timestamps in DB2 used to all have 6 digit precision for the fractional seconds portion. In string form, "YYYY-MM-DD-HH:MM:SS.000000"

However, DB2 LUW 10.5 and DB2 for IBM i 7.2 support from 0 to 12 digits of precision for the fraction seconds portion. In string form, you could have from "YYYY-MM-DD-HH:MM:SS" to "YYYY-MM-DD-HH:MM:SS.000000000000".

The default precision is 6, so if you specify a timestamp without a precision (length), you get the six digit precision. Otherwise you may specify a precision from o to 12.

create table mytable (
  ts0 timestamp(0)
  , ts6 timestamp
  , ts6_also timestamp(6)
  , ts12 timestamp(12)
);

Note however, that while the external (not exactly a string) format the DBMS surfaces could vary from 19 to 32 bytes. The internal format the TS is stored in may not. On DB2 for IBM i at least the internal storage format of TS field takes between 7 and 13 bytes depending on precision.

  • timestamp(0) -> 7 bytes
  • timestamp(6) -> 10 bytes (default)
  • timestamp(12) -> 13 bytes

Upvotes: 2

bhamby
bhamby

Reputation: 15450

Since you refer to 10 as the length, I'm going to assume you're looking in SYSIBM.SYSCOLUMNS (or another equivalent table in the catalog.

The LENGTH column in the catalog refers to the internal length of the field. You can calculate this using the following formula:

FLOOR( ((p+1)/2) + x )
  • p is the precision of the timestamp (the number of places after the decimal [the microseconds])
  • x is 7 for a timestamp without a timezone, or 9 if it has a timezone (if supported by your platform)

If you are comparing the to a field in the SQLCA, that field will be the length of a character representation of the timestamp. See this Information Center article for an explanation between the two fields.

If you truly want to change the scale of your timestamp field, then you can use the following statement. x should be an integer for the number of places after the decimal in the seconds position.

The number of allowed decimals varies by platform and version. If you're on an older version, you can likely not change the scale, which is set at 6. However, some of the newer platforms (like z/OS 10+, LUW 9.7+), will allow you to set the scale to a number between 0 and 12 (inclusive).

ALTER TABLE SESSION.TSTAMP_TEST 
ALTER COLUMN tstamp 
SET DATA TYPE TIMESTAMP(x);

Upvotes: 0

Related Questions