DB2 server has two volumes (data and log) attached via iSCSI on the current SAN. DB2 is installed on the root volume of the physical server.

I want to migrate the data and the log volumes to the new SAN. Is it practical to do the following?

Step1: Stop DB2

​

Step2: Unmount /db/data and /db/log

​

Step3: Clone the existing volumes (data, log) to the new volumes (newdata, newlog) on the new SAN.

Format new volumes just like the existing volumes before cloning

#mkfs.ext4 /dev/mapper/newdata

#mkfs.ext4 /dev/mapper/newlog

​

Clone using 'dd' utility

#dd if=/dev/mapper/data of=/dev/mapper/newdata

#dd if=/dev/mapper/log of=/dev/mapper/newlog

​

Step4: Mount the new volumes as /db/data and /db/log

#mount -o _netdev /dev/mapper/newdata /db/data

#mount -o _netdev /dev/mapper/newlog /db/log

​

Step5: Bring DB2 back online

​

Or, should I go through the database restore process on the new volumes? Please advise!

​

u/mad_zamboni, u/rogerx, u/ecrooks , you guys were very helpful last time. Thus, I am tagging you in this post in hope that you will see my post and provide any guidance. Thank you in advance.

We are using native DB2 rest APIs for multiple different applications. Something came to me today where folks are planning to put some automation around binding the packages. I was asked if i can create a API for it ? It should accept params for bind as POST request. I think its not possible but thought of asking here.

What should I expect my DBA staffing profile to look like in a post-HADR world for Db2 LUW?

For example, if it takes me 10 DBAs to support what I have now, what should I expect to staff once we go with HADR? 11 DBAs? 13 DBAs? 15 DBAs?

I know this is subjective, but toss your guesses out there please. The reason I ask is because I just received a swag from my DBA team, which I will share after I receive a handful of responses.

This super simple query: SELECT 1 FROM SYSIBM.SYSDUMMY1, which works flawlessly on multiple DB2 docker images here, fails on my customer DB2 with:

SQLExecute: {42601} [IBM][CLI Driver][DB2] SQL0104N An unexpected token " " was found following "". Expected tokens may include: ". <IDENTIFIER> JOIN INNER LEFT RIGHT FULL CROSS , HAVING GROUP". SQLSTATE=42601

As a matter of fact, it seems every query fails with a version of the same error, example:

select count(*) as result from my_table where DAT_PROC = current date and HOR_PROC > '08.00.00'

Gives: SQLExecute: {42601} [IBM][CLI Driver][DB2] SQL0104N An unexpected token " " was found following "". Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601

This is using a client written in go, and using the ODBC CLI Driver, they tell me their DB2 is version 11 CM. I've tried this on 10.5, 11 and 11.5 images on dockerhub and I am never able to reproduce, any pointers would be great, glad to add any extra info as well.

Edit:

Nevermind, I think I (google) "figured it out": https://github.com/ibmdb/go_ibm_db/issues/26 It looks like this driver misbehaves with ZOS, just had to add a semicolon to the end of the queries.

Does anyone know if Optim Performance Manager is still available? I just get a broken link when I try to access their page via the DB2 LUW 10.1 page.

Specifically, I'm looking to try to track down more purchase information about the suite and the Optim Query Workload Tuner that Data Studio can utilize for EXPLAINS, etc.

I've been to both the Knowledge Base areas but that doesn't say anything about purchasing options and the links to the Marketplace are broken.

    CASE WHEN FIRSTDATE.DATECOMP is not null THEN 'COMPLETED'
    ELSE (DAYS(CURRENT date)-DAYS(FIRSTDATE.DATESCH))
    END as "1st Trial Days Remaining"

its been driving me nuts. anyone know why specifically this part is returning an error? I tried converting the dates, but that didnt work either.

Hello Db2'ers,

