Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING!

Note: This is just one of the various ways I capture and tune SQL. I use many different methods to capture suboptimal SQL depending on the urgency of the situation and the details I require to grab the SQL and work on a solution. It is not all encompassing. Db2 Design Advisor is your friend, and in my experience is not used enough!!! But the hard part comes when you have captured a suboptimal SQL and Design Advisor doesn’t have any recommendations!!!!  At this point you can’t just throw your hands up and give up, it has to be fixed!!!  This is where we shine with our clients, there isn’t an SQL out there that we can’t improve using all of the knowledge we have and all the tools in our toolbox. I’ll post more on SQL rewrite possibilities, index design and redesign, how to capture suboptimal SQL, statistics effects on optimization, statistical views, informational constraints, and hints, to name a few….

1. Connect to database and run “db2 call monreport.pkgcache” to get top statements.

 

— Command options —

CACHE_INTERVAL:                    All statements

SECTION_TYPE:                      Dynamic and static SQL

MEMBER:                            All

 

================================================================================

Part 1 – Summaries by ‘top’ metrics

 

Top 10 statements by TOTAL_CPU_TIME

——————————————————————————–

#   TOTAL_       STMT_TEXT

CPU_TIME

—  ———–  —————————————————————

1   217980426    INSERT INTO SPECTRUM_RESULT_5GHZ (WHEN,MEAS_ID_FK,EYE_ID,ANT_ID

2   137210647    select MEAS_ID from final table (INSERT INTO CLIENT_PCAP_RADIO

3   33451937     INSERT INTO SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL,

4   24715177     INSERT INTO SPECTRUM_RESULT (WHEN,MEAS_ID_FK,EYE_ID,ANT_ID,SUBC

5   24691176     select MEAS_ID from final table (INSERT INTO RADIO_ENVIRONMENT_

6   18695817     INSERT INTO AP_FTP_QOS_TEST (WHEN,MEAS_ID,NW_ID,AP_ID,EYE_ID,AN

7   17015041     select MEAS_ID from final table (INSERT INTO AP_PCAP_RADIO (WHE

8   16529630     select MEAS_CATEGORY_ID from final table (INSERT INTO AP_FTP_QO

9   8262363      select MEAS_ID from final table (INSERT INTO CLIENT_PCAP_QOS (W

10  4794274      select MEAS_CATEGORY_ID from final table (INSERT INTO MOS_QOS_T

 

2. Go to topsql.sql script to get full SQL for above insert:

 

2017-09-22-13.09.08.775452             14586120             14586087

0                 5465                14507                    1

1                    2                    2                    2 INS

ERT INTO SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL, HT_MODE, BEACON_OK,

SIGNAL_LEVEL_APEYE, SNR_APEYE, QUALITY, MAXQUAL, DURATION, WHEN ) VALUES ( ?, ?,

?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

3. Then format the SQL for explain design advisor usage:

 

INSERT INTO CARAT7.SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL,

HT_MODE, BEACON_OK,

SIGNAL_LEVEL_APEYE, SNR_APEYE, QUALITY,

MAXQUAL, DURATION, WHEN )

VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );

 

 

4. Run db2advis [db7sign@east1 ~]$ db2advis -d meas7 -i iscanradio.txt | more

 

Using user id as default schema name. Use -n option to specify schema

execution started at timestamp 2017-09-22-12.35.55.976835

found [1] SQL statements from the input file

Recommending indexes…

0  indexes in current solution

[ 11.0000] timerons  (without recommendations)

[ 11.0000] timerons  (with current solution)

[0.00%] improvement

 

 

—

—

— LIST OF RECOMMENDED INDEXES

— ===========================

—  no indexes are recommended for this workload.

 

 

—

—

— RECOMMENDED EXISTING INDEXES

— ============================

 

 

—

—

— UNUSED EXISTING INDEXES

— ============================

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_WHEN”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APEYEWHENBEACON”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APBEACONWHENNWSIG”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYENWBEACONWHENSIG”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APNWBEACONWHENSIG”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_NWBEACONWHENSIG”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APBEACONWHENNWSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APNWBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_NWBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYENWBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APBEACONWHENNWCH”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_NWBEACONWHENCH”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYENWBEACONWHENCH”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APNWWHENBEACON”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYENWWHENBEACONAP”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_WHENEYESIGHTCHANTAP”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APEYEANTBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYEANTBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYECHBEACONWHENSNR”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_APWHENNWBEACON”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_CH_ANTID_APID_DBI3”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3”;

— DROP INDEX “CARAT7  “.”I_SCAN_RADIO_EYEWHEN”;

— ===========================

—

 

0 record(s) selected.

5. Review output and determine if index recommended or if not possible SQL rewrite or further manual analysis.
6. Review unused indexes on the table in question.

[db7sign@east1 ~]$ db2 “select lastused, indname from syscat.indexes where tabname=’SCAN_RADIO'” | more

 

LASTUSED   INDNAME

 

———- ———————————————————————

———————————————————–

09/21/2017 I_SCAN_RADIO_WHEN

 

09/22/2017 I_SCAN_RADIO_APEYEWHENBEACON

 

04/20/2017 I_SCAN_RADIO_APBEACONWHENNWSIG

 

04/17/2017 I_SCAN_RADIO_EYENWBEACONWHENSIG

 

09/22/2017 I_SCAN_RADIO_APNWBEACONWHENSIG

 

03/10/2017 I_SCAN_RADIO_NWBEACONWHENSIG

 

04/20/2017 I_SCAN_RADIO_APBEACONWHENNWSNR

 

11/04/2016 I_SCAN_RADIO_APNWBEACONWHENSNR

 

09/01/2017 I_SCAN_RADIO_NWBEACONWHENSNR

 

03/25/2017 I_SCAN_RADIO_EYENWBEACONWHENSNR

 

09/15/2017 I_SCAN_RADIO_APBEACONWHENNWCH

 

09/22/2017 I_SCAN_RADIO_NWBEACONWHENCH

 

09/08/2017 I_SCAN_RADIO_EYENWBEACONWHENCH

 

09/21/2017 I_SCAN_RADIO_APNWWHENBEACON

 

09/22/2017 I_SCAN_RADIO_EYENWWHENBEACONAP

 

09/07/2017 I_SCAN_RADIO_WHENEYESIGHTCHANTAP

 

03/31/2017 I_SCAN_RADIO_APEYEANTBEACONWHENSNR

 

09/14/2017 I_SCAN_RADIO_EYEANTBEACONWHENSNR

 

03/31/2017 I_SCAN_RADIO_EYECHBEACONWHENSNR

 

09/21/2017 I_SCAN_RADIO_APWHENNWBEACON

 

09/22/2017 I_SCAN_RADIO_CH_ANTID_APID_DBI3

 

09/22/2017 I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3

 

09/22/2017 I_SCAN_RADIO_EYEWHEN

 

 

23 record(s) selected.

 

7. Review index definitions and compare to query.

Index                                                                                                                            Index                                                                                                                            Unique         Number of      Index                       Index          Null   Index           Data                                                                                                                                     Max Varchar     Xml                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  BUSINESS_TIME        Column

schema                                                                                                                           name                                                                                                                             rule           columns        type                        partitioning   keys   ID              type                                                                                                                             Hashed  Length          pattern                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              WITHOUT OVERLAPS     names

——————————————————————————————————————————– ——————————————————————————————————————————– ————– ————– ————————— ————– —— ————— ——————————————————————————————————————————– ——- ————— ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————– ——————– ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–

CARAT7                                                                                                                           I_SCAN_RADIO_WHEN                                                                                                                D                           1 RELATIONAL DATA             –              Y                    1 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +WHEN

CARAT7                                                                                                                           I_SCAN_RADIO_APEYEWHENBEACON                                                                                                     D                           4 RELATIONAL DATA             –              Y                    2 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+EYE_ID+WHEN+BEACON_OK

CARAT7                                                                                                                           I_SCAN_RADIO_APBEACONWHENNWSIG                                                                                                   D                           5 RELATIONAL DATA             –              Y                    3 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+BEACON_OK+WHEN+NW_ID+SIGNAL_LEVEL_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_EYENWBEACONWHENSIG                                                                                                  D                           5 RELATIONAL DATA             –              Y                    4 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_APNWBEACONWHENSIG                                                                                                   D                           5 RELATIONAL DATA             –              Y                    5 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_NWBEACONWHENSIG                                                                                                     D                           4 RELATIONAL DATA             –              Y                    6 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_APBEACONWHENNWSNR                                                                                                   D                           5 RELATIONAL DATA             –              Y                    7 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+BEACON_OK+WHEN+NW_ID+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_APNWBEACONWHENSNR                                                                                                   D                           5 RELATIONAL DATA             –              Y                    8 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+NW_ID+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_NWBEACONWHENSNR                                                                                                     D                           4 RELATIONAL DATA             –              Y                    9 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +NW_ID+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_EYENWBEACONWHENSNR                                                                                                  D                           5 RELATIONAL DATA             –              Y                   10 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+NW_ID+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_APBEACONWHENNWCH                                                                                                    D                           5 RELATIONAL DATA             –              Y                   11 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+BEACON_OK+WHEN+NW_ID+CHANNEL

CARAT7                                                                                                                           I_SCAN_RADIO_NWBEACONWHENCH                                                                                                      D                           4 RELATIONAL DATA             –              Y                   12 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +NW_ID+BEACON_OK+WHEN+CHANNEL

CARAT7                                                                                                                           I_SCAN_RADIO_EYENWBEACONWHENCH                                                                                                   D                           5 RELATIONAL DATA             –              Y                   13 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+NW_ID+BEACON_OK+WHEN+CHANNEL

CARAT7                                                                                                                           I_SCAN_RADIO_APNWWHENBEACON                                                                                                      D                           4 RELATIONAL DATA             –              Y                   14 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+NW_ID+WHEN+BEACON_OK

CARAT7                                                                                                                           I_SCAN_RADIO_EYENWWHENBEACONAP                                                                                                   D                           5 RELATIONAL DATA             –              Y                   15 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+NW_ID+WHEN+BEACON_OK+AP_ID

CARAT7                                                                                                                           I_SCAN_RADIO_WHENEYESIGHTCHANTAP                                                                                                 D                           7 RELATIONAL DATA             –              Y                   16 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +WHEN+EYE_ID+SIGNAL_LEVEL_APEYE+HT_MODE+CHANNEL+ANT_ID+AP_ID

CARAT7                                                                                                                           I_SCAN_RADIO_APEYEANTBEACONWHENSNR                                                                                               D                           6 RELATIONAL DATA             –              Y                   17 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+EYE_ID+ANT_ID+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_EYEANTBEACONWHENSNR                                                                                                 D                           5 RELATIONAL DATA             –              Y                   18 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+ANT_ID+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_EYECHBEACONWHENSNR                                                                                                  D                           5 RELATIONAL DATA             –              Y                   19 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+CHANNEL+BEACON_OK+WHEN+SNR_APEYE

CARAT7                                                                                                                           I_SCAN_RADIO_APWHENNWBEACON                                                                                                      D                           4 RELATIONAL DATA             –              Y                   20 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +AP_ID+WHEN+NW_ID+BEACON_OK

CARAT7                                                                                                                           I_SCAN_RADIO_CH_ANTID_APID_DBI3                                                                                                  D                           4 RELATIONAL DATA             –              Y                   21 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +CHANNEL+ANT_ID+WHEN+AP_ID

CARAT7                                                                                                                           I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3                                                                                              D                           4 RELATIONAL DATA             –              Y                   22 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID+ANT_ID+CHANNEL+AP_ID

CARAT7                                                                                                                           I_SCAN_RADIO_EYEWHEN                                                                                                             D                           2 RELATIONAL DATA             –              Y                   23 –                                                                                                                                –                     – –                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    NO                   +EYE_ID-WHEN

 

8. Get additional index usage data from tcbstats.

 

Index usage analysis from tcbstats:

 

TCB Index Stats:

Address            TableName          IID   PartID EmpPgDel   RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans      IxOnlyScns KeyUpdates InclUpdats NonBndSpts

PgAllocs   Merges     PseuDels   DelClean   IntNodSpl

 

0x00007F99D34C6EE0 SCAN_RADIO         25    n/a    0          0          9          0          0          550        328        0          0          134928     136332     0          0          0

1395

0x00007F99D34C6EE0 SCAN_RADIO         24    n/a    0          0          0          0          0          66         66         0          0          133191     133986     0          0          0

795

0x00007F99D34C6EE0 SCAN_RADIO         23    n/a    0          0          39         0          0          215        204        0          0          243578     247262     0          0          0

3645

0x00007F99D34C6EE0 SCAN_RADIO         22    n/a    0          0          31         0          0          120        120        0          0          241152     245526     0          0          0

4343

0x00007F99D34C6EE0 SCAN_RADIO         21    n/a    0          0          102        0          0          16         0          0          0          216808     220810     0          0          0

3900

0x00007F99D34C6EE0 SCAN_RADIO         20    n/a    0          1          68         0          0          0          0          0          0          518080     526974     0          0          0

8825

0x00007F99D34C6EE0 SCAN_RADIO         19    n/a    0          1          13962      0          0          310        310        0          0          427545     447655     0          0          0

6147

0x00007F99D34C6EE0 SCAN_RADIO         18    n/a    0          0          173        0          0          2          2          0          0          219850     223930     0          0          0

3907

0x00007F99D34C6EE0 SCAN_RADIO         17    n/a    0          0          39         0          0          6          6          0          0          243637     247319     0          0          0

3643

0x00007F99D34C6EE0 SCAN_RADIO         16    n/a    0          0          193        0          0          0          0          0          0          136991     139493     0          0          0

2309

0x00007F99D34C6EE0 SCAN_RADIO         15    n/a    0          0          364        0          0          128        128        0          0          134664     137137     0          0          0

2109

0x00007F99D34C6EE0 SCAN_RADIO         14    n/a    0          0          52         0          0          90         90         0          0          257142     261392     0          0          0

4198

0x00007F99D34C6EE0 SCAN_RADIO         13    n/a    0          0          214        0          0          118        118        0          0          179502     182811     0          0          0

3095

0x00007F99D34C6EE0 SCAN_RADIO         12    n/a    0          0          527        0          0          0          0          0          0          169237     172449     0          0          0

2685

0x00007F99D34C6EE0 SCAN_RADIO         11    n/a    0          1          54         0          0          0          0          0          0          438870     446363     0          0          0

7438

0x00007F99D34C6EE0 SCAN_RADIO         10    n/a    0          1          54         0          0          0          0          0          0          438871     446405     0          0          0

7479

0x00007F99D34C6EE0 SCAN_RADIO         9     n/a    0          0          507        0          0          0          0          0          0          167238     170397     0          0          0

2652

0x00007F99D34C6EE0 SCAN_RADIO         8     n/a    0          1          52         0          0          1          1          0          0          432856     440264     0          0          0

7355

0x00007F99D34C6EE0 SCAN_RADIO         7     n/a    0          0          191        0          0          4          4          0          0          175999     179221     0          0          0

3031

0x00007F99D34C6EE0 SCAN_RADIO         6     n/a    0          1          50         0          0          0          0          0          0          432808     440203     0          0          0

7344

0x00007F99D34C6EE0 SCAN_RADIO         5     n/a    0          0          38         0          0          1441       1398       0          0          244424     248014     0          0          0

3552

0x00007F99D34C6EE0 SCAN_RADIO         4     n/a    0          0          533        0          0          25         14         0          0          326689     332578     0          0          0

5356

0x00007F99D34C6EE0 SCAN_RADIO         3     n/a    0          0          1          0          0          2737       2737       0          0          133980     134785     0          0          0

804

0x00007F99D34C6EE0 SCAN_RADIO         2     n/a    0          1          292        0          0          25         25         0          0          399343     406545     0          0          0

6909

0x00007F99D34C6EE0 SCAN_RADIO         1     n/a    0          0          66704      0          0          517        500        0          0          26401      93604      0          0          0

499

0x00007F99D3AD61E0 SCAN_MANAGED       25    n/a    0          0          28         0          0          1431       1431       0          0          38589      39298      0          0

• 681

 

9. Develop recommended action based on all above analysis and recommend indexes to be dropped to application developers. Get feedback from developers on indexes in question.

 

10. Drop indexes and monitor post drop to ensure no negative impacts.

 

GOTO THE NEXT STATEMENT  and REPEAT UNTIL Tuning Complete or return not beneficial.

Phil Gunning

Product and Solution Areas: Db2 for LUW

Areas of expertise: SQL Tuning, High Availability, Performance and Tuning, Installation and Migration, Tivoli Systems Automation for Multiplatform (TSAMP), general clustering, Database Administration, training and knowledge transfer. Capacity planning and system sizing. Db2 author. Db2 Security, TLS Db2 configuration and setup.

 

Certifications and technical papers:  Db2 LUW 11.1 Database Administration, Certified Information Systems Security Professional (CISSP) 539059. IBM Champion 2009 to 2019.

Presentations: www.philipkgunning.com/presentations

Recent Engagements: Major sheet music distributor, Db2 LUW on SUSE Linux. Save our System critical SQL tuning problem and locking issues. Upgrade from Db2 10.5 to 11.1.4.4.

Outsourcing of Db2 Database Administration Managed Services for a major online gaming institution.

Major WIFI performance monitoring company with many Db2 10.5 LUW on Redhat Linux installations. Performance and Tuning problems. SQL Tuning. Configuration update and reconfiguration of memory usage and buffer pools.

Blog: www.philipkgunning.com/blog

topgun@gts1consulting.com

https://www.linkedin.com/in/phil-gunning-mba-cissp-1b5843/

https://twitter.com/db2luw

 

What version of the Db2 JDBC driver are applications using? Many times driver versions lag behind database upgrades and for the most part work okay. However with 9.7 and 10.1 out of support, and if you have upgraded recently, you will want to make sure your client drivers are at the appropriate level. You can get this info from application connection details as follows:

“SELECT
DISTINCT APPLICATION_NAME, CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_PRDID FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) as temp”

and the output shows that the JDBC version 3.0 driver version is at Db2 10.5 FP10

and use this link to determine what level driver is being used.

https://www-01.ibm.com/support/docview.wss?uid=swg21363866

 

 

#Db2 #IBMChampion. Check out our booth and get a t-shirt at IDUG EMEA in Rotterdam, Netherlands, Oct 20-24, 2019 idug.org/emea2019. My session is on Db2 SQL Coding Best Practices. Real world client examples will be presented and analyzed. SQL predicate Analysis and index design will be presented via Db2 explain facility. Learn how to identify suboptimal SQL and review and explain and develop a solution. You will learn a few different techniques to identify capture and tune high cost SQL. Predicate review and index redesign will be discussed.

My friend and Principal Offering Manager for Db2, @IBM_lindJon,  Jon Lind has a nice article on the new AI capabilities in the newly announced and available for download Db2 11.5, The AI Database. Check it out! https://www.ibmbigdatahub.com/blog/ai-database-db2-11dot5

Recently announced and available for download this week is Db2 11.5, packed with improvements to take advantage of AI and Cloud. Db2 11.5 the AI and Cloud database.

Db2 11.5 !

 

Just want to remind everyone that 10.5 end of support is April 2020. I know there are a lot of you still on 10.5 so now is the time to plan your upgrade strategy and timeline!

https://www-01.ibm.com/common/ssi/ShowDoc.wss?docURL=/common/ssi/rep_ca/8/897/ENUS918-138/index.html&request_locale=en

Data Server Manager or DSM has come a long way and is now the tool of choice for many companies for managing Db2. I get a lot of questions on how to get it and what Db2 editions are supported. Check out Peter Kohlman’s info here and you should find what you need. https://community.ibm.com/community/user/hybriddatamanagement/blogs/peter-kohlmann/2018/08/29/using-dsm-with-bundled-copies-of-db2?CommunityKey=e0967b49-f3c2-42c0-9cca-a415e3896fb3&Tab=

I have lots of clients running Db2 on Windows Server 2008 R2… End of Service for Windows 2008 R2 is 1/14/2020. Db2 11.1 and above requires Windows 2012 Standard Edition or above so if you are running a Db2 10.5 on Windows Server 2008 R2 you should be making plans to upgrade to Db2 11.1 and Windows Servers 2012 Standard Edition or preferably Windows Servers 2016 Standard Edition.. Get the details for all of this at the below links:

Db2 — http://www-01.ibm.com/support/docview.wss?uid=swg27038033#1111Work

Windows — https://www.microsoft.com/en-us/cloud-platform/windows-server-2008

Don’t miss Dale McInnis’s presentation on the Db2 logger and update from Dr. John Lind on Vnext!

Register today: https://conta.cc/2UAGYWa
9am-10:30 SESSION 1
Futures (vNext coming soon!) presented by Jon Lind

10:30-1045 BREAK

10:45-12:00 SESSION 2
IBM Streams presented by Roger Rea

12-12:45 – Networking Lunch

12:45-2:00 SESSION 3
Demystifying the Db2 Logger presented by Dale McInnis

2:00- 2:15 BREAK

2:15-2:30 IDUG Overview