Integrated Analytics System

Expand all | Collapse all

Backup with LDAP Admin user defined in admin group

  • 1.  Backup with LDAP Admin user defined in admin group

    Posted Wed March 20, 2019 12:29 PM
    Hello
    We are using LDAP authentication with Active Directory.  The customer decided to create three different user ids per environment, instead of having the same BLUADMIN for security reasons. But this brings the situation that backups can only be run as BLUADMIN user as per documentation. 
    I am able to run backups from the Console, but we need to run schema level backups, so I am trying to urn the backups from the container as db2inst1 user, but the regular db2 backup command is not working.
    Does anyone know how to run schema level backups from the container?
    For now, I just tried a full online as follows:

    [db2inst1@node0101-fab - Db2wh ~]$ db2 backup database bludb online to /scratch
    SQL2032N The "DBPARTITIONNUMS" parameter is not valid. SQLSTATE=22531

    Thanks

    ------------------------------
    RICARDO Pedraza
    ------------------------------


  • 2.  RE: Backup with LDAP Admin user defined in admin group

    Posted Wed March 20, 2019 12:45 PM
    Does db_backup work? It seems to me users should use db_backup command instead of db2 backup.

    ------------------------------
    Daniel Wang
    ------------------------------



  • 3.  RE: Backup with LDAP Admin user defined in admin group

    Posted Wed March 20, 2019 01:30 PM
    The problem is that db_backup has to be run by BLUADMIN id.  That is the reason I was going inside as db2inst1.  Here is the error:
    [bluadmdev@node0101-fab - Db2wh backup]$ sudo -E db_backup -schema testschm -path /scratch/ -type onl
    Checking if Backup or Restore is in progress.
    Performing System Health Check...
    System is ready to Run Backup(s) and/or Restore(s).
    Checking if backup path is configured under EXTBL_LOCATION paths...
    Checking if the schema "TESTSCHM" exists
    Identifying tables in schema "TESTSCHM"
    Identifying views in schema "TESTSCHM"
    Identifying stored procedures in schema "TESTSCHM"
    Backing up schema "TESTSCHM"
    ___________________________________________________________________
    Backup failed.
    ___________________________________________________________________

    For more info see /scratch/bluadmin_BNR/logs/backup20190320130802.log

    And the logfile has the following error:
    (. . .)

    2019-03-20 13:08:11.377070 INFO: The schema backup directory '' does not exist.
    2019-03-20 13:08:11.385035 ERROR: There was a problem creating backup directory: /scratch/20190320130802/BLUDB/ONL/
    2019-03-20 13:08:11.385197 ERROR:
    2019-03-20 13:08:11.385348 ERROR: su: user bluadmin does not exist
    2019-03-20 13:08:11.385498 ERROR:
    2019-03-20 13:08:11.387984

    As you can see it demands BLUADMIN user, which does not exist in LDAP AD.  The first error is strange because /scratch has 777 privs owned by root by default.

    ------------------------------
    RICARDO Pedraza
    ------------------------------



  • 4.  RE: Backup with LDAP Admin user defined in admin group

    Posted Wed March 20, 2019 06:16 PM
    Ok, I found the syntax issue, but still not able to run schema level backups from here.  That is the reason I was using db_backup command.  So regular backup as db2inst1 works:

    [db2inst1@node0101-fab - Db2wh backup]$ db2 backup db bludb on all dbpartitionnums online to /scratch/backup include logs
    (. . .)
    Backup successful. The timestamp for this backup image is : 20190320174441

    But the db_backup command fails as shown in a post before:
    [bluadmdev@node0101-fab - Db2wh backup]$ sudo -E db_backup -schema testschm -path /scratch/ -type onl
    Checking if Backup or Restore is in progress.
    (. . .)
    2019-03-20 13:08:11.385348 ERROR: su: user bluadmin does not exist
    2019-03-20 13:08:11.385498 ERROR:

    So I still have this issue of not being able to run schema level backups because of the BLUADMIN limitation. 
    And even worst not being able to restore tables from backups.

    thanks

    ------------------------------
    RICARDO Pedraza
    ------------------------------



  • 5.  RE: Backup with LDAP Admin user defined in admin group

    Posted Thu March 21, 2019 05:35 AM
    Hi Ricardo,

    I don't have IIAS but Db2 Warehouse on premises, which is basically is the same.

    In my case, i have created dedicated os user "db2admin" for admin tasks that must be performed from OS, but maybe this can help you to figure out best approach for your environments/policies.

    Basically, if you need to have host os user equivalent to db2inst1 user within db2 container, you need to match their group id's on host user creation. For eg.:

    --check db2inst1 in container (it is standard db2 story)
    [root@dwh-db2whs ~]$ docker exec -it --user db2inst1 Db2wh /usr/bin/bash -c "id db2inst1"
    uid=500(db2inst1) gid=1003(db2iadm1) groups=1003(db2iadm1),1004(guardium),3000(bluadmin)

    --create your user
    [root@dwh-db2whs ~]# useradd db2admin -c 'mgm user for db2 container' -m
    [root@dwh-db2whs ~]# usermod -u 888 db2admin
    [root@dwh-db2whs ~]# groupmod -g 1003 db2admin -- this is the same group id as db2iadm1 within container. That is the key, because i don't want to run or change anything within container, but use docker exec command from host
    [root@dwh-db2whs ~]# usermod -aG docker db2admin -- adding user to docker group because i don't want to mess around with root user, so i can run docker commands with db2admin user

    [root@dwh-db2whs ~]# id db2admin
    uid=888(db2admin) gid=1003(db2admin) groups=1003(db2admin),983(docker)

    Then you can try this:

    [db2admin@dwh-db2whs scripts]$ docker exec -it --user db2inst1 Db2wh /usr/bin/bash -c "/mnt/blumeta0/home/db2inst1/sqllib/bin/db2 get db cfg for bludb | grep EXTBL_LOCATION"
    Strict I/O for EXTBL_LOCATION (EXTBL_STRICT_IO) = NO
    Allowed paths for external tables (EXTBL_LOCATION) = /mnt/blumeta0/home/db2inst1;/mnt/blumeta0/home;/mnt/bludata0/scratch;/mnt/external

    [db2admin@dwh-db2whs ~]$ mkdir -p /mnt/clusterfs/scratch/schema_bkp
    [db2admin@dwh-db2whs ~]$ chmod 774 /mnt/clusterfs/scratch/schema_bkp

    [db2admin@dwh-db2whs scripts]$ docker exec -t Db2wh /usr/bin/bash -lc "/usr/bin/db_backup -path /mnt/bludata0/scratch/schema_bkp -type onl -schema syscat -compress gzip"
    Checking if Backup or Restore is in progress.
    Performing System Health Check...
    Checking if backup path is configured under EXTBL_LOCATION paths...
    Checking if the schema "SYSCAT" exists
    Checking for available space for path: /mnt/bludata0/scratch/schema_bkp/20190321102833/BLUDB/ONL
    Sufficient space available.
    Identifying views..
    Identifying stored procedures..
    Identifying tables..
    Identifying tables with large objects..
    Identifying tables with spatial data..
    Identifying tables with binary data..
    Backing up schema "SYSCAT"
    WARNING: No tables were found for schema "SYSCAT"
    Backing up "SYSCAT" schema objects


    ___________________________________________________________________


    Backup successful. The timestamp for this backup is: 20190321102833
    Backup path(s): /mnt/bludata0/scratch/schema_bkp
    ___________________________________________________________________

    For more info see /mnt/bludata0/scratch/bluadmin_BNR/logs/backup20190321102833.log
    [db2admin@dwh-db2whs scripts]$

    Regards,
    Ivan

    ------------------------------
    Ivan Milojevic
    Comtrade System Integration
    Belgrade
    ------------------------------



  • 6.  RE: Backup with LDAP Admin user defined in admin group

    Posted Thu March 21, 2019 06:16 AM
    Ivan,

    Off topic but wondering are you running on VM or hardware ? How are you using WarehouseDB ? I did a install on VM's for a POC on a earlier version. How have you found it to work so far ?

    Thanks
    Frank

    ------------------------------
    frank pantaleo
    ------------------------------



  • 7.  RE: Backup with LDAP Admin user defined in admin group

    Posted Thu March 21, 2019 07:00 AM
    Hi Frank,

    We are running couple dev & test of environments on VM - SMP architecture and 3.3.0 version. For some lets say "real" dwh workloads, 64G memory and 8cpu is a must for SMP. Didn't had any big issues except sorting memory overflows for complex joins, but that is not a big deal (happens on db2 also). I had some issues with docker storage drivers, so my advice is to stick with the latest overlay2 because devicemapper is deprecated in docker 18.09. Currently we trying to make use of Spark engine and in also testing MPP on Power LPARS/GPFS, so we'll see how it goes :)

    Regards,
    Ivan

    ------------------------------
    Ivan Milojevic
    Comtrade System Integration
    Belgrade
    ------------------------------



  • 8.  RE: Backup with LDAP Admin user defined in admin group

    Posted Thu March 21, 2019 09:32 PM
    Hi Ivan

    I took your suggestions and made few changes to match IIAS and created the id in the same group as db2iadm1... but after running the db_backup command... same error requiring BLUADMIN user :( ...

    [root@node0101-fab - Db2wh /]# id db2inst1
    uid=500(db2inst1) gid=1003(db2iadm1) groups=1003(db2iadm1),1004(guardium)
    [root@node0101-fab - Db2wh ~]# useradd bluadmdev -c 'mgm user for db2 container'
    [root@node0101-fab - Db2wh ~]# usermod -u 888 bluadmdev
    [root@node0101-fab - Db2wh ~]# groupmod -g 1003 bluadmdev
    groupmod: GID '1003' already exists
    [root@node0101-fab - Db2wh /]# id bluadmdev
    uid=888(bluadmdev) gid=1006(bluadmdev) groups=1006(bluadmdev)
    ==> did not accept to add group 1003 to bluadmdev with this command
    ==> change to docker fails because this group does not exist

    [root@node0101-fab - Db2wh /]# usermod -aG docker bluadmdev
    usermod: group 'docker' does not exist
    ==> did not accept to add group 1003 to bluadmdev with this command, so changed to command below:
    [root@node0101-fab - Db2wh ~]# usermod -aG db2iadm1 bluadmdev
    ==> db2iadm group was added
    [root@node0101-fab - Db2wh scratch]# id bluadmdev
    uid=888(bluadmdev) gid=1006(bluadmdev) groups=1006(bluadmdev),1003(db2iadm1)


    [db2inst1@node0101-fab - Db2wh ~]$ db2 get db cfg for bludb | grep EXTBL_LOCATION
    Strict I/O for EXTBL_LOCATION (EXTBL_STRICT_IO) = NO
    Allowed paths for external tables (EXTBL_LOCATION) = /head/home/db2inst1;/scratch;/scratch/home;/bludrdb2data/repl

    [bluadmdev@node0101-fab ~]$ /usr/bin/db_backup -path /scratch/schema_bkp -type onl -schema syscat -compress gzip

    This program requires the bluadmin login scripts to be processed.

    Correct this problem by invoking the following command, instead:
    su - bluadmin -c "sudo -E /usr/bin/db_backup -path /scratch/schema_bkp -type onl -schema syscat -compress gzip"

    [bluadmdev@node0101-fab ~]$ su - bluadmin -c "sudo -E /usr/bin/db_backup -path /scratch/schema_bkp -type onl -schema syscat -compress gzip"
    su: user bluadmin does not exist

    Anyway, it was worth the try.  I believe this would an enhancement or new request.

    Thanks a lot to all for your help!!!



    ------------------------------
    RICARDO Pedraza
    ------------------------------



  • 9.  RE: Backup with LDAP Admin user defined in admin group

    Posted Fri March 22, 2019 05:10 AM
    Well, considering you are on iias, don't experiment more. There is no reason not to open SR and ask support for right approach for your demands. Also, i presume that schema level backup will be integrated as backup option in web console in one of next updates.

    Regards,
    Ivan

    ------------------------------
    Ivan Milojevic
    Comtrade System Integration
    Belgrade
    ------------------------------



  • 10.  RE: Backup with LDAP Admin user defined in admin group

    Posted Fri March 22, 2019 12:32 PM
    Agreed. It is better to directly open a PMR for the tech support for issues like this. This group needs more SMEs to join/participate in questions/discussions and share their experiences with the new IIAS.

    IIAS is still evolving, so, some features that are not available in this release now may exist in later releases tomorrow, and for the same consideration, some "defects"/bugs that exist in this release may be removed/fixed in later releases. So, we have to keep updating our knowledge accordingly from the new release notes:-)

    We also see some notes/comments/warnings that, even though IIAS application is a member of the big DB2 family, but, not every command is applicable here. Therefore, it is very critical and also difficult to completely follow and comply with the specific IIAS documentations. 

    Finally, I believe IBM also welcomes enhancement requests as well, at least to certain degree......

    ------------------------------
    Daniel Wang
    ------------------------------