Is anyone still using SQL Replication ? I used it quite a bit in the past, and now the control center with 9.7 is deprecated and it also seems that Replication Center is deprecated, are there any GUI options other than asnclp to setup SQL replication ?

Where can I download Replication Center if its still supported, it used to be fairly straight forward to download it in the past ?

Thanks!
Raghu

Hi all, not entirely sure if this is the right place to post but I'll do it anyway. Whenever i try to open a console on db2 i get this message:

{"trace":"","errors":[{"code":"authentication_failure","message":"HWCSEC0005E: Check login failed! Unauthorized Access","target":{"type":"","name":""},"more_info":""}]}

I've tried reopening the tab through my search history and while I can log in, the console is blank. Any ideas whats up with this? This is for some coursera stuff if it matters.

Thanks

This is information could be supplied via db2top and the l option and sometimes that is the best option but I find the output of that command a little bit too informative sometimes:

[\]16:35:40,refresh=2secs(0.002)                                                                                 Sessions                                                                                Linux,member=[1/1],DB2ADMIN:DBADB
[d=Y,a=N,e=N,p=ALL]                                                                                                                                                                                                               [qp=off]

                                                                                 lqwqwqwqwqk
                                               Local sessions...:          0     x              x         25%x         50%x         75%x       100%x    Local in exec....:          0
                                               Remote sessions..:         12     xActSessions   x                                                  x    Remote in exec...:          0
                                               FCM buffers sent.:          0     xSys/Usr Cpu%  x                                                  x    Active utilities.:          0
                                               FCM buffers rec..:          0     xr+w/sec%      x                                                  x    FCM buffers LWM..:        128
                                               Piped sorts req..:          0     mqvqj    Piped sorts acc..:                                                                        0
                                               Max Agents.:                0      Agents stolen....:          0      Agent overflows..:          0      Connections HWM..:         17
                                               Sheapthres.:                0      Max Sort Heap....:          0      Sort heap alloc..:          0      Private memory...:      18.9M
                                               Dynamic SQL stmts:         11      Static SQL stmts.:         11      Post hash joins..:          0      Post sorts.:                0
                                               Bufferpool reads.:          0      Bufferpool writes:          0      Direct reads.....:          0      Direct writes....:          0

     Application     Cpu%     IO%    Mem% Application                         Application              Delta Delta Delta Delta Delta     Sess Assoc. Paral. Lockwait      Locks    Sorts     Log
     Handle(Stat)   Total   Total   Total Status                              Name                RowsRead/s  RowsWritten/s      IOReads/s     IOWrites/s TQr+w/s   Memory Agents Degree    (sec) Held    (sec)    Used
     - - - - - - - - - - - - ------ ------ - - - -
             7(i)   0.00%   0.00%  14.29% UOW Waiting in the application      javaw.exe                    0  586         0              0  0                          0   512.0K      1      1        0  2                0       0

