analytics/informix/etc/boot912.sql

{ ************************************************************************* }
{                                                                           }
{  Licensed Materials - Property of IBM and/or HCL                                     }
{                                                                           }
{  IBM Informix Dynamic Server                                              }
{  (c) Copyright IBM Corporation 1996, 2004 All rights reserved.            }
{  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.             }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{   Title:      boot912.sql                                                 }
{                                                                           }
{   Description:                                                            }
{       Bootstrapping script for a 9.12 database, run during database       }
{       construction.                                                       }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{  ** IMPORTANT - PLEASE READ                                               } 
{                                                                           }
{     All types and routines referenced in this file must be prefixed       }
{     with the user name "informix". E.g., use "informix.boolean"           }
{     and not "boolean"                                                     }
{									    }
{ ************************************************************************* }
{ Danish locale (da_dk.ISO-7-Danish) seems to be unique in that it contains }
{ no [,], { or } character at all. That is why we avoid using these         }
{ characters. User should not use subscript operators and change it to      }
{ substr function.                                                          }
{ ************************************************************************* }
{ Any changes made to this file in 9.20 or later releases will most likely  }
{ have an impact on database conversion/reversion. So avoid making changes  }
{ within this file and possibly add new SQL statements in newer boot        }
{ scripts to achieve your task.						    }
{ ************************************************************************* }

-- Add sysdomains view during database construction
create view informix.sysdomains (id, owner, name, type) as
       select extended_id, owner, name, type from informix.sysxtdtypes
              where domain = 'D';

grant select on informix.sysdomains to public as informix;

-- Create user predefined data types
create opaque type informix.lvarchar 
(
	internallength=variable,
	alignment = 1,
	maxlen = 32739
);

create opaque type informix.sendrecv 
(
	internallength=variable,
	alignment = 1,
	maxlen = 2048,
	cannothash
);

create opaque type informix.impexp 
(
	internallength=variable,
	alignment = 1,
	maxlen = 2048,
	cannothash
);

create opaque type informix.impexpbin
(
	internallength=variable,
	alignment = 1,
	maxlen = 2048,
	cannothash
);

create opaque type informix.boolean
(
	internallength=1,
	alignment = 1,
	passedbyvalue
);


create opaque type informix.pointer 
(
	internallength=8,
	alignment = 8,
	passedbyvalue
);

create opaque type informix.indexkeyarray
(
	internallength=variable,
	alignment = 4,
	maxlen = 1024,
	cannothash
);

-- Let us create the output function for this. We do not need an input function
create dba function informix.indexkeyarray_out(informix.indexkeyarray)
returning informix.lvarchar
external name '(indexkeyarray_out)' language C
not variant;

grant execute on function informix.indexkeyarray_out(informix.indexkeyarray) to public as informix ;

create dba function informix.indexkeyarray_send(informix.indexkeyarray)
returning informix.sendrecv
external name '(indexkeyarray_send)' language C
not variant;

grant execute on function informix.indexkeyarray_send(informix.indexkeyarray) to public as informix ;

create explicit cast (informix.indexkeyarray as informix.lvarchar
                with informix.indexkeyarray_out);

create explicit cast (informix.indexkeyarray as informix.sendrecv
                with informix.indexkeyarray_send);

-- Used to create the sysindexes view definition
create dba function informix.ikeyextractcolno(informix.indexkeyarray, integer)
returning smallint
external name '(ikeyextractcolno)' language C
not variant;

grant execute on function informix.ikeyextractcolno(informix.indexkeyarray, integer) to public as informix ;

-- Default opclass for btrees: btree_ops
create opclass informix.btree_ops for btree
strategies (lessthan, lessthanorequal, equal, greaterthanorequal, greaterthan)
support (compare);

create opaque type informix.rtnparamtypes
(
	internallength=variable,
	alignment = 1,
	maxlen = 4096,
	cannothash
);

-- Let us create the output function for this. We do not need an input function
create dba function informix.rtn_param_out(informix.rtnparamtypes)
returning informix.lvarchar specific paramtypes_out 
external name '(rtn_param_out)' language C
not variant;

