Db2 (On Premises and Cloud)

Expand all | Collapse all

Problem to access a databases after migration to 11.5

  • 1.  Problem to access a databases after migration to 11.5

    Posted Mon March 16, 2020 04:25 PM
    Hi,

    I migrated to 11.5, but when I connect to the databases, it says:
    $ db2 connect to MYDB
    SQL5035N The connection failed because the database product has been updated
    and the database needs to be upgraded SQLSTATE=55001

    When I perform the upgrade:
    $ db2 upgrade db MYDB
    SQL1704N Database upgrade failed. Reason code "9".

    When I see the db2diag.log I got:
    2020-03-16-10.40.22.886051-300 I231717E678 LEVEL: Error
    PID : 10098 TID : 139989975295744 PROC : db2sysc 0
    INSTANCE: mydb NODE : 000 DB : MYDB
    APPHDL : 0-34 APPID: *LOCAL.db2inst1.200316154021
    AUTHID : RAI HOSTNAME: db2server
    EDUID : 23 EDUNAME: db2agent (MYDB) 0
    FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:2318
    MESSAGE : ZRC=0x80020435=-2147351499=SQLB_MIG_NOT_ALLOWED_BY_TSP_STATE
    "Database migration is not allowed due to tablespace state."
    DATA #1 : <preformatted>
    Tablespace TS_4K_TMP_SYS (ID 1) has state 4000, migration is not allowed.

    That tablespace is a system temporary one, and it seems it is offline.

    In the ./SQL00001/SQLSPCS.1 file, I got the location of that TS, an it is:
    $ strings ./SQL00001/SQLSPCS.1
    SYSCATSPACE
    e]SV
    /db2/datos/mydb/mydb/mydb/NODE0000/MYDB/T0000000/C0000000.CAT
    TS_4K_TMP_SYS
    /db2/temp/mydb/mydb/TS_4K_TMP_SYS
    USERSPACE1
    1f]SV
    ...
    And that directory exists:
    $ ls -la /db2/temp/mydb/mydb/TS_4K_TMP_SYS
    total 4
    drwx------. 2 rai backups 24 Sep 6 2019 .
    drwxr-x---. 11 rai backups 221 Sep 6 2019 ..
    -rwx------. 1 rai backups 512 Sep 6 2019 SQLTAG.NAM

    I need to access the database, or at least the data, but I cannot connect, migrate o dump via db2dart.

    What can I do?

    ------------------------------
    Andres Gomez Casanova
    ------------------------------


  • 2.  RE: Problem to access a databases after migration to 11.5

    Posted Mon March 16, 2020 05:29 PM
    SQL1704N with Reason code 9 --> "Table space access is not allowed".


    --Sunit

    ------------------------------
    Sunit
    ------------------------------



  • 3.  RE: Problem to access a databases after migration to 11.5

    Posted Mon March 16, 2020 06:34 PM
    Hi Andres,
    If the status of the TS_4K_TMP_SYS tablespace on the instance before the upgrade was not offline, it might be better to contact IBM for technical support.
    There is a similar APAR in version 9.7, but it may not help because it is an older version.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: Problem to access a databases after migration to 11.5

    Posted Tue March 17, 2020 09:43 AM
    Hi,
    If possible can you revert your instance to older release. If it is connecting in older release you could further analyse what could be the problem with tablespace or create another system temporary tablespace and proceed with upgrade. If a db has only one temporary tablespace and if it is not online then db might prevent connection. So falling back to earlier release with the hopes of connecting to db can be helpful. I cannot guarantee but a humble suggestion.
    Thanks.
    Harish P

    ------------------------------
    Harishkumar Pathangay
    ------------------------------



  • 5.  RE: Problem to access a databases after migration to 11.5

    Posted Thu March 19, 2020 02:21 AM
    hi,
    it could be file permission issue as well.
    https://www.ibm.com/support/pages/db2-v10x-file-permission-issue-causing-sql5035n-when-connecting-database
    thanks,
    harish pathangay


    ------------------------------
    Harishkumar Pathangay
    ------------------------------



  • 6.  RE: Problem to access a databases after migration to 11.5

    Posted Thu March 19, 2020 05:00 PM
    Hi,

    Well, as you said Harish and SangGyu, I had to contact the IBM support and the problem was solved. They gave me a new set of SPCS files, I just replaced them and the database was online again.

    The source of the problem was that when I performed the db2 upgrade command, there was at the same time a difference between the real and effective GID of the instance user, generating a problem in the files including the tablespaces. This difference was due to Centrify that was misconfigured. When we did a ls -l filew looked file (all files belonged to the primary group of the instance), but when performing a ls -ln, the real GID was shown, and files belonged to two different GID.

    We just synchronized the GID with Centrfiy and /etc/passwd, assign or change the GID to the corresponding group (primary group of the instance user) and that was all.

    ------------------------------
    Andres Gomez Casanova AngocA
    DBA senior
    Experian
    Bogota
    3166214032
    ------------------------------



  • 7.  RE: Problem to access a databases after migration to 11.5

    Posted Fri March 20, 2020 03:39 AM
    Hi Andres , 
    all is well that ends well.  Good to hear your problem was solved !

    Scary that things like this can happen at all, with some configuration mismatch in Db2 instance GID (or UID) and a 'centralised' Identity Access Management software solution to integrate Unix/Linux servers with Active Directory infrastructure .... 
    Must say, I had never heard of Centrify.  ( see Centrify.com ). 
    Looks impressive what they can do with their "Centrify Zero Trust Privilege"  (or even the free available "Centrify Express for Linux ").  I like their slogan "Go Beyond Legacy Privileged Access Management PAM , Think Zero Trust Privilege"

    So far I only have some experience with RedHat 's Identity Management (IdM) solution with PAM  / SSSD, integrating a Linux domain with a centralised (Microsoft) Active Directory domain.  ( see docu RedHat Idm ). It does works, also with Db2 on Linux in a "idm" subdomain , but so far there are unsolved nasty issues with cached user's group memberships and cached passwords. Because of this, and other robustness reasons, it's best to have Db2 instance owner (and group) to be local user/group and not a domain user. All other users / groups accessing Db2 (remotely), can be domain users / groups ; taking into account the nasty caching issue ....


    ------------------------------
    Erwin Hattingh
    Systems Engineer / Db2 DBA
    Triodos Bank
    ------------------------------