ibm db2 58009_将具有IBM Bluemix Weather Web服务记录集的IBM DB2 for i表加入-优快云博客

本文介绍如何在IBM DB2 for i中使用httpGetCLOB和JSON_TABLE函数调用Bluemix上的Weather Company Data服务，实现天气数据的检索与解析，以及与DB2表的联接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在当今世界，API可以提供对防火墙以外越来越多的数据和功能的访问。 API是当今数字经济中战略价值的来源。

天气很好地说明了API为企业带来的附加价值：构建一个由天气驱动的解决方案以更好地制定决策可以解决因天气对结果有重大影响的业务问题。例如，零售商店可以在晴天销售更多商品，因此应相应更改销售目标。

但是，将这些Web服务与现有业务数据和应用程序集成起来有多困难？您如何将核心业务关系数据库中的数据与Web服务返回的数据结合起来？

IBM®DB2®for i提供了所有功能，可轻松开发以数据为中心的解决方案以访问API，并在以JSON和DB2表形式返回的数据之间建立连接。

本文介绍了如何在IBMBluemix®中创建Weather Company Data服务，并提供了示例，使用JSON_TABLE和httpGetCLOB函数直接从DB2 for i使用它。

图1. DB2 for i和Bluemix服务之间的联接概述

你需要什么

要尝试这些示例并创建和运行提供的DB2 for i SQL语句，您需要：

一个Bluemix帐户（注册您的免费试用帐户，或者如果已经拥有一个帐户，则登录到Bluemix ）
连接到IBM i 7.2最低版本的DB2 for i SQL编辑器。
例如，您可以使用嵌入式的IBM i Access客户端解决方案（ACS）和SQL编辑器。

创建天气公司数据服务实例

为了能够从DB2 for i获取天气数据，您需要在Bluemix中创建Weather Company Data服务的实例。该服务使您可以将The WeatherCompany®的天气数据集成到您的应用程序中。

免费计划使您每分钟最多可以对The Weather Company进行10次调用，每个Bluemix帐户最多可以进行10,000次API调用。您可以在应用程序中测试数据，而不受支持的地理位置（检查Bluemix服务以了解支持的国家或地区），预测类型或时间序列观察值的限制，并且仅对呼叫次数有限制。如果您需要更多，可以订阅基本，标准或高级计划。

执行以下步骤在Bluemix中创建Weather Company Data服务实例：

登录到Bluemix， https： //console.ng.bluemix.net/
选择您要工作的区域和空间。
导航到目录，然后单击“ 天气公司 数据” （“ 数据和分析”类别）
图2. Bluemix目录中的Weather Company Data服务
创建一个实例。
在“ 服务名称”字段中，输入新的服务名称或保留建议的名称，例如本示例中的Weather Company Data-dd 。在Connect to字段中，保留文本Leave Unbound ，因为我们不会将此服务绑定到Bluemix应用程序。
图3.天气公司数据服务创建页面
点击创建。
检索凭据。
在Bluemix仪表板中，检索创建的服务，然后单击它。接下来，点击服务凭据，然后点击查看凭据。然后，复制URL参数，因为它将在以后用于从SQL语句调用您自己的weather实例。

图4.天气公司数据服务凭证

另外，请记住用户名和密码，因为您可能需要用它来测试在线Weather Company Data API。

天气公司数据API

天气数据可通过代表性状态传输（REST）API获得。

您可以访问当前状况，天气预报（每日，每小时，日内等），历史数据或天气警报。

如果您已在美国南部地区创建了天气公司数据服务，则所有API都有文档记录，并且可以在https://twcservice.mybluemix.net/rest-api/网站中进行测试（否则，请使用https://twcservice.eu -gb.mybluemix.net/rest-api或https://twcservice.au-syd.mybluemix.net/rest-api ）。

图5. API的天气公司数据服务列表

这些API在线记录良好，其中包含您可能需要的有关方法，路径，参数，返回的数据，响应消息等信息。

在下图中，如果返回码= 200（OK），则可以看到返回的API路径和数据模型。

图6.当前条件API文档

使用Weather Company Data服务凭据中的用户名和密码，您可以测试所有API并查看返回的数据。

这是在线测试的结果，提供了给定纬度和经度的当前天气观测结果。

图7.当前条件API在线测试示例

我们将在本文后面的示例1和2SQL请求中的“ 请求URL”字段中使用URL。

https://twcservice.eu-gb.mybluemix.net/api/weather/v1/geocode/<LAT>/<LON>/observations.json?language=en-US

现在已经实例化了Weather Company Data服务，我们可以使用自己的凭据来调用其API。我们准备创建第一个SQL请求，以从DB2 for i调用API。

使用httpGetCLOB函数调用REST Web服务

