Author Topic: Recommended DB2 on z/OS Service, V12  (Read 6570 times)

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Recommended DB2 on z/OS Service, V12
« on: January 08, 2018, 02:42:53 PM »
ABEND04E RC00E70005 AT DSNXRRID M107 WHEN SORT PUTS RIDS IN WORKFILE AND RLF TIME LIMIT IS EXCEEDED   

http://www-01.ibm.com/support/docview.wss?uid=swg1PI82133 

APAR was exposed by CMOD. 

Ed             
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
DB2 ABEND 00C90101
« Reply #1 on: March 23, 2018, 12:55:21 PM »
DB2 ABEND 00C90101

From DB2 Level 2 description:

Quote
This is a known problem to us.   It has to do with synchronization of a
shadow copy of a DBET (Database Exception Table - Internal to DB2 )     
entry with its master copy in the SCA.  There's small timing window   
where an object,  a NPI has grown a new piece,  but this info hasn't   
been sync'd with the member shadow copies.  This discrepancy causes the
sanity check to kick off which subsequently causes the abend.           
                                                                       
In this case it was for UNIQUE INDEX xxxxxxxxxxxxxx built on table     
yyyyyyyyyyyyyy which is in a PBG tablespace with MAXPARTS 1000.     
                                                                       
This abend is from PI58369 / UI44629 which is marked PE.   We recommend
that you remove this ptf to circumvent this abend.  If not,  you can   
apply the Fixing APAR PI87946 / UI51196.           

UI51194 for DB2 V12
#zOS #ODF

Nolan

  • Full Member
  • ***
  • Posts: 152
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #2 on: May 16, 2018, 10:35:32 AM »
Thanks, any other requirements for DB2 V12 with V9.5 of CMOD?
J.

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

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #3 on: May 16, 2018, 01:27:23 PM »
Thanks, any other requirements for DB2 V12 with V9.5 of CMOD?

None that we've found.  CMOD itself doesn't exploit any new DB2 function. 

Ed
« Last Edit: May 16, 2018, 01:29:09 PM by Ed_Arnold »
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #4 on: August 08, 2018, 11:23:05 AM »
Symptom:

After rolling out service to Db2 products, ARSSOCKD starts, hangs, and abends S0C4.

The successful startup message...

   ARS0351I The ARSSOCKD instance ARCH1010 is ready

...is never produced.
___________________________________________________________

Looking at the dump:

PSW=47042001 80000000 00000000 11769074                           
    (Running in PRIMARY, key 0, AMODE 64, DAT ON, SUPERVISOR STATE)
    Enabled for PER I/O EXT MCH                                   
Unable to identify the area at ASID(X'00A3') address 11769074.     
  ASCB166 at F96400, JOB(ARSTECH1), for the home ASID             
  ASXB166 at 9FD000 and TCB166V at 9C62E8 for the home ASID       
  HOME ASID: 00A6 PRIMARY ASID: 00A3 SECONDARY ASID: 00A6         
                                                                   
  General purpose register values                                 
     0-1  00000000_18616C48  00000000_129FE000                     
     2-3  0000030E_00000000  00000000_12A1D000                     
     4-5  00000000_1245E380  00000000_129FE000                     
     6-7  00000000_11DF1CA0  0000030E_04AC1D08                     
     8-9  00000200_40500240  0000030E_04AC1D08                     
    10-11 00000000_00000008  0000030E_04AE5630                     
    12-13 00000000_1A0D7EE8  00000000_7E655800                     
    14-15 0000030E_04AC2488  00000000_11769FF4                     


LIST 11769074. ASID(X'00A3') POSITION(X'-04') LENGTH(X'08') INSTRUCTION 
11769070 | 50F0 A000      | ST      R15,X'0'(,R10)                       
11769074 | 5050 A004      | ST      R5,X'4'(,R10)                       


R10 is the base.   Looking back up we see (the psw is this 0x1074)

