IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-08-31T23:04:03Z by artblose
artblose
artblose
2 Posts

Pinned topic Create View with JSON_TABLE fails SQL0901

‏2019-08-31T00:57:08Z |

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,
  entryType                             ,
  iasp_number        FOR COLUMN IASP,
  text_description   FOR COLUMN TEXT
  ) AS 
 select 
    ordinal_position, schema_name, system_schema_name, type, iasp_number, text_description 
  FROM 
  admStmLog LOG,
  JSON_TABLE(
     LOG.jsonliblst, 
    'lax $.LIBL[*]'
    COLUMNS(
      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.
 

 

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Create View with JSON_TABLE fails SQL0901

    ‏2019-08-31T11:28:28Z  

    Views can be created over JSON_TABLE.

    I created and used views over JSON_TABLES in several programs and have multiple running examples that I show in my sessions and workshops.

    Birgitta

  • artblose
    artblose
    2 Posts

    Re: Create View with JSON_TABLE fails SQL0901

    ‏2019-08-31T23:04:03Z  

    Brigitta,

    Thanks for the quick response. I have learned a lot from your Developerworks posts in the past. I did a web search and found a presentation where you do show JSON_TABLE wrapped in a view. So, it looks like we have a PMR to file with IBM.

     

    Thanks again,

     

    Art Blose