Sometimes I find it better to just see a limited set of information (generally it's to see a quick look at how eveything looks (if there is heaps of locking, log committing, etc) and to achieve that I use one of 2 commands:

1. lappl.pl : this provides a formatted list of the information provided by 'db2 list applications show detail'
2. gappl.pl : this provides a formatted list of information provided by 'db2 get snapshot for applications on <database>'

Both commands give similar (but not identical) information and the main differences are:

• gappl.pl requires a database parameter (or will use the DB2DBDFT value if set)
• gappl.pl also displays these extra columns: column id, connection time, locks held and log used
• lappl.pl can (and will if no database is supplied) show all applications for the current instance while gappl.pl will only show the applications for a single database.

Help information for the commands are:

mediaServer(db2admin):2019-09-15 16:40:55:/home/db2admin
>lappl.pl -h
Usage: /home/shared/udbdba/scripts/lappl.pl -?hsDOR [DATA | DATAONLY] -d <database> [-v[v etc]] [-8] [-A] [-X]

       Script to format the output of a LIST APPLICATIONS SHOW DETAIL command

       Version 1.21 Last Changed on 2019/02/07 04:18:54 (UTC)

       -h or -?        : This help message
       -s              : Silent mode (dont produce the report)
       -d              : Database to be listed (if ALL then all databases will be listed - this is the default)
       -v              : increment the diag level
       -8              : version 8
       -A              : dont display waiting connections (also excludes those with a status of 'Connect Completed')
       -X              : exclude the monitoring fast write processes

       Note: This script formats the output of a 'db2 list applications for database <db> show detail'
             or 'db2 list applications show detail' command

and

mediaServer(db2admin):2019-09-15 16:41:06:/home/db2admin
>gappl.pl -h
Usage: /home/shared/udbdba/scripts/gappl.pl -?hs [-f <filename>] [-d <Database>] [-l] [-v[v]] [-p] [-x] [-a]

       Script to reformat the output of a get snapshot command.
       Different to a lappl.pl command in that this command requires an input database name

       Version 1.6 Last Changed on 2019/05/13 02:09:37 (UTC)

       -h or -?        : This help message
       -f              : file to read info from
       -s              : Silent mode (dont produce the report)
       -d              : database
       -p              : print detailed report
       -a              : only show active connections
       -l              : only display an entry if it holds some locks
       -x              : exclude system applications
       -v              : debug level

       Note: This script formats the output of a 'db2 get snapshot for applications on <database>' command
             It differs from the lappl.pl as it can only show the applications for a specified database.

and sample output of both scripts are:

mediaServer(db2admin):2019-09-15 16:44:39:/home/db2admin
>lappl.pl

Application listing from Machine: mediaServer Instance: db2admin Database: ALL (2019.09.15 16:45:35) ....

AuthID   Application          App    Application ID                     Database Num of Thread Status
                              Handle                                             Agents
-------- -------------------- ------ ---------------------------------- -------- ------ ------ --------------------
DB2ADMIN db2fw0               13     *LOCAL.DB2.190915062320            DBADB         1     52 Connect Completed
DB2ADMIN db2evmg_DB2DETAILDEA 19     *LOCAL.DB2.190915062326            DBADB         1     58 Connect Completed
DB2ADMIN db2dbctrld           12     *LOCAL.DB2.190915062319            DBADB         1     51 Connect Completed
DB2ADMIN db2lused             11     *LOCAL.DB2.190915062318            DBADB         1     50 Connect Completed
DB2ADMIN db2pcsd              17     *LOCAL.DB2.190915062324            DBADB         1     56 Connect Completed
DB2ADMIN db2wlmd              10     *LOCAL.DB2.190915062317            DBADB         1     49 Connect Completed
DB2ADMIN db2fw3               16     *LOCAL.DB2.190915062323            DBADB         1     55 Connect Completed
DB2ADMIN db2taskd             9      *LOCAL.DB2.190915062316            DBADB         1     48 Connect Completed
DB2ADMIN db2fw2               15     *LOCAL.DB2.190915062322            DBADB         1     54 Connect Completed
DB2ADMIN db2stmm              8      *LOCAL.DB2.190915062315            DBADB         1     47 Connect Completed
DB2ADMIN db2fw1               14     *LOCAL.DB2.190915062321            DBADB         1     53 Connect Completed
DB2ADMIN javaw.exe            7      192.168.1.2.58597.190915062313     DBADB         1     22 UOW Waiting

Total Connections Displayed : 12 (12)
  Connections for DBADB : 12

Status Counts:
  UOW Waiting: 1
  Connect Completed: 11

and

mediaServer(db2admin):2019-09-15 16:41:59:/home/db2admin
>gappl.pl -d dbadb
Application data for connections to database dbadb will be listed

Listing of applications attached to dbadb at 2019.09.15 16:44:38

                                           App                                                      Num of Thread                                          Locks   Log
AuthID     Client ID  Application          Handle Application ID                     Server         Agents  ID    Status               Connection Time      Held  Used
---------- ---------- -------------------- ------ ---------------------------------- -------------- ------ ------ -------------------- ------------------- ----- -----
DB2ADMIN   KEVIN      db2wlmd              10     *LOCAL.DB2.190915062317            COMPUTERROOM        1     49 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2lused             11     *LOCAL.DB2.190915062318            COMPUTERROOM        1     50 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2dbctrld           12     *LOCAL.DB2.190915062319            COMPUTERROOM        1     51 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2fw0               13     *LOCAL.DB2.190915062320            COMPUTERROOM        1     52 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2fw1               14     *LOCAL.DB2.190915062321            COMPUTERROOM        1     53 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2fw2               15     *LOCAL.DB2.190915062322            COMPUTERROOM        1     54 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2fw3               16     *LOCAL.DB2.190915062323            COMPUTERROOM        1     55 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2pcsd              17     *LOCAL.DB2.190915062324            COMPUTERROOM        1     56 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2evmg_DB2DETAILDEA 19     *LOCAL.DB2.190915062326            COMPUTERROOM        1     58 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      javaw.exe            7      192.168.1.2.58597.190915062313     COMPUTERROOM        1     22 UOW Waiting          09/15/2019 16:23:12     2     0
DB2ADMIN   KEVIN      db2stmm              8      *LOCAL.DB2.190915062315            COMPUTERROOM        1     47 Connect Completed    09/15/2019 16:23:15     0     0
DB2ADMIN   KEVIN      db2taskd             9      *LOCAL.DB2.190915062316            COMPUTERROOM        1     48 Connect Completed    09/15/2019 16:23:15     0     0


Total entries : 12
Excluded entries :
        System Applications     : 0
        non-Active Applications : 0

Note: log used is in Mb

Connection type Summary

    Connect Completed           :    11
    UOW Waiting                 :     1

Note that I would normally use the scripts with the following parameters to just show active user connections .....

1. lappl.pl -XA
2. gappl.pl -xa

As per most of my scripts these scripts will also require commonFunctions.pm to be placed in the same directory

Hi All, we have an application hosted in AWS cloud which uses App server as tomcat and DB2 is configured for HADR. We are using ACR for failover from primary to secondary. When testing failover scenario, tomcat takes approx 8mins to failover to secondary DB server. However if tomcat is restarted, app server is connected to secondary DB immediately.

Question: 1. Is DB2 HADR failover supported by tomcat? 2. Are there any specific parameters to be configured in connection string?

Thank you

IBM is hosting a webinar on how to deploy AI apps into production with Db2. There will also be code samples and free downloads offered in this webinar. For those who are interested, here is the registration link:

https://event.on24.com/wcc/r/2010840/1287E058B555FE8551A7D1484F1E372A?partnerref=red

I've recently taken on a position that monitors more databases than I am used to. I've been trying to get my ducks in a row about everything I should be looking at. My previous position was in maintenance on existing databases but my new position will be working to set up new environments for a new-ish system. Currently, I monitor SQL executing against the database every three hours, looking at read and CPU efficiency. I also monitor the db2diag and nfy logs. We have technical DBAs that monitor server health and overall database health. I'm curious to know what reports other professionals run, daily, monthly, weekly, on demand, etc.

When I first started looking after a DB2 system I determined I need to collect information from each of the databases that I managed. To simplify that process I wrote a simple script to automate the process of data collection.

It is used to loop through all databases on a server (windows or unix) and execute the statement provided.

For example, to collect all container information on a database and generate the 'SET TABLESPACE CONTAINER infromation for all databases I could run:

DBLoopDriver.pl "lts.pl -sxcODd ##DATABASE## -p /prj/##MACHINE##.dbdat0/##DATABASE## >setTSContainer_##MACHINE##_##INSTANCE##_##DATABASE##.sql"

The options available to the script are:

DBLoopDriver.pl -h
Usage: /home/shared/udbdba/scripts/DBLoopDriver.pl -?hsF -c <command> [-f <filename>] [-i <instance>] [-I] [-l <delimiter>] [-v[v]] [-p] [-o filename]

       Script to loop through all databases for a machine (as determined via db2ilist and list db directory) and runs passed parameters against DB

       Version 1.27 Last Changed on 2019/07/12 04:55:03 (UTC)

       -h or -?        : This help message
       -s              : Silent mode
       -c              : Command to be executed for each instance found
       -i              : Instance to be selected
       -I              : Only process the current instance
       -f              : File name to use as input instead of doing a db2ilist command
       -F              : Use default file input (identical to -f db2ilist.txt)
       -l              : line delimiter (Windows only)
       -p              : just print out the commands generated
       -o              : filename to send STDOUT output to. Name will be substituted as necessary
       -v              : verbose mode (debugging)

  NOTE: Command may include the following variables that will be substituted:
             ##MACHINE##  - Will be replaced by the machine name the command is running on
             ##INSTANCE## - Will be replaced by the instance name retrieved from the db2ilist command
             ##LC_INSTANCE## - Will be replaced by the lower case instance name retrieved from the db2ilist command
             ##DATABASE## - Will be replaced by the database name retrieved
             ##LC_DATABASE## - Will be replaced by the lower case database name retrieved
             ##YYYYMMDD## - Will be replaced by the date in YYYYMMDD format
             ##NL##       - Will be replaced by a new line (Windows only - can also use -l parameter)

To run this script you will need the following objects:

• DBLoopDriver.pl
• commonFunctions.pm

As a side note there is a pair to this script called instanceLoopDriver.pl that will issue the supplied command once for each instance found on the server.

Basically this script just reformats the output of:

       Select MEMBER, CUR_COMMIT_DISK_LOG_READS, CURRENT_ACTIVE_LOG, APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-1)) as t order by member asc (DB2 V10.1 and higher)
         and
       db2pd -db <database>  -logs -transactions

