Author Topic: Recommended DB2 on z/OS Service, V10 and V11  (Read 12251 times)

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Recommended DB2 on z/OS Service, V10 and V11
« on: November 21, 2014, 08:05:07 AM »
Under certain circumstances, CMOD will trigger a poor performance choice in DB2 for retrieval.

Note:  no abend, just a performance improvement.

This is fixed by PI20473:  INCORRECT FILTER FACTOR WHEN INDEX PROBING IS USED FOR A LIKE PREDICATE UNDER REOPT

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

Ed



#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #1 on: January 30, 2015, 09:01:33 AM »
Had a report of ARSEXOAM jobs failing after migration to DB2 V11.

Abend S04E-00E70005, LOC=DSNXGRDS.DSNXRDWG

Solution is PI31075

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #2 on: January 30, 2015, 02:02:17 PM »
Also for V11, I received a note about DECFLOAT numbers not being able to be displayed after a DB2 upgrade to V11.

We expect that problem to be fixed by PI28601.

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
-805's after service on both V10 and V11
« Reply #3 on: February 17, 2015, 08:45:45 AM »
We just went through a round of DB2 maintenance, and unfortunately missed a HOLD ACTION on one test system which left us stumped for a couple days as the HOLD ACTION only affected the arsdb command, nothing else that we could find CMOD-wise.

Unfortunately, I don't have the PTF number of the DB2 HOLD ACTION we missed.

However, what I do want to publicize is DB2 IVP program SDSNSAMP(DSNTIJRV) which exposes the problem perfectly.

SDSNSAMP(DSNTIJRV) is easy to run, just change a few dataset names and submit it.

Errors that we got look like this:

DSNT034I DSNTRVFY - AN ERROR WAS ENCOUNTERED DURING CALL TO ROUTINE,           
         ERROR =  DSNT408I SQLCODE = -805, ERROR:  DBRM OR PACKAGE NAME L2DB1X.DSNAUTON.DSNX7EXE.0E4C9F1F06F3F1F5 NOT FOUND IN PLAN
DSNTRVFY.           REASON 02  DSNT418I SQLSTATE   = 51002 SQLSTATE RETURN CODE  DSNT415I SQLERRP    = DSNXEPM SQL PROCEDURE DETECTI
NG ERROR  DSNT416I SQLERRD    = -251  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION  DSNT416I SQLERRD    = X'FFFFFF05'  X'00000000'  X'0
0000000'  X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION   


and

ERROR = DSNT531I SYSPROC.WLM_REFRESH RECEIVED SQLCODE   805- FROM STATEMENT 'SELECT USER, CURRENT SQLID FROM SYSIBM.SYSDUMMY1'

Recommendation:   Run SDSNSAMP(DSNTIJRV) right now to see how it behaves normally, and before and after DB2 maintenance.


Oh, and this what the arsdb export error looked like:



********************************* Top of Data **********************************
ARS4036I Exporting table ARSSERV3.arsann                                       
ARS0013E DB Error: {DB2 FOR OS/390}{ODBC DRIVER}{DSN11015}                     
 DSNT408I SQLCODE = -443, ERROR:  ROUTINE SQLCOLUMNS (SPECIFIC NAME SQLCOLUMNS)
          HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT -805             
          L2DB1A.DSNASPCC.DSNACOLU.0E4C9F1F07F1F2F2 D                           
 DSNT418I SQLSTATE   = 38112 SQLSTATE RETURN CODE                               
 DSNT415I SQLERRP    = DSNXRRTN SQL PROCEDURE DETECTING ERROR                   
 DSNT416I SQLERRD    = -818  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION         
 DSNT416I SQLERRD    = X'FFFFFCCE'  X'00000000'  X'00000000'  X'FFFFFFFF'       
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                   
  ERRLOC=5:10:2 -- SQLSTATE=38112, SQLCODE=-443, File=arsmvsie.c, Line=505     
ARS4084E Unable to export table arsann.  err=6                                 
arsdb:.ARS4037E Unable to export table ARSSERV3.arsann                         
******************************** Bottom of Data ********************************


Ed

« Last Edit: February 17, 2015, 02:34:49 PM by Ed_Arnold »
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
DB2 Problem That Bit One Of Our Users
« Reply #4 on: April 20, 2015, 11:23:54 AM »
The reported symptom was "ARSSOCKP Task Abend WIth S04E"

The resolving fix was PI13330

AN AEND04E RC00C90101 OCCUR AT DSNISRID ERQUAL5001 WHEN
RUNNING A SQL STATEMENT WITH HUGE DATA AND RIDLIST SORT


UI16375 and UI16376 are the PTFs for DB2 V10 and V11.
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #5 on: December 14, 2015, 08:12:47 AM »
Running arsdb, encountered a -750 DB2 message.

Resolution:   PM66561: ALT SQLCODEN750 WHEN TABLE HAS TRIGGER DEFINED

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #6 on: November 14, 2016, 10:26:43 AM »
ARSSOCKD SA03 Abends, EREP Messages, DB2 S04E 

EREP Message:

Quote
JOBNAME: ARSSOCKD   SYSTEM NAME: FQQX
ERRORID: SEQ=45311  CPU=4001  ASID=009D  TIME=10:55:21.2

SEARCH ARGUMENT ABSTRACT

  PIDS/5740XYR00 RIDS/DSNXGRDS#L RIDS/DSNXOGP AB/S004E PRCS/00E70005
REGS/0E00
  REGS/CB194 RIDS/DSNTFRCV#R

Problem was fixed by applying DB2 APAR PI47373

Quote
ABEND04E RC00E70005 IN DSNXOGP M103 WHEN TRYING TO PREPARING A
SQL STATEMENT WITH A COMPLEX VIEW
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #7 on: November 30, 2016, 02:54:09 PM »
ARSSOCKD abends with a S0C4, but it points to DB2 code.

PI72456 - ABEND0C4 RC10 at DSNOEMNU OFFSET 2CCA for statement accessing a LOB

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #8 on: December 26, 2017, 08:38:12 AM »
It's been over a year since I've found or heard of any issues with DB2 that affect CMOD.

Just adding this post to let people know that yes, I'm still keeping this updated.  There's just been nothing to update.

FYI - thus far, I'm not aware of any issues with DB2 V12.  I'll probably start a new thread if and when I need to on that subject.

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
V11 DB2 Abend brings down ARSSOCKD
« Reply #9 on: January 08, 2018, 02:37:16 PM »
And of course right after I say there's nothing new, there is something new:

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

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
DB2 ABEND 00C90101
« Reply #10 on: March 23, 2018, 12:52:07 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.           

#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #11 on: August 08, 2018, 11:22:19 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, V10 and V11
« Reply #12 on: January 12, 2019, 12:29:22 PM »
Letting everyone know this is still current.

Nothing new under the sun, haven't exposed any Db2 defects lately.

Ed
#zOS #ODF

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #13 on: March 30, 2020, 07:21:11 AM »
Db2 V10 is, of course, out of support.

Db2 V11 - nothing new to report.  Info in this thread is still good.

Ed
#zOS #ODF

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Recommended DB2 on z/OS Service, V10 and V11
« Reply #14 on: March 30, 2020, 01:18:55 PM »
Heh...  Even though there's no news to report...  It's still good news to have!  :D

-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