grant execute on function informix.rtn_param_out(informix.rtnparamtypes) to public as informix ;

create cast (informix.rtnparamtypes as informix.lvarchar with informix.rtn_param_out);

{ ifx_param_ids, ifx_ret_ids, ifx_param_types, ifx_ret_types         }
{ functions to display the type identifiers or names of the          }
{ parameters and return types of routines. integer argument          }
{ is the procid, e.g. select ifx_ret_ids(procid) from sysprocedures; } 

create dba function informix.ifx_param_ids(integer)
returning informix.lvarchar
external name '(spr_param_ids)' language c;

grant execute on function informix.ifx_param_ids(integer) to public as informix ;

create dba function informix.ifx_ret_ids(integer)
returning informix.lvarchar
external name '(spr_ret_ids)' language c;

grant execute on function informix.ifx_ret_ids(integer) to public as informix ;

create dba function informix.ifx_param_types(integer)
returning informix.lvarchar
external name '(spr_param_types)' language c;

grant execute on function informix.ifx_param_types(integer) to public as informix ;

create dba function informix.ifx_ret_types(integer)
returning informix.lvarchar
external name '(spr_ret_types)' language c;

grant execute on function informix.ifx_ret_types(integer) to public as informix ; 

create opaque type informix.selfuncargs
(
	internallength=24,
	alignment = 1,
	cannothash
);

create opaque type informix.blob
(
	internallength=72,
	alignment = 4,
	cannothash
);

create opaque type informix.clob
(
internallength=72,
	alignment = 4,
	cannothash
);

create opaque type informix.lolist
(
	internallength=variable,
	alignment = 4,
	maxlen = 2048,
	cannothash
);

-- lolist function and cast definitions
create dba function informix.lolist_in(informix.lvarchar) 
	returns informix.lolist
        external name '(lolist_in)'
        language C not variant;

grant execute on function informix.lolist_in(informix.lvarchar) to public as informix ;
 
create dba function informix.lolist_out(informix.lolist) 
	returns informix.lvarchar
        external name '(lolist_out)'
        language C not variant;

grant execute on function informix.lolist_out(informix.lolist) to public as informix ;
 
create dba function informix.lolist_send(informix.lolist) 
	returns informix.sendrecv
        external name '(lolist_send)'
        language C not variant;

grant execute on function informix.lolist_send(informix.lolist) to public as informix ;

create dba function informix.lolist_recv(informix.sendrecv) 
	returns informix.lolist
        external name '(lolist_recv)'
        language C not variant;

grant execute on function informix.lolist_recv(informix.sendrecv) to public as informix ;

create implicit cast (informix.lvarchar as informix.lolist with 
	informix.lolist_in); 

create explicit cast (informix.lolist as informix.lvarchar with 
	informix.lolist_out); 

create implicit cast (informix.sendrecv as informix.lolist with 
	informix.lolist_recv); 

create explicit cast (informix.lolist as informix.sendrecv with 
	informix.lolist_send); 
 
-- cast function definitions
create dba function informix.collcast(set) returns set
        external name '(collcast)' language C not variant;
create dba function informix.collcast(multiset) returns multiset
        external name '(collcast)' language C not variant;
create dba function informix.collcast(list) returns list
        external name '(collcast)' language C not variant;
create dba function informix.rowcast(row) returns row
        external name '(rowcast)' language C not variant;

grant execute on function informix.collcast(set) to public as informix;
grant execute on function informix.collcast(multiset) to public as informix;
grant execute on function informix.collcast(list) to public as informix;
grant execute on function informix.rowcast(row) to public as informix;

-- Collection function definitions
create dba function informix.collectionoutput(set) returns informix.lvarchar
        external name '(collectionoutput)' language C not variant;
create dba function informix.collectionoutput(multiset) returns informix.lvarchar
        external name '(collectionoutput)' language C not variant;
