Skip to content

Db2 SQL Procedures — An Oldie but Goodie

A presentation I gave at the 2002 Db2 Tech Conference in Anaheim, CA.

https://slideplayer.com/amp/8146026/?fbclid=IwAR1CR6X3M3ZuTTFOIRUMQNiTAOoS3pGUXcjqejoTRQqhSyaDEI7-ybUHWLo

State of Db2 for Linux, UNIX and Windows for end of year 2022

This post is based on my perspective with over 25 years of experience with Db2 LUW. First, I still have clients continuously running Db2 for over 17 years that I have continuously supported for all those years. What kind of industries do you see using Db2 on Linux, UNIX and Windows? As previously stated, a client of mine has been running Db2 on Windows for over 17 years in a high volume OLTP environment with HADR and reads on standby. Also, a client is using Db2 to store and analyze WIFI performance data on RedHat Linux. Another client on RedHat Linux uses Db2 as their frontend for their Web Commerce portal with HADR. And finally, a client uses Db2 on many AIX servers for their health benefits and Medicare processing application with some on-prem and some in the cloud (SAAS). Some are still running Db2 10.5 and 11.1, while some have recently upgraded to 11.5.7. In my opinion, whether using Db2 on-prem or in-cloud, Db2 has an offering for you and is going strong. Oh, and the most interesting use of Db2 that I was involved in over the last 25 years was a small gold mine in Ontario using Db2 in their mining process!

Db2 13 for z/OS Function Level and APARS

Check out the below links for the latest on Db2 for z/OS..

https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-db2-function-levels

https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-new-function-apars-db2

Db2 Continues to be Rock Solid

C:\Users\Administrator\GTS_METRICS>db2pd -alldbs -hadr

Database Member 0 — Database XXXXXXXX — Active — Up 989 days 01:35:48 — Date 2022-11-18-09.43.54.177000

                        HADR_ROLE = PRIMARY
                      REPLAY_TYPE = PHYSICAL
                    HADR_SYNCMODE = SUPERASYNC
                       STANDBY_ID = 1
                    LOG_STREAM_ID = 0
                       HADR_STATE = REMOTE_CATCHUP
                       HADR_FLAGS =
              PRIMARY_MEMBER_HOST = 10.221.37.1
                 PRIMARY_INSTANCE = XXX
                   PRIMARY_MEMBER = 0
              STANDBY_MEMBER_HOST = 10.221.37.2
                 STANDBY_INSTANCE = XXX
                   STANDBY_MEMBER = 0
              HADR_CONNECT_STATUS = CONNECTED
         HADR_CONNECT_STATUS_TIME = 10/29/2022 14:51:25.842500 (1667069485)
      HEARTBEAT_INTERVAL(seconds) = 30
                 HEARTBEAT_MISSED = 2
               HEARTBEAT_EXPECTED = 146013918
            HADR_TIMEOUT(seconds) = 120
    TIME_SINCE_LAST_RECV(seconds) = 27
         PEER_WAIT_LIMIT(seconds) = 0
       LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000

LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
PRIMARY_LOG_FILE,PAGE,POS = S0793409.LOG, 23758, 135762673334534
STANDBY_LOG_FILE,PAGE,POS = S0793409.LOG, 23184, 135762670997601
HADR_LOG_GAP(bytes) = 3018
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0793409.LOG, 23184, 135762670997601
STANDBY_RECV_REPLAY_GAP(bytes) = 1781
PRIMARY_LOG_TIME = 11/18/2022 09:43:53.000000 (1668782633)
STANDBY_LOG_TIME = 11/18/2022 09:43:25.000000 (1668782605)
STANDBY_REPLAY_LOG_TIME = 11/18/2022 09:43:25.000000 (1668782605)
STANDBY_RECV_BUF_SIZE(pages) = 4096
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 18750000
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N

DB2 12 for z/OS Function level and APARS

New APARs are out for DB2 12 function levels. You can get information on them at the following links:

https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-db2-function-levels

https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-new-function-apars-db2

Db2 11.5.8 Announced

Get the details here https://www.ibm.com/cloud/blog/announcements/ibm-db2-1158-release

Running Db2 on Oracle Linux is not supported

Recently I had a client that was running on an old version of a free version of RedHat Linux which became Oracle Linux at some point. I got involved when they wanted to upgrade to Db2 11.5.7. Client purchased and installed RedHat Enterprise Linux and ported the database to it and we were able to successfully upgrade from Db2 11.1 to 11.5.7 in the new RHEL 8 environment with no problems noted. Although Db2 11.1 ran fine on Oracle Linux it is not supported…..

Data Management Console 3.1.7 is now available

This release contains enhancements to SQL tuning, job scheduling and monitoring. By using Data Management Console, combined with all the other built-in monitoring capabilities of Db2 11.x.x, DBAs and developers have an arsenal of tools at their disposal to develop, tune and deploy SQL and then monitor performance with historical capability. The ability “to go back in time” and see what was running, at a particular time is a “combat multiplier”.

https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=new-version-317

Db2 11.1 End of Support

As we end the year and get ready to begin a new year, now is a good time to plan for the upgrade of any 11.1 installations you have. Get working on that upgrade project plan and checklist. See the withdrawal announcement here: https://www.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=897&letternum=ENUS920-049.

db2detaildeadlock Event Monitor

This default deadlock event monitor has been deprecated for quite some time, since at least Db2 9.7. However, most places I run into still have it defined and it is running but many times its output is not used or it is still being used instead of the new LOCKING event monitor that took it’s place. To see how to create the new LOCKING event monitor, drop the old db2detaildeadlock and format the new event monitor data, refer to this link: https://www.ibm.com/docs/en/db2/11.1?topic=statements-create-event-monitor-locking. If Db2 was installed correctly and the DB2 Java is installed, you shouldn’t have any problem compiling and using the db2evmonfmt.java program. Follow this link on how to set it up https://www.ibm.com/docs/en/db2/11.1?topic=tables-db2evmonfmt-tool-reading-event-monitor-data.