Db2 z/OS Swagger doc for SP-calling REST service

Raymond Bell

Db2 z/OS Swagger doc for SP-calling REST service
Folks,

Bit of a long shot here but I'm hoping someone has some experience with these things. I'm trying to get a Swagger doc generated for a REST service that calls a Db2 z/OS (V11 NFM, if it matters) native SQLP SP, which is passing back the data in a 'dynamic result set'. And it's not working. And I'm interested to see how other DBMSs do it, or if it can be made to work on Db2 z/OS.

So we have native SQL SPs that we've turned into REST services via BIND SERVICE. Our Developer community can't do squat without a magic Swagger doc that describes the data being sent to/from the REST service/SP. Bit like a DCLGEN for children... Anyhoo, it looks like using a bit of client software like Postman can generate a Swagger doc via a GET instruction, giving the REST services' URL. And that's fine, if the data is only passed between the service and the called SP via the SP's input and/or output parms. But if the SP assembles a dynamic result set (like, say, declaring a cursor and sending back multiple data rows) then the GET instruction, and generated Swagger doc, are useless because it doesn't describe the ResultSet; it only describes the input/output parms.

So using a bit of Kiwi ingenuity I've partially got round this by ripping out the SQL in the SP's cursor definition and creating a REST service that issues this SQL statement directly, then GETting this service to produce the Swagger doc. Which is better, but still isn't entirely what the Developers want because now it describes the data being sent back but not the input and/or output parms in the original SP - because this time there's no SP.

Basically the Db2 z/OS discover service isn't providing the required metadata to the Swagger generation utility.

I've raised an Aha about this (DB24ZOS-I-959) which has just been marked as, 'kicked into the long grass'. Not ideal. But I'm curious to know if other DBMSs are better at building Swagger docs than our beloved Db2 zOS. So 2 questions:


1. Has anyone had success outside Db2 zOS in building a Swagger doc for a REST service that calls a native SQL SP (or that DBMS' equivalent thereof) that generates a dynamic result set?

2. Has anyone had success inside Db2 zOS doing the same?

Feedback gratefully received. Because at the moment this looks like a one-dot-oh-oh (as an ex BMC Product Manager used to say; where are you, Mr. Weaver?) implementation of REST services in Db2 zOS.

Cheers,


Raymond


Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (6.7k)

Geoff Davies

RE: Db2 z/OS Swagger doc for SP-calling REST service
(in response to Raymond Bell)

Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface).  Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.  

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services.  Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service.  Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this.  But this is then separate to the SP input/output schemas.  IBM support also subsequently suggested this as an (unwieldy) option.  Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em.  At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

Raymond Bell

Db2 z/OS Swagger doc for SP-calling REST service
(in response to Geoff Davies)
Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

J&#248;rn Thyssen

RE: Db2 z/OS Swagger doc for SP-calling REST service
(in response to Raymond Bell)

Hi Raymond,

The RFE is probably declined because Db2 does not know what is returned in the result set until the procedure is actually called.

You probably already thought of this:

  1. Issue GET to the service endpoint to get metadata for the endpoint and input/output parameters.
  2. CALL the stored procedure and describe the result set.
  3. Combine 1 and 2 to build the Swagger doc

I think you should be able to fully automate this in most circumstances. You obviously need some test data for the call in step 2 and hopefully the procedure can only return one type of result set (a procedure that returns a result set with INTEGERs in some cases and VARCHARs in other cases would be problematic).

An easier alternative is to call the end point and reverse engineer the Swagger doc, but data types will obviously be less accurate.

 

In Reply to Raymond Bell:

Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

Raymond Bell

Db2 z/OS Swagger doc for SP-calling REST service
(in response to Jørn Thyssen)
Hi Jørn,

I get Db2 doesn’t currently know, but it damn well can! Hell, if I can work it out Db2 should be able to. Agree it’s not obvious until the SP is actually called, but all the required data is in the SP package aka the cursor statement. Pull the SQL out, do a kind of mini-bind and construct a Swagger doc from that. I don’t know how a doc should look that has a ResponseSchema section with both the ‘Output Parameters’ and ‘Anonymous ResultSets’ sections filled in (usefully) but I can guess. Although avoiding me guessing is the point.

