How to create a Date Series in DB2

Question: How can I create a Date Series in DB2 LUW. e.g. 01-01-2017 to 30-01-2017 . ? Answer: There are a number of different ways to create a date series in DB2 LUW.One way is the example used below, which lists the dates from 1st January 2017 - 30 January 2017. : db2 "with tempdateseries (date) as ( select date('01.01.2017') as date from sysibm.sysdummy1 union all select date + 1 day from tempdateseries where date < date('30.01.2017') ) select * from tempdateseries" This method will give you an output example such as : 01/01/2017 02/01/2017 03/01/2017 04/01/2017 05/01/2017 06/01/2017 07/01/2017... Read more →

DB2 Archived Log Compression

Question: Can I compress DB2 LUW archived logs? How can I configure a DB2 database to apply compression to the archived logs automatically? Answer: It is possible to configure a DB2 LUW database to compress Archived Logs. The two primary database configurables are to use Primary log archive compression (logarchcompr1) and secondary log archive compression (logarchcompr2). By simply changing the default value of NO to YES , does not automatically mean there will be archival logging compression. LOGARCHMETH1 must be set to either DISK, TSM, or VENDOR. If these values are not used there will be no compression of archivedlog... Read more →

DB2 Archived Log Compression

Question: Can I compress DB2 LUW archived logs? How can I configure a DB2 database to apply compression to the archived logs automatically? Answer: It is possible to configure a DB2 LUW database to compress Archived Logs. The two primary database configurables are to use Primary log archive compression (logarchcompr1) and secondary log archive compression (logarchcompr2). By simply changing the default value of NO to YES , does not automatically mean there will be archival logging compression. LOGARCHMETH1 must be set to either DISK, TSM, or VENDOR. If these values are not used there will be no compression of archivedlog... Read more →

HOW TO clear inactive DB2 LUW transaction log files

How to clear inactive DB2 LUW transaction log files is a solution to a common problem. Before we discuss how to prune the inactive transaction logs we’ll need to establish which log files are inactive. It is important to identify which logs to delete. If you delete an active transaction log file, you will cause an outage on the database Note: Log files located in the transaction log path cannot be removed with 'db2 prune history xxxxxxx and delet' if database configuration 'LOGARCHMETH1' is set to 'LOGRETAIN'. Before you start any activity , speak to the owners and users of... Read more →

How to write a DB2 loop with INSERT

Question: I’d like write a sql statement to loop through an INSERT statement and increment with a count. The purpose is to create some test tables for load testing. Answer: It is possible to create a loop in DB2 which loops through an incremental INSERT. This is a basic example, which can be customised for your purposes.Note the use of ATOMIC. The purpose of ATOMIC is to rollback before the call is passed back to the requestor, if there is a problem. In this example the CNT variable increments at every INSERT, up until it is under 100000. db2 “CREATE... Read more →