IT Smorgasbord: 2019

Wednesday, 31 July 2019

[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
;

Friday, 19 July 2019

[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

Wednesday, 15 May 2019

[Db2 LUW] Source for arbitrary record

I sometimes are in need for a source of an arbitrary record, e. g. to test some function. In Oracle you can use DUAL. In Db2 LUW (to make it clear what DB I am referring to, I know LUW is officially not used anymore by IBM) there is a compatibility vector thing which provides DUAL as well. But it is not installed, switched on, activated or whatever one has to do to make it usable, one can use SYSIBM.SYSDUMMY1.

Thursday, 9 May 2019

[screen] Howto scroll back

screen

is a terminal multiplexer pretty useful when you want to have several terminals over one single remote connection and also if you want disconnects not effect your sessions.

However, when in screen you cannot just scroll back. Longtime I just thought it would not be possible but it is. You need to enter scrollback/copy mode by [ctrl]-[a] [esc].

Tuesday, 30 April 2019

[Windows 10] After startup CPU and I/O gets hogged

If the culprit for this is WUDFHost.exe or something like that, I actually have not found a solution so far. Apparently, it has to do with media library synchronisation when you plugin a portable device.

Some mention you should just disable this device in the device manager, but it did not help with me. At least the device stayed usable.
Killing the process is cumbersome. First of all, you need to have admin role to do so. Apparently people suggesting the kill, do not restrict their usual user with removing that role. But much more important, the WUDFHost process started again immediately after the kill.
Rename the WUDFHost.exe (alternatively remove it), but as it is owned by "Trusted Installer" it is a bit tedious and in my case did not help. Maybe after a reboot, things look brighter. However, to do as mentioned:

Get admin rights and change the owner of the file (C:\Windows\system32\WUDFHost.exe) best to Administrator.
Rename the file with admin rights.

Saturday, 13 April 2019

[Windows 10] login screen/switch user list misses user

Prologue

I installed Windows 10 anew as it borked itself up for an unknown reason. However, as usually I activated the administrator account and kicked my personal (initial) user out of the administrators group. After reboot I was rather pissed when I did not have my personal user in the login screen and in the switch user list (I do not know the correct term for it, but it is the list showing up when clicking on the start button and then on the user button - the icon showing a bust).

Solution

I added the group "Benutzer"/users to the user in the Microsoft Management Console.
I changed the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Authentication\LogonUI\UserSwitch\Enabled to 1

After math

I am not sure whether both measures were needed.

Thursday, 28 March 2019

[bash] String manipulation within

In stead of turning to subprocesses with sed and the like. Works at least as of version 4.2.46(2)

# _LAST_PATCH being something like 00.0042. It is a real
# world example and as such not everything makes sense. _MAJOR for instance
# is just dead weight.
shopt -s extglob                    # To get support for more sophisticated pattern
_MAJOR=${_LAST_PATCH%%.+([0-9])}    # Remove longest pattern match from behind
_MINOR=${_LAST_PATCH##+([0-9]).}    # Remove longest pattern match from front
_MINOR=${_MINOR##+(0)}              # Remove leading 0s
_MAJOR=${_MAJOR:-0}                 # If nothing remains, set to 0
_MINOR=${_MINOR:-0}
shopt -u extglob

# Generate the new minor number
((_MINOR += 1))                     # Increment _MINOR
_MINOR=$(printf %04d ${_MINOR})     # left padding with 0. printf is a bash internal!

Tuesday, 29 January 2019

[Gnu tools] find quirks

Intended effect

List all directories recursively called "ready" or "data" .

Failing command

find . -type d -a \(-name ready -o -name data\)

find: paths must precede expression: `(-name'

Cause

find is somewhat picky with respect to parenthesis. It is not enough to escape them.

Solution

Make sure, whitespace is around them.

find . -type d -a \( -name ready -o -name data \)

Thursday, 3 January 2019

[Video] for Bluray-Player Samsung

Supported formats

HT-J4500

Tested

Result	Container format	Video Codec	Audio Codec	Remarks
OK	raw	raw	raw	I noticed that at least some DVDs can be copied to a NAS disk which in turn is connected and read from the player just as they appear on the file system browser under the Windows 10 explorer/FileZilla. I assume it does not matter whether this is done with the via of the local file system as I did or directly or whether it is important to do it under Windows. It was an accidental discovery. Unfortunately and obviously, copy protected media deny such treatment - at least under Windows 10.
OK	Matroska (.mkv)	Advance Video Coding (AVC/h.264/MPEG-4 Part 10)	MPEG 3 (MP3)
OK	MPEG 4 (.3gp / .3gpp)	MPEG 4 (Advanced Simple BVOP)	Advanced Audio Coding (AAC)
OK	QTFF Quicktime file format (.mov)	Advance Video Coding (AVC/h.264/MPEG-4 Part 10)	Pulse Code Modulation (PCM)
OK	MPEG PS (.vob)	MPEG 2 (Main BVOP)	MPEG 2 (MP2)	The file suffix of the container format seems to be wrong
NOK	Matroska (.mkv)	MPEG 2 (MP2)	Pulse Code Modulation (PCM)	Unsupported format error (it is assumed that mkv is meant even tough specs tells it was (possibly in combination with the video codec)
NOK	Matroska (.mkv)	MPEG 2 (MP2)	Dolby Digital / AC-3	Unsupported format error (it is assumed that mkv is meant even tough specs tells it was (possibly in combination with the video codec)
Result	Container format	Video Codec	Audio Codec	Remarks

avi

Video
- DivX 3.11/4/5/6
- mp4 v3
- H.264 BP/MP/HP

Audio

mp3
WMA
PCM
Dolby Digital (= AC-3)
DTS

mkv

Video

VC-1 AP
DivX 5/6
H.264 BP/MP/HP

Audio

mp3
Dolby Digital (= AC-3)
DTS

Problems

A conversion from H.264/opus to H.264/mp3 with copy or omitting -vcodec made first player complain about an unsupported format.

wmv (9)

Video

VC-1 AP/SM

Audio

mp4

Video

mp4v
H.264 BP/MP/HP

Audio

BD-F5100

avi

Video
- DivX 3.11/4.x/5.x/6.1
- mp4 v3
- H.264 BP/MP/HP

Audio

mp3
WMA
PCM
Dolby Digital (= AC-3)
DTS

mkv

Video

VC-1 AP
DivX 5.1/6.0
H.264 BP/MP/HP

Audio

mp3
Dolby Digital (= AC-3)
DTS

wmv (9)

Video

VC-1 AP/SM

Audio

mp4

Video

mp4v
H.264 BP/MP/HP

Audio

Conversion with ffmpeg (3.4.4-0ubuntu0.18.04.1)

To convert audio/video use switches -acodec and -vcodec respectively. The value following the switch specifies the target format. If you do not want to convert one of the streams, you can omit the switch entirely (it seems to get very slow though) - or you use copy.
However, I had some troubles with my tests. It was a download from youtube where the video was mp4 stream and the audio webm (opus). youtube-dl complained "WARNING: Requested formats are incompatible for merge and will be merged into mkv." So I ended up with a Matroska container. This probably is because as of time of writing "opus in MP4 support is experimental, add '-strict -2' if you want to use it." as ffmpeg told me.

Change audio to mp3
ffmpeg -i some_file.mkv -acodec mp3 -vcodec copy some_file_of_other_container.mp4
Change video to H.265
ffmpeg -i some_file.mkv -vcodec hevc some_file_h265.mkv