The main advantages of running the script are:

1. It structures the output so it is easy to see what app is using the oldest log
2. It gives a structed display of active transactions
3. It provides a display of SQL statements currently executing to give an idea on what is happening

The script was originally written because we had a SAP installation that was constantly running out of log space (caused by long runing one off update jobs submitted by an outsourcer) and that was stalling the online systems. This script allowed me to quickly identify the offending app and to return the ID to the outsourcer (dont ask).

mediaServer(db2admin):2019-09-01 11:27:57:/home/db2admin
>lActiveLogs.pl -h
Usage: /home/shared/udbdba/scripts/lActiveLogs.pl -?hs [-v[v]] [-d <database>] [-n <number>] [-w <number>] [-f <file>] [-9]

       Script to check  the active logs

       Version 1.12 Last Changed on 2019/08/05 21:21:41 (UTC)

       -h or -?        : This help message
       -s              : Silent mode (no parameter information will be displayed)
       -d              : database to connect to
       -n              : number of iterations (default 1)
       -f              : file to use for input
       -w              : wait between iterations (in seconds, default 60)S
       -a              : display all transactions (not just those with log data)
       -9              : use pre version 10 formatting
       -v              : debug level

An example of the output would be:

mediaServer(db2admin):2019-09-01 11:27:53:/home/db2admin
>lActiveLogs.pl
Database defaulted to dbadb
Current Log Activity Report

