JSON_OBJECT RETURNING Data-Type

Hi All,

If I build a JSON Object using new new DB2 JSON Publishing Functions I can
pull it into a host variable that is defined as an SQLTYPE CLOB and it
works fine - e.g.

Dcl-S JObj SQLType( CLOB : 2000000 );

Select JSON_OBJECT( 'msgType' value 'truckOrderCreate',
'tpReference' value s.TPReference,
'TrailerID' value s.TrailerID,
'toSite' value s.ToSite,
'orders' value s.Orders format JSON )
Into :JObj

From s;

But from reading the DB2 Manual ( And I'll happily confess that I find the
rail-road syntax diagrams somewhat confusing ) I expected to be able to
define my host variable as VARCHAR and use RETURNING to say that I wanted
the Data Type as VarChar instead of CLOB e.g

Dcl-S JObj VarChar( 20000 );

Select JSON_OBJECT( 'msgType' value 'truckOrderCreate',
'tpReference' value s.TPReference,
'TrailerID' value s.TrailerID,
'toSite' value s.ToSite,
'orders' value s.Orders format JSON
RETURNING VARCHAR(20000) )
Into :JObj

From s;

The Compile listing informs me that:

1. It doesn't like the attribute Varchar specified after my RETURNING and
2. JObj is Undefined or not usable.

If I try doing something similar from STRSQL:

with t1 as (
select JSON_Object( 'ord' value Strip(OrderNumber,T) ,
'cust' value Strip(CustomerName,T)
returning varchar(60) ) as data
from awdhdr )
select data, length(data) from t1

I get:

DATA LENGTH ( DATA
)
{"ord":"0000001","cust":"HENLEY CARPETS & FLOORING LTD"}
56
{"ord":"0000002","cust":"BOYNE CARPETS"}
40
{"ord":"0000003","cust":"MANOR CARPET WAREHOUSE"}
49

Which appears to work the way I expected.

I then wondered if it was because in my original statement, one of the
columns ( orders ) was already a JSON object but excluding this from the
selection didn't make any difference.

Anything obviously wrong here?
I do I just have to conclude that you can't use this functionality in the
way I was attempting with a host variable and select into?

Any thoughts appreciated,
thanks,
Craig