create dba function informix.collectionoutput(list) returns informix.lvarchar
        external name '(collectionoutput)' language C not variant;

create dba function informix.collectioninput(informix.lvarchar) returns set
        external name '(collectioninput)' language C not variant;

grant execute on function informix.collectionoutput(set) to public as informix;
grant execute on function informix.collectionoutput(multiset) to public as informix;
grant execute on function informix.collectionoutput(list) to public as informix;
grant execute on function informix.collectioninput(informix.lvarchar) to public  as informix;

-- Send/recv Collection Functions
create dba function informix.collectionrecv(informix.sendrecv) returns set
        external name '(collectionrecv)' language C not variant;

grant execute on function informix.collectionrecv(informix.sendrecv) to public as informix ; 

-- Row Functions
create dba function informix.rowoutput(row) returns informix.lvarchar
        external name '(rowoutput)' language C not variant;

create dba function informix.rowinput(informix.lvarchar) returns row
        external name '(rowinput)' language C not variant;

create dba function informix.rowrecv(informix.sendrecv) returns row
        external name '(rowrecv)' language C not variant;

create dba function informix.rowsend(row) returns informix.sendrecv
        external name '(rowsend)' language C not variant;

create dba function informix.assign(row) returns row   
	external name '(rowassign)' language C not variant;

create dba function informix.destroy(row) returns row 
	external name '(rowdestroy)' language C not variant;

create dba function informix.assign(set) returns set 
	external name '(collectionassign)' language C not variant;
create dba function informix.assign(multiset) returns multiset
	external name '(collectionassign)' language C not variant;
create dba function informix.assign(list) returns list
	external name '(collectionassign)' language C not variant;

create dba function informix.destroy(set) returns set
	external name '(collectiondestroy)' language C not variant;
create dba function informix.destroy(multiset) returns multiset
	external name '(collectiondestroy)' language C not variant;
create dba function informix.destroy(list) returns list
	external name '(collectiondestroy)' language C not variant;

grant execute on function informix.rowoutput(row) to public as informix;
grant execute on function informix.rowinput(informix.lvarchar) to public as informix;
grant execute on function informix.rowrecv(informix.sendrecv) to public as informix;
grant execute on function informix.rowsend(row) to public as informix;
grant execute on function informix.assign(row) to public as informix;
grant execute on function informix.destroy(row) to public as informix;
grant execute on function informix.assign(set) to public as informix;
grant execute on function informix.assign(multiset) to public as informix;
grant execute on function informix.assign(list) to public as informix;
grant execute on function informix.destroy(set) to public as informix;
grant execute on function informix.destroy(multiset) to public as informix;
grant execute on function informix.destroy(list) to public as informix;


-- blob and clob support functions with informix.their associated casts

create dba function informix.blobinput(informix.lvarchar) returns informix.blob
	external name '(blob_input)'
	language c not variant;

grant execute on function informix.blobinput(informix.lvarchar) to public as informix;

create dba function informix.clobinput(informix.lvarchar) returns informix.clob
	external name '(clob_input)'
	language c not variant;

grant execute on function informix.clobinput(informix.lvarchar) to public as informix;

create implicit cast (informix.lvarchar as informix.blob with 
	informix.blobinput);

create implicit cast (informix.lvarchar as informix.clob with 
	informix.clobinput);

create dba function informix.bloboutput(informix.blob) returns informix.lvarchar
	external name '(blob_output)'
	language c not variant;

grant execute on function informix.bloboutput(informix.blob) to public as informix;

create dba function informix.cloboutput(informix.clob) returns informix.lvarchar
	external name '(clob_output)'
	language c not variant;

grant execute on function informix.cloboutput(informix.clob) to public as informix;

create explicit cast (informix.blob as informix.lvarchar with 
	informix.bloboutput);
create explicit cast (informix.clob as informix.lvarchar with 
	informix.cloboutput);

create dba function informix.blobrecv(informix.sendrecv) 
	returns informix.blob
	external name '(blob_recv)'
	language c not variant;

