Ah...thats very interesting
@Michael Roecken . We have observed that in both the Runtime case(specific to including logs in backup images) and the database recovery case, we do receive patterns with the chain being unknown. When we send back multiple files as potentially matching patterns, DB2 does seem to choose the right file based on some internal logic. That leads me to a few questions:
1. I hope the assumption is correct that when an input pattern is sent, DB2 can expect more than one file (and not just one candidate) to be sent back (because it is a pattern and it could match multiple files). Db2 just keeps making db2VendorGetNextObj calls until we tell it there aren't anymore using an SQLUV_END_OF_DATA return code. This seems to be the case based on our observations.
2. We have also noticed in case of recovery that once we have exhausted files in one chain, DB2 automatically starts querying for log files from the next chain (chain specified exactly, but sequence specified as a pattern). This continues until the vendor indicates through an SQLUV_OBJ_NOT_FOUND return code that there are no more files available matching that chain. This makes sense, since it would want to roll forward to the latest logs (in a point in time recovery or end of logs case). So, it should keep looking for log files even in newer chains. Just an observation.
3. The fact that DB2 looks for the log sequence number with the highest chain does put some recovery scenarios out of reach. For ex:
- T1: Backup B1 including logs S1, S2
- T2: Timestamp after logs S3 and S4 created and archived
- T3: Restore same database to B1
- T4: Logs S3 and S4 created and archived
- T5: Restore to time T2 from B1 (Not possible because DB2 has no way to retrieve log S3 from chain 0 because it is always looking for S3 from the latest chain, which, in this case would be chain 1)
It is a convoluted case, but just trying to make sure that my interpretation of the behavior is correct.
Again, thank you so much for your detailed answers, they are super helpful!!
------------------------------
pulapura
------------------------------
Original Message:
Sent: Tue December 06, 2022 07:20 PM
From: Michael Roecken
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
Hi. Typically, we ask for log files based on some pattern of Cnnnnnnn_Smmmmmmm.LOG. n/m can be '*'. Cnnnnnnn represents a log chain. Smmmmmmm.LOG represents a log file number.
We know what Smmmmmmm.LOG but we don't always know what Cnnnnnnn we need. We have internal logic that helps us narrow down most log chains, but sometimes we need to query vendor to find the log chain with the highest log file number and then that is what we use. High percentage of use cases this will match without problem. For the others, we have way to make it work but take on a case by case basis.
Log retrieval falls into two classes:
- Runtime: like replication, include logs into backup image, recovery of older log files ... we normally know what log chain the database is on and can ask for an exact match as the files come from the past.
- Database recovery: we may needs files going forward blind, in which case we query for the highest log chain that contains the next log file to read.
------------------------------
Michael Roecken
Original Message:
Sent: Tue December 06, 2022 11:46 AM
From: Sudip Pulapura
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
Thank you @Michael Roecken , one observation that I have had is that when DB2 queries for LOG files using patterns and the vendor sends back candidate files that match the pattern, DB2 seems to magically select the right files to retrieve!! This is really great since it helps with the vendor not having to decide which LOG files to retrieve and leave that to DB2. However, am curious how DB2 decides on which files to query from among the candidate files being sent back. Any insights in to this? Does it look at log records and identify whether the file retrieved has the right "next" records?
Thanks
------------------------------
pulapura
Original Message:
Sent: Mon December 05, 2022 11:22 PM
From: Michael Roecken
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
Hi. Keep in mind that our vendor interface was designed some 15-20 years ago for TSM (Spectrum Protect). TSM uses the machine name as one of the filtering capabilities. This is an alternative for db2instance. From my knowledge we have very few, if any, multiple Db2 installations (instances) from the same hostname. This allowed us to not have to use db2instance. As other vendors adopted Db2 and built their own vendor libraries, due to the low number of installations of those vendor libraries, we seem to have avoided any issues with this implementation.
For reference, the current cross node recovery steps we use for TSM is https://www.ibm.com/docs/en/db2/11.5?topic=data-using-db2adutl.
If we had a chance to re-architect this again, we probably should have included db2instance name as we do for our DISK archiving method. At this point, any change would break what we currently have documented and there currently is no known customer issues with the current architecture.
I suggest you look at using hostname to organize your data objects. If that doesn't work for you then I suggest you contact IBM Service and we can gather further information and see how to proceed from there.
Thanks.
------------------------------
Michael Roecken
Original Message:
Sent: Fri November 25, 2022 04:49 AM
From: Sudip Pulapura
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
Thank you @Michael Roecken . I tried spending some time trying to identify ways to do restore without having a db2 instance, but am confused as to how you would do it. So, let me clarify:
1) The same db name, chain and sequence number can appear as part of multiple db instances, is that correct? If yes, then how would the vendor decide which log files would match the criteria for the query in case the db instance name isn't provided. Is this state that the vendor would need to maintain? Am curious why Db2 wouldn't provide this information as part of the request.
2) You mentioned cross system recovery is possible, hence instance name does not need to match. However, the source log files using which the restore / rollforward is being done would belong to a specific instance and hence isn't the source instance name needed?
Thank you for your patience with answering these questions!!
------------------------------
pulapura
Original Message:
Sent: Tue November 22, 2022 05:02 PM
From: Michael Roecken
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
> From the sequence of calls I have executed, the db2Instance at in->DB2_session->db2instance comes in as "*"
This is a wildcard, meaning find me all objects that match the other search criteria.
When Db2 retrieves a log file, it searches for a log file matching usually, database name (dbname), database alias (alias), filename (C???????_S???????.LOG or some filled out numeric variation depending on the query context).
You can do cross system recovery, so instance name does not need to match.
Thanks.
------------------------------
Michael Roecken
Original Message:
Sent: Tue November 22, 2022 01:59 PM
From: Sudip Pulapura
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
Hi Kelly
From the sequence of calls I have executed, the db2Instance at in->DB2_session->db2instance comes in as "*". Thats where I have a problem since the db2Instance isn't specified.
Thanks
Sudip
------------------------------
pulapura
Original Message:
Sent: Tue November 22, 2022 10:30 AM
From: Kelly Rodger
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
I might have to go double-check but believe this should be available. Do you have access to the instance through the structures of sqluvint( )'s Init_input ( in->DB2_session->db2instance )?
------------------------------
Kelly Rodger
Senior Development Manager
IBM Canada
Markham ON
Original Message:
Sent: Wed November 16, 2022 10:40 PM
From: Sudip Pulapura
Subject: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore
In reference to the sequence of calls made during backup / restore operations listed at Db2 call sequence for vendor APIs , the documentation states that DB2 will issue the following sequence of calls when it needs to "search" for files.
- sqluvint (action=SQLUV_QUERY_LOGS)
- db2VendorGetNextObj
- sqluvend
As a sample use case, if DB2 needs to retrieve archived log files as part of a backup process, it would try to search for those files using the above sequence from the vendor. However, we do see that the db2instance name is also not provided as part of this sequence of calls. Is there something specific that needs to be done for this since without the db2instance name, it would not be possible to deterministically provide an accurate set of files matching the search results. Any pointers in this regard would be appreciated.
Thanks
------------------------------
pulapura
------------------------------
#Db2