[Db2 LUW] When is a table reorg needed?

Some operations on a table of a Db2 instance let the table in an unusable state. I shall try to list the operations. Please be aware the list is probably not complete!

Require reorg

• drop at least one column
• increase the size of a decimal column

Do not require reorg

• add columns (at least 5 are possible, did not test more)
• increase the size of a varchar column

[Db2 LUW] buggy (left) outer join?!?

Problem

Following queries lose against DB2 v11.1.1.1 fixpack number 1(Linux) lose the left row without match on the right side.

with G as
    (select   *
        from ADW_ETL.ETL_GDT_UNIQUE_KEY
        where TABLE_NAME in('BAP_KOMMUNIKATION',
                            'BAP_GESCHAEFTSVORFALL',
                            'BAP_GFSCHRITT',
                            'BAP_GFSCHRITTADD',
                            'BAP_AUFTRAG',
                            'BAP_AUFTRAGPRODUKT')
    )
  select   G.TABLE_NAME,
      C.FLAG_ACTIVE,
      G.STORE_FLAG,
      G.OGG_PACKER_FREQUENCY,
      C.DWH_TIME_DELAY,
      I.*
    from G
    left outer join ADW_STATMON.DWH_ODS_CTRL C
    on G.TABLE_NAME = C.DWH_ODSCTRL_GDT_NAME
    left outer join ADW_STATMON.DWH_INBOUND_REG I
    on I.DWH_TRG_TABLE_NAME = G.TABLE_NAME
    where nvl(I.DWH_REG_TS, current_timestamp) >= current_timestamp - 9
;

with G as
    (select   *
        from ADW_ETL.ETL_GDT_UNIQUE_KEY
        where TABLE_NAME in('BAP_KOMMUNIKATION',
                            'BAP_GESCHAEFTSVORFALL',
                            'BAP_GFSCHRITT',
                            'BAP_GFSCHRITTADD',
                            'BAP_AUFTRAG',
                            'BAP_AUFTRAGPRODUKT')
    )
  select   G.TABLE_NAME,
      C.FLAG_ACTIVE,
      G.STORE_FLAG,
      G.OGG_PACKER_FREQUENCY,
      C.DWH_TIME_DELAY,
      I.*
    from G
    left outer join ADW_STATMON.DWH_ODS_CTRL C
    on G.TABLE_NAME = C.DWH_ODSCTRL_GDT_NAME
    left outer join ADW_STATMON.DWH_INBOUND_REG I
    on I.DWH_TRG_TABLE_NAME = G.TABLE_NAME
    where I.DWH_REG_TS                         >= current_timestamp - 9
       or I.DWH_REG_TS                         is null
;

I am pretty sure it is not iso standard behavior, and to me counterintuitive for sure.

Solution

Either pack the filtering into with constructs or move the filter on the outer table (the one not necessarily retaining its records) into the on clause. Example for the latter:

with G as
    (select   *
        from ADW_ETL.ETL_GDT_UNIQUE_KEY
        where TABLE_NAME in('BAP_KOMMUNIKATION',
                            'BAP_GESCHAEFTSVORFALL',
                            'BAP_GFSCHRITT',
                            'BAP_GFSCHRITTADD',
                            'BAP_AUFTRAG',
                            'BAP_AUFTRAGPRODUKT')
    )
  select   G.TABLE_NAME,
      C.FLAG_ACTIVE,
      G.STORE_FLAG,
      G.OGG_PACKER_FREQUENCY,
      C.DWH_TIME_DELAY,
      I.*
    from G
    left outer join ADW_STATMON.DWH_ODS_CTRL C
    on G.TABLE_NAME = C.DWH_ODSCTRL_GDT_NAME
    left outer join ADW_STATMON.DWH_INBOUND_REG I
    on I.DWH_TRG_TABLE_NAME = G.TABLE_NAME
    and I.DWH_REG_TS >= current_timestamp - 9
;

[git] Get the unpushed commits of a local repository and their commit timestamp using zsh

Run within the local repository

_LOG="$(git log)"
for _COMMIT in "${(@f)$(git cherry -v)}"
# '${(@f)VARIABLE}' splits VARIABLE (can be command substitution) at linebreaks (otherwise it would get split into words)
do
    _DATE_STRING=$(echo "${_LOG}" | grep -A2 ${_COMMIT[(w)2]} | grep -F Date)
    # (w) spilts into words, [..2] takes the second element
    echo "${_DATE_STRING}  ${_COMMIT}"
done

or as oneliner

_LOG="$(git log)" ; for _COMMIT in "${(@f)$(git cherry -v)}" ; do _DATE_STRING=$(echo "${_LOG}" | grep -A2 ${_COMMIT[(w)2]} | grep -F Date) ; echo "${_DATE_STRING}  ${_COMMIT}" ; done