grant execute on function informix.blobrecv(informix.sendrecv) to public as informix;

create dba function informix.clobrecv(informix.sendrecv) 
	returns informix.clob
	external name '(clob_recv)'
	language c not variant;

grant execute on function informix.clobrecv(informix.sendrecv) to public as informix;

create implicit cast (informix.sendrecv as informix.blob with 
	informix.blobrecv);
create implicit cast (informix.sendrecv as informix.clob with 
	informix.clobrecv);

create dba function informix.blobsend(informix.blob) returns informix.sendrecv
	external name '(blob_send)'
	language c not variant;

grant execute on function informix.blobsend(informix.blob) to public as informix;

create dba function informix.clobsend(informix.clob) returns informix.sendrecv
	external name '(clob_send)'
	language c not variant;

grant execute on function informix.clobsend(informix.clob) to public as informix ;

create implicit cast (informix.blob as informix.sendrecv with 
	informix.blobsend);
create implicit cast (informix.clob as informix.sendrecv with 
	informix.clobsend);

create dba function informix.blobimport(informix.impexp) 
	returns informix.blob
	external name '(blob_import)'
	language c not variant;

grant execute on function informix.blobimport(informix.impexp) to public as informix ;

create dba function informix.clobimport(informix.impexp) 
	returns informix.clob
	external name '(clob_import)'
	language c not variant;

grant execute on function informix.clobimport(informix.impexp) to public as informix ;

create implicit cast (informix.impexp as informix.blob with 
	informix.blobimport);
create implicit cast (informix.impexp as informix.clob with 
	informix.clobimport);

create dba function informix.blobexport(informix.blob) 
	returns informix.impexp
	external name '(blob_export)'
	language c not variant;

grant execute on function informix.blobexport(informix.blob) to public as informix ;

create dba function informix.clobexport(informix.clob) 
	returns informix.impexp
	external name '(clob_export)'
	language c not variant;

grant execute on function informix.clobexport(informix.clob) to public as informix ;

create implicit cast (informix.blob as informix.impexp with 	
	informix.blobexport);
create implicit cast (informix.clob as informix.impexp with 
	informix.clobexport);

create dba function informix.assign(informix.blob) 
	returns informix.blob
	external name '(blob_assign)'
	language c not variant;

grant execute on function informix.assign(informix.blob) to public as informix ;

create dba function informix.assign(informix.clob) 
	returns informix.clob
	external name '(clob_assign)'
	language c not variant;

grant execute on function informix.assign(informix.clob) to public as informix ;

create dba function informix.destroy(informix.blob) 
	returns informix.blob
	external name '(blob_destroy)'
	language c not variant;

grant execute on function informix.destroy(informix.blob) to public as informix ;

create dba function informix.destroy(informix.clob) 
	returns informix.clob
	external name '(clob_destroy)'
	language c not variant;

grant execute on function informix.destroy(informix.clob) to public as informix ;

create dba function informix.lohandles(informix.blob) 
	returns informix.lolist
	external name '(blob_handles)'
	language c not variant;

grant execute on function informix.lohandles(informix.blob) to public as informix ;

create dba function informix.lohandles(informix.clob) 
	returns informix.lvarchar
	external name '(blob_handles)'
	language c not variant;

grant execute on function informix.lohandles(informix.clob) to public as informix ;

-- blob and clob user functions

create dba function informix.filetoblob(informix.lvarchar, char(6)) 
	returns informix.blob
	external name '(blob_from_file)'
	language c variant;

grant execute on function informix.filetoblob(informix.lvarchar, char(6)) to public as informix ;

create dba function informix.filetoclob(informix.lvarchar, char(6)) 
	returns informix.clob
	external name '(blob_from_file)' -- yes blob_from_file
	language c variant;

grant execute on function informix.filetoclob(informix.lvarchar, char(6)) to public as informix ;

create dba function informix.filetoblob(informix.lvarchar, char(6), char(18), char(18)) 
	returns informix.blob
	external name '(blob_from_file_colspec)'
	language c variant;