要从Weather Company Data Web服务检索数据，请使用DB2 for i httpGetCLOB SQL函数。 httpGetCLOB是SYSTOOLS标量函数，使用GET方法检索作为字符大对象（CLOB）的资源的表示形式。

表1. httpGetCLOB函数参数

名称	HTTP方法	返回类型	参量	参数类型
httpGetCLOB	得到	CLOB（2G）	网址	VARCHAR（2048）
			HTTP头	CLOB（10K）

httpGetCLOB函数接受两个输入参数URL和HTTPHEADER ，并返回一个值。

网址（输入）

URL参数包含天气数据的完整路径，包括身份验证数据（您的用户名和密码）和天气参数。

例如：

https://<username>:<password>@twcservice.eu-gb.mybluemix.net/api/weather/v1/geocode/<LAT>/<LON>/observations.json?language=en-US

该URL允许从Bluemix Europe（伦敦= eu-gb.mybluemix.net）中托管的用户<username>的Weather Company Data服务获取由<LAT> / <LON>标识的位置的当前天气状况。

HTTPHEADER （输入）

HTTPHEADER参数的值可以为NULL或空字符串，这将导致使用默认属性。仅当必须使用非默认属性或必须将标头字段发送到服务器时，才需要提供显式标头。

要查询Weather Company Data API，请使用默认属性NULL 。

返回值

Weather Company Data API在JSON对象中返回天气数据。 JSON对象表示为键值对列表。该对象格式取决于调用的API。请参阅在线文档以了解将为每个API获取的JSON格式。

如果您需要有关如何使用此功能的更多信息，请阅读Yi Yuan和Nick Lawrence撰写的IBMDeveloperWorks®文章“将Web服务合并到SQL查询中，在IBMDB2®for i上引入HTTP函数 ”。

从7.1版开始，具有DB2 PTF组SF99701Level 23和Java™1.6或更高版本（5761-JV1选项11、12、14或15）的IBM DB2 for i提供了DB2 for i HTTP功能。

使用JSON_TABLE函数解析JSON对象

需要解析通过httpGetCLOB检索到的JSON对象， httpGetCLOB在SQL中使用。这是使用JSON_TABLE函数完成的。

JSON_TABLE表函数从SQL / JSON路径表达式的评估返回结果表。 SQL / JSON行路径表达式的结果序列中的每一项都代表结果表中的一行或多行。

JSON_TABLE在IBM DB2 for i（版本7.2和7.3）上可用。它随附于2016年11月发布的7.2数据库组PTF SI99702级别14和7.3数据库组PTF SI99703级别3。

如果需要有关如何使用此功能的其他信息，请阅读John Eberhard JSON_TABLE的IBM DeveloperWorks文章“ 功能强大的JSON_TABLE函数：在IBM DB2 i中使用JSON信息 ”。

例子

在以下示例中，我们将使用零售演示中的样本DB2表（名为storebot.store ）。

如果需要，可以使用以下SQL语句创建它：

CREATE TABLE STOREBOT.STORE ( 
	           STOREID INTEGER GENERATED ALWAYS AS IDENTITY ( 
	              START WITH 1 INCREMENT BY 1 
	              NO MINVALUE NO MAXVALUE 
	              NO CYCLE NO ORDER 
	              CACHE 20), 
	           NAME VARCHAR(50) DEFAULT NULL, 
	           ADDRESS1 VARCHAR(50) DEFAULT NULL, 
	           ADDRESS2 VARCHAR(50) DEFAULT NULL, 
	           POSTALCODE VARCHAR(10) DEFAULT NULL, 
	           CITY VARCHAR(50) DEFAULT NULL, 
	           COUNTRY VARCHAR(50) DEFAULT NULL, 
	           LAT DOUBLE PRECISION DEFAULT NULL, 
	           LON DOUBLE PRECISION DEFAULT NULL 
	       )

您可以使用SQL INSERT语句在此表中添加行。例如，如果要插入两个位于蒙彼利埃（法国）和罗切斯特（美国MN）的商店：

INSERT INTO STOREBOT.STORE 
            (NAME, ADDRESS1, ADDRESS2, POSTALCODE, CITY, COUNTRY, LAT, LON) 
            VALUES('Store_1','Rue de la Vieille Poste', NULL, '34000', 'Montpellier',
                   'France', 43.614934, 3.908162);
        INSERT INTO STOREBOT.STORE 
            (NAME, ADDRESS1, ADDRESS2, POSTALCODE, CITY, COUNTRY, LAT, LON) 
            VALUES('Store_2', '37th ST NW', NULL, 'MN 55901', ' Rochester',
                   USA, 44.061604, -92.504532);
        COMMIT;

浏览表，我们可以看到每个商店都有要使用的Weather Company Data API所要求的经度和纬度。

图8. storebot.store表内容（样本）

让我们从ACS SQL编辑器开始。当然，您可以使用任何其他DB2 for i兼容SQL编辑器。

