如何更改DB2中的时间戳列大小？

Any idea how to change timestamp column size in DB2?

知道如何在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)

2 个解决方案

#1

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"

这取决于您的DB2平台和版本。 DB2中的时间戳对于小数秒部分都具有6位精度。以字符串形式,“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".

但是,DB2 LUW 10.5和DB2 for IBM i 7.2支持小数秒部分的精度为0到12位。在字符串形式中,您可以从“YYYY-MM-DD-HH:MM:SS”到“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.

默认精度为6,因此如果指定时间戳而没有精度(长度),则获得六位数的精度。否则,您可以指定从o到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.

但请注意,虽然DBMS表面的外部(不完全是字符串)格式可能在19到32个字节之间变化。 TS存储的内部格式可能不是。在DB2 for IBM i上,TS字段的内部存储格式至少需要7到13个字节,具体取决于精度。

timestamp(0) -> 7 bytes

timestamp(0) - > 7个字节

timestamp(6) -> 10 bytes (default)

timestamp(6) - > 10个字节(默认)

timestamp(12) -> 13 bytes

timestamp(12) - > 13个字节

#2

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.

由于您将10称为长度,因此我假设您正在查看SYSIBM.SYSCOLUMNS(或目录中的其他等效表)。

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

目录中的LENGTH列指的是字段的内部长度。您可以使用以下公式计算:

FLOOR( ((p+1)/2) + x )

p is the precision of the timestamp (the number of places after the decimal [the microseconds])

p是时间戳的精度(十进制后的位数[微秒])

x is 7 for a timestamp without a timezone, or 9 if it has a timezone (if supported by your platform)

对于没有时区的时间戳,x为7;如果有时区,则为9(如果您的平台支持)

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.

如果要将该字段与SQLCA中的字段进行比较,则该字段将是时间戳的字符表示的长度。有关这两个字段之间的说明,请参阅此信息中心文章。

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.

如果您确实想要更改时间戳字段的比例,则可以使用以下语句。 x应该是秒位置中小数点后面的位数的整数。

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

允许的小数数量因平台和版本而异。如果您使用的是旧版本,则可能无法更改比例,即设置为6.但是,某些较新的平台(如z / OS 10 +,LUW 9.7+)将允许您设置比例到0到12之间的数字(含)。

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

#1