The sysmaster query I suggested above uses two criteria for flagging a table: the "recently appended" flag and a time stamp, just as you described.
Note that my first reply to your original question was lacking, and I later edited it to include a query that should work for you.
Original Message:
Sent: Fri January 03, 2025 03:07 AM
From: Milan Rafaj
Subject: Recently appended logged partitions
Hi John, the problem is that this flag is set when light append operation is used but it is not unset after level 0 backup is done so I cannot differentiate among tables which are writeble from those for which level 0 backup is still required – this was my goal in order to identified tasks which do not contain level 0 backup as a part of them. May be some timestamp on such partitions are compared with timestamp of last Level 0 backup by engine to generate -197 error.
Milan Rafaj
Senior Lead, Infrastructure/Cloud Architecture
Kyndryl Consult
+420 737 264 248
www.kyndryl.cz
Planned absence/Plánovaná nepřítomnost:
Kyndryl Česká republika, spol. s r. o.
Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,
IČ: 14890992
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00
Company ID: 14890992
Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)
--
Original Message:
Sent: 1/2/2025 12:33:00 PM
From: John Lengyel
Subject: RE: Recently appended logged partitions
A partition's "creation date" is more malleable than you might think. Each time you load data into a raw table or light append data into a logged table such as in your onpload scenario, the partition's creation date is updated to the current time. (You can see this in an oncheck -pt output.) When you later try to write to the table in the normal way, we compare that "creation date" to the time of the last level 0 archive of the containing dbspace. If the partition's time is greater than the level 0 time, and it has that 0x8000 flag on, we raise a 197 error.
You may be able to do better here given that insight but this is what I've whipped up for you:
select trim(a.dbsname) || ":" || trim(a.owner) || "." || trim(a.tabname)
from systabnames a, sysptnhdr b, sysdbstab c
where a.partnum = b.partnum
and bitval(b.flags, '0x8000') = 1
and b.dbsnum = c.dbsnum
and b.created > c.level0;
------------------------------
John Lengyel
------------------------------
Original Message:
Sent: Thu January 02, 2025 08:41 AM
From: Milan Rafaj
Subject: Recently appended logged partitions
Thank you Art, yes I want to identify tasks which are using unlogged operations to check where L0 backup shoud become part of processing to avoid this risk.
Milan Rafaj
Senior Lead, Infrastructure/Cloud Architecture
Kyndryl Consult
+420 737 264 248
www.kyndryl.cz
Planned absence/Plánovaná nepřítomnost:
Kyndryl Česká republika, spol. s r. o.
Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,
IČ: 14890992
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00
Company ID: 14890992
Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)
--
Original Message:
Sent: 1/2/2025 8:33:00 AM
From: Art Kagel
Subject: RE: Recently appended logged partitions
Milan:
OK, after an EXPRESS mode load into a table altered to type(raw) and back to type(standard). Got it. Yea, an archive is required and really not just a FAKE archive either since the newly loaded data will not be restored after a crash since it was not logged! That is why an archive is required.
No, there is no flag indicating that as far as I am aware.
As a side note, I hope the client is not using replication because any data loaded into the table while in a raw state will not be replicated to the secondaries for the same reason that an archive is needed!
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Thu January 02, 2025 08:20 AM
From: Milan Rafaj
Subject: Recently appended logged partitions
Hello Art, I am referrig to a situation when onpload or external tables are used to load batch of data into tables temporarily changed to raw and back. Our customer is using such tasks but in some situations level 0 backup is not run and then some jobs fails with -197 error. I would like to have some tool to discover if such tables exist.
Milan Rafaj
Senior Lead, Infrastructure/Cloud Architecture
Kyndryl Consult
+420 737 264 248
www.kyndryl.cz
Planned absence/Plánovaná nepřítomnost:
Kyndryl Česká republika, spol. s r. o.
Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,
IČ: 14890992
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00
Company ID: 14890992
Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)
--
Original Message:
Sent: 1/2/2025 8:13:00 AM
From: Art Kagel
Subject: RE: Recently appended logged partitions
Milan:
I'm not sure what you are asking about. There is no flag that indicates that a level 0 archive is needed to permit writing to a table because there is no such write block condition.
Are you referring to when you add, extend, or drop a chunk or dbspace and the message log indicates that a level 0 archive is required? That does not block writing to anything. It really should just be a stern warning and strong recommendation because if you forget to archive soon and the server crashes, the latest changes to the infrastructure will not be restored, so you REALLY REALLY REALLY should take a level 0 archive ASAP.
Am I missing something?
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com