True, an SP that returns multiple result sets would be a ‘mare but all I’ve seen so far just have one. You’re right; the potential to return different data types – or even different data altogether – is there, so maybe limit the functionality to those SPs with DYNAMIC RESULT SETS 1. I have seen an SP at the back of a REST service with over 30 cursors (!) but they’re all identical but for the ordering of the result set.

I’m effectively doing 2. below by crafting my cursor SQL statement into its own REST service and GETting that, and ignoring 3 because I don’t trust myself to get it right. Too many squiggly brackets, double quotes and colons to mistype. Depends what you mean by, ‘describe the result set’. Via a REST command? Handraulically?

Will see if I can see how LUW does this. If one flavour of Db2 can…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 12:03
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

The RFE is probably declined because Db2 does not know what is returned in the result set until the procedure is actually called.

You probably already thought of this:

1. Issue GET to the service endpoint to get metadata for the endpoint and input/output parameters.
2. CALL the stored procedure and describe the result set.
3. Combine 1 and 2 to build the Swagger doc

I think you should be able to fully automate this in most circumstances. You obviously need some test data for the call in step 2 and hopefully the procedure can only return one type of result set (a procedure that returns a result set with INTEGERs in some cases and VARCHARs in other cases would be problematic).

An easier alternative is to call the end point and reverse engineer the Swagger doc, but data types will obviously be less accurate.



In Reply to Raymond Bell:
Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

J&#248;rn Thyssen

RE: Db2 z/OS Swagger doc for SP-calling REST service
(in response to Raymond Bell)

Hi Raymond,

By "describe" I mean:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_describeprocedure.html

I've no actual personal experience doing DESCRIBE, but your (least) favorite Db2 administration tool uses the DESCRIBE when you issue the line command "CALL" on a procedure that returns a result set. Hence I think you should be able to automate this by calling the stored procedure from your favorite programming language; issue the describe to get the metadata about the result set(s) and reverse engineer the swagger doc from that.

 

Jørn

In Reply to Raymond Bell:

Hi Jørn,

I get Db2 doesn’t currently know, but it damn well can! Hell, if I can work it out Db2 should be able to. Agree it’s not obvious until the SP is actually called, but all the required data is in the SP package aka the cursor statement. Pull the SQL out, do a kind of mini-bind and construct a Swagger doc from that. I don’t know how a doc should look that has a ResponseSchema section with both the ‘Output Parameters’ and ‘Anonymous ResultSets’ sections filled in (usefully) but I can guess. Although avoiding me guessing is the point.

True, an SP that returns multiple result sets would be a ‘mare but all I’ve seen so far just have one. You’re right; the potential to return different data types – or even different data altogether – is there, so maybe limit the functionality to those SPs with DYNAMIC RESULT SETS 1. I have seen an SP at the back of a REST service with over 30 cursors (!) but they’re all identical but for the ordering of the result set.

I’m effectively doing 2. below by crafting my cursor SQL statement into its own REST service and GETting that, and ignoring 3 because I don’t trust myself to get it right. Too many squiggly brackets, double quotes and colons to mistype. Depends what you mean by, ‘describe the result set’. Via a REST command? Handraulically?

Will see if I can see how LUW does this. If one flavour of Db2 can…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 12:03
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

The RFE is probably declined because Db2 does not know what is returned in the result set until the procedure is actually called.

You probably already thought of this:

1. Issue GET to the service endpoint to get metadata for the endpoint and input/output parameters.
2. CALL the stored procedure and describe the result set.
3. Combine 1 and 2 to build the Swagger doc

I think you should be able to fully automate this in most circumstances. You obviously need some test data for the call in step 2 and hopefully the procedure can only return one type of result set (a procedure that returns a result set with INTEGERs in some cases and VARCHARs in other cases would be problematic).

An easier alternative is to call the end point and reverse engineer the Swagger doc, but data types will obviously be less accurate.



In Reply to Raymond Bell:
Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

Raymond Bell

Db2 z/OS Swagger doc for SP-calling REST service
(in response to Jørn Thyssen)
Jørn my friend,

Nice one. DESCRIBE sounds exactly what I want Db2 to do when asked to GET a service that CALLs a native SQL SP. Unfortunately I too have no experience of issuing it (well, maybe a long time ago while programming in a company far, far away) but I know a Big Blue Vendor that should. :o)

I’m afraid the Db2 Administration Tool will have to go some to win the coveted title of Raymond’s Least Favourite Db2 Admin Tool. That title goes to something I used to call DBA-Amateur, which was a better reflection of its abilities than the actual name. Expert my derrière…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 15:12
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