示例1：从DB2 for i获取当前的天气状况（不与DB2表联接）

在第一个示例中，我们将调用Weather Company Data API来获取给定纬度和经度的当前条件。尚未与DB2表联接。

我们可以使用清单1所示SQL语句来获取当前的天气状况。

清单1.给定纬度和经度的当前天气情况

SELECT * 
        FROM JSON_TABLE(
                 SYSTOOLS.HTTPGETCLOB('https://<username>:<password>@' ||
                         'twcservice.eu-gb.mybluemix.net:443/api/weather/v1/geocode/' ||
                         '/43.617383/3.907809/observations.json?language=en-US&units=m',''),
                 '$' 
                 COLUMNS( OBSERVATION VARCHAR(100) PATH '$.observation.wx_phrase',
                                   FEELS_LIKE VARCHAR(100) PATH '$.observation.feels_like')
             ) AS X

在此语句中，使用SYSTOOLS 。 httpGetCLOB函数用于从气象公司数据服务API中检索JSON对象，仅针对一个地理位置，直接在服务URI中指定（纬度= 43.617383，经度= 3.907809）。然后将该对象用作JSON _ TABLE函数的输入。

图9. ACS SQL编辑器：获取给定纬度和经度的当前条件

JSON_TABLE接受以下三个参数：

JSON对象：在此示例中，可以使用SYSTOOLS检索JSON对象。调用天气公司数据服务API的httpGetCLOB函数
路径：它是用于在JSON对象中定位信息的表达式。
列列表：包括列名，数据类型以及我们要从JSON对象提取的JSON值SQL / JSON路径。在此示例中，我们仅检索两列， Observation和Feels_like （感知温度）。

如果在调用API时发生错误，您将从SYSTOOLS.httpGetCLOB收到服务错误消息。

例子：

HTTP返回码= 401：身份验证错误（错误的ID和密码）

图10.天气公司数据API返回码401

HTTP返回代码= 400：错误的请求，语法错误（缺少经度参数）

图11.天气公司数据API返回码400

您可以在Weather Company Data API网站上找到API错误代码列表。

图12.天气公司数据API返回代码列表

示例2：从DB2 for i获取当前天气状况（与DB2表一起）

在前面的示例中，纬度和经度参数是常量。

在第二个示例中，我们想为SQL请求中的每个选定行调用Weather Company Data API，类似于DB2 for i表和Web服务之间的联接。

我们必须使用STOREBOT.STORE作为主表，以及前面提到的（示例1）与辅助表相同的JSON_TABLE编写联接SQL请求。

但是，加入条件在哪里？在Weather Company Data API调用中，我们需要用lat和lon列替换纬度和经度常量（如清单2所示）。

清单2. DB2表中商店的当前天气状况

SELECT storeid, name, city, country, lat, lon, observation, feels_like
        FROM storebot.store A, 
             JSON_TABLE (
                      SYSTOOLS.HTTPGETCLOB('https://<username>:<password>@' ||
                                           'twcservice.eu-gb.mybluemix.net:443' || 
                                           '/api/weather/v1/geocode/' ||
                                           trim(char(cast(a.lat as decfloat))) || '/' || 
                                           trim(char(cast(a.lon as decfloat))) || '/' ||  
                                           'observations.json?language=en-US&units=m',''),
                     '$' 
                     COLUMNS( OBSERVATION VARCHAR(100) PATH '$.observation.wx_phrase',
                              FEELS_LIKE VARCHAR(100) PATH '$.observation.feels_like') 
                ) AS X 
        WHERE storeid = 13
        OR storeid = 5;

您可能会注意到，我们必须转换这些列（ trim(char(cast(a.lat as decfloat))) ）以匹配Web服务所需的格式和数据类型，而不管其中的列的格式和数据类型如何DB2。在API调用中，它将用作路径中包含的字符（URI）。

图13. ACS SQL编辑器：从SQL请求中获取每个选定行的当前条件

请注意，您在Bluemix中为Weather Company Data服务订阅的计划（免费，标准或高级）在呼叫数量方面有限制。根据SELECT语句返回的行数和运行的请求数，您可能需要切换计划。

示例3：从DB2 for i获取3天的天气预报（与DB2表的内部连接）

JSON_TABLE函数允许您使用JSON对象中嵌入的数组元素检索多行：然后在JSON_TABLE函数中使用嵌套的列定义。

我们可以使用它来检索接下来三天（当前天+三天）的天气。

如果我们查看Weather Company Data API文档，则可以看到当日+ 3天预报的JSON结构：