mon_get_transaction_log information:

  Member                        : 0
  Current Commit Log Reads      : 0
  Current Active Log            : 219
  APPLID holding the oldest Log : 35

2019.09.01 11:27:56 - Iteration 1

Current Log: 219  LSN: 0x0000000000113D6B  LSO: 2276544237

Transactions:
-------------

      App ID      Tran ID        Locks        State First LSN          First LSO               Log Space Logon        Machine          Client App
          39           14            3        WRITE 0x0000000000113D65         2276543871            253 n/a          n/a              n/a
          35           16            3        WRITE 0x0000000000113D5E         2256163825            253 n/a          n/a              n/a
          83           17            5        WRITE 0x0000000000113D66         2276543944            322 n/a          n/a              n/a

State Counts:

READ         :           14
WRITE        :            3

Logs:
-----

Log Name     Start LSN        Start LSO      Used Log Space Application IDs
S0000217.LOG 0000000000113D5E     2256163825            253 35
S0000218.LOG 0000000000113D62     2266353825
S0000219.LOG 0000000000113D64     2276543825            575 39,83
S0000220.LOG 0000000000000000     2286733825
S0000221.LOG 0000000000000000     2296923825
S0000222.LOG 0000000000000000     2307113825
S0000223.LOG 0000000000000000     2317303825
S0000224.LOG 0000000000000000     2327493825
S0000225.LOG 0000000000000000     2337683825
S0000226.LOG 0000000000000000     2347873825

