I developed this script in
- RHEL 8.10 Ootpa (see /etc/redhat-release)
- bash 4.4.20(1)-release (see echo $BASH_VERSION)
- DB2 v11.5.9.0 Community Edition (see db2level)
The script runs in any instance user that created the SAMPLE database by the IBM deliviered program db2sampl (usually in ~/sqllib/bin/db2sampl). The SAMPLE database must contain two tables DEPT (DEPARTMENT) and EMP (EMPLOYEE) and be accessible to the user that runs the script (which should be true for an DB2 instance user as SAMPLE creator).
Note
The script may run in DB2 versions down to 9.7.2 because of the SQL options RAND() and LIMIT, which should be available at least since DB2 9.7.2.
select empno from $dbtabempl order by rand() limit 1
Simply copy the script to the home directory of the instance user or to any directory this user can access. Add the mod-bits for execution (chmod u+x ) to execute it.
Currently (Mar 2025) the script supports two parameters
- -h : show some help of usage and parameters
- -d : only delete formerly inserted records, do not insert new ones.
I wrote this script to modify the SAMPLE database as this database is easily creatable by just calling db2sampl from any instance user, but I didn't find something to comfortable modify this database for testing DB2 archive logging and DB2 HADR DB mirroring.
As I cannot imagine, anyone uses the SAMPLE database for other purposes as testing, this database should be at any time easily dropable to create it again from scratch by the mentioned db2sampl program.
As this script first deletes records in tables DEPT and EMPLOYEE, then (without parameter -d) inserts these records again, it creates an amount of archive log data, so after some runs, a new archive log is created.
In a DB2 HADR DB mirroring environment, possible even in the free DB2 community edition, DB2 ships the modified data to the standby database. So I could do tests whether both databases - primary and standby - are consistent, rollback tests, takeover tests a.s.o. and check the results as the records contain a unique reference timestamp that is taken inside the script at start.
Finally and beyond the scope of DB2 or bash scripting, by writing this readme, I become familiar with github markup language - killing two birds with one stone.
By default, db2sampl creates the database with name SAMPLE, this could be changed by the "-name" parameter. So my script will be enhanced by a "-n" parameter in future as a very simple task (database name only used in the db2 connect statement).
As there are a bunch of tables in DB SAMPLE, I could extend the script with further actions on other tables. Not necessary to generate data but for enhancing my DB2 application site knowledge.