{
  "metadata": {
      . . .
  },
  "forecasts": [                                           <- “forecasts” = Table of Days
    { 	                                                   <- Day 1 (current)
      "class": "fod_long_range_daily",
      "expire_time_gmt": 1486490521,
      "fcst_valid": 1486468800,
      "fcst_valid_local": "2017-02-07T07:00:00-0500",
      "num": 1, 
      "max_temp": 64,
      "min_temp": 54,
      "torcon": null,
      "stormcon": null,
      "blurb": null,
      "blurb_author": null,
      "lunar_phase_day": 11,
      "dow": "Tuesday",
      "lunar_phase": "Waxing Gibbous",
      "lunar_phase_code": "WXG",
      "sunrise": "2017-02-07T07:29:26-0500",
      "sunset": "2017-02-07T18:14:58-0500",
      "moonrise": "2017-02-07T15:05:27-0500",
      "moonset": "2017-02-07T04:25:08-0500",
      "qualifier_code": null,
      "qualifier": null,
    
  "narrative": "Thunderstorms, some may contain heavy rain.",
      "qpf": 0.66,
      "snow_qpf": 0,
      "snow_range": "",
      "snow_phrase": "",
      "snow_code": "",
      "night": {. . .}  	                              <- “night” = night data of day 1
      "day": {. . .}   	                                  <- “day” = day data of day 1
     }
    {}  	                                              <- Day 2
    {}  	                                              <- Day 3
    {}  	                                              <- Day 4
  ]
 }

查看一天中的一天数据，我们可以看到要为每个商店显示的数据，并且必须在JSON_TABLE的COLUMNS部分中进行JSON_TABLE 。

. . . 
  "night": {. . .}  
  "day": {
        "fcst_valid": 1486468800,
        "fcst_valid_local": "2017-02-07T07:00:00-0500",    	                  <- Date
        "day_ind": "D",
        "thunder_enum": 2,
        "daypart_name": "Today",
        "long_daypart_name": "Tuesday",
        "alt_daypart_name": "Today",
        "thunder_enum_phrase": "Thunder expected",
        "num": 1,
        "temp": 64,    	                                                      <- Temperature
        "hi": 63,
        "wc": 60,
        "pop": 90,
        "icon_extd": 402,
        "icon_code": 4,
        "wxman": "wx6500",
        "phrase_12char": "Hvy T-Storms",
        "phrase_22char": "Heavy Thunderstorms",
        "phrase_32char": "Heavy Thunderstorms",	                              <- Observation
        "subphrase_pt1": "Heavy",
        "subphrase_pt2": "T-Storms",
        "subphrase_pt3": "",
        "precip_type": "rain",
        "rh": 74, 
        . . .
  }     
  . . .

在清单3的JSON_TABLE部分的COLUMNS / NESTED部分中，您可以看到我们声明了JSON表forecast 。 JSON_TABLE函数将在JSON forecast表中按元素返回一行。每行将包含三个字段： day.fcst_valid_local ， day.phrase_22char和day.temp 。

清单3. DB2表中商店的三天天气预报

SELECT storeid, name, city, country, lat, lon, 
               SUBSTR(dailydate, 1, 10) as "Date", daylyobs, dailytemp 
        FROM storebot.store A, 
            JSON_TABLE(
                       SYSTOOLS.HTTPGETCLOB('https://<username>:<password>@' ||
                                            'twcservice.eu-gb.mybluemix.net:443' || 
                                            '/api/weather/v1/geocode/' ||
                                            '/' || trim(char(cast(a.lat as decfloat))) || 
                                            '/' || trim(char(cast(a.lon as decfloat))) ||
                                            '/forecast/daily/3day.json?language=en-US&units=m',''),
                       '$'
                       COLUMNS( 
                               NESTED '$.forecasts[*]' 
                                      COLUMNS ( DAILYDATE VARCHAR(22)PATH '$.day.fcst_valid_local' , 
                                                DAILYOBS VARCHAR(22) PATH '$.day.phrase_22char' , 
                                                DAILYTEMP VARCHAR(10) PATH '$.day.temp' 
                                      ) 
                       ) 
                ) AS X 
        WHERE storeid = 13 OR storeid = 5;

由于forecast表中有四个元素（今天的情况+三天），因此每个商店将获得四行要连接的行。

图14. ACS SQL编辑器：从SQL请求中为每个选定的行获取三天的天气预报

结果显示，对于每个商店，我们都有Weather Company Data服务中的条件日期，观测值和温度列，以及storebot.store表中的其他列。

摘要

在的力量httpGetCLOB SQL函数组合JSON_TABLE功能提供了一种简单的方法来检索和REST Web服务解析数据，并加入与传统的DB2 JSON数据我关系数据。由于SQL是标准的数据库查询语言，因此您可以在程序（例如RPG，PHP，Node.js等）或WebQuery IBM DB2 Web Query for i报表中使用此语句。

因此，让我们开始现代化您的IBM i应用程序，并通过API集成加速您的数字化转型！

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-DB2-i-table-Bluemix-trs/index.html