Apologies in advance if this has been discussed/explained elsewhere. Exhaustive searches have come up empty.
We want to be able to create SQL Views over queries that use the JSON_TABLE function.
The create fails with an SQL0901, which we understand is the logger function, and can be reported to IBM.
However, we would like a sanity check first. We have been successful in the past creating views over XMLTABLE. So we thought we'd have the same luck with JSON_TABLE. Embedding the shredding in a view hides the complexity from developers.
We are at V7R2 (for at least a few months yet), DB group SF 99702 25.
We are trying to create some error logging that captures data from an IBM SQL service (QSYS2.LIBRARY_LIST_INFO, QSYS2.STACK_INFO). Those views works fine, using JSON_OBJECT, and JSON_ARRAYAGG to create a JSON document. We then store the output in a CLOB.
At the suggestion of some NO-SQL folks on the project, we are trying to store (little-used) results in JSON, then "shred" them back out to relational (to be used in a UI) when needed. Example of the failing statement:
create view admstmLBL2_library_list_json_view2
for system name ADMSTMLBL2
(ordinal_position for column ordpos,
schema_name FOR COLUMN name,
system_schema_name FOR COLUMN sys_name,
iasp_number FOR COLUMN IASP,
text_description FOR COLUMN TEXT
ordinal_position, schema_name, system_schema_name, type, iasp_number, text_description
ordinal_position INT PATH 'lax $.ORDINAL_POSITION'
,schema_name varchar(128) PATH 'lax $.SCHEMA_NAME'
,system_schema_name varchar(10) PATH 'lax $.SYSTEM_SCHEMA_NAME'
,type varchar(15) PATH 'lax $.TYPE'
,iasp_number SMALLINT PATH 'lax $.IASP_NUMBER'
,text_description varchar(50) PATH 'lax $.TEXT_DESCRIPTION'
)) AS X
By placing a semi-colon after the "AS", the statement works fine. But trying to create the view gets the SQL0901 treatment.
So if anyone can clarify, can Views be created over JSON_TABLE, or we out over our skis here?
Again, thanks in advance for any advice.