By "describe" I mean:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_describeprocedure.html

I've no actual personal experience doing DESCRIBE, but your (least) favorite Db2 administration tool uses the DESCRIBE when you issue the line command "CALL" on a procedure that returns a result set. Hence I think you should be able to automate this by calling the stored procedure from your favorite programming language; issue the describe to get the metadata about the result set(s) and reverse engineer the swagger doc from that.



Jørn

In Reply to Raymond Bell:
Hi Jørn,

I get Db2 doesn’t currently know, but it damn well can! Hell, if I can work it out Db2 should be able to. Agree it’s not obvious until the SP is actually called, but all the required data is in the SP package aka the cursor statement. Pull the SQL out, do a kind of mini-bind and construct a Swagger doc from that. I don’t know how a doc should look that has a ResponseSchema section with both the ‘Output Parameters’ and ‘Anonymous ResultSets’ sections filled in (usefully) but I can guess. Although avoiding me guessing is the point.

True, an SP that returns multiple result sets would be a ‘mare but all I’ve seen so far just have one. You’re right; the potential to return different data types – or even different data altogether – is there, so maybe limit the functionality to those SPs with DYNAMIC RESULT SETS 1. I have seen an SP at the back of a REST service with over 30 cursors (!) but they’re all identical but for the ordering of the result set.

I’m effectively doing 2. below by crafting my cursor SQL statement into its own REST service and GETting that, and ignoring 3 because I don’t trust myself to get it right. Too many squiggly brackets, double quotes and colons to mistype. Depends what you mean by, ‘describe the result set’. Via a REST command? Handraulically?

Will see if I can see how LUW does this. If one flavour of Db2 can…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 12:03
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

The RFE is probably declined because Db2 does not know what is returned in the result set until the procedure is actually called.

You probably already thought of this:

1. Issue GET to the service endpoint to get metadata for the endpoint and input/output parameters.
2. CALL the stored procedure and describe the result set.
3. Combine 1 and 2 to build the Swagger doc

I think you should be able to fully automate this in most circumstances. You obviously need some test data for the call in step 2 and hopefully the procedure can only return one type of result set (a procedure that returns a result set with INTEGERs in some cases and VARCHARs in other cases would be problematic).

An easier alternative is to call the end point and reverse engineer the Swagger doc, but data types will obviously be less accurate.



In Reply to Raymond Bell:
Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

J&#248;rn Thyssen

RE: Db2 z/OS Swagger doc for SP-calling REST service
(in response to Raymond Bell)

he he, I will see what we can do to avoid that coveted title :)

Calling the stored procedure and using DESCRIBE PROCEDURE will give us a real example of the dynamic result set(s). In this case we should even be able to handle multiple result sets. However, it does require that good input parameters are provided.

In some circumstances we can derive the result set from a DESCRIBE CURSOR:

If RESULT SETS = 1 then

   Strip all cursors defined "with return" from the stored procedure

   Run DESCRIBE CURSOR and extract the result set metadata

   If all the cursors return the same result set we can build the swagger doc. If they don't we have problems... I hope this is an rare occurrence.

 

This is very similar to what you're already doing, so I am not sure it is an improvement. You "just" need to "merge" the Swagger for the input/output parameter with the Swagger doc for the result set.

In Reply to Raymond Bell:

Jørn my friend,

Nice one. DESCRIBE sounds exactly what I want Db2 to do when asked to GET a service that CALLs a native SQL SP. Unfortunately I too have no experience of issuing it (well, maybe a long time ago while programming in a company far, far away) but I know a Big Blue Vendor that should. :o)

I’m afraid the Db2 Administration Tool will have to go some to win the coveted title of Raymond’s Least Favourite Db2 Admin Tool. That title goes to something I used to call DBA-Amateur, which was a better reflection of its abilities than the actual name. Expert my derrière…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 15:12
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

By "describe" I mean:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_describeprocedure.html

I've no actual personal experience doing DESCRIBE, but your (least) favorite Db2 administration tool uses the DESCRIBE when you issue the line command "CALL" on a procedure that returns a result set. Hence I think you should be able to automate this by calling the stored procedure from your favorite programming language; issue the describe to get the metadata about the result set(s) and reverse engineer the swagger doc from that.



Jørn

In Reply to Raymond Bell:
Hi Jørn,

