db2 – Auomatically detect if database needs rebinding after restore?

In our deploy chain, we restore backups from our master environment and migrate the data models according to whatever branch is deployed to the target environment. Occasionally the db2level for the target environment is later than the db2level for the master environment. I would like to detect that automatically and rebind packages after restore. Currently, I’m doing something like:

currv=$(db2level | grep "Informational tokens are" | awk '{ print $5 }' | sed -e "s/",//")

bupv=$(db2ckbkp -h <bupfile> | grep "Release ID" | awk '{ print $6 }' | sed -e "s/)//")

and check if $currv is greater than $bupv. I’m not too happy with that since the text I’m grepping for may change in future releases (as an example).

Does anyone have a neater way of doing this?

How to Delete Multiple Rows With Multiple SELECT Statements In WHERE Clause in DB2

In the given inventory table below:

SKU COST DATE YYMMDD
202114000 0 210109
202114000 12 210228
202115000 50 210108
202115000 30 210109
202116000 54 210103
202116000 60 210301
202116000 100 210605

I want to retain SKU with the latest date and remove all others, and just retaining one SKU per row.

My query script goes:

SELECT *
FROM `inventory`
WHERE
    INUMBR IN (
        SELECT 
            SKU
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
    AND
    DATE IN (
        SELECT
            MIN (DATE) as DATE
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
ORDER BY
    SKU

However, this script is problematic. Why? because it will delete the two rows with the SKU 20211500 because, on the second IN criteria in the WHERE clause, two of DATEs will match: 210109 and 210108. How can I improve my query?

NOTE: The table is just an example, I will be dealing with millions of rows here.

db2 – Steps to relocate the primary hadr database to a new server?

Currently we have several databases with a HADR configuration where the primary databases are on a Linux Server “A”, with the standby all on Linux Server “B”. DB2 version is 9.7.

We are attempting to relocate the primary database of one of these databases (with the intent of moving all later) to a new Linux Server “C”. Efforts to find something similar have just brought back results of HADR takeover, that is not what we are aiming to do.

Lets call this database MYDB.

I have taken the steps below, and while HADR will start between the two by issuing the relevant start HADR commands first on the standby then on the primary, issuing ‘db2pd -db MYDB -hadr’ is showing them as disconnected with ‘S0000000.log’ as the log file on the opposite end. The correct log is being displayed locally.

STEPS TAKEN

  1. Quiesced the database and then stopped HADR on the primary, confirmed on the secondary there was now a log gap, stopped HADR on the Standby and deactivated.

  2. Took an offline backup on the current primary database and sent that to the new server “C”, where an identical version of DB2 is already set up.

  3. Created a new database ‘MYDB’ and restored from the backup sent over.

  4. Updated the relevant database configurations:

    On new server C :

    db2 update db cfg for mydb using HADR_LOCAL_HOST C

    db2 update db cfg for mydb using HADR_LOCAL_SVC hadr_mydb_c

    On the existing standby B:

    db2 update db cfg for mydb using HADR_REMOTE_HOST C

    db2 update db cfg for mydb using HADR_REMOTE_SVC hadr_mydb_c

    db2 update alternate server for database mydb using hostname c port 3700

    ‘hadr_mydb_c’ has been added to /etc/services on both ‘B’ and ‘C’ with a defined port of 3734
    ‘C’ has been added to both ‘B’ and ‘C’ hosts files. Log locations ect have been created to
    match the existing server ‘A’.

  5. At this stage we have done a db2stop and start on the backup, then when we issue ‘db2 start hadr on db mydb as standby’ we get a confirmation that HADR has started.

  6. On the new primary we issue ‘db2 start hadr on db mydb as primary’ we again get a confirmation that HADR has started.

db2pd shows HADR as active but the databases not connected.

Issuing the following command on the backup instance to see HADR details I can’t see the MYDB database listed along with the other databases.
db2 "SELECT SUBSTR(DB_NAME, 1, 8) AS DBNAME, HADR_ROLE, HADR_STATE,HADR_SYNCMODE, HADR_CONNECT_STATUS,HADR_HEARTBEAT,HADR_TIMEOUT,HADR_LOG_GAP FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T"

Luckily reverting the HADR config back to what they were previously we can reconnect HADR between the old primary and backup. Any ideas of how best to proceed?

db2 luw – Flyway, problem validating state of database before commit of version

I’m playing around with Flyway and amongst other things, I would like to verify certain things in the database before committing the version. As an example, I would like the following migration to fail:

--#SET TERMINATOR @
CREATE TABLE TMP.DUMMY(x int) @

create or replace procedure TMP.DOUBLE_DUMMY()
begin
    update TMP.DUMMY set x = 2*x;
end @

-- TMP.DOUBLE_DUMMY() is invalidated
DROP TABLE TMP.DUMMY @

This is easy enough to detect since I can try to recompile invalidated procedures as:

for c1 as c1 cursor for
    select rtrim(procschema) || '.' || rtrim(procname) as p
        ,  rtrim(procschema) || '.' || rtrim(specificname) as s
        ,  text
    from syscat.procedures
    where procschema = in_schema
      and valid <> 'Y'
      and language = 'SQL'
    order by create_time
do
    execute immediate 'drop specific procedure ' || s;
    execute immediate text;
end for;

and if something is broken, recompilation of the procedure fails. However, I can’t seem to find a callback that can be used after the last statement in the file, but before the new version is inserted and committed.

The next idea I got was to create a trigger on the flyway version table. Something like:

CREATE OR REPLACE TRIGGER FLYWAY.IRA_FLYWAY_SCHEMA_HISTORY
AFTER INSERT ON FLYWAY.FLYWAY_SCHEMA_HISTORY
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
    CALL COMPILE_SCHEMAS();
END @

A before trigger would have been a natural choice, but apparently, one is not allowed to do a CALL from such trigger.

However, it turns out that flyway uses one connection for the statements in the file and one connection for the version table. This leads to a lock timeout since there is an X lock on the object when the recompile appears.

I can’t seem to find some property that would prevent flyway to use two connections.

Any other thoughts on how to inject such a control mechanism?

hadr – Meaning of LFL and XHDR in Db2

I have seen the following messages in Db2diag.log but I do not know what they are. I cannot find any documentation about LFL or XHDR. I know it is related to HADR and log archiving, but what exactly?

2021-07-20-06.26.20.280271-240 E264696A655          LEVEL: Info
PID     : 6947266              TID : 5757           PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000           DB   : SAMPLE
HOSTNAME: db2server
EDUID   : 5757                 EDUNAME: db2lfr.0 (SAMPLE) 0
FUNCTION: DB2 UDB, recovery manager, sqlplfrFMReadLog, probe:2825
DATA #1 : SQLPLFR_SCAN_ID, PD_TYPE_SQLPLFR_SCAN_ID, 8 bytes
LFR Scan Num            = 898596933
LFR Scan Caller's EDUID = 20147
MESSAGE : Reached the max number of retries waiting for the LFL in XHDR. Moving
          to next extent.
DATA #2 : SQLPG_EXTENT_NUM, PD_TYPE_SQLPG_EXTENT_NUM, 4 bytes
702955

Do you know one of these terms and where can I get more information?

DB2 Linux authentication fails – Database Administrators Stack Exchange

I have DB2 Express-C v10.5 instance configured to authenticate against LDAP. The LDAP sever is going to be shutdown and I should configure the same DB2 instance to use Linux authentication.

I copied users from the LDAP  server to a local Linux host running DB2. Then I did shutdown the LDAP server. After that I changed DB2 authentication settings db2 update dbm cfg using SRVCON_PW_PLUGIN IBMOSauthserver (used to be IBMLDAPauthserver before) and restarted DB2.

Applications access the database with the username db2smth (name changed due to privacy reasons). I can connect to a database with db2 connect to dbname user db2inst1 using '********' but connecting to the same database as db2smth fails:

db2 => connect to dbname user db2smth using '********'

SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001

su - db2smth and su - db2inst1 works fine which means that Linux authentication works fine.

How can I diagnose what’s wrong with the authentication?

java – alter column from not null to null in db2 ILLEGAL USE OF KEYWORD NOT

Necessito alterar o atributo da tabela NOT NULL da coluna em uma tabela no banco DB2, contudo estou recebendo essa mensagem de erro ao executar o comando abaixo:

Script

ALTER TABLE t_pessoa
ALTER COLUMN cd_cpf 
DROP NOT NULL DEFAULT;

Mensagem:

ILLEGAL USE OF KEYWORD NOT. TOKEN WITH DEFAULT DATA INLINE CACHE MAXVALUE MINVALUE NOCACHE WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.18.60

A coluna cd_cpf anteriormente não aceitava valores nulos: cd_cpf (VARCHAR(11) Nullable)

DB2 Databases vs Schemas

Suppose I have a DB2 database instance that manages a single database with 2 Schemas, say A and B
What would be the benefits (if any) of having the instance instead manage 2 databases with one database containing Schema A and the other containing Schema B?