Population of Transaction Start ID in System-Period Temporal Table

Jeff Haekenkamp

Population of Transaction Start ID in System-Period Temporal Table

I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column.  In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.

The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...".  The first statement seems to indicate that the column value is always null if the column is nullable.  But in that case, what would be the point of having the column there at all?

I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null.  What am I missing?

Thanks,

Jeff

 

 

Daniel Luksetich

Population of Transaction Start ID in System-Period Temporal Table
(in response to Jeff Haekenkamp)
z/OS or LUW?



If z/OS the column is not used.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Jeff Haekenkamp [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 12:42 PM
To: [login to unmask email]
Subject: [DB2-L] - Population of Transaction Start ID in System-Period Temporal Table



I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column. In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.

The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...". The first statement seems to indicate that the column value is always null if the column is nullable. But in that case, what would be the point of having the column there at all?

I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null. What am I missing?

Thanks,

Jeff







-----End Original Message-----

Jeff Haekenkamp

RE: Population of Transaction Start ID in System-Period Temporal Table
(in response to Daniel Luksetich)

We have DB2 on both z/OS and LUW, but in this particular case I was testing on z/OS.  If the column is not used on z/OS, then it goes back to my question: What is the point? 

The manual says that the column is required for a system-period temporal table, that it is not updatable, and that it cannot have a default clause.  Why require a column that will never be used?

I see that the wording in the DB2 LUW manual is slightly different.  "The null value is assigned to the transaction-start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted."

I guess I can try defining the column as NOT NULL just so it will contain something that might be meaninful.

 

Thanks,

Jeff

Daniel Luksetich

Population of Transaction Start ID in System-Period Temporal Table
(in response to Jeff Haekenkamp)
Yea, as far as I know the difference is for compatibility across platforms. I have been told by the developers directly to ignore that column on z/OS. That was back when they first came out in V10, but I have not heard differently since.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Jeff Haekenkamp [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 1:55 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table



We have DB2 on both z/OS and LUW, but in this particular case I was testing on z/OS. If the column is not used on z/OS, then it goes back to my question: What is the point?

The manual says that the column is required for a system-period temporal table, that it is not updatable, and that it cannot have a default clause. Why require a column that will never be used?

I see that the wording in the DB2 LUW manual is slightly different. "The null value is assigned to the transaction-start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted."

I guess I can try defining the column as NOT NULL just so it will contain something that might be meaninful.



Thanks,

Jeff



-----End Original Message-----

Myron Miller

Population of Transaction Start ID in System-Period Temporal Table
(in response to Daniel Luksetich)
Why in the world is the column required if it isn't used? What am i missing or not understanding here.


Thanks Myron W. Miller


________________________________
From: Daniel L Luksetich <[login to unmask email]>
Sent: Tuesday, March 14, 2017 1:57 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table


z/OS or LUW?



If z/OS the column is not used.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Jeff Haekenkamp [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 12:42 PM
To: [login to unmask email]
Subject: [DB2-L] - Population of Transaction Start ID in System-Period Temporal Table



I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column. In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.

The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...". The first statement seems to indicate that the column value is always null if the column is nullable. But in that case, what would be the point of having the column there at all?

I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null. What am I missing?

Thanks,

Jeff







-----End Original Message-----

-----End Original Message-----

Carol Anne Sutfin

Population of Transaction Start ID in System-Period Temporal Table
(in response to Myron Miller)
In order for the vendor to not have two versions for the program, one for VSAM or sequential data sets and then one for a different DB2 layout.

Carol Sutfin

From: Myron Miller [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 5:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table


[External Content] Please use caution.

________________________________

Why in the world is the column required if it isn't used? What am i missing or not understanding here.


Thanks Myron W. Miller

________________________________
From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, March 14, 2017 1:57 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table


z/OS or LUW?



If z/OS the column is not used.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows



From: Jeff Haekenkamp [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 12:42 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Population of Transaction Start ID in System-Period Temporal Table



I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column. In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.

The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...". The first statement seems to indicate that the column value is always null if the column is nullable. But in that case, what would be the point of having the column there at all?

I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null. What am I missing?

Thanks,

Jeff






-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Horacio Villa

Configure a DDF connection from DB2 10 to another DB2 10 datasharing
(in response to Carol Anne Sutfin)
Trying to configure this, with no success.
The request is to query from a DB2 V10 AR to a DB2 V10 AS which is a 4-way
datasharing, with no userid translation.
What is a clear resource I can read to accomplish this?
All I've found is confusing.
Thanks,

Horacio Villa

Myron Miller

Population of Transaction Start ID in System-Period Temporal Table
(in response to Carol Anne Sutfin)
HUH? Correct me if I'm wrong, but isn't it a DB2 column required for using System period temporal tables. What does this have to do with VSAM files? I can have a Table that has the same columns as a VSAM file and does not use System Period temporal tables. I just created one last week that is an exact mapping of a vsam file so it would be easy to convert the program from VSAM to DB2 with minimal changes and the table is not a system-period temporal table.


Thanks Myron W. Miller


________________________________
From: Carol A. Sutfin <[login to unmask email]>
Sent: Tuesday, March 14, 2017 6:17 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table


In order for the vendor to not have two versions for the program, one for VSAM or sequential data sets and then one for a different DB2 layout.



Carol Sutfin



From: Myron Miller [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 5:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table



[External Content] Please use caution.

________________________________

Why in the world is the column required if it isn't used? What am i missing or not understanding here.



Thanks Myron W. Miller



________________________________

From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, March 14, 2017 1:57 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table



z/OS or LUW?



If z/OS the column is not used.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Jeff Haekenkamp [mailto:[login to unmask email]
Sent: Tuesday, March 14, 2017 12:42 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Population of Transaction Start ID in System-Period Temporal Table



I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column. In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.

The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...". The first statement seems to indicate that the column value is always null if the column is nullable. But in that case, what would be the point of having the column there at all?

I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null. What am I missing?

Thanks,

Jeff







-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

-----End Original Message-----

Daniel Luksetich

Population of Transaction Start ID in System-Period Temporal Table
(in response to Myron Miller)


It is my understanding, albeit from several years ago, that the column
exists for cross-platform compatibility, but is not used on z/OS.

Dan

On 03.14.2017 16:29, Myron Miller wrote:

> HUH? Correct me if I'm wrong, but isn't it a DB2 column required for using System period temporal tables. What does this have to do with VSAM files? I can have a Table that has the same columns as a VSAM file and does not use System Period temporal tables. I just created one last week that is an exact mapping of a vsam file so it would be easy to convert the program from VSAM to DB2 with minimal changes and the table is not a system-period temporal table.
>
> Thanks Myron W. Miller
>
> -------------------------
>
> FROM: Carol A. Sutfin <[login to unmask email]>
> SENT: Tuesday, March 14, 2017 6:17 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table
>
> In order for the vendor to not have two versions for the program, one for VSAM or sequential data sets and then one for a different DB2 layout.
>
> Carol Sutfin
>
> FROM: Myron Miller [mailto:[login to unmask email]
> SENT: Tuesday, March 14, 2017 5:06 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table
>
> [External Content] Please use caution.
>
> -------------------------
>
> Why in the world is the column required if it isn't used? What am i missing or not understanding here.
>
> Thanks Myron W. Miller
>
> -------------------------
>
> FROM: Daniel L Luksetich <[login to unmask email]>
> SENT: Tuesday, March 14, 2017 1:57 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: Population of Transaction Start ID in System-Period Temporal Table
>
> z/OS or LUW?
>
> If z/OS the column is not used.
>
> Dan
>
> Daniel L Luksetich
>
> DanL Database Consulting
>
> IBM GOLD Consultant
>
> IBM Champion for Analytics
>
> IDUG Content Committee Chairman
>
> IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
>
> IBM Certified System Administrator - DB2 11 for z/OS
>
> IBM Certified Application Developer - DB2 11 for z/OS
>
> IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows
>
> FROM: Jeff Haekenkamp [mailto:[login to unmask email]
> SENT: Tuesday, March 14, 2017 12:42 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - Population of Transaction Start ID in System-Period Temporal Table
>
> I have been doing some research on temporal tables, and I'm having a hard time understanding the function of the transaction start id column. In particular, I don't understand how the column is populated if it is defined as nullable, which is how most of the examples I've seen have it defined.
>
> The manual says this: "The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock ...". The first statement seems to indicate that the column value is always null if the column is nullable. But in that case, what would be the point of having the column there at all?
>
> I've done some testing with a temporal table, with the transaction start id column defined as nullable, and so far the column value is always null. What am I missing?
>
> Thanks,
>
> Jeff
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=180477&amp;anc=p180477#p180477
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/page/dts2017
[5] http://www.idug.org/p/cm/ld/fid=2

Daniel Luksetich

Configure a DDF connection from DB2 10 to another DB2 10 datasharing
(in response to Horacio Villa)


maybe start here:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246952.pdf [6]

On 03.14.2017 16:04, Horacio Villa wrote:

> Trying to configure this, with no success.
> The request is to query from a DB2 V10 AR to a DB2 V10 AS which is a 4-way datasharing, with no userid translation.
> What is a clear resource I can read to accomplish this?
> All I've found is confusing.
> Thanks,
>
> Horacio Villa
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=180476&amp;anc=p180476#p180476
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/page/dts2017
[5] http://www.idug.org/p/cm/ld/fid=2
[6] http://www.redbooks.ibm.com/redbooks/pdfs/sg246952.pdf

David Joyce

RE: Population of Transaction Start ID in System-Period Temporal Table
(in response to Jeff Haekenkamp)

Jeff,

   I don't know if this helps but this is the definition I use for the CREATE_ID column,

CREATE_ID TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL
GENERATED ALWAYS AS TRANSACTION START ID,

The default value assigned is 0001-01-01-00.00.00.000000000000 and does not change when the "old" version of the row is inserted to the history table.