grant execute on function informix.filetoblob(informix.lvarchar, char(6), char(18), char(18)) to public as informix ;

create dba function informix.filetoclob(informix.lvarchar, char(6), char(18), char(18)) 
	returns informix.clob
	external name '(blob_from_file_colspec)'
	language c variant;

grant execute on function informix.filetoclob(informix.lvarchar, char(6), char(18), char(18)) to public as informix ;

create dba function informix.lotofile(informix.blob, informix.lvarchar, char(6)) returns informix.lvarchar
	external name '(blob_to_file)'
	language c variant;

grant execute on function informix.lotofile(informix.blob, informix.lvarchar, char(6)) to public as informix ;

create dba function informix.lotofile(clob, informix.lvarchar, char(6)) returns informix.lvarchar
	external name '(blob_to_file)'
	language c variant;

grant execute on function informix.lotofile(clob, informix.lvarchar, char(6)) to public as informix ;

create dba function informix.locopy(informix.blob) 	
	returns informix.blob
	external name '(blob_copy)'
	language c variant;

grant execute on function informix.locopy(informix.blob) to public as informix ;

create dba function informix.locopy(informix.blob, char(18), char(18)) 
	returns informix.blob
	external name '(blob_copy_colspec)'
	language c variant;

grant execute on function informix.locopy(informix.blob, char(18), char(18)) to public as informix ;

create dba function informix.locopy(informix.clob) 	
	returns informix.clob
	external name '(blob_copy)'
	language c variant;

grant execute on function informix.locopy(informix.clob) to public as informix ;

create dba function informix.locopy(informix.clob, char(18), char(18)) 
	returns informix.clob
	external name '(blob_copy_colspec)'
	language c variant;

grant execute on function informix.locopy(informix.clob, char(18), char(18)) to public as informix ;

{Types to support fastpathing of lo operations}
create opaque type informix.ifx_lo_spec
(
internallength = 596,
alignment = 8
);

create opaque type informix.ifx_lo_stat
(
internallength = 644,
alignment = 8
);

create dba function informix.send(informix.ifx_lo_spec) 
	returns informix.sendrecv
	external name '(send_ifx_lo_spec)' 
	language c not variant;

grant execute on function informix.send(informix.ifx_lo_spec) to public as informix ;

create dba function informix.send(informix.ifx_lo_stat) 
	returns informix.sendrecv
	external name '(send_ifx_lo_stat)' 
	language c not variant;

grant execute on function informix.send(informix.ifx_lo_stat) to public as informix ;

create dba function informix.recv_spec(informix.sendrecv) 
	returns informix.ifx_lo_spec
	external name '(recv_ifx_lo_spec)' 
	language c not variant;

grant execute on function informix.recv_spec(informix.sendrecv) to public as informix ;

create dba function informix.recv_stat(informix.sendrecv) 
	returns informix.ifx_lo_stat
	external name '(recv_ifx_lo_stat)' 
	language c not variant;

grant execute on function informix.recv_stat(informix.sendrecv) to public as informix ;

create cast (informix.ifx_lo_spec as informix.sendrecv with 
	informix.send);

create cast (informix.ifx_lo_stat as informix.sendrecv with 
	informix.send);

create implicit cast (informix.sendrecv as informix.ifx_lo_spec with 
	informix.recv_spec);
create implicit cast (informix.sendrecv as informix.ifx_lo_stat with 
	informix.recv_stat);

-- fastpath entry points to support client sblob I/O
create dba function informix.ifx_lo_create(ifx_lo_spec, integer, out informix.blob)
	returns integer
	external name '(sq_lo_create)' 
	language c           -- this is variant;

grant execute on function informix.ifx_lo_create(ifx_lo_spec, integer,informix.blob) to public as informix ;

create dba function informix.ifx_lo_open(informix.blob, integer)
	returns integer
	external name '(sq_lo_open)' 
	language c           -- this is variant;

grant execute on function informix.ifx_lo_open(informix.blob, integer) to public as informix ;

