Author Topic: How to make fallback from upgrade?  (Read 783 times)

rjchavez

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: How to make fallback from upgrade?
« Reply #15 on: February 10, 2017, 09:00:14 AM »
Hi
Interesting Reading.. but I've challanged Greg for many years do put a fallback funcionality into the Product.

Because today it's a mess.
I've been Reading all replies, but I'm missing one thing(which may have been there but I haven't seen it).
If you upgrade OD to a new version and doing all the arsdb-stuff fine,
as soon as you do one load of an Appl Group... you're toast!

If you 'back-out' your updates in the ARS-tables... you will end up with a missing load in ARSLOAD-table .
And one index-table, that's not consistent with the other ARS-tables, because you've loaded data into the Appl Group.
And since you probably load the data into OAM.
There will be data there that are non reacheable from Ondemand, because the Resource used on the load is not registered in ARS-table and so on.

/H Carlberg

That's absolutely right!  We use OAM to store the objects so there would be a total De-synchronization between CMOD and OAM unless you backup and restore OAM objects as well (what a mess).  I think the moral of the story is:  Do not attempt a fallback or backout.  If there's a problem after migration, work through it using PMR's, DBA's, etc.  Make sure that your management (and customers) know the ramifications of falling back.

Greg Ira

  • Full Member
  • ***
  • Posts: 156
    • View Profile
Re: How to make fallback from upgrade?
« Reply #16 on: February 10, 2017, 11:48:49 AM »
Sorry I missed much of this discussion.  The process I listed was meant more for an immediate backout.  Anything loaded after the initial migration would be lost after falling back  In our one of our cases it was dummy data for test loads as part of the verification process which we deemed expendable.  If you started loading production data you're in a different situation and the process I listed less optimal.   We actually ran into this situation with another customer and the fallback was much more difficult, though not impossible.   In a very basic summary: We had to identify all loads processed after the migration which, fortunately, wasn't an overwhelming number.  Did a DB2 unload of the ARSRES table to help identify any new resources loaded during that period.  Ran our fallback procedure which reverts all system tables to pre migration status but doesn't touch the index data.  We then had to increment the LOAD_ID and RESGRP manually for each Application group that had been loaded in to cover the loads made after migration.  Then we added the new entries to ARSRES using the ARSRES unload we did earlier.  Since everything we do loads through OAM nothing is set to expire by Load and ARSLOAD isn't an issue.

rjchavez

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: How to make fallback from upgrade?
« Reply #17 on: February 10, 2017, 12:30:00 PM »
Sorry I missed much of this discussion.  The process I listed was meant more for an immediate backout.  Anything loaded after the initial migration would be lost after falling back  In our one of our cases it was dummy data for test loads as part of the verification process which we deemed expendable.  If you started loading production data you're in a different situation and the process I listed less optimal.   We actually ran into this situation with another customer and the fallback was much more difficult, though not impossible.   In a very basic summary: We had to identify all loads processed after the migration which, fortunately, wasn't an overwhelming number.  Did a DB2 unload of the ARSRES table to help identify any new resources loaded during that period.  Ran our fallback procedure which reverts all system tables to pre migration status but doesn't touch the index data.  We then had to increment the LOAD_ID and RESGRP manually for each Application group that had been loaded in to cover the loads made after migration.  Then we added the new entries to ARSRES using the ARSRES unload we did earlier.  Since everything we do loads through OAM nothing is set to expire by Load and ARSLOAD isn't an issue.
Greg, unfortunately not ALL tables are DROPPED and recreated as I found out during fallback.  The System Log tables (SLxx) did not get reverted to the previous release and this the -117 after backout.  I've been testing a DB2 procedure which would allow me to DROP and CREATE these tables with their previous number of columns.  Using the arsdb process to export/drop/recreate/import these tables does not work when falling back from 9.5.0.7 to 8.5.0.6

rjchavez

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: How to make fallback from upgrade?
« Reply #18 on: February 10, 2017, 12:53:11 PM »
In terms of identifying loads that have been successful after the "migration point-in-time", I have a query I run to identify them.  You'll have to update the SLxx table to be queried and the DATE/TIME in the query:

-- Version 9.5.0.7 and above only
-- Extract information from the CMOD System Log for all LOAD activity past
-- a certain date/time.  Can be used to determine any LOADS performed after
-- an upgrade of CMOD for fallback/backout purposes
--
-- Create TEMP1 containing the APPLICATION, APPLICATION_GROUP, and File(...) variable which
-- will be used to extract the JOB and DDNAME into the TEMP2 temporary table
WITH
  TEMP1 AS
     (SELECT
     DATE(TIME_STAMP_DT-5 HOURS) AS DATE, TIME(TIME_STAMP_DT)-5 HOURS AS TIME, SUBSTR(MSG_TEXT, LOCATE_IN_STRING(MSG_TEXT, 'File(', 1, 1, OCTETS) + 5 -- Start of File(...)value
        , (LOCATE_IN_STRING(MSG_TEXT, 'InputSize(', 1, 1, OCTETS) - 2) - (LOCATE_IN_STRING(MSG_TEXT, 'File(', 1, 1, OCTETS) + 5)) AS FILEVAL,
          SUBSTR(MSG_TEXT, LOCATE_IN_STRING(MSG_TEXT, 'Appl(', 1, 1, OCTETS) + 5 -- Start of App(...)value
        , (LOCATE_IN_STRING(MSG_TEXT, 'InputFileSize(', 1, 1, OCTETS) - 2) - (LOCATE_IN_STRING(MSG_TEXT, 'Appl(', 1, 1, OCTETS) + 5)) AS APP,
          SUBSTR(MSG_TEXT, LOCATE_IN_STRING(MSG_TEXT, 'Name(', 1, 1, OCTETS) + 5 -- Start of Name(...)value
        , (LOCATE_IN_STRING(MSG_TEXT, 'LoadId(', 1, 1, OCTETS) - 2) - (LOCATE_IN_STRING(MSG_TEXT, 'Name(', 1, 1, OCTETS) + 5)) AS APP_GRP
        --                                       
        FROM ARSSRVER.SL32 -- <-- Change
        --                                 
        WHERE (MSG_NUM = 87) -- Only include successful loads                                           
         AND DATE(TIME_STAMP_DT-5 HOURS) >= '02/02/2017'    -- Included date(s)
            AND (TIME(TIME_STAMP_DT-5 HOURS) >= '15:26:00'    -- From TIME
         AND TIME(TIME_STAMP_DT-5 HOURS) <= '23:35:00')    -- To TIME                                                                  
            AND SUBSTR(MSG_TEXT                      -- Exclude the JCL% applications
        , POSSTR(MSG_TEXT, 'Name(') + 5, ((POSSTR(MSG_TEXT, ') LoadId(')) - (POSSTR(MSG_TEXT, 'Name(') + 5))) NOT LIKE 'JCL%')
        -- Create TEMP2 by extracting the JOB and DDNAME from the File(...) variable in the lob
,
  TEMP2 AS
     (SELECT DATE, TIME, SUBSTR(FILEVAL, LOCATE_IN_STRING(FILEVAL, '.', 1, 1) + 1, (LOCATE_IN_STRING(FILEVAL, '.', 1, 2) - 1) - (LOCATE_IN_STRING(FILEVAL, '.', 1, 1))) AS JOB,
          SUBSTR(FILEVAL, LOCATE_IN_STRING(FILEVAL, '.', 1, 2) + 1, (LOCATE_IN_STRING(FILEVAL, '.', 1, 3) - 1) - (LOCATE_IN_STRING(FILEVAL, '.', 1, 2))) AS DDNAME,
          APP, APP_GRP
        FROM TEMP1)
SELECT *
  FROM TEMP2

Greg Ira

  • Full Member
  • ***
  • Posts: 156
    • View Profile
Re: How to make fallback from upgrade?
« Reply #19 on: February 10, 2017, 01:07:00 PM »
Thanks.  That could be handy in a number of occasions.
One thing I find interesting is we never ran into the SLxx issue.  Our existing SLxx tables never got modified with new column, only new instance created under 9.5 have the extra SLxx column.

rjchavez

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: How to make fallback from upgrade?
« Reply #20 on: February 10, 2017, 01:50:16 PM »
The new column seems to have appears after the arssyscr commands which alter the System Log tables.  Here's a before and after snapshot of the SLxx tables on our system where the number of columns goes from 18 to 19.  It appears that the new column (TIME_STAMP_DT) is replacing the old and "proprietary" column TIME_STAMP.  No longer do we need to compute the time (only the GMT difference):

Before:
Name               Schema   T DB Name  TS Name    Cols        Rows Chks C
*                  *        * *        *             *           *    * *
------------------ -------- - -------- -------- ------ ----------- ---- -
SL29               ARSSRVER T ARSDBASE SL29         18    20000013    0 
SL30               ARSSRVER T ARSDBASE SL30         18    20000011    0 
SL31               ARSSRVER T ARSDBASE SL31         18     2850767    0 


After:
Name               Schema   T DB Name  TS Name    Cols        Rows Chks C
*                  *        * *        *             *           *    * *
------------------ -------- - -------- -------- ------ ----------- ---- -
SL29               ARSSRVER T ARSDBASE SL29         19    20000013    0   
SL30               ARSSRVER T ARSDBASE SL30         19    20000011    0   
SL31               ARSSRVER T ARSDBASE SL31         19     2850767    0   


I'm going to try and DROP/RECREATE and LOAD the SLxx tables without the new column as part of my fallback.  The only concern is:  Are there any other tables which will exhibit the same INSERT issue?  We'll find out.



ewirtz

  • Full Member
  • ***
  • Posts: 133
    • View Profile
Re: How to make fallback from upgrade?
« Reply #21 on: February 13, 2017, 03:00:36 AM »
Hi,
you will not find a general solution for a fallback, if loads are done and you want to preserve the loads that have been done. To avoid these problems we are doing the loads again after the fallback. After the final migration the unnecessary loads are deleted.

regards

Egon

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 1268
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: How to make fallback from upgrade?
« Reply #22 on: February 13, 2017, 04:22:17 AM »
One thing I find interesting is we never ran into the SLxx issue.  Our existing SLxx tables never got modified with new column, only new instance created under 9.5 have the extra SLxx column.

This is because of the switch from the old internal date formats (number of seconds since January 1st, 1970, UTC) to the DB2 date format.  The new date field is added, and the old date field is left unused and empty.

-JD.
#Install, #Educate, #Repair, #Upgrade, #Migrate, #Enhance, #Optimize.

Call:  +1-866-533-7742
Click:  http://JustinDerrick.com/
eMail:  jd@justinderrick.com
Wiki:  http://CMOD.wiki/

Interests:  #AIX6 #AIX7 #Linux #Multiplatforms #DB2v9 #DB2v10 #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #Training

Greg Ira

  • Full Member
  • ***
  • Posts: 156
    • View Profile
Re: How to make fallback from upgrade?
« Reply #23 on: February 13, 2017, 09:42:45 AM »
Thanks, I was aware of the new format for the SLxx tables using the DB2 date format.  We just never saw the requirement to run arssyscr in the instructions to upgrade from 8.5 to 9.5 so we never ran it.

rjchavez

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: How to make fallback from upgrade?
« Reply #24 on: March 08, 2017, 09:00:24 AM »
I wanted to circle back and let everyone know how we "sort of" simplified the fallback to the previous (8.5) release from 9.5.  As you know, going to a new release "may" and "will" change the structure of tables, columns, tablespaces, etc.  The only clean way to return to the old release would be to return to the same database structure. Now that we have tested this process and it works well but, you will need DBA (and tools) help.  Note also that I'm only posting on the DB2 aspect of the fallback and not the entire fallback.

We start off by using IBM's DB2 Tools to "migrate" the current database (ARSDBASE) to a new database (XRSDBASE).  The tool extracts the DDL from the current database and unloads all tablespaces and tables as well.  It will then create a new database (XRSDBASE) using the DDL and LOAD all tables.  This essentially is creating a mirror copy of the ARSDBASE "prior" to migrating to the new release.

Once the copy of the database has been performed, we then perform the migration of OnDemand to the new release.

To fallback to the previous release/database you have 2 choices: 1: point OnDemand to the migrated database XRSDBASE or 2: drop and recreate the ARSDBASE using the reverse process of copying from XRSDBASE.

We chose option 1 by configuring OnDemand to use a new (XRSDBASE) database which in turn returned it to the 8.5 release.  Here are the high-level steps we followed:

1. Shutdown CMOD
2. Restore the OnDemand PDS's to the previous release
3. Mount the previous release zFS/HFS on its production mountpoint
4. Backout any PROCLIB changes made
5. Recompile and BIND any exits and/or in-house written programs
6. Reload any dynamic exits (SETPROG EXIT)
7. Reconfigure OnDemand to use new database
8. Restart CMOD and test

As I mentioned, we have performed this migration and fallback scenario and it has worked successfully for us. 

Then there's the issue of missing loads which you will have to work around.

Hope this helps.
« Last Edit: March 08, 2017, 09:06:25 AM by rjchavez »

ewirtz

  • Full Member
  • ***
  • Posts: 133
    • View Profile
Re: How to make fallback from upgrade?
« Reply #25 on: March 29, 2017, 12:49:57 AM »
Hi Hakan,
if you know the loadids from the buggy migration , you can do an unload after the final successful migration. This works fine and we have tested it. CMOD need not to find the loadids in the system log. If you have all reports that have been loaded by the buggy migration, you can load them again. We have worked with a one week window to decide whether we go back or not

Of course Ondemand could / should provide this or another technique as a general solution.

regards

Egon


hakan_carlberg

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Re: How to make fallback from upgrade?
« Reply #26 on: March 29, 2017, 09:31:27 PM »
Hi Egon

Yes, in a way you can do that.
But we have some reports that are loaded from z/OS(with input files from GDG's), but many are also loaded from windows directly into a z/OS OnDemand Server.
And trying to keep in sync with all the files that are loaded from different platforms, I wouldn't want to do that.
Also some of the files, loaded from z/OS, are loaded a couple of times a day. So the GDG-limit might only be 10, and that is being rolled a couple of times per week.
Setting the environment to keep track of all loads... that's a mess, and you're toast if you miss one, it might be the 'Log' from the internet banking.

And since some of the Reports are viewable from Customers... if you do an unload and trying to reload them... what if the Customer are looking for the report and it's not there? But it was there a couple of minutes ago!!  Nightmare

So... It must be in the product. Wrong... it should !!! be in the product, the possibility to upgrade/downgrade and still be in sync

At least, that's my opinion. Only look at DB2, where you can upgrade and do a fallback without 'any?' problem, at least it's possible

Regards
/Hakan Carlberg

ewirtz

  • Full Member
  • ***
  • Posts: 133
    • View Profile
Re: How to make fallback from upgrade?
« Reply #27 on: March 29, 2017, 11:22:57 PM »
Hi Hakan,
it's my opinon as well, the IBM should provide the solution. To have no dependency between different versions, I think a solution must track the loads to be able to replay the loads after a fallback. Otherwise you have to look for the details of the changes between version 1 and version 2 which is not relevant for this approach.

By the way if you switch to new functions mode after a DB2 migration you cannot do a fallback afterwards. Also with DB2 you have a point of no return.

regards

Egon

Marthin

  • Jr. Member
  • **
  • Posts: 52
    • View Profile
Re: How to make fallback from upgrade?
« Reply #28 on: March 30, 2017, 12:31:31 AM »
Thank you all for the many tips, tricks and suggestions to this topic.
I can see that not only for me, fallback plans are a big issue when considering upgrading OnDemand.

The bottom line is:
IBM, please add a fallback feature to OnDemand ASAP, so all these good people donít have to invent their own.

scottnys

  • Newbie
  • *
  • Posts: 7
    • View Profile
Re: How to make fallback from upgrade?
« Reply #29 on: April 13, 2017, 11:26:16 AM »
/H Carlberg, you are totally right. :(
After there have been loads in the upgrade, the individual "next LOAD_ID"s (in the ARSAG) have been incremented.  If you fall back, then they are being re-set to before the migration.  That is bad!!!
To enhance the fall back procedure is to have an inventory of how many applications had loads to it and preserve the LOAD_ID of where it got to (how many loads).  When you fall back to before the upgrade, update the restored LOAD_IDs  to the preserved values (or greater).  That way, when you start loading at the restored version, the loads won't be Overlaying/replacing loads that were done at the upgraded version.
Resources weren't a problem, providing there were no changes to them during the migration.

This was a painful lesson.  We used the image copies to get back the OAM objects for the reports.  We Unloaded from the IMAGECOPY the documents that were overlaid.  We then went thru each appl grp that had loads, manually altered the "load_id" so there were no overlaps and LOAD RESUMEed them back into the tables - not pretty for sure.