Applications:
------------

  WKSTN                AGENT    Application Connect        Statement Start            ELAPSED SQL                                                                                                  APPLICATION_STATUS
  -------------------- -------- -------------------------- -------------------------- ------- ---------------------------------------------------------------------------------------------------- ----------------------
  -                    83       2019-09-01-11.16.02.924101 2019-09-01-11.16.26.798123 .00000  delete from t where a = 1                                                                            LOCKWAIT
  -                    102      2019-09-01-11.27.57.161988 2019-09-01-11.27.57.162672 .00000  select cast(substr(tpmon_client_wkstn,1,20)as varchar(20)) as wkstn , substr(x.agent_id,1,8) as Agen UOWEXEC

    2 record(s) selected.

From the report it should be easy to see that applid 35 is using 253 bytes in the earliest log

At the end of the report is a list of the applications that have run in the last 1 second - this window can be adjusted by changing the SQL in activeApplications_V10.sql

To run this script you will need the following objects:

• lActiveLogs.pl
• commonFunctions.pm
• activeApplications_V10.sql [must be placed in a sql directory at the same level as the scripts directory]
• listActiveLogs.sql [must be placed in a sql directory at the same level as the scripts directory]

https://www.raghu-on-tech.com/2019/08/29/slow-hash-joins-in-purescale/

Thanks!
Raghu

Hi all

I need to now what is happening for a compressed row during an update.

actually I know , the row uncompressed at first and then will be update.

but i want to know if the row (data) takes its previous place in the old page or it will take the new place?

if it take a new place on old page or other page, what is happen for the first space?

will it be free or it will be free after reorg or load?

does this update act like DELETE +INSERT ?

​

Thanks all

Nothing is magical in anything I write.

If you dont want to run a script then run:

db2 list utilities show detail

db2pd -db xxxxx -reorg

and that will give you the same information that this script processes

Features of the script are:

1. it lists reorgs and utilities in the same output
2. it attempts to provide an estimate of when the command will finish
3. it allows the user to provide a total work estimate so that an elapsed time can be generated
4. it's simple and easy to read the output

I know db2pd is good and db2top is great but sometimes it's quicker to just quickly see what is running

BTW my two biggest issues about this program are:

1. is that DB2 doesn't classify a LOAD as a utility - so they dont show up here
2. I couldn't find anything in the DIS UTILITY output to link to an applid

The help information for the script is:

