Recently appended logged partitions

Hello all and wish you happy new year. Is it possible to list tables/partitions for which (fake) level0 backup must be run to allow writing to it? There is a flag with value 32768 in sysptnhdr but this flag is on even after L0 backup is finished. Thank you 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) -- Unless stated otherwise above: Kyndryl Česká republika, spol. s r. o. Sídlo: V Parku 2308/8, Chodov, 148 00 Praha 4, IČ: 096 28 886 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: V Parku 2308/8, Chodov, 148 00 Prague 4 Company ID: 096 28 886 Registered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

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

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

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

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

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

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

Milan, 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. ------------------------------ John Lengyel ------------------------------

Thank you John, it is exaxctly what I needed. 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) --