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

ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
How to make fallback from upgrade?
« on: October 13, 2016, 01:30:02 AM »
I know this has been discussed before, but I see the readme for OnDemand 9.5.0 still recommends backup of the OD database, knowing that this is not valid after running the upgrade steps.

The Readme says:

2) Run "arsdb -I <instance_name> -vd ARSAGPERMS ARSANN ARSAPP ARSAPPUSR ARSNAMEQ
ARSPRTOPTS ARSSYS ARSDFBDT" to drop the tables.

So the question is: would it be worth to consider renaming the tables, in stead of DROP tables ?

RENAME TABLE DB2T.ARSSYS TO ARSSYS_OLD
RENAME TABLE DB2T.ARSAPP TO ARSAPP_OLD
..

Then you would only have to rename the tables back if a fallback is necessary?

RENAME TABLE DB2T.ARSSYS_OLD TO ARSSYS
RENAME TABLE DB2T.ARSAPP_OLD TO ARSAPP
...

Then the OBID, PSID and DBID will not be out of sync.

Are there anyone out there who has tried a fallbabk after upgrade ?
« Last Edit: October 13, 2016, 03:47:00 AM by Marthin »

Greg Ira

  • Full Member
  • ***
  • Posts: 238
    • View Profile
Re: How to make fallback from upgrade?
« Reply #1 on: October 14, 2016, 10:42:57 AM »
Hi Marthin,
  We do our fallback a bit different.  We've run a restore this way probably about a dozen times with no real issues.
 We unload the cmod tables to a backup directory and if we need to fall back we drop the tables and recreate them with the previous version and loading from the backups.

This is the JCL we use to unload the pre upgrade tables:
//**************************************************************
//* This job is used to back up all CMOD tables (14P) AT 8.5.
//* this is done as part of the fallback process.
//**************************************************************
//**************************************************************
//*  Copy statement to UNLOAD CMOD tables prior to migration
//**************************************************************
//CPYLOAD  EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd95.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /SYSTEM/etc/ond/14P/backup/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vx
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run the command created in previous step
//**************************************************************
//RNLOAD  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd95.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*

The following JCL would fallback to 8.5 from 9.5:

//**************************************************************
//* This job is used to fall back a CMOD instance (14P) to 8.5.
//* This is accomplished in 3 steps:
//* 1) Deleting all system tables under the upgraded release.
//* 2) Re-Create all system tables.
//* 3) Restore the CMOD Table Data from unloaded tables.
//**************************************************************
//**
//**************************************************************
//*     Delete all CMOD tables with prompt of "Y" in STDIN.
//**************************************************************
//DELSYS  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT),
//       PARM='SH /usr/lpp/ars/V9R5M0/bin/arsdb -I ond14P -vd'
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/SYSTEM/etc/ond/responseyes.txt', <= Reply to DELETE
//          PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//**
//**************************************************************
//*    Copy statement to Create/Build CMOD System tables.
//**************************************************************
//CPYCRT   EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd85.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /etc/ond/14P/temp/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vrt -O
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run command created in previous step
//**************************************************************
//RNCREAT EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd85.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*
//**************************************************************
//*    Copy statement to Load from backups.
//**************************************************************
//CPYLOAD  EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd85.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /etc/ond/14P/backup/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vi
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run command created in previous step
//**************************************************************
//RNLOAD  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd85.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*

This has worked pretty well for us but I suggest running through it with a test instance a number of times to get comfortable with it.

Nolan

  • Global Moderator
  • Full Member
  • *****
  • Posts: 150
    • View Profile
Re: How to make fallback from upgrade?
« Reply #2 on: October 18, 2016, 06:17:12 PM »
<LIKE>   :)
J.

#zOS #AIX #Windows #Multiplatforms
#DB2 #TSM #ODF #zODF #ODWEK
#CapacityPlanning #AFP #ReportDistribution
#Finance #ICN

ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
Re: How to make fallback from upgrade?
« Reply #3 on: October 19, 2016, 02:46:06 AM »
Nice ! Something like this should have been included in the readme in the first place by IBM.
No doubt that it seems to work. But I don't hope you have needed it too many times  :D.

What is the reason to first copy the commands and then run them in next steps, instead of just run them ?

/Marthin

Greg Ira

  • Full Member
  • ***
  • Posts: 238
    • View Profile
Re: How to make fallback from upgrade?
« Reply #4 on: October 19, 2016, 05:37:59 AM »
Truthfully I can't remember the exact reason we created the command then ran them in a separate step.  I think we were running into issues when trying to run the various commands using the PARM='SH..' parameter of BPXBATCH so we just went the "easy" route.

scottnys

  • Jr. Member
  • **
  • Posts: 38
    • View Profile