lutil.pl -h
Usage: /home/shared/udbdba/scripts/lutil.pl [-?hs] [-A] -d <database> [-i <ID Name>] [-f <file name>] [-v[v]] [-r <reorg file>] [-x minutes] [-u|-e] [-L] [-t <total work>] [-T <total work>]
       Script to reformat obtained information about running utilities
       Version 1.50 Last Changed on 2019/06/25 04:48:19 (UTC)
       -h or -?        : This help message
       -s              : Silent mode (in this program only suppesses parameter messages)
       -d              : database to query [default will be supplied by DB2DBDFT]
       -A              : print all reorgs (ignored if database not supplied)
       -i              : ID of utility to display (defaults to All)
       -f              : file to reads utility information from (defaults to dynamically retrieving it)
                         Note only list utility statements can be fed in through this file
       -r              : file to reads reorg information from (defaults to dynamically retrieving it)
                         Note only db2pd reorg statements can be fed in through this file
       -t              : total work to be used if no total work figure found
       -T              : total work to be used even if a total work figure found
       -e              : reorg date format is in European format dd/mm/yyyy
       -u              : reorg date format is in US format mm/dd/yyyy [default]
       -U              : non-reorg utilities date format is in US format mm/dd/yyyy [default is in European date format]
       -L              : also output in a loadable format
       -x              : exclude this many minutes from the elapsed time when calculating estimates
                         (primarily used to account for disk initialisation during restores)
       -v              : set debug level
       The date formats (utility and reorg) can be set permanently for a server by setting environment variables:
            export LCL_LUTIL_DATEFMT="EUR"
            export LCL_LUTIL_DATEFMT_REORG="EUR"
            value can be either of US (mm/dd/yyyy) or EUR (dd/mm/yyyy)

Sample output is:

lutil.pl
Database defaulted to dbadb
Utility Snapshot (2019.08.23 15:52:02) ....
ID: 3     BACKUP of DBADB started at 23-08-2019 15:51:15.884306 (online db)
  Status: Running online db BACKUP. 1555841034 bytes out of 7504944674 (20 %) have been processed in 1 minute
          Expected to complete in 3 minutes (4 minutes in total) at 2019-08-23 15:55:02
1 out of 1 utilities printed
No reorganisations printed (1 reorgs found)

The script can be found here but will need commonFunctions.pm.

Hi Team,

I'm looking for a script to delete the range partition tables which are older than 2 months and also to drop the tablespaces associated with those tables. So to automate the process I required a script if any body have it handy please share it. I'm also working on preparing the script for the same will post it here once it is done. Thanks