11768000   A7F40055   00000000   12A1D000   C3D8C3D4   | x4.......~}.CQCM |
11768010   C5E2C2F1   F1F1F040   4040F0F1   61F2F261   | ESB1110   01/22/ |
11768020   F1F8F1F5   4BF4F440   C8C3D8C3   F1F1F040   | 1815.44 HCQC110  |
11768030   D6E2A9F1   4BF94000   D7C9F9F0   F8F5F740   | OSz1.9 .PI90857  |
11768040   E2D440C3   D8C3F0F0   F4F65C40   F5F6F5F5   | SM CQC0046* 5655 |
11768050   60E5F4F2   40B440D9   D6C3D2C5   E340E2D6   | -V42 . ROCKET SO |
11768060   C6E3E6C1   D9C56B40   C9D5C34B   40F1F9F9   | FTWARE, INC. 199 |
11768070   F96B40F2   F0F1F840   C1939340   D9898788   | 9, 2018 All Righ |
11768080   A3A240D9   85A28599   A585844B   40000000   | ts Reserved. ... |
11768090   40404040   40404040   C0F403D1   0A8F0700   |         {4.J.... |


Likely candidates for this problem are PI97869 or maybe PI97867.

But it's not a CMOD problem - contact Db2 support.

Ed

#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #5 on: August 09, 2018, 09:20:19 AM »
Just FYI - here's the level of Db2 V12 we're currently running:

DSNG007I  -DSNC DB2 CATALOG LEVEL (V12R1M500 ) 
        CODE LEVEL (V12R1M502 )                       
        CURRENT FUNCTION LEVEL (V12R1M501 )           
        HIGHEST ACTIVATED FUNCTION LEVEL (V12R1M501 )
DSNR002I  -DSNC RESTART COMPLETED
       

Ed             
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #6 on: January 10, 2019, 12:11:55 PM »
Just noting that there's nothing new under the sun, haven't exposed any Db2 defects lately.

Still at:

DSNG007I  -DSNC DB2 CATALOG LEVEL (V12R1M500 )   
        CODE LEVEL (V12R1M502 )                       
        CURRENT FUNCTION LEVEL (V12R1M501 )           
        HIGHEST ACTIVATED FUNCTION LEVEL (V12R1M501 )
DSNR002I  -DSNC RESTART COMPLETED   


Ed                 
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #7 on: February 19, 2020, 02:30:47 PM »
It's now 2020 and still no new Db2 defects have been exposed.

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #8 on: February 24, 2020, 11:38:50 AM »
Had a report during an upgrade to Db2 V12:

Quote
... in the ARSSOC startup : ARS0013E ARSSOCKD DB Error: Warning: Unexpected sql_rc -- SQLSTATE=   
Not Defined, SQLCODE=-805, File=arssys.c, Line=935                     

Note the -805, bind issue.

I t turns out there was a variation of DSNACLI that was put in by IBM CMOD Services team when they implemented.  It includes CMODPROD_MIGTOOLS and CMODPROD_USEREXIT, CMODTEST_MIGTOOLS and CMODTEST_USEREXIT in the package list.

Their DSNACLI variation was called ARSACLI.

Once that was rebound all was well.

Please note that this could also occur if you have an ODSCRT table in your system, which is also from the IBM CMOD Services team.

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #9 on: April 05, 2020, 09:06:52 AM »
Symptom:  S0C1 in ARSSOCKD after upgrade of Db2 to V12

Also could show up as Fault Analyzer IDI0002I Abend U3000 along with a SA03                                                                                           


Resolution: Db2 ODBC APAR PH11103:

ODBC WORKAROUND TO RETAIN TRAILING PERIOD IN THE CHAR REPRESENTATION OF DECIMAL COLUMNS

PTF UI63117 resolved the issue.
« Last Edit: April 06, 2020, 08:35:26 AM by Ed_Arnold »
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Error exposed by doing a lot of loads during a migration
« Reply #10 on: December 10, 2020, 07:49:23 AM »
PH25530: ABEND04E RC00E70005 DSNXEDS1 M199 OCCURS ON PREPARE OF DYNAMIC SQL STATEMENT IN A VERY ACTIVE SYSTEM

https://www.ibm.com/support/pages/apar/PH25530

Error description

ABEND04E RC00E70005 DSNXEDS1 M199 occurs on PREPARE of dynamic SQL statement in a very active system.

Problem criteria:

Multiple threads race to PREPARE the same dynamic SQL statement concurrently. The winner of the race is inserted into the cache, but then is immediately removed, by EDM's LRU algorithm.

The loser of the race (first loser if there are multiple), will then get this abend.
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #11 on: February 01, 2021, 02:43:31 PM »
PH08646: INEFFICIENT MULTI-INDEX ACCESS PLAN CHOSEN WITH WORSE PERFORMANCE THAN A PLAN WITH SINGLE INDEX ACCESS

PTF UI63294
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V12
« Reply #12 on: February 07, 2021, 10:45:29 AM »
CMOD APAR PH34239

Db2 V12 introduced a change in behavior during runstats. 

Prior to Db2 V12, runstats always invalidated the cache. 

Starting with Db2 12, Db2 does not invalidate the cache unless you specify INVALIDATECACHE YES. 

Due to this change, the Db2 optimizer may not pick the same access path, leading to poor performance of CMOD queries.

In order to restore the prior runstats behavior, CMOD is being enhanced to specify INVALIDATECAHCHE YES when running Db2 12 or higher.
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
RUNSTATS Behavior Change with Db2 V12
« Reply #13 on: February 23, 2021, 11:02:33 AM »
As per my previous post default RUNSTATS behavior changed with Db2 V12.

This affects everything, not just CMOD.

I received the following on an issue that Db2 had over the weekend:

The dynamic statement cache for UPDATE was not invalidated during ALTER Table, so the invalid MSIB field for a varchar column caused SQL UPDATE to move large storage, which caused storage overlay and brought down Db2.

Customer can use RUNSTATS with INVALIDATECACHE on table space foo.foo to invalidate the statement cache and not to run ALTER statement to avoid additional abends.


Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: RUNSTATS Behavior Change with Db2 V12
« Reply #14 on: March 12, 2021, 12:27:57 PM »
Text from the PTF for PH34239:


PH34239 -                                                         
  CMOD IS ENHANCED TO SPECIFY INVALIDATECACHE YES WHEN RUNNING   
  DB2 12 OR HIGHER.                                               
  PROBLEM SUMMARY:                                               
  ****************************************************************
  * USERS AFFECTED: All Content Manager OnDemand for z/OS 10.1   *
  *                 and above customers                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 12 introduced a change in that      *
  *                      RUNSTATS no longer invalidates the DB2  *
  *                      statement cache.  This can cause        *
  *                      performance issues with CMOD            *
  *                      application group data tables.          *
  ****************************************************************
  * RECOMMENDATION: ARGMVSIE is changed so that if running on    *
  *                 DB2 12 or higher an INVALIDATECACHE YES      *
  *                 clause is added to the RUNSTATS command.     *
  *                 This will restore the pre-DB2 12 RUNSTATS    *
  *                 behavior.                                    *
  ****************************************************************
  DB2 12 introduced a change in that RUNSTATS no longer           
  invalidates the DB2 statement cache.  This can cause performance
  issues with CMOD application group data tables.                 
#zOS #ODF