Data Henrik: Oracle

Showing posts with label Oracle. Show all posts

Thursday, June 2, 2022

Create a REST API with OpenAPI spec for your database objects

Swagger UI for my events API

Recently, I wanted to create REST API for data managed in a Db2 on Cloud database. It was needed for a chatbot project using Watson Assistant. After looking into my options, I settled on APIFlask. In this blog, I am going to share my experiences and some resources for creating a REST API with an OpenAPI specification for database objects.

Best practices for successful support cases

Successful support cases

In my day job, I work with latest technology and cloud services. In addition, I work with customers / users and help them succeed. And in my private life, I also use technical equipment. In all three of these scenarios, mostly in the first two, I often create support cases. And I like to get the issues resolved, not just support cases closed. Over the years, I came up with these best practices for successful support cases.

Db2 SQL PL book as PDF and many useful doc links

Tell the world about Db2 documentation

Yesterday was one of those days when I, again, worked as information broker. I received a question, asked around myself, passed on information that I learned, and finally handed back an answer to the originator. With this blog post, I am closing the loop and share what I learned. A, I forgot, it is about the Db2 documentation.

New! Db2 11.5 is available

Db2 11.5 is available

A quick post to inform you that Db2 11.5 has been released. Check out the What's New for Db2 Version 11.5 GA section in the Db2 Knowledge Center. You can download the GA version from the regular Download Db2 Fix Packs by Version site.

With a new release I typically go over the What's changed documentation to look for changes to registry variables, system views and deprecated functionality.

So much for the quick update. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Friday, April 27, 2018

Db2: CTE and CONNECT BY - two kinds of recursion

Writing recursive SQL

Recently, I gave a talk on SQL Recursion. One of the cool features in Db2 that I (re-)discovered for that presentation is that there are two different forms of syntax for recursive queries. One is the Common Table Expression (CTE), another the CONNECT BY syntax. Let me introduce you to the two and show you a sample query in both styles.

If you had to write recursive queries, e.g., to create a bill of material or to find dependencies in data, you would typically use a SELECT statement with a common table expression (CTE). CTEs are easily spotted by starting with the keyword WITH. Some form of temporary table is defined, then the initial data (seed) is specified followed by UNION ALL and the SELECT for the recursive part. A regular fullselect is then applied to the result of that computation.

The CONNECT BY provides another syntax for hiearachical and recursive queries. It was first introduced by Oracle and made its way into Db2 as part of the SQL compatibility features. It allows to write a, more or less, regular SELECT statement followed by a START WITH clause to define the initial data (see) and a CONNECT BY clause to specify the recursion. There are some additional keywords to access data from the prior step, the root ancestor or the (string) path from the root to a node. It is quite powerful and often shorter than a CTE. The only drawback is that you have to enable it in the Db2 compatibility vector.

The following two queries return the same result. It is the organizational structure of the departments in the SAMPLE database. The first query uses CONNECT BY and is directly taken out of the Db2 documentation.

SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME
FROM DEPARTMENT
     START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

The second query is my version of the above with a CTE. As you can see, it is longer. Readability depends on what you are used to.

WITH tdep(level, deptname, deptno) as (
    SELECT 1, CAST( DEPTNAME AS VARCHAR(40)) AS DEPTNAME, deptno
    FROM department 
    WHERE DEPTNO = 'A00'
    UNION ALL
    SELECT t.LEVEL+1, CAST(SPACE(t.LEVEL  * 4) || '/' || d.DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME, d.deptno
    FROM DEPARTMENT d, tdep t
    WHERE d.admrdept=t.deptno and d.deptno<>'A00')
SELECT level, deptname
FROM tdep

If you only knew one style of recursive query then it is time to try out the other. Both queries above work with the SAMPLE database. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Tuesday, August 1, 2017

Db2 Catalog - A Guided Tour

The title of this blog entry is how I have named a new presentation I am currently preparing. It hope that this tour will provide fun and many useful insights into understanding the Db2 catalog and make better use of it.

New data for the Db2 Catalog?!

What is an empty database? How did the entries for system tables make it into the system tables? What are packed descriptors? How can I simulate small or big data sets? Is it possible to call the catalog "data dictionary" and access it in the Oracle style? How does the Db2 Catalog relate to SQL performance and data security?

Mom, I joined the cloud! (or: Use old stuff with new stuff - DB2 federation)

Everybody is talking about Hybrid Clouds, combining on-premises resources like database systems and ERMs with services in the public or dedicated cloud. Today I am showing you exactly that, how I combined my on-prem DB2 with a cloud-based DB2 that I provisioned via Bluemix. The interesting thing is that really old technology can be used for that purpose: database federation. So relax, sit back, and follow my journey in joining the cloud...

Database Services in the Bluemix Catalog

For my small adventure I used a local DB2 10.5 and a Bluemix-based SQLDB service. The steps I followed are an extended version of what I wrote in 2013 about using three-part names in DB2 to easily access Oracle or DB2 databases. Smilar to the entry I started by enabling my DB2 instance for Federation (FEDERATED is the configuration parameter).
[hloeser@mymachine] db2 update dbm cfg using federated yes
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

MySQL-Style LIMIT and OFFSET in DB2 Queries

I was recently asked whether DB2 support MySQL-style syntax to page through query result sets. The good news is that DB2 supports LIMIT and OFFSET in addition to its own syntax. The only drawback is that the extra syntax supports needs to be enabled before it can be used. In the following I am going to show you how.

MySQL syntax in DB2

To support features offered by other database systems like Oracle, Microsoft SQL Server, Sybase, or MySQL that are incompatible with existing DB2 features, the DB2_COMPATIBILITY_VECTOR registry variable was introduced in DB2 version 9.7. The vector is used to selectively enable features and there are predefined settings named ORA (enable all Oracle features), SYB (Sybase), and MYS (MySQL). With that background knowledge we can directly get started on enabling LIMIT and OFFSET.

Introduction and resources for migrating from Oracle to DB2

Well, a boring headline for an interesting topic. Originally I had planned to title today's blog entry "Forget about Black Friday and Cyber Monday - Save big by moving to DB2". But then writing this entry has dragged on for some days...

Database Conversion Workbench

In the past weeks I have been asked several times about how to migrate off Oracle and on to DB2. Let me give you a quick overview of the technical side, for the financial part you have to ask an IBM business partner or an IBM seller. Although you can move to DB2 from database systems like Microsoft SQL Server, MySQL, and others, I will focus on Oracle because of the compatibility features built into DB2.

When moving off Oracle this could be for a SAP system (or other vendor application) or other applications ("non-SAP"). For SAP environments and applications from several other software vendors there is a (kind of) standardized process to migrate a system. The reason is that there are database-specific definitions and feature exploitations. A great example is how SAP is making use of the DB2-only BLU Acceleration to boost performance for analytic environments. Many software vendors provide tools for database migration and related processes or services.

For custom scenarios where the application code is available, a manual migration applies. The traditional barrier to a migration, the (more or less) incompatibility of products, has been torn down by adding compatibility features to DB2. Some of those features come ready to use by any user, some require specific preparation of DB2 because they may impact the traditional handling/"look and feel". The DB2 Knowledge Center has a good introduction and overview into that topic: "DB2 Compatibility Features". If you are comping to DB2 with a background in Oracle then use the Terminology Mapping to discover how products, features, and commands are named in the world of DB2.

From release to release there have been several enhancements to the SQL compatibility with database vendors such as Oracle. An overview by release can be found in the related "What's New" section of each of the recent DB2 releases:

I have to point out that the features mentioned in the linked documents are only related to the SQL language, but that there have been several other features dedicated to making a transition from Oracle to DB2 as smooth as possible. Some of them are covered in the section "Application development enhancements":

New features and enhancements related to application development in DB2 9.7
Application development improvements in DB2 10.1
The related section for DB2 10.5 (only JSON support listed)

If you prefer a book instead of the DB2 Knowledge Center, then I recommend the IBM Redbook "Oracle to DB2 Conversion Guide: Compatibility Made Easy". It gives an overview of DB2, the tools needed for a migration in a non-SAP environment, and the conversion approach. In the appending you will also find a nice terminology mapping, i.e., explaining how Oracle commands and features are named in the world of DB2.

A key tool for custom migrations is the Database Conversion Workbench (DCW). It is a plugin into the IBM Data Studio, a free tool for database administration, design, and SQL development. The DCW allows to analyze a database schema with respect to DB2 compatibility. The result is a valuable foundation for estimating the overall conversion effort. Once that is done (or not needed), the Database Conversion Workbench helps in the process of moving the database schema, database-side SQL packages, and thereafter the data from another database system to DB2. DCW also includes a package visualizer to help understand package dependencies which simplifies the code conversion. See this page for an overview and more resources around the Database Conversion Workbench.

An important DB2 feature related to the compatibility is the ability to run PL/SQL code. Several administrative PL/SQL packages ship with DB2 which can be found in the "built-in modules" section. Moreover, there are also some PL/SQL packages that can be used "as is" and are available from IBM developerWorks in the database conversion community: See here for the add-on PL/SQL packages.
That's all for now with my overview of resources for the Oracle to DB2 migration. I hope that it provides a good introduction into that (sometimes confusing) topic.

BTW: I have been covering many migration-related topics in my blog. Serge Rielau and now Rick Swagerman have provided many SQL tips for DB2 in their blog.

Monday, October 21, 2013

Heavy hitter: SYSIBM.SYSTABLES

I tried out the GET_DEPENDENCY procedure on one of the central catalog tables. SYSIBM.SYSTABLES is the catalog table behind SYSCAT.TABLES (and some more). As you can see from the output below, a total of 23 views are dependent on it.

db2 => call dbms_utility.get_dependency('TABLE','SYSIBM','SYSTABLES')

Return Status = 0

DEPENDENCIES ON SYSIBM.SYSTABLES
------------------------------------------------------------------
*TABLE SYSIBM.SYSTABLES()
*   VIEW SYSCAT .AUDITUSE()
*   VIEW SYSCAT .INDEXES()
*   VIEW SYSCAT .NICKNAMES()
*   VIEW SYSCAT .PERIODS()
*   VIEW SYSCAT .TABLES()
*   VIEW SYSIBM .CHECK_CONSTRAINTS()
*   VIEW SYSIBM .COLUMNS()
*   VIEW SYSIBM .SQLCOLPRIVILEGES()
*   VIEW SYSIBM .SQLCOLUMNS()
*   VIEW SYSIBM .SQLFOREIGNKEYS()
*   VIEW SYSIBM .SQLPRIMARYKEYS()
*   VIEW SYSIBM .SQLSPECIALCOLUMNS()
*   VIEW SYSIBM .SQLSTATISTICS()
*   VIEW SYSIBM .SQLTABLEPRIVILEGES()
*   VIEW SYSIBM .SQLTABLES()
*   VIEW SYSIBM .TABLES()
*   VIEW SYSIBM .TABLE_CONSTRAINTS()
*   VIEW SYSIBM .VIEWS()
*   VIEW SYSIBMADM.OBJECTOWNERS()
*   VIEW SYSIBMADM.PRIVILEGES()
*   VIEW SYSSTAT .COLDIST()
*   VIEW SYSSTAT .COLUMNS()
*   VIEW SYSSTAT .TABLES()
*   PACKAGE SYSIBMADM.DBMS_ALERT()
*   PACKAGE SYSIBMADM.DBMS_JOB()
*   PACKAGE SYSIBMADM.DBMS_UTILITY()
*   PACKAGE SYSIBMADM.UTL_DIR()

Do you know of another system table with more dependencies?

Friday, October 18, 2013

Friday DB2 Quiz: Not much to report...

It is Friday. How about a quick quiz? What did I do to produce the following output in DB2? Don't comment on the (in)activities you see... :)

Result set 1
--------------

TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database:                                 HL
Generated:                                10/18/2013 09:43:38
Interval monitored:                       10

================================================================================
Part 1 - System performance

Work volume and throughput
--------------------------------------------------------------------------------
                                    Per second             Total
                                    --------------------- -----------------------
TOTAL_APP_COMMITS                 0                      0
ACT_COMPLETED_TOTAL               24                     246
APP_RQSTS_COMPLETED_TOTAL         0                      3

TOTAL_CPU_TIME                    = 25439
TOTAL_CPU_TIME per request        = 8479

Row processing
    ROWS_READ/ROWS_RETURNED         = 0 (40/246)
    ROWS_MODIFIED                   = 0

Wait times
--------------------------------------------------------------------------------

-- Wait time as a percentage of elapsed time --

                                           %    Wait time/Total time
                                           --- ----------------------------------
For requests                             2    260/10659
For activities                           2    221/10512

-- Time waiting for next client request --

CLIENT_IDLE_WAIT_TIME               = 4665
CLIENT_IDLE_WAIT_TIME per second    = 466

-- Detailed breakdown of TOTAL_WAIT_TIME --

                                %    Total
                                --- ---------------------------------------------
TOTAL_WAIT_TIME               100 260

I/O wait time
    POOL_READ_TIME              0    0
    POOL_WRITE_TIME             0    0
    DIRECT_READ_TIME            80   210
    DIRECT_WRITE_TIME           0    0
    LOG_DISK_WAIT_TIME          0    0
LOCK_WAIT_TIME                0    0
AGENT_WAIT_TIME               0    0
Network and FCM
    TCPIP_SEND_WAIT_TIME        0    0
    TCPIP_RECV_WAIT_TIME        0    0
    IPC_SEND_WAIT_TIME          0    0
    IPC_RECV_WAIT_TIME          0    0
    FCM_SEND_WAIT_TIME          0    0
    FCM_RECV_WAIT_TIME          0    0
WLM_QUEUE_TIME_TOTAL          0    0
CF_WAIT_TIME                  0    0
RECLAIM_WAIT_TIME             0    0
SMP_RECLAIM_WAIT_TIME         0    0

Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --

                                      %                 Total
                                      ---------------- --------------------------
Total processing                    100               10399

Section execution
    TOTAL_SECTION_PROC_TIME           2                 220
      TOTAL_SECTION_SORT_PROC_TIME    0                 0
Compile
    TOTAL_COMPILE_PROC_TIME           1                 109
    TOTAL_IMPLICIT_COMPILE_PROC_TIME 0                 0
Transaction end processing
    TOTAL_COMMIT_PROC_TIME            0                 0
    TOTAL_ROLLBACK_PROC_TIME          0                 0
Utilities
    TOTAL_RUNSTATS_PROC_TIME          0                 0
    TOTAL_REORGS_PROC_TIME            0                 0
    TOTAL_LOAD_PROC_TIME              0                 0

Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios

Type             Ratio            Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data             100              29/0
Index            100              28/0
XDA              0                0/0
COL              0                0/0
Temp data        0                0/0
Temp index       0                0/0
Temp XDA         0                0/0
Temp COL         0                0/0
GBP Data         0                (0 - 0)/0
GBP Index        0                (0 - 0)/0
GBP XDA          0                (0 - 0)/0
GBP COL          0                (0 - 0)/0
LBP Data         100              (29 - 0)/(29 + 0)
LBP Index        100              (28 - 0)/(28 + 0)
LBP XDA          0                (0 - 0)/(0 + 0)
LBP COL          0                (0 - 0)/(0 + 0)

I/O
--------------------------------------------------------------------------------
Buffer pool writes
    POOL_DATA_WRITES      = 0
    POOL_XDA_WRITES       = 0
    POOL_INDEX_WRITES     = 0
    POOL_COL_WRITES       = 0
Direct I/O
    DIRECT_READS          = 802
    DIRECT_READ_REQS      = 32
    DIRECT_WRITES         = 0
    DIRECT_WRITE_REQS     = 0
Log I/O
    LOG_DISK_WAITS_TOTAL = 0

Locking
--------------------------------------------------------------------------------
                          Per activity                    Total
                          ------------------------------ ----------------------
LOCK_WAIT_TIME          0                               0
LOCK_WAITS              0                               0
LOCK_TIMEOUTS           0                               0
DEADLOCKS               0                               0
LOCK_ESCALS             0                               0

Routines
--------------------------------------------------------------------------------
                                Per activity              Total
                                ------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS     0                         237
TOTAL_ROUTINE_TIME            41                        10294

TOTAL_ROUTINE_TIME per invocation   = 43

Sort
--------------------------------------------------------------------------------
TOTAL_SORTS                         = 4
SORT_OVERFLOWS                      = 0
POST_THRESHOLD_SORTS                = 0
POST_SHRTHRESHOLD_SORTS             = 0

Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send          = 0          (0/0)
TCPIP_RECV_VOLUME per receive       = 0          (0/0)

Communications with local clients
IPC_SEND_VOLUME per send            = 94         (189/2)
IPC_RECV_VOLUME per receive         = 160        (482/3)

Fast communications manager
FCM_SEND_VOLUME per send            = 0          (0/0)
FCM_RECV_VOLUME per receive         = 0          (0/0)

Other
--------------------------------------------------------------------------------
Compilation
    TOTAL_COMPILATIONS                = 2
    PKG_CACHE_INSERTS                 = 16
    PKG_CACHE_LOOKUPS                 = 17
Catalog cache
    CAT_CACHE_INSERTS                 = 3
    CAT_CACHE_LOOKUPS                 = 9
Transaction processing
    TOTAL_APP_COMMITS                 = 0
    INT_COMMITS                       = 0
    TOTAL_APP_ROLLBACKS               = 0
    INT_ROLLBACKS                     = 0
Log buffer
    NUM_LOG_BUFFER_FULL               = 0
Activities aborted/rejected
    ACT_ABORTED_TOTAL                 = 0
    ACT_REJECTED_TOTAL                = 0
Workload management controls
    WLM_QUEUE_ASSIGNMENTS_TOTAL       = 0
    WLM_QUEUE_TIME_TOTAL              = 0

DB2 utility operations
--------------------------------------------------------------------------------
    TOTAL_RUNSTATS                    = 0
    TOTAL_REORGS                      = 0
    TOTAL_LOADS                       = 0

================================================================================
Part 2 - Application performance drill down

Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +
per request             WAIT_TIME % COMMITS        ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
8479                    2            0              40

Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_   TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +
HANDLE         per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
------------- -------------------   -----------    ------------- -------------
804            8479                  2              0              40

Application performance by service class
--------------------------------------------------------------------------------
SERVICE_       TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +
CLASS_ID       per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
--------       -------------------   -----------    ------------- -------------
4              0                     0              0              0
11             0                     0              0              0
12             0                     0              0              0
13             8479                  2              0              40

Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_      TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +
NAME           per request             WAIT_TIME % COMMITS        ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0                       0            0              0
SYSDEFAULTUSE 8479                    2            0              40

================================================================================
Part 3 - Member level information

- I/O wait time is
    (POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).

          TOTAL_CPU_TIME          TOTAL_       RQSTS_COMPLETED_ I/O
MEMBER per request             WAIT_TIME % TOTAL             wait time
------ ---------------------- ----------- ---------------- -----------------
0       8479                    2            3                 261


241 record(s) selected.

Return Status = 0

Data Henrik

Pages