LVARCHAR
Hi,
I am running this query and I am getting this error which does not make sense.
THis query has a column cnote_desc LVARCHAR (10000). And I am trying to run
this query from one instance to another instance on same server. We are on IDS
10.0 FC7 and HP-UX 11.11. If I run this query without cnote_desc column it
works fine. So something to do with LVARCHAR that does not go across
databases....I think.
SELECT cnote_id, body_loc_cd, cnote_typ_cd, event_dt, event_tm , critical_flg,
prnt_ok_flg, updt_usr_id, updt_dt,cnote_desc FROM cis@online15:cnote
SQL Error (-999): Not implemented yet.
Thanks,
Sunita
SUNITA RAINA wrote:
> Hi,
>
This one pops up every few months: LVARCHAR, BINARY, BOOLEAN, and a few
other 'built-in' data types are implemented as UDTs. Prior to IDS 9.40
you could not use User Defined Types (UDTs) in distributed queries
between databases even if both databases resided in the same instance,
IMS. As of 9.40 it is possible to use UDTs across databases within the
same instance but not between databases in different instances. In 11.10
and persumable later versions it is finally possible to use built-in and
system specific UDTs in distributed queries across instances, so long as
the UDT is defined on the local server executing the query. The
following from the introduction to the 11.10xC2 manuals:
*Enhanced Data-Type and UDR Support in Cross-Server Distributed
Operations
*
Earlier releases of Informix® Dynamic Server restricted the remote
execution of UDRs in databases of other Dynamic Server instances to
SPL routines that the EXECUTE FUNCTION or EXECUTE PROCEDURE
statement invoke explicitly, and to SPL routines that queries and
other DML operations invoked implicitly. This release extends
support for UDRs in cross-database and cross-server distributed
operations to most contexts where a UDR is valid in the local
database. In addition, external routines written in the C or Java
languages are now valid in any distributed operation where an SPL
routine is valid. This release also extends the data types that are
valid as parameters or return values of cross-server UDRs, which
were formerly restricted to non-opaque built-in SQL data types, by
supporting these additional data types: BOOLEAN LVARCHAR
DISTINCT of built-in types that are not opaque DISTINCT of BOOLEAN
DISTINCT of LVARCHAR DISTINCT of the DISTINCT types listed
above. These data types can be returned by SPL, C, or Java language
UDRs that use these data types as parameters or as return values, if
the UDRs are defined in all the participating databases. Any
implicit or explicit casts defined over these data types must be
duplicated across all the participating Dynamic Server instances.
The DISTINCT data types must have exactly the same data type
hierarchy defined in all databases that participate in the
distributed query.
> I am running this query and I am getting this error which does not make
sense.
> THis query has a column cnote_desc LVARCHAR (10000). And I am trying to run
> this query from one instance to another instance on same server. We are on
IDS
> 10.0 FC7 and HP-UX 11.11. If I run this query without cnote_desc column it
> works fine. So something to do with LVARCHAR that does not go across
> databases....I think.
>
> SELECT cnote_id, body_loc_cd, cnote_typ_cd, event_dt, event_tm ,
critical_flg,
> prnt_ok_flg, updt_usr_id, updt_dt,cnote_desc FROM cis@online15:cnote>
> SQL Error (-999): Not implemented yet.
>
> Thanks,
> Sunita
>
Art S. Kagel
Oninit
>
>
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
Art S. Kagel (Oninit) wrote:
Oops, those quotes are for UDRs, however, UDT rules are similar. In the
IDS 10.00 manual on page 4-28 it states:
DISTINCT column values cannot be retrieved by a distributed query (nor
modified by INSERT, DELETE, or UPDATE operations on a remote database)
unless the base type is a built-in data type, and all of the tables that
the DML operation accesses are in databases of the local Dynamic Server
instance.
but the 11.10 manual on page 4-32 amends this allowing remote access to
UDT as long as certain conditions are met, which amount to 'both
databases in whatever server instance(s) they live know about the the
data type'.
Art S. Kagel
> SUNITA RAINA wrote:
>
>> Hi,
>>
>>
>
> This one pops up every few months: LVARCHAR, BINARY, BOOLEAN, and a few
> other 'built-in' data types are implemented as UDTs. Prior to IDS 9.40
> you could not use User Defined Types (UDTs) in distributed queries
> between databases even if both databases resided in the same instance,
> IMS. As of 9.40 it is possible to use UDTs across databases within the
> same instance but not between databases in different instances. In 11.10
> and persumable later versions it is finally possible to use built-in and
> system specific UDTs in distributed queries across instances, so long as
> the UDT is defined on the local server executing the query. The
> following from the introduction to the 11.10xC2 manuals:
>
> *Enhanced Data-Type and UDR Support in Cross-Server Distributed
>
> Operations
>
> *
>
> Earlier releases of Informix® Dynamic Server restricted the remote
>
> execution of UDRs in databases of other Dynamic Server instances to
>
> SPL routines that the EXECUTE FUNCTION or EXECUTE PROCEDURE
>
> statement invoke explicitly, and to SPL routines that queries and
>
> other DML operations invoked implicitly. This release extends
>
> support for UDRs in cross-database and cross-server distributed
>
> operations to most contexts where a UDR is valid in the local
>
> database. In addition, external routines written in the C or Java
>
> languages are now valid in any distributed operation where an SPL
>
> routine is valid. This release also extends the data types that are
>
> valid as parameters or return values of cross-server UDRs, which
>
> were formerly restricted to non-opaque built-in SQL data types, by
>
> supporting these additional data types: BOOLEAN LVARCHAR
>
> DISTINCT of built-in types that are not opaque DISTINCT of BOOLEAN
>
> DISTINCT of LVARCHAR DISTINCT of the DISTINCT types listed
>
> above. These data types can be returned by SPL, C, or Java language
>
> UDRs that use these data types as parameters or as return values, if
>
> the UDRs are defined in all the participating databases. Any
>
> implicit or explicit casts defined over these data types must be
>
> duplicated across all the participating Dynamic Server instances.
>
> The DISTINCT data types must have exactly the same data type
>
> hierarchy defined in all databases that participate in the
>
> distributed query.
>
>
>> I am running this query and I am getting this error which does not make
>>
> sense.
>
>> THis query has a column cnote_desc LVARCHAR (10000). And I am trying to run
>> this query from one instance to another instance on same server. We are on
>>
> IDS
>
>> 10.0 FC7 and HP-UX 11.11. If I run this query without cnote_desc column it
>> works fine. So something to do with LVARCHAR that does not go across
>> databases....I think.
>>
>> SELECT cnote_id, body_loc_cd, cnote_typ_cd, event_dt, event_tm ,>>
> critical_flg,
>
>> prnt_ok_flg, updt_usr_id, updt_dt,cnote_desc FROM cis@online15:cnote
>>
>> SQL Error (-999): Not implemented yet.
>>
>> Thanks,
>> Sunita
>>
>>
>
> Art S. Kagel
> Oninit
>
>
>>
>
>
>
================================================================================
===========
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
>
> http://www.oninit.com/home/disclaimer.php
>
>
>
================================================================================
===========
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ------------------------------------------------------------------------
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========