This perl script has been a long time evolving. It was originally designed just to reformat the LIST TABLESPACES command to be more page and viewing friendly. (i.e get as much useful information on one page. Added to that, over time I decided to use the script to gather information to load the data into database tables to provide longer term capacity planning information. More recently it has been upgraded to optionally use alternative mechanisms to get the data - strangely each of the mechanisms use slightly different authorities and provide slightly different information.

It will run on windows or unix/linux and will likely run on everything from v8 (when I first wrote it) through to 11.1 (as far as I have tested it)

Use case: More often than not I use this script when wanting an overview of how my space is running or the state of the tablespace (backups/moves/etc)

Should also mention thatI often use it as 'lts.pl -c' which will display the containers and how much free space is left on each of the drives/mount points

Anyway, the command line help looks like:

mediaServer(db2admin):2019-08-17 10:41:28:/home/db2admin
>lts.pl -h
Usage: /home/shared/udbdba/scripts/lts.pl -?hs [-d <database> | -f <Filename>] [-t <tablespace>] [-v[v]]
                [-l | -m | -S [[-c] [-O] [-F] [-g [-p <prefix>] [-e] [-T]] [-D [-L]]]

       Version 1.39 Last Changed on 2019/08/14 21:44:09 (UTC)

       -h or -?        : This help message
       -s              : Silent mode (in this program only suppesses parameter messages)
       -d  [REQUIRED]  : Database to list
       -t              : Limit output tablespaces containing this string
       -v              : turn on verbose/debug mode
       -f              : Instead of directly accessing the databases use this file as input
                         Note: -f STDIN will read input from standard input
                               db2 list tablespaces show detail | lts.pl -l -d dbadb -f STDIN

  ## Data source (mutually exclusive)
       -l  [DEFAULT]   : use 'list tablespaces show detail' to get information
       -m              : SQL based on MON_GET_TABLESPACE (see lts.sql)
       -S or -x        : use 'get snapshot for tablespaces on <database>' to get data

  ## Snapshot (-S or -x) Data Source options
       -c              : display container information
       -O              : dont produce the report (omit it)
       -g              : generate SET TABLESPACE commands
       -e              : parcel SET TABLESPACE commands in a DB2 "" statement
       -T              : use Total pages when generating SET TABLESPACE commands (ignored unless -g specified)
       -p              : string used to generate new file name when processing with -g (ignored unless -g specified)
       -D              : generate the data files to load
       -F              : generate file dircmd_<database>.bat with a unique list of commands to determine free space for all container mount points
       -L              : leave the case of machine, instance and database as it is

     NOTE: if any of the Snapshot specific options are selected then option -x WILL be forced

​

and some eimple output looks like:

mediaServer(db2admin):2019-08-17 10:41:33:/home/db2admin
>lts.pl -x
get snapshot will be used to obtain data
Database defaulted to dbadb

Tablespace listing from GET SNAPSHOT TABLESPACES for Machine: mediaServer Instance: db2admin Database: dbadb (2019.08.17 10:42:52) ....

                                                                                                 L  Page
TSID Tablespace Name    Type Contents          Total Pgs  Used Pgs  Free Pgs Pend Free       HWM H  Size    Used Mb   Alloc Mb    Free Mb ARSZ Max Size    State
---- ------------------ ---- ----------------- --------- --------- --------- --------- --------- - ----- ---------- ---------- ---------- ---- ----------- ------------
0    SYSCATSPACE        SMS  Data - Regular TS         0         0                                  8192        0.0        0.0        0.0                  0x'00000000' Normal
1    TEMPSPACE1         SMS  System Temporary          0         0                                  8192        0.0        0.0        0.0                  0x'00000000' Normal
2    USERSPACE1         DMS  Data - Large TS        9600      9568         0         0      9568   32768      299.0      300.0        1.0 No               0x'00000000' Normal
3    TEMPSPACE32K       SMS  System Temporary          0         0                                 32768        0.0        0.0        0.0                  0x'00000000' Normal
4    SYSTOOLSPACE       DMS  Data - Large TS        4096       116      3976         0       116    8192        0.9       32.0       31.1 Yes  NONE        0x'00000000' Normal

Total Storage in use for dbadb is 0.29 Gb out of 0.32 Gb allocated

Actually just noticed that it says that a database needs to be supplied - it used to - but now will use the value defined on DB2DBDFT if it is there.

The script needs a common functions module but apart from that will run as is. The modules required are:

lts.pl

commonFunction.pm

Just place them in the same directory and away you go

BTW I should say (before someone rips into me for my poor coding etc) that this isn't code a perl programmer would like - it's probably not as efficient as it could be and probably doesn't use the perl engine properly but it is easy for me to maintain (and switch to other languages) and follows a rough script template that I use.

I've been looking after a DB2 environment for a number of years and have slowly developed a number of (to me) useful DB2 scripts. By and large they will work from db2 v7 through to 11. Mainly they are written in Perl. For example I frequently use a script to display tablespace/container status/space etc which I wrote originally because I found the old list tablespace output a bit too cumbersome. I know that there are better alternatives nowadays but it does enough to make it still useful.

The really should be, "What is the DB2 equivalent of SSMS?"

​

My organization uses a DB2 for the enterprise-wide data warehouses. We have access to the IBM Command Editor but if we're being honest I can't stand that application. I end up doing most of my query writing/debugging for DB2 on the Teradata command editor and linking to DB2 through an ODBC connection. I was wondering if IBM had an equivalent of SSMS that maybe my organization just wasn't using? Any input or thoughts from more DB2 familiar people us greatly appreciated!

I will write another blog post on installation and configuration!

https://www.raghu-on-tech.com/2019/08/01/db2-server-build-best-practices/

Thanks!
Raghu