create dba function informix.ifx_lo_close(integer)
	returns integer
	external name '(sq_lo_close)' 
	language c;

grant execute on function informix.ifx_lo_close(integer) to public as informix ;

create dba function informix.ifx_lo_truncate(integer, int8)
	returns integer
	external name '(sq_lo_truncate)' 
	language c;
grant execute on function informix.ifx_lo_truncate(integer, int8) to public as informix ; 

create dba function informix.ifx_lo_alter(informix.blob, informix.ifx_lo_spec)
	returns integer
	external name '(sq_lo_alter)' 
	language c;

grant execute on function informix.ifx_lo_alter(informix.blob, informix.ifx_lo_spec) to public as informix ;

create dba function informix.ifx_lo_stat(integer, out informix.ifx_lo_stat)
	returns integer
	external name '(sq_lo_stat)' 
	language c;

grant execute on function informix.ifx_lo_stat(integer, informix.ifx_lo_stat) to public as informix ;

create dba function informix.ifx_lo_tell(integer, out int8)
	returns integer
	external name '(sq_lo_tell)' 
	language c;

grant execute on function informix.ifx_lo_tell(integer, int8) to public as informix ; 

create dba function informix.ifx_lo_seek(integer, int8, integer, out int8)
	returns integer
	external name '(sq_lo_seek)' 
	language c;

grant execute on function informix.ifx_lo_seek(integer, int8, integer, int8) to public as informix ; 

create dba function informix.ifx_lo_filename(informix.blob, informix.lvarchar)
	returns informix.lvarchar
	external name '(sq_lo_filename)' 
	language c;

grant execute on function informix.ifx_lo_filename(informix.blob, informix.lvarchar) to public as informix ;

create dba function informix.ifx_lo_col_sbinfo(informix.lvarchar, out informix.ifx_lo_spec)
	returns integer
	external name '(sq_lo_col_sblobinfo_by_colname)' 
	language c;

{args: lofd, from-filename, file-flags, file-offset, nbytes}

grant execute on function informix.ifx_lo_col_sbinfo(informix.lvarchar, informix.ifx_lo_spec) to public as informix ;

create dba function informix.ifx_lo_from_file(int, informix.lvarchar, int, int, int)
	returns integer
	external name '(sq_lo_from_file)' 
	language c;

grant execute on function informix.ifx_lo_from_file(int, informix.lvarchar, int, int,int)to public as informix ;

create dba function informix.ifx_lo_to_file(informix.blob, informix.lvarchar, int, out int)
	returns informix.lvarchar
	external name '(sq_lo_to_file)' 
	language c;

grant execute on function informix.ifx_lo_to_file(informix.blob, informix.lvarchar, int, int) to public as informix ;

create dba function informix.ifx_file_to_file(informix.lvarchar, int, informix.lvarchar, int)
	returns informix.lvarchar
	external name '(sq_file_to_file)' 
	language c;

grant execute on function informix.ifx_file_to_file(informix.lvarchar, int, informix.lvarchar, int) to public as informix ;

create dba function informix.dosyscast(informix.pointer, int, int)
	returning informix.pointer
	external name '(dosyscast)' 
	language C not variant;

grant execute on function informix.dosyscast(informix.pointer, int, int) to public as informix ;



