Db2

 View Only
Expand all | Collapse all

Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

  • 1.  Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Thu November 17, 2022 10:05 AM
    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


  • 2.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue November 22, 2022 10:31 AM
    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
    ------------------------------



  • 3.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue November 22, 2022 02:00 PM
    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
    ------------------------------



  • 4.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Wed November 23, 2022 10:03 AM
    > 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
    ------------------------------



  • 5.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Fri November 25, 2022 04:50 AM
    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
    ------------------------------



  • 6.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Mon December 05, 2022 11:23 PM
    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
    ------------------------------



  • 7.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 06, 2022 11:46 AM
    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
    ------------------------------



  • 8.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 06, 2022 07:21 PM

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



  • 9.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 06, 2022 11:00 PM
    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
    ------------------------------



  • 10.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Mon December 12, 2022 11:55 AM

    Hello, answers below:

    1. Yes.  We will keep asking for more until we think we found what we need.  If the file internally is not correct (e.g. wrong database, wrong chain, etc.) we will report an error.  At this point an investigation would need to take place why.

    2. Yes.  We have knowledge to try to find the right chain.  There are cases where this may not be good enough, in which we would report chain error.  The RECOVER command tries to be smarter by using the history file, but even that has limitations.  ROLLFORWARD, when looking for files in the future will look for the file in the highest chain.  In newer releases, we have a lookup table stored in our meta data that helps us find the right chain as well for certain ranges of log files.

    3. I think is a case where you are restoring and want to roll forward to an older chain.  Yes, ROLLFORWARD will want the files in the higher chain.  So more care needs to be done when going "backwards" in time like this.  The RECOVER command should be able to handle this.

    Thanks.



    ------------------------------
    Michael Roecken
    ------------------------------



  • 11.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Mon December 12, 2022 10:49 PM
    Thanks @Michael Roecken , I hope this will be my last post to you in this thread :)

    Based on our conversation above, ​my understanding is that typically, vendors would have to maintain some state at their end about a restore process since the search queries that come in as part of the roll forward really do not have anything to tie it to the "restore" or "roll forward" operation. It just comes in as an independent search for a query pattern.

    It's an interesting question on what that state is since, again, there is nothing that ties the search query to the restore or roll forward operation. Even the DB name that comes in is the target DB name and there is no timestamp provided either. I am guessing it will come down to storing something with the source DB's configuration so that the backup image (and hence the restored DB) carries that through when the restore happens. We are bound to do some optimisations in terms of narrowing down the search results especially in cases where the chain number is provided, but not the sequence number(C0000001_S???????.LOG) since that could be a potentially huge list that matches the pattern.

    Also, as I understand, RECOVER can only be used to recover the same database, but not restore to a different database. For a restore to a different database, I will have to use the combination of restore + roll forward. Or am I missing something?

    Thank you for your patience throughout this exchange.

    ------------------------------
    pulapura
    ------------------------------



  • 12.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 13, 2022 10:18 AM

    Hello, comments below:

    > Based on our conversation above, ​my understanding is that typically, vendors would have to maintain some state at their end about a restore process since the search queries that come in as part of the roll forward really do not have anything to tie it to the "restore" or "roll forward" operation. It just comes in as an independent search for a query pattern.

    Should not.  We ask for objects based on a "defined" search criteria.  The vendor should not care what those objects are going to be used for.  The vendor should just match those criteria and return in the API flow documented.  What Db2 does with those objects should not be of a concern for you.  So I wonder what makes you think otherwise.


    > RECOVER can only be used to recover the same database, but not restore to a different database. For a restore to a different database, I will have to use the combination of restore + roll forward.

    You can only RECOVER the same database name.  The RESTORE's INTO clause is not available.  You can still recover into a non-existing database (e.g. DR case) and there is a HISTORY FILE option to assist with that.


    Thanks.



    ------------------------------
    Michael Roecken
    ------------------------------



  • 13.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 13, 2022 08:18 PM
    Ok, the previous one wasn't the last post :)

    > Should not.  We ask for objects based on a "defined" search criteria.  The vendor should not care what those objects are going to be used for.  The vendor should just match those criteria and return in the API flow documented.  What Db2 does with those objects should not be of a concern for you.  So I wonder what makes you think otherwise.

    Ok, this is what is confusing me. I do see calls coming from DB2 with the chain known, but sequence number unknown. Ex: C0000001_S???????.LOG .  This typically happens during roll forward once we tell DB2 that we have no more files in the previous chain (in our case C0000000). Db2 then proceeds to make a call to fetch files from the "next" chain. The number of files matching C0000001_S???????.LOG could be potentially very large. Wouldn't this potentially cause an issue where there is a huge list of matching files that we need to return to DB2?

    Thanks


    ------------------------------
    pulapura
    ------------------------------



  • 14.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Wed December 14, 2022 09:57 AM
    Hi.  We have various types of queries that we may make to find out what is all in the archives and what is available to us for recovery.  Some of these queries can be:

    - find the highest log chain
    - find the highest log file for a given log chain
    - find me the highest log chain containing a specific file number
    - find me an exact log file from an exact log chain

    All what we expect vendors to do is match the object(s) to the criteria specified in the API struct we pass in.  We deal with what comes back based on the context it is being called for.

    Thanks.

    ------------------------------
    Michael Roecken
    ------------------------------



  • 15.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Thu December 15, 2022 11:45 AM
    Thank you @Michael Roecken for the clarifications. Really appreciate you answering all my questions patiently.

    Thanks​

    ------------------------------
    pulapura
    ------------------------------



  • 16.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Thu December 15, 2022 03:53 PM
    No problem.  Let me know if anything else comes up.

    Thanks.

    ------------------------------
    Michael Roecken
    ------------------------------



  • 17.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Tue December 20, 2022 10:42 PM
    Edited by System Fri January 20, 2023 04:46 PM
    > We have various types of queries that we may make to find out what is all in the archives and what is available to us for recovery.  Some of these queries can be:

    - find the highest log chain
    - find the highest log file for a given log chain
    - find me the highest log chain containing a specific file number
    - find me an exact log file from an exact log chain

    Hi @Michael Roecken, had one more clarification:
    - From the above statement, it looks like Db2 may be looking for a specific file to proceed, in the cases above, essentially the latest file in a chain or the latest chain. However, I believe as you mentioned earlier, we should not make any assumptions and simply match the criteria and return one or more files as responses (even though we know that it may be looking for the highest).

    This brings me back to the pattern C0000001_S???????.LOG. A chain can have potentially a million log files. In case we run into a scenario where we receive the above query and the chain has a million files, I am not sure if it would make sense to return all the files in the chain. So, my question is two fold:
    1) Can we make any base assumptions here, that is, assume we should be returning the highest/lowest log file in the chain
    2) Or, do we understand that the scenario I mentioned is not practical and we should just match the criteria and send all files in the chain (and assume that this would be a small number under practical scenarios)

    P.S: I only see this kind of pattern come in when I really don't have any more files to serve to DB2, that is, I have given Db2 all relevant files from previous chains using the C??????_S00000X.LOG pattern and the chain being requested does not exist. So, am curious in what scenarios this would be a valid pattern to respond to.

    Thanks




    ------------------------------
    pulapura
    ------------------------------



  • 18.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Wed December 21, 2022 11:00 AM
    > I believe as you mentioned earlier, we should not make any assumptions and simply match the criteria and return one or more files as responses (even though we know that it may be looking for the highest).

    This is correct and answers your point (1).  Match all objects to the criteria and be prepared to give us one of them on GetNextObj call until there are no more to give or we close the scan.

    To answer your (2), at this current moment the current scenario is find me the highest log chain that has a log file in it.  But, that can always change with every new deliverable we release.

    For any wildcard search, lowest to highest is probably always the best default to use.

    Thanks.


    ------------------------------
    Michael Roecken
    ------------------------------



  • 19.  RE: Usage of search functionality using SQLUV_QUERY_LOGS for vendor side implementation of backup / restore

    Posted Wed January 11, 2023 10:56 PM
    Ah, missed your response through the holidays. Thank you for your response and wish you a very Happy New Year.

    Thanks

    ------------------------------
    pulapura
    ------------------------------