I get Db2 doesn’t currently know, but it damn well can! Hell, if I can work it out Db2 should be able to. Agree it’s not obvious until the SP is actually called, but all the required data is in the SP package aka the cursor statement. Pull the SQL out, do a kind of mini-bind and construct a Swagger doc from that. I don’t know how a doc should look that has a ResponseSchema section with both the ‘Output Parameters’ and ‘Anonymous ResultSets’ sections filled in (usefully) but I can guess. Although avoiding me guessing is the point.

True, an SP that returns multiple result sets would be a ‘mare but all I’ve seen so far just have one. You’re right; the potential to return different data types – or even different data altogether – is there, so maybe limit the functionality to those SPs with DYNAMIC RESULT SETS 1. I have seen an SP at the back of a REST service with over 30 cursors (!) but they’re all identical but for the ordering of the result set.

I’m effectively doing 2. below by crafting my cursor SQL statement into its own REST service and GETting that, and ignoring 3 because I don’t trust myself to get it right. Too many squiggly brackets, double quotes and colons to mistype. Depends what you mean by, ‘describe the result set’. Via a REST command? Handraulically?

Will see if I can see how LUW does this. If one flavour of Db2 can…

Cheers,


Raymond


From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 10 June 2019 12:03
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Raymond,

The RFE is probably declined because Db2 does not know what is returned in the result set until the procedure is actually called.

You probably already thought of this:

1. Issue GET to the service endpoint to get metadata for the endpoint and input/output parameters.
2. CALL the stored procedure and describe the result set.
3. Combine 1 and 2 to build the Swagger doc

I think you should be able to fully automate this in most circumstances. You obviously need some test data for the call in step 2 and hopefully the procedure can only return one type of result set (a procedure that returns a result set with INTEGERs in some cases and VARCHARs in other cases would be problematic).

An easier alternative is to call the end point and reverse engineer the Swagger doc, but data types will obviously be less accurate.



In Reply to Raymond Bell:
Hi Geoff,

Thanks for that. Good to see I’m not the only one running up against Big Blue’s first attempt to support REST services. Yes, what a great way to expose MF services to MickeyMouseLand. And yes, what a poor implementation that doesn’t let you define the ResultSet in a format the MMC (Mickey Mouse Club) members like/need.

Perhaps my Aha (https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-959) needs a few more votes to move it out of, ‘Will not implement’. Given we currently have a standard that says all REST services should ideally call a native SQL SP, and given these SPs are very likely to pass back the data in cursors/ResultSets that you can’t build the full ResponseSchema for, I’m struggling to see how they may be accepted by the off-host fraternity. I agree setting up the REST services is a doddle, once you’ve got used to Postman or, like me, the new BIND SERVICE command – once the bugs were ironed out of the random bind options acceptance/rejection. But if we have to hand-crank a half-arsed solution (your ‘view on a cursor’ method, my ‘cursor in a new SQL-issuing service’ method) which even then only does half the job… well, to bend an old UK telco ad’, the future may well be bright, but it may end up Orange. :o(

Cheers,


Raymond

From: Geoff Davies [mailto:[login to unmask email]
Sent: 08 June 2019 11:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/OS Swagger doc for SP-calling REST service


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi

I've played about with the Rest Services - and found them dead easy to set up, test etc (using Postman client) - and initially thought these are gonna be fantastic to open up mainframe data, assets (existing COBOL also easy to expose - probably often requiring a simple 'shell' program to act as interface). Further, new Native SPs - with Dynamic Resultsets, then filled a great gap whereby data needed just a bit of logic wrapped around it/formatting etc.

Alas, getting these exposed to our Web people, is proving way harder than expected - initially when trialling z/OS Connect - this was too difficult trying to get this product to meaningfully interact with Db2 Rest Services. Further, the Dynamic resultset thing of SPs - which should have been a perfect way to expose business data - oh no, the details of the open cursor as a JSON responseschema cannot be automatically generated from the Service. Of course we could create a view that matched the cursor, create a Service over this - and subsequently generate the JSON that represents this. But this is then separate to the SP input/output schemas. IBM support also subsequently suggested this as an (unwieldy) option. Methinks that although the Db2 Services themselves are indeed great, to get used properly, they need to be able to interface with the tools most likely to use 'em. At this stage our architects here are not keen to utilise them, for these reasons. We may end up using (vastly more cumbersome) CICS Web Services.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal.