Can't access sysindexes remotely

Posted in 2010

Summary

A user on IDS 11.50.FC6 (Solaris) got "Execution of remote routine ... informix.ikeyextractcolno with non-built-in types is not allowed" when selecting from sysindexes across a database link. Replies explained sysindexes is a view over sysindices whose key column is an opaque UDT (indexkeyarray), and UDT-based routines can't be used in cross-server queries. Workaround given by John Miller: create an SPL function on the remote server that calls ikeyextractcolno and returns plain tabname/idxname/part1-16 columns, then query it remotely via a derived table (SELECT * FROM TABLE(db@server:view_sysindices(...))), optionally passing a table name to limit network traffic.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

SHAHZAD SALAM KASI — Tue, 10 Aug 2010 15:14:17 — source: IIUG Forums & Mailing Lists

Hi, Iam unable to access sysindexes (of another instance/database) remotely. Why is it so ? I can access other details from sysmaster (from other instances) remotely. Iam using the following query: SELECT * From test@ids_net_test02:sysindexes and am getting the following error: Execution of remote routine (test@ids_net_test02:informix.ikeyextractcolno) with non-built-in types is not allowed. Thanks. Shahzad Salam Kasi

↪ replying to SHAHZAD SALAM KASI

Obnoxio The Clown — Tue, 10 Aug 2010 15:16:04 — source: IIUG Forums & Mailing Lists

SHAHZAD SALAM KASI wrote: > Hi, > > Iam unable to access sysindexes (of another instance/database) remotely. > Why is it so ? Version? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

SHAHZAD SALAM KASI — Tue, 10 Aug 2010 15:20:49 — source: IIUG Forums & Mailing Lists

Hi, We are using IBM Informix Dynamic Server Version 11.50.FC6 using Platform: SunOS 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200 Thanks

↪ replying to SHAHZAD SALAM KASI

Paul Watson — Tue, 10 Aug 2010 15:22:55 — source: IIUG Forums & Mailing Lists

Cos sysindexes is a view onto sysindices and has a 'non-standard' column type Paul Watson Oninit www.oninit.com Advanced DataTools www.advancedatatools.com Tel: +1 913 674 0360 Cell: +1 913 387 7529 Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. ie indexkeyarray -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SHAHZAD SALAM KASI Sent: Tuesday, August 10, 2010 10:14 AM To: ids@iiug.org Subject: Can't access sysindexes remotely [20800] Hi, Iam unable to access sysindexes (of another instance/database) remotely. Why is it so ? I can access other details from sysmaster (from other instances) remotely. Iam using the following query: SELECT * From test@ids_net_test02:sysindexes and am getting the following error: Execution of remote routine (test@ids_net_test02:informix.ikeyextractcolno) with non-built-in types is not allowed. Thanks. Shahzad Salam Kasi **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 100810-0, 08/10/2010 Tested on: 8/10/2010 10:22:51 AM avast! - copyright (c) 1988-2010 ALWIL Software.

↪ replying to SHAHZAD SALAM KASI

Obnoxio The Clown — Tue, 10 Aug 2010 15:29:54 — source: IIUG Forums & Mailing Lists

SHAHZAD SALAM KASI wrote: > Hi, > We are using > IBM Informix Dynamic Server Version 11.50.FC6 > using Platform: > SunOS 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200 Both instances? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

SHAHZAD SALAM KASI — Tue, 10 Aug 2010 15:32:46 — source: IIUG Forums & Mailing Lists

Yes.

↪ replying to Paul Watson

SHAHZAD SALAM KASI — Tue, 10 Aug 2010 15:44:36 — source: IIUG Forums & Mailing Lists

Is there any way to access the sysindexes detail remotely ?

↪ replying to SHAHZAD SALAM KASI

Art Kagel — Tue, 10 Aug 2010 16:41:21 — source: IIUG Forums & Mailing Lists

Sysindexes is a VIEW into the table sysindices and it uses a UDR to translate the 9.3x+ sysindices key field, which is encoded, into the older sysindexes format used by 7.xx, 9.21, and all earlier releases of Informix. Anything involving User Defined Types (UDTs) will not work remotely and the key column in sysindices is implemented as a variable length OPAQUE UDT named indexkeyarray. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 10, 2010 at 11:14 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Hi, > > Iam unable to access sysindexes (of another instance/database) remotely. > Why is it so ? > I can access other details from sysmaster (from other instances) remotely. > > Iam using the following query: > SELECT * From test@ids_net_test02:sysindexes> > and am getting the following error: > Execution of remote routine (test@ids_net_test02 > :informix.ikeyextractcolno) > with non-built-in types is not allowed. > > Thanks. > Shahzad Salam Kasi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364590585eb3fc048d7acd72

↪ replying to SHAHZAD SALAM KASI

Art Kagel — Tue, 10 Aug 2010 16:48:38 — source: IIUG Forums & Mailing Lists

Remotely as in from another client besides the server, yes, remotely as through another instance? Not currently. The restrictions on remote UDTs is being lifted type by type, but I can't say whether there is any plan to lift it for this particular type since it's not a type that's open to users normally. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 10, 2010 at 11:44 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Is there any way to access the sysindexes detail remotely ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636b1462e75f57e048d7ae7b4

↪ replying to SHAHZAD SALAM KASI

John Miller iii — Tue, 10 Aug 2010 18:50:00 — source: IIUG Forums & Mailing Lists

Here is how you access sysindex information remotely. It is 2 simple steps: 1. Create a procedure to select the data. This SPL will transform the UDT to native types 2. Select the data using a derived table. Both of the select return the same data, but the first will run faster because only the indexes for the desire table are transferred across the network. SELECT * FROM table (sysadmin@talos_cisco_lmm:view_sysindices("systables")) as indexinfo( tabname, idxname, owner, tabid, idxtype, clustered, part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11, part12, part13, part14, part15, part16 ) ; select * from table (sysadmin@talos_cisco_lmm:view_sysindices(NULL)) as indexinfo( tabname, idxname, owner, tabid, idxtype, clustered, part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11, part12, part13, part14, part15, part16) WHERE tabname = "systables" ; Create the following procedure on the remote server. create function view_sysindices(i_tabname VARCHAR(128) DEFAULT NULL ) returning VARCHAR(128), VARCHAR(128),CHAR(32),INTEGER, CHAR(1),CHAR(1), INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER; DEFINE r_tabname VARCHAR(128); DEFINE r_idxname VARCHAR(128); DEFINE r_owner CHAR(32); DEFINE r_tabid INTEGER; DEFINE r_idxtype CHAR(1); DEFINE r_clustered CHAR(1); DEFINE r_part1 INTEGER; DEFINE r_part2 INTEGER; DEFINE r_part3 INTEGER; DEFINE r_part4 INTEGER; DEFINE r_part5 INTEGER; DEFINE r_part6 INTEGER; DEFINE r_part7 INTEGER; DEFINE r_part8 INTEGER; DEFINE r_part9 INTEGER; DEFINE r_part10 INTEGER; DEFINE r_part11 INTEGER; DEFINE r_part12 INTEGER; DEFINE r_part13 INTEGER; DEFINE r_part14 INTEGER; DEFINE r_part15 INTEGER; DEFINE r_part16 INTEGER; SET DEBUG FILE TO "/tmp/work.out"; TRACE ON; IF i_tabname IS NULL THEN FOREACH select T.tabname, I.idxname, I.owner, I.tabid, I.idxtype, I.clustered, informix.ikeyextractcolno(indexkeys,0), informix.ikeyextractcolno(indexkeys,1), informix.ikeyextractcolno(indexkeys,2), informix.ikeyextractcolno(indexkeys,3), informix.ikeyextractcolno(indexkeys,4), informix.ikeyextractcolno(indexkeys,5), informix.ikeyextractcolno(indexkeys,6), informix.ikeyextractcolno(indexkeys,7), informix.ikeyextractcolno(indexkeys,8), informix.ikeyextractcolno(indexkeys,9), informix.ikeyextractcolno(indexkeys,10), informix.ikeyextractcolno(indexkeys,11), informix.ikeyextractcolno(indexkeys,12), informix.ikeyextractcolno(indexkeys,13), informix.ikeyextractcolno(indexkeys,14), informix.ikeyextractcolno(indexkeys,15) INTO r_tabname, r_idxname, r_owner, r_tabid, r_idxtype, r_clustered, r_part1, r_part2, r_part3, r_part4, r_part5, r_part6, r_part7, r_part8, r_part9, r_part10, r_part11, r_part12, r_part13, r_part14, r_part15, r_part16 from sysindices I, systables T WHERE I.tabid = T.tabid RETURN r_tabname, r_idxname, r_owner, r_tabid, r_idxtype, r_clustered, r_part1, r_part2, r_part3, r_part4, r_part5, r_part6, r_part7, r_part8, r_part9, r_part10, r_part11, r_part12, r_part13, r_part14, r_part15, r_part16 WITH RESUME; END FOREACH ELSE FOREACH select T.tabname, I.idxname, I.owner, I.tabid, I.idxtype, I.clustered, informix.ikeyextractcolno(indexkeys,0), informix.ikeyextractcolno(indexkeys,1), informix.ikeyextractcolno(indexkeys,2), informix.ikeyextractcolno(indexkeys,3), informix.ikeyextractcolno(indexkeys,4), informix.ikeyextractcolno(indexkeys,5), informix.ikeyextractcolno(indexkeys,6), informix.ikeyextractcolno(indexkeys,7), informix.ikeyextractcolno(indexkeys,8), informix.ikeyextractcolno(indexkeys,9), informix.ikeyextractcolno(indexkeys,10), informix.ikeyextractcolno(indexkeys,11), informix.ikeyextractcolno(indexkeys,12), informix.ikeyextractcolno(indexkeys,13), informix.ikeyextractcolno(indexkeys,14), informix.ikeyextractcolno(indexkeys,15) INTO r_tabname, r_idxname, r_owner, r_tabid, r_idxtype, r_clustered, r_part1, r_part2, r_part3, r_part4, r_part5, r_part6, r_part7, r_part8, r_part9, r_part10, r_part11, r_part12, r_part13, r_part14, r_part15, r_part16 from sysindices I, systables T WHERE T.tabid = I.tabid AND T.tabname = i_tabname RETURN r_tabname, r_idxname, r_owner, r_tabid, r_idxtype, r_clustered, r_part1, r_part2, r_part3, r_part4, r_part5, r_part6, r_part7, r_part8, r_part9, r_part10, r_part11, r_part12, r_part13, r_part14, r_part15, r_part16 WITH RESUME; END FOREACH END IF END FUNCTION; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/10/2010 08:44:36 AM: > [image removed] > > Re: RE: Can't access sysindexes remotely [20807] > > SHAHZAD SALAM KASI > > to: > > ids > > 08/10/2010 08:46 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Is there any way to access the sysindexes detail remotely ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >