The new DB2 for i HTTP functions give application developers integration opportunities with a whole realm of modern business applications.

The real power of the new DB2 for i HTTP functions comes from their ability to let the IBM i talk with any number of HTTP-based web services and applications. One such HTTP-based app is Microsoft's SQL Server Reporting Services (SSRS). SSRS has rich HTTP-based APIs (e.g., SOAP and REST) that can be accessed by the IBM i HTTP functions. Therefore "i" apps can interact with the SSRS report server, including the ability to request a report's execution (in any of the available output formats, such as HTML, Excel, or PDF).

If you're new to the HTTP functions, please see the references at the end of this tip.  To get a brief overview of how to use Reporting Services in the IBM i world, see Rev Up "i" Reporting with SQL Server 2008 Reporting Services. The sample shown in this tip requires SSRS 2008 or higher.

For the sake of this article, I'll hypothesize that the IBM i is responsible for running a large month-end batch job. The last step of the batch job is to send management an email of the sales and expenses for the month. A new SSRS report with all of the bells and whistles has just been created to access DB2 to provide a dashboard summary of the entire month. Management has requested that this fancy SSRS report be attached as a PDF and distributed with the current month-end email.

Because of SSRS' versatile HTTP-based API and the powerful new HTTP functions in DB2 for i, this task to generate a PDF of the report will be a snap.

To get started, the first step will be to create a local Windows user account on the SSRS box (assuming SSRS is running in native mode) and assign the user permissions to access the report server and report. The local user I built for this example is called RSUser. In this article, the new month-end report is simply named TestReport, and RSUser was granted execution permission. RSUser will be the login used by the IBM i to log in to SSRS.

By default, only Windows users (using Windows authentication) are allowed to access a reporting services app. In order to access reporting services from the IBM i, the SSRS instance should have "basic authentication" enabled. Recall that basic authentication is used to allow an HTTP client to pass a user name and password to an HTTP server in plain text (unless HTTPS is used). As a security measure, basic authentication is disabled by default in SSRS because it is easy to sniff passwords, and besides, Microsoft would like the world to use Windows Authentication for everything.

To enable basic authentication in SSRS, locate and edit the rsreportserver.config XML file. (Microsoft didn't create an easy way to turn this option on). This URL explains how to do this for SQL Server 2008 Reporting Services & Higher: http://msdn.microsoft.com/en-us/library/cc281309.aspx.

Now that SSRS has been configured to allow authentication via a simple HTTP-based login, the next step is to figure out how to create the report using a simple HTTP request. After viewing a Microsoft article on how to integrate Reporting Services with other applications, it is apparent that creating the report as a PDF is as simple as building a URL with the following format:

http://servername:port/ReportServer/Pages/ReportViewer.aspx?ReportName&rs:Command=Render&rs:Format=pdf.

Substituting the reporting server name with my SSRS installation's IP of 172.29.97.8, the port of 8080 and report name as TestReport, the URL becomes:

http://172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?TestReport&rs:Command=Render&rs:Format=pdf.

The remaining parameters and their constants are rs:Command=Render and rs:Format=pdf. These values instruct SSRS to render the report in PDF format. See the "Report Server Commands" section in the URL Access Parameter Reference documentation for more information on these commands.

Note that some parts of the URL may vary, depending on how the report server is configured and where the report is placed within the folder structure. In this example, TestReport is in the root folder. Further, although beyond the scope of this article, it is possible to pass values to the report parameters via the URL. For more info, see Pass a Report Parameter Within a URL.

For this example, the local Windows user account and password are RSUser / radical8!. The credentials can easily be added to the URL (as long as there are no escapable characters) so that the final URL is:

http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?TestReport&rs:Command=Render&rs:Format=pdf.

With the URL built, it's easy to test the report rendering by pasting the URL in a browser. Preferably test with something other than Internet Explorer so that Windows authentication isn't used; that way, you can tell whether the credentials are working correctly. When testing, the report server will either return an error when there is an invalid URL (or value in the URL) or return a PDF document. The URL can be tweaked and tested in the browser.

Once tested, the only thing left to do is insert the tested URL as a parameter to one of the DB2 HTTP functions as follows:

-- Table function usage -- return PDF as a BLOB
SELECT *
FROM TABLE(
SYSTOOLS.HTTPGETBLOBVERBOSE(
'http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?%2fTestReport&rs:Command=Render&rs:Format=pdf','')) WS;

-- Scalar function usage - return PDF as BLOB 
SELECT data FROM (VALUES(SYSTOOLS.HTTPGETBLOB(
'http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?%2fTestReport&rs:Command=Render&rs:Format=pdf',''))) WS(data);

Once the data has been returned as a BLOB, it can be dumped directly to the IFS with the help of an embedded SQL program using the BLOB_FILE data type. Reusing the same code from the prior tip (with the URL and IFS file name changed), we have this:

D PDF              S                SQLTYPE(BLOB_FILE)
D WebURL           S      255   Varying      
/Free
   WebURL='http://RSUser:radical8!@172.29.97.8:8080/-
          ReportServer/Pages/ReportViewer.aspx?-
          %2fTestReport&rs:Command=Render&rs:Format=pdf';  
   PDF_Name='/tmp/MonthEndReport.pdf';
   PDF_NL=%Len(%TrimR(PDF_Name));
   PDF_FO=SQFOVR; // Overwrite

   Exec SQL
      SELECT SYSTOOLS.HTTPGETBLOB(:WebURL,'') PDF_DATA 
      INTO :PDF
      FROM SYSIBM.SYSDUMMY1;

       *InLR=*On;
/End-Free

The rendered report is placed on the IFS so it can be attached to an email. Including this last step in the month-end batch job is all that's required to get the new report in PDF format from SSRS.  Once on the IFS, it can be added as an email attachment and the work is done.

Don't forget, as I mentioned in Part 2 of the series, for safety it is better to pass the user name and password in the Authentication header field instead of placing it directly in the URL.

In summary, the new DB2 for i HTTP functions give application developers new integration opportunities with a whole realm of modern business applications—SSRS being just one of many. The process in this article gives an overview of how to integrate with other HTTP-based systems:

1. Identify an HTTP-based API your IBM i needs  (web services, REST, etc.).
2. Activate basic authentication, if necessary.
3. Often, it's easy to run a request right in a browser so you can verify how it works on the fly.
4. Incorporate the HTTP request into your application using one of the new HTTP functions.

Integrating with other applications is becoming increasingly easy to do using HTTP requests. These new functions can help tighten the links between disparate systems and reduce the cost of integration.

References

• Stairway to SQL Server Reporting Services—This tutorial on reporting services published by SQLServerCentral.com provides a great overview of what SSRS can accomplish. Registration is free and required.
• Rev Up "i" Reporting with SQL Server 2008 Reporting Services—This tutorial shows a simple SSRS report and covers some of the issues you may encounter when working with DB2 for i.
• Reporting Services Tutorials—This is Microsoft's latest SSRS tutorial for SQL Server 2012.
• Accessing web services using IBM DB2 for i HTTP UDFs and UDTFs
• Check Out the New DB2 for i HTTP Functions
• DB2 for i HTTP Functions, Part 2: Request and Response Header Fields

Also Read

Open Source Starter Guide for IBM i Developers: Language Basics

Open Source Starter Guide for IBM i Developers: Ruby and IBM i, An Introduction

Open Source Starter Guide for IBM i Developers: i Object!