Re: How to make fallback from upgrade?
« Reply #5 on: October 24, 2016, 08:08:12 AM »
Martin,
    An additional to Greg's process.  Using ARSDB previous version/Current version works for our fallback process nicely.  Smooth and clean.  We've tested it multiple times for fallback process to doing the actual upgrade.
    We did, however, get bit with an upgrade that "tooled along" for a couple of days.  Loads had happened (activity).  Realizing an unacceptable problem (with the Banner), we did our fallback process. The "next loadid" in the ARSAG table was incremented in the upgraded version.  When we did our fallback process, all of the "next loadids" that were incremented for those couple of days were restored to PRE-MIGRATION values.  This was BAD.  After the fallback was complete, duplicates happened for all of the Application Groups that were loaded for those couple of days.  Learning from our pain, we would have taken a copy of the all of the new SYSTEM tables, identifying the application groups that were loaded and incremented and incremented out backed up/previous version tables PRIOR to starting LOADs after the fallback.

    For a bit more clarification on what Greg posted for our fallback process.  Although you are seeing the 6-8 tables that are dropped, they are NOT the only ARS* tables that are effected by the upgrade.  There are ALTER statements that happen to other tables as well.  OBIDs are an issue and IMAGCOPYs don't provide much comfort for tables that are Dropped/Recreated.  Our process uses ARSDB for the whole process of Fallback.
    1)  ARSDB unload of ALL tables (run under current version of CMOD)
    2)  ARSDB upgrade using new version.  This will both Drop/recreate that list of tables as well as altering other Tables/indexes.
    3)  Fallback - ARSDB drop all tables using new version.  (run from V95 directory)
    4)  Fallback - ARSDB create all tables under current version. (run from v85 directory)
    5)  Fallback - ARSDB load all of the unloaded backed up data.  (run from v85 directory) CMOD will build all of the system tables the way the CURRENT version was before starting.  Also, ARSDB will load all of that data properly under the pre-migration format (DDL)
    6)  Fallback - ARSDB will rebuild all necessary indexes for the correct version as well.

ewirtz

  • Full Member
  • ***
  • Posts: 134
    • View Profile
Re: How to make fallback from upgrade?
« Reply #6 on: October 24, 2016, 11:47:19 PM »
Hi scottnys,
this is right. if you have new loads you have to do much more. If we do a migration p.e. from V1 to V2 we run a script that stores every file before it is loaded in Ondemand.
In case of a fallback the following has to be done after restoring the old database content and preventing the duplicates that you have mentioned by changing arsag.
- loading all files that have been loaded in V2
- deleting all loads that have been loaded with V2 before the fallback (In general this can only be done after the final migration to v2)

regards

Egon


ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
Re: How to make fallback from upgrade?
« Reply #7 on: November 04, 2016, 03:12:54 AM »
Thanks Scotnys and Egon
I am confident that only arsdb is doing the fallback. Especially to avoid the issues with OBID's.

Egon - do you have en example of the script that copies all loads before storing them?

/Marthin

ewirtz

  • Full Member
  • ***
  • Posts: 134
    • View Profile
Re: How to make fallback from upgrade?
« Reply #8 on: November 04, 2016, 04:31:25 AM »
Hi Marthin,

we are loading from spool (Z/OS) p.e. dest1. We change the load process from listening p.e for dest1 to dest12. We have a module that is listening for dest1. This module copies the file in the file system and at end it changes the dest1 to dest12.

regarding

Egon

rjchavez

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Re: How to make fallback from upgrade?
« Reply #9 on: February 02, 2017, 06:58:14 AM »
Hi Marthin,
  We do our fallback a bit different.  We've run a restore this way probably about a dozen times with no real issues.
 We unload the cmod tables to a backup directory and if we need to fall back we drop the tables and recreate them with the previous version and loading from the backups.

This is the JCL we use to unload the pre upgrade tables:
//**************************************************************
//* This job is used to back up all CMOD tables (14P) AT 8.5.
//* this is done as part of the fallback process.
//**************************************************************
//**************************************************************
//*  Copy statement to UNLOAD CMOD tables prior to migration
//**************************************************************
//CPYLOAD  EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd95.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /SYSTEM/etc/ond/14P/backup/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vx
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run the command created in previous step
//**************************************************************
//RNLOAD  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd95.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*

The following JCL would fallback to 8.5 from 9.5:

//**************************************************************
//* This job is used to fall back a CMOD instance (14P) to 8.5.
//* This is accomplished in 3 steps:
//* 1) Deleting all system tables under the upgraded release.
//* 2) Re-Create all system tables.
//* 3) Restore the CMOD Table Data from unloaded tables.
//**************************************************************
//**
//**************************************************************
//*     Delete all CMOD tables with prompt of "Y" in STDIN.
//**************************************************************
//DELSYS  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT),
//       PARM='SH /usr/lpp/ars/V9R5M0/bin/arsdb -I ond14P -vd'
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/SYSTEM/etc/ond/responseyes.txt', <= Reply to DELETE
//          PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//**
//**************************************************************
//*    Copy statement to Create/Build CMOD System tables.
//**************************************************************
//CPYCRT   EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd85.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /etc/ond/14P/temp/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vrt -O
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run command created in previous step
//**************************************************************
//RNCREAT EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd85.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*
//**************************************************************
//*    Copy statement to Load from backups.
//**************************************************************
//CPYLOAD  EXEC PGM=IKJEFT01,COND=(4,LT)
//OUTHFS   DD PATH='/etc/ond/14P/temp/upgd85.cmd',
//            PATHDISP=(KEEP,DELETE)
//INMVS    DD *
cd /etc/ond/14P/backup/
/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vi
/*
//SYSTSPRT DD SYSOUT=(,)
//SYSTSIN  DD *
OCOPY INDD(INMVS) OUTDD(OUTHFS) TEXT CONVERT(YES) PATHOPTS(USE)
/*
//*
//**************************************************************
//*    Run command created in previous step
//**************************************************************
//RNLOAD  EXEC PGM=BPXBATCH,REGION=0M,COND=(4,LT)
//STEPLIB DD DSN=ONDDODP.V10.SDSNEXIT,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOAD,DISP=SHR
//        DD DSN=ONDDODP.V10.SDSNLOD2,DISP=SHR
//STDIN  DD PATH='/etc/ond/14P/temp/upgd85.cmd',PATHOPTS=(ORDONLY)
//SYSOUT DD SYSOUT=(,)
//SYSERR DD SYSOUT=(,)
//STDOUT DD SYSOUT=(,)
//*

This has worked pretty well for us but I suggest running through it with a test instance a number of times to get comfortable with it.

Thanks Marthin!  This is a great script but, I think that the -O parameter in the CPYCRT step only creates the ODF tables and not ALL of the ARS* tables:

/usr/lpp/ars/V8R5M0/bin/arsdb -I ond14P -vrt -O

I added a second command without the -O in order to include ALL the ARS* tables.

rjchavez

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Re: How to make fallback from upgrade?
« Reply #10 on: February 03, 2017, 06:59:56 AM »
I attempted the fallback process and ran into an issue when CMOD tries to insert into the SLxx tables.  It receives an SQL error -117 indicating that the number of insert values in the value list of the insert operation is not the same as the number of object columns.  I think this is because these tables were not dropped/re-created for 8.5.  I ran the following during fallback:

/usr/lpp/ars/bin/arsdb -I ARCHIVE -vrt            * ARS create
/usr/lpp/ars/bin/arsdb -I ARCHIVE -vrt -O       * ODF create
/usr/lpp/ars/bin/arsdb -I ARCHIVE -vi             * IMPORT
/usr/lpp/ars/bin/arssyscr -I ARCHIVE -l           * System log
/usr/lpp/ars/bin/arssyscr -I ARCHIVE -a          * System load

Any suggestions?  How are the SLxx (System log tables) re-created?

rjchavez

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Re: How to make fallback from upgrade?
« Reply #11 on: February 03, 2017, 07:06:22 AM »
I attempted the fallback process and ran into an issue when CMOD tries to insert into the SLxx tables.  It receives an SQL error -117 indicating that the number of insert values in the value list of the insert operation is not the same as the number of object columns.  I think this is because these tables were not dropped/re-created for 8.5.  I ran the following during fallback:

/usr/lpp/ars/bin/arsdb -I ARCHIVE -vrt            * ARS create
/usr/lpp/ars/bin/arsdb -I ARCHIVE -vrt -O       * ODF create
/usr/lpp/ars/bin/arsdb -I ARCHIVE -vi             * IMPORT
/usr/lpp/ars/bin/arssyscr -I ARCHIVE -l           * System log
/usr/lpp/ars/bin/arssyscr -I ARCHIVE -a          * System load

Any suggestions?  How are the SLxx (System log tables) re-created?

There is a new COLUMN in the SLxx table names TIME_STAMP_DT so the table now has 19 columns.  The 8.5 INSERT being attempted has 18: INSERT INTO ARSSRVER.SL31 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

rjchavez

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Re: How to make fallback from upgrade?
« Reply #12 on: February 09, 2017, 05:48:33 AM »
I've been performing A LOT of testing reverting back from 8.5.0.6 to 9.5.0.7 and this procedure will NOT work in this  case.  Whenever a column is added to a DB2 table, and an INSERT with the VALUE clause is performed, the correct number of columns must be provided in the INSERT (such as in this case).  When you revert back to the previous release of CMOD (8.5.0.6) the INSERT is also reverted back to a lesser number of columns than the table actually has.  This results in the -117 error.  I did not find that any of the arsdb command nor the arssyscr commands DROP and reCREATE the SLxx tables.  As a result, the ONLY way this can be accomplished is by using DB2 tools such as UNLOAD and LOAD after performing your own DROP and CREATE of the tables.

If anyone has any other solution, I would welcome it.

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2138
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: How to make fallback from upgrade?
« Reply #13 on: February 09, 2017, 07:53:16 AM »
You'll probably break the System Log records written by the newer version of CMOD, but you should be able to do this:

Use arstblsp to close the SL table.  Rename your System Log Application Group to be something else like "System Log v9".

Then use arssyscr to create a new System Log App Group.

You should be able to start back up under v8.  But again, this may break CMOD in new and interesting ways.

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

hakan_carlberg

  • Jr. Member
  • **
  • Posts: 83
    • View Profile
Re: How to make fallback from upgrade?
« Reply #14 on: February 10, 2017, 08:54:24 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