{ executing this procedure will allow you to have a newline character in }
{ the string as in "insert into tab values ("line ends here^M new line"); }
create dba procedure informix.ifx_allow_newline (informix.boolean);
external name '' language C
end procedure;

grant execute on procedure informix.ifx_allow_newline (informix.boolean) to public as informix ;

create dba function informix.mi_get_typedesc(int,int)
        returns informix.lvarchar
        external name '(mife_type_typedesc)'
        language C;

grant execute on function informix.mi_get_typedesc(int,int) to public as informix ;

create dba function informix.mi_get_typedesc(informix.lvarchar)
        returns informix.lvarchar
        external name '(mife_describe_typestring)'
        language C;

grant execute on function informix.mi_get_typedesc(informix.lvarchar) to public as informix ;

create dba function informix.reload_module(varchar(255), varchar(255)) 
        returns int
        external name '' language C;

grant execute on function informix.reload_module(varchar(255), varchar(255)) to public as informix ;
 
create dba procedure informix.ifx_load_module (modulename varchar(255), 
        languagename varchar(255)) returns int
     
     define retvalue int;
     let retvalue = reload_module(modulename, languagename);
     if retvalue = 0 then
         return 0;
     else
         raise exception -9720;
     end if;
end procedure;

grant execute on procedure informix.ifx_load_module (varchar(255), varchar(255)) to public as informix ;

create dba function informix.ifx_invalid_module(varchar(255), varchar(255))
        returns int 
        external name '' language C;

grant execute on function informix.ifx_invalid_module(varchar(255), varchar(255)) to public as informix ;

create dba function informix.ifx_new_external(varchar(255), varchar(255),
        varchar(255))
        returns informix.lvarchar
	external name '' language C;

grant execute on function informix.ifx_new_external(varchar(255), varchar(255),varchar(255)) to public as informix ;

create dba function informix.ifx_match_external(varchar(255), varchar(255))
        returns int
	external name '' language C;

grant execute on function informix.ifx_match_external(varchar(255), varchar(255)) to public as informix ;

create dba function informix.ifx_load_internal(varchar(255), varchar(255))
        returns int
	external name '' language C;

grant execute on function informix.ifx_load_internal(varchar(255), varchar(255)) to public as informix ;

-- Add sysindexes view during database construction,
-- after we have added the type indexkeyarray
create view informix.sysindexes
    (idxname, owner, tabid, idxtype, clustered,
    part1, part2, part3, part4, part5, part6, part7, part8,
    part9, part10, part11, part12, part13, part14, part15, part16,
    levels, leaves, nunique, clust)
  as select
    idxname, owner, tabid, idxtype, 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),
    levels, leaves, nunique, clust
  from informix.sysindices;

grant select on informix.sysindexes to public as informix;

-- Define rtree access method:
create dba function informix.rlt_create(pointer)
    returns int
    external name '(rlt_create)'
    language C;

 grant execute on function informix.rlt_create(pointer) to public as informix ;

create dba function informix.rlt_drop(pointer)
    returns int
    external name '(rlt_drop)' 
    language C;

grant execute on function informix.rlt_drop(pointer) to public as informix ;

create dba function informix.rlt_open(pointer)
    returns int
    external name '(rlt_open)' 
    language C;

grant execute on function informix.rlt_open(pointer) to public as informix ;

create dba function informix.rlt_close(pointer)
    returns int
    external name '(rlt_close)' 
    language C;

grant execute on function informix.rlt_close(pointer) to public as informix ;

create dba function informix.rlt_insert(pointer, pointer, pointer)
    returns int
    external name '(rlt_insert)' 
    language C;

grant execute on function informix.rlt_insert(pointer, pointer, pointer) to public as informix ;

create dba function informix.rlt_delete(pointer, pointer, pointer)
    returns int
    external name '(rlt_delete)' 
    language C;

grant execute on function informix.rlt_delete(pointer, pointer, pointer) to public as informix ;

create dba function informix.rlt_update(pointer, pointer, pointer, 
				    pointer, pointer)
    returns int
    external name '(rlt_update)' 
    language C;

grant execute on function informix.rlt_update(pointer, pointer, pointer,pointer, pointer) to public as informix ;

create dba function informix.rlt_beginscan(pointer)
    returns int
    external name '(rlt_beginscan)' 
    language C;

grant execute on function informix.rlt_beginscan(pointer) to public as informix ;

create dba function informix.rlt_endscan(pointer)
    returns int
    external name '(rlt_endscan)' 
    language C;

grant execute on function informix.rlt_endscan(pointer) to public as informix;

create dba function informix.rlt_rescan(pointer)
    returns int
    external name '(rlt_rescan)' 
    language C;

grant execute on function informix.rlt_rescan(pointer) to public as informix ;

create dba function informix.rlt_getnext(pointer, pointer, pointer)
    returns int
    external name '(rlt_getnext)' 
    language C;

grant execute on function informix.rlt_getnext(pointer, pointer, pointer) to public as informix ;

-- Define optimizer related functions for rtree access method
create dba function informix.rlt_stat(pointer, pointer)
    returns int
    external name '(rlt_stat)'
    language C;

grant execute on function informix.rlt_stat(pointer, pointer) to public as informix ;

create dba function informix.rlt_cost(pointer, pointer)
    returns real 
    external name '(rlt_cost)'
    language C;

grant execute on function informix.rlt_cost(pointer, pointer) to public as informix ;

create secondary access_method informix.rtree (
    am_create = informix.rlt_create,
    am_drop = informix.rlt_drop,
    am_open = informix.rlt_open,
    am_close = informix.rlt_close,
    am_insert = informix.rlt_insert,
    am_delete = informix.rlt_delete,
    am_update = informix.rlt_update,
    am_beginscan = informix.rlt_beginscan,
    am_endscan = informix.rlt_endscan,
    am_rescan = informix.rlt_rescan,
    am_getnext = informix.rlt_getnext,
    am_stats = informix.rlt_stat,
    am_scancost = informix.rlt_cost,
    am_sptype = 'D');

-- Default opclass for rtrees: rtree_ops
create opclass informix.rtree_ops for informix.rtree
    strategies (overlap, equal, contains, within)
    support (union, size, inter);
alter access_method informix.rtree add am_defopclass = informix.rtree_ops;

{ these functions are created to extract type information for triggers }
create dba function informix.trigger_cols(int)
	returns informix.lvarchar
	external name ''
	language C;

grant execute on function informix.trigger_cols(int)to public as informix ;

create dba procedure informix.ifx_trigger_cols(trigid int)
	returns informix.lvarchar
	return (informix.trigger_cols(trigid));
end procedure;

grant execute on procedure informix.ifx_trigger_cols(int) to public as informix ;

create dba function informix.trigger_action (int, int)
	returns informix.lvarchar
	external name ''
	language C;

grant execute on function informix.trigger_action (int, int) to public as informix ;

create dba procedure informix.ifx_trigger_action(trigid int, when CHAR(10))
	returns informix.lvarchar; 
	define cleanwhen	char(10);
	define intwhen		int;

	let cleanwhen = trim (BOTH FROM when);
	if (cleanwhen = 'BEFORE' or cleanwhen = 'before') then
	    let intwhen = 1;
	elif (cleanwhen = 'ROW' or cleanwhen = 'row') then
	    let intwhen = 2;
	elif (cleanwhen = 'AFTER' or cleanwhen = 'after') then
	    let intwhen = 3;
	else
	    return NULL;
	end if;
	
	return (informix.trigger_action(trigid, intwhen));
end procedure;

grant execute on procedure informix.ifx_trigger_action(int,CHAR(10)) to public as informix ;
	    
create dba function informix.ifx_lo_release(informix.blob)
	returns integer
	external name '(sq_lo_release)'
	language c           -- this is variant;

grant execute on function informix.ifx_lo_release(informix.blob) to public as informix ;

-- Define recovery related functions for the rtree access method:
create dba function informix.rlt_redo(pointer, pointer, int, int)
    returns int
    external name '(rlt_redo)'
    language C;

grant execute on function informix.rlt_redo(pointer, pointer, int, int) to public as informix ;

create dba function informix.rlt_undo(pointer, pointer, int, int)
    returns int
    external name '(rlt_undo)'
    language C;

grant execute on function informix.rlt_undo(pointer, pointer, int, int) to public as informix ;

create dba function informix.rlt_commit(int)
    returns int
    external name '(rlt_commit)'
    language C;

grant execute on function informix.rlt_commit(int) to public as informix ;

create dba function informix.rlt_abort(int)
    returns int
    external name '(rlt_abort)'
    language C;

grant execute on function informix.rlt_abort(int) to public as informix ;