DB2 - L

 View Only

  • 1.  Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 24, 2022 03:02 PM

    We are looking at potentially expanding the use of RESTful API's here at my company to access Db2 z/OS data from distributed applications as an alternative to the older and more traditional use of a Db2 Connect client to access Db2 z/OS data from a distributed app.

    Is anybody here aware of any performance benchmarks or whitepapers for distributed access to Db2 z/OS via RESTful API versus Db2 Connect that they can share?

    Or even just anecdotal experiences.

    Anything would be helpful.

    Thanks!



    ------------------------------
    Bill Gallagher
    Senior Systems Engineer, DBA
    ------------------------------



  • 2.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 28, 2022 07:59 AM
    I only did some simple testing at a customer site and found performance to be exceptional, but it certainly depends on your network and the location of the client. We had dramatic difference in performance, but that's the case for any network configuration. Basically, it was the same as calling a stored procedure over the network.
    Dan


    Original Message


  • 3.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 28, 2022 10:02 AM

    Dan, Bill,

     

    I just ran a quick search on "RESTful API" in the idug.org's DB2-L forum.  I have two questions:

     

    1.       Is it possible to use DB2 RESTful Services to participate in a RESTful API without purchasing z/OS connect?
    (I ask this because I saw someone, I think on this list, mention using DDF directly to power the invocation of a RESTful API. That seemed to bypass the premium product. This would, of course, be a Good Thing for we-who-are-trying-to-make-bricks-without-straw out in the fields.)

    2.       If the answer to (1) is "Yes," has anyone written a coding example (or better yet, a guide)?

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     




    Original Message


  • 4.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 28, 2022 11:38 AM

    Phil – RESTful APIs are far more general.  Some bullets and examples from one of earlier presentation:

     

    • Data as a Service is a programmatic interface to the data server
      • Can be a database (Db2, IMS, Oracle, SQL Server, Teradata)
      • Not necessarily database (can be VSAM or any file, Hadoop data store)
      • Implemented via RESTful calls (Representational State Transfer)
      • Uses URI (Uniform Resource Identifier)

                                          https://sureshsane.com/Db2Topics/tellmeabout?cn='temporal'

      • For databases, alternative to SQL Connect (e.g. ODBC connection)

    Hope this helps.

     

    Thx

    Suresh

     

     




    Original Message


  • 5.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 28, 2022 02:17 PM

    Hi Phil,

    Re 1: Correct, you don't need z/OS Connect. The Db2 native REST is a bit quirky. For example, your developers might be used to GET /customers?id=7 but for Db2 native REST this will be a POST /services/mycoll/getCustomer with JSON document: { "id": 7 }.
    z/OS Connect provide those kind of translations plus additional things like API discovery, Swagger docs, monitoring, and much more.

    Re 2:
    Step 1: https://www.ibm.com/docs/en/db2-for-zos/12?topic=services-enabling-db2-rest
    Step 2: https://www.ibm.com/docs/en/db2-for-zos/12?topic=services-creating-db2-rest-service
    Step 3: https://www.ibm.com/docs/en/db2-for-zos/12?topic=services-invoking-db2-rest-service

    Also note that if you have an existing application with 100 SQL statements you need to create 100 native Db2 REST services, so it is a good idea to find a way to automate creation and deployment of REST services.



    ------------------------------
    Jørn Thyssen
    Rocket Software
    2022 IBM Champion
    ------------------------------

    Original Message


  • 6.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 28, 2022 03:34 PM

    Jørn,


    Looking at things here. Thanks very much!

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     




    Original Message


  • 7.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 29, 2022 01:24 AM
    And they are all like mini-static packages with REBIND and EXPLAIN problems... Check out my AHA Idea to sort this all oiut for use RestFull guys and gals out there!


    DB24ZOS-I-1338

    Full package support for Restful services

    A restful service contains one and only one SQL statement and is, simply put, a mini-static package. It was delivered with a couple of problems. Firstly, the only way to tell if this static package is a restful service is to look at the HOSTLANG column not the TYPE column which you would expect. OK I can live with checking an extra field but then the worst, and second pain point, is that there is no support for EXPLAIN(ONLY). These restful services have access paths and we must be able to extract and check these without effecting running systems by doing a BIND SERVICE. Naturally there is also no REBIND SERVICE as all we got were BIND and FREE.

    Long story short: Make restful services the same as static packages


    The more votes the merrier!!


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich


    Original Message


  • 8.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 29, 2022 01:56 AM
    You can use the following samples to get started. It's written by James Gill from Triton and still can be used.


    https://www.triton.co.uk/db2-native-rest-api-creating-services/

    After testing with this sample one of our customers has implemneted REST API and this works very good. Performance is outstanding. 


    Times           in         HH:MM:SS.TTTT
    Elapsed Time App                0.0139
    Storeproc Elapsd                  0.0126
    CPU Time Appl                       0.0114
    TCB Time Appl                       0.0005
    TCB Time StorPrc                   0.0109
    Total DB2 Elapsed                 0.0098
    Wt Stor Proc TCB                   0.0000
    Appl DB2 Elapsed                  0.0012
    Storeproc SQL                         0.0086
    Total DB2 CPU                          0.0083
    TCB Time DB2                          0.0004

    ------------------------------
    PaulJansenATOS
    ------------------------------

    Original Message


  • 9.  RE: Performance of Db2 RESTful API versus Db2 Connect

    Posted Mar 29, 2022 04:28 AM

    Hi Phil,

     

    Just briefly, as you've had lots of good info so far (excluding wot Mr. Boxwell said as he talks nonsense; and I should know, being an expert on that subject.  Talking nonsense, that is) as REST services can indeed be only one SQL statement, we've tried to get our developers to make that statement CALL.  In other words, we try to get them to develop services in pairs; the REST service itself and the native SQL SP it calls.  That way they can put whatever they like in the SP and we get to manage it like a 'proper' program, with all the explain benefits Roy points out are missing from 'normal' REST services.  Of course, sometimes a boggo SQL statement is more appropriate, but most of ours are CALLs to SPs.

     

    And no, you don't need z/OS Connect to write and call Db2 REST services.  But I think you do to invoke CICS transactions as REST services and you do if you want the standard REST API interface calls the off-host Dev community are used to.  One man's POST is another man's PUT.  ��

     

    Cheers,

     

     

    Raymond

     




    Original Message