Data version in Image copy dataset

Bharath Nunepalli

Data version in Image copy dataset

Hi,

I want to find the version of the data in a image copy dataset. I believe I can get that using DSN1PRNT utility.

Which field in the output of DSN1PRNT for an image copy dataset provides the version of data in that dataset?

 

Thanks,

Bharath Nunepalli

Avram Friedman

RE: Data version in Image copy dataset
(in response to Bharath Nunepalli)

I think the value you are looking for is called the LEVELID
It is recorded from time to time to the header page of the dataset and various catalog tables.
It is the logrecord number or LRSN at the time of recording.
The header page is documented in the diagnostic manual

The question that most people have with this field is DB2 doing down level data detection.
It usually applies to tablespaces being restored outside of DB2s control.
This detection can be disabled with a zparm or selectivly bypassed with a DIAGNOSE REPAIR

 

Avram Friedman
DB2-L hall of fame contributer
DB2-L acting administrator

[login to unmask email]

Bharath Nunepalli

RE: Data version in Image copy dataset
(in response to Avram Friedman)

I Header page of the Image copy dataset, I see below

HPGLEVEL='D33BB529A7EA'X 

HPGPLEVL='D33BB2C832CB'X 

 

So, HPGLEVEL is the version of the data present in the image copy dataset?

How to correspond that with value VERSION column of sysibm.systables?

Bharath Nunepalli

RE: Data version in Image copy dataset
(in response to Bharath Nunepalli)

Is HPGLEVEL the timestamp value when the VERSION of the Table is changed?

Kai Stroh

RE: Data version in Image copy dataset
(in response to Bharath Nunepalli)

The LEVELID is not the same as VERSION in SYSABLES or CURRENT_VERSION in SYSTABLESPACE. To find the table version, look for entries like this in the tablespace header page,:

 

DVI HASH BUCKET:  HPGDBKT#='01'X  HPG1BEYE='4E'X                         
  HPGDCOLL#='0100'X  HPG1OBID='001B'X  HPG1V='01'X  HPG1RID='0000000201'X

 

Note: Do not confuse DVI hash buckets with SI hash buckets. They are not the same.

 

HPGDBKT# is the bucket number, HPG1BEYE is an eye catcher. Multiple HPGDCOLL# entries may exist under the same DVI HASH BUCKET entry (the maximum number of entries per bucket can be found in HPGDNUMC).

HPGDCOLL# is the entry number, assigned from high to low. HPG1OBID is the OBID of the table, HPG1V is the version number (this is what you are looking for). HPG1RID is the RID of the row that stores the OBD record for this table and version. There will be one HPGDCOLL# for each active version of each table in the tablespace. Active version means that there may be rows in the tablespace with that version number. If you reprganize the tablespace, all rows are converted to the most recent version and you should only have one HPGDCOLL# entry per table in the header page.

Tables that have never been altered are version 0 and do not have a HPGDCOLL# entry (this will change soon though, there will be a PTF that makes DB2 create system pages with OBD records and hash bucket entries for all tables, even when they are version 0).

The hash buckets are used for faster access to the system pages that contain the OBD records for the version (the OBD records describe the table structure, i.e. number of columns and so on). In partitioned and universal tablespaces, there is only one table and hence only one bucket with up to 256 entries. In simple and segmented tablespaces. there are 31 buckets with up to 7 entries each. The bucket number for any given table is MOD(OBID, HPGDNUMB) + 1, but DB2 does not really seem to care in which bucket an entry is located. This becomes apparent when you copy a tablespace with DSN1COPY and do OBID translation. The OBIDs of the tables will be translated, but the entries in the DVI hash buckets will not be rearranged.

In a simple or segmented tablespace, if you have more than 7 tables for the same bucket, the overflow bit (left-most bit in HPG1BEYE) will be on. This indicates that there are more active versions than the bucket can store, and you have to follow the SPOPREV chain in the system pages to find all versions of all tables. This situation is not very likely though as the bucket number is chosen based on the OBID. But it can happen if you have a very large number of tables in the same tablespace.

 

Hope that helps

Kai

Bharath Nunepalli

RE: Data version in Image copy dataset
(in response to Kai Stroh)

Hi Kai,

I did DSN1PRNT on Tablespace VSAM, but didnt see DVI HASH BUCKET entries in the Header page.
Did I miss anything here?

 

I believe that those SYSLGRNX entries are updated when a page set (or partition) is pseudo-closed (i.e., when the state of the object is switched to read-only from read/write after the object has gone for a pseudo-close interval with no data-change activity) and when the object's state is switched back to read-only (when the first data-change statement after a pseudo-close comes along).
Is this correct?

And, the HPGLEVEL and HPGPLEVL are updated by Tablespace REORG, right?

Kai Stroh

RE: Data version in Image copy dataset
(in response to Bharath Nunepalli)

If there are no DVI hash buckets in the header page, then the data version of the table is 0.

Note that when you do a DSN1PRNT of a VSAM page set right after you alter a table, you may not see the DVI hash bucket entries if they are still in the buffer pool and have not been flushed to DASD yet. You can stop the tablespace in order to force Db2 to write the pages from the buffer pool to DASD.

For a good explanation of the LEVELID, have a look at http://www.idug.org/p/fo/et/thread=21896

Bharath Nunepalli

RE: Data version in Image copy dataset
(in response to Kai Stroh)

Thanks Kai

venkata rama rajesh mallina

RE: Data version in Image copy dataset
(in response to Bharath Nunepalli)

Are you looking for data records version ?

If yes, Following information may useful to you

When you performed changes like ADD column, Change datatype length, Version of of the table will increment by one. But existing records of the table are still old version. one byte record type in row header will have the version information. Old version records in the table can still accessible. The system version page will have information of all existing version.  This system version page will help to access data of old versions and perform recovery to the image copy having old version data. The column OLDEST_VERSION in SYSIBM.SYSCOPY will have the oldest version number among all version of data records in image copy.
Note: Reorg will make all old version data records to current version format.

Venkata Rama Rajesh

Bharath Nunepalli

RE: Data version in Image copy dataset
(in response to venkata rama rajesh mallina)

Thanks for the details.

Charles Brown

Data version in Image copy dataset
(in response to Bharath Nunepalli)
Have you tried sysibm.syscopy. It contains wealth of info on the image copy dataset particularly its generation. Hope that helps

Sent from my iPad

> On Nov 7, 2017, at 1:20 PM, Bharath Nunepalli <[login to unmask email]> wrote:
>
> Hi,
> I want to find the version of the data in a image copy dataset. I believe I can get that using DSN1PRNT utility.
>
> Which field in the output of DSN1PRNT for an image copy dataset provides the version of data in that dataset?
>
>
>
> Thanks,
